JDBC SQL error

[tomjosif]

Hi All,

My outbound channel is DBReader => File Writer. (MySQL 5.0).

I was using mirth 1.3.2 and now trying to upgrade to 1.5.0 and the DBReader-Source
is non-JavaScript.

The SQL query looks like :

SELECT `field_1`, `field_2`, ...
`no_log_id`, ` field_n`
FROM
`table_1`, `table_2`
WHERE
condition_1
condition_2;

Once I fill in the above into the SQL panel, I can see the variable-panel is getting
populated with the variables declared in the SELECT query.

I also have an On-Update SQL that looks like:

UPDATE `table_1`, `table_2`
SET
`field_1` = X, `field_2` = Y
WHERE
condition_1
AND `field_x` = ${no_log_id};

Please note that "no_log_id" is from the first SQL(select) statement, and is available
as a variable in the variable-panel. But while starting the channel mirth complains
that it "Can not retrieve argument no_log_id". This query is working fine with
mirth 1.3.x. Please see the error message below.

Any idea why this is happening ? May be 1.5.0 SQL(non-JavaScirpt)panel behaves differently ?

Thanks for your help

tomjosif ./

ERROR 2007-05-28 13:42:13,062 [b9c377d7-838e-4f50-a10d-a1b0b3e7baae_source_connector._jdbcEndpoint#-284287630.receiver.2] com.webreach.mirth.server.mule.providers.jdbc.Jdbc MessageReceiver: Error in the ACK sentence of the JDBC connection, but the message is being sent anywayjava.lang.IllegalArgumentException: Can not retrieve argument no_log_id
INFO | jvm 1 | 2007/05/28 13:42:13 | ERROR 2007-05-28 13:42:13,109 [b9c377d7-838e-4f50-a10d-a1b0b3e7baae_source_connector._jdbcEndpoint#-284287630.receiver.2] org.mule.impl.DefaultComponentExceptionStrategy: Caught exception in Exception Strategy for: b9c377d7-838e-4f50-a10d-a1b0b3e7baae: org.mule.umo.routing.RoutingException: Failed to transform message before applying the filter. Failed to route event via endpoint: ImmutableMuleEndpoint{connector=com.webreach.mirth [email protected], endpointUri=jdbc://query, transformer=Transformer{name='ResultMapToXML', returnClass=false, returnClass=false, sourceTypes=[class java.util.HashMap]}, name='_jdbcEndpoint#-284287630', type='receiver', properties={}, transactionConfig=org.mule.impl.MuleTransactionCon [email protected], filter=null, deleteUnacceptedMessages=false, initialised=true, securityFilter=null, synchronous=true, initialState=started, createConnector=0}. Message payload is of type: org.apache.commons.dbutils.BasicRowProcessor$CaseI nsensitiveHashMap

[chrisl]

Re:JDBC SQL error

Since you are selecting from multiple tables, Mirth will append the table name in front of the variables (if there are ambiguous column names). This is not reflected in the UI (and is scheduled to be addressed in a future release).

Look at the message viewer and you should see XML that contains the contents of your SELECT statement. Use the tag names here in your Update statement.

(You might have something like table_1.no_log_id or table_2.no_log_id in your XML. This is what you need to place in the Update statement: ${table_1.no_log_id})

Hope this helps!

Post edited by: chrisl, at: 05/29/2007 02:38

[tomjosif]

Re:JDBC SQL error

Yes !

That was it, Chris ..

Thanks a lot,

tomjoaif ./

[elankreijer]

Re:JDBC SQL error

The same On-Update error will occur in the current 1.6.1 version and Oracle

SELECT
cs.CLIENT_STATUS_ID,
cs.UITNODIGING_ID,
<snip>
FROM CLIENT_STATUS cs
JOIN CLIENT c
ON c.CLIENT_ID = cs.CLIENT_ID
<snip>

variables in the On-Update SQL statement will be referenced as ${cs.uitnodiging_id} my solution was to use the SQL AS statement like this

SELECT
cs.CLIENT_STATUS_ID AS CLIENT_STATUS_ID,
cs.UITNODIGING_ID AS UITNODIGING_ID,
<snip>

now the variables can be accessed with ${uitnodiging_id} wich will be accepted by the message processor.

Hope this workaround can help some fellow Mirth users B)