Announcement

Collapse

NextGen (Mirth) Connect 3.10.0 Released!

NextGen (Mirth) Connect 3.10.0 is now available as an appliance update and on our GitHub page. This release includes better SQL Server database support, security improvements through fixes and library updates, and improvements for the Advanced Clustering plugin with a focus on improving performance of many of the tasks that are carried out on a frequent interval. See the release notes for the list of fixes and updates.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

JDBC SQL error

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC SQL error

    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

  • #2
    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
    Chris Lang

    Comment


    • #3
      Re:JDBC SQL error

      Yes !

      That was it, Chris ..

      Thanks a lot,

      tomjoaif ./

      Comment


      • #4
        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)

        Comment

        Working...
        X