Announcement

Collapse
No announcement yet.

oracle / ms sql merge statement in database writer

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

  • oracle / ms sql merge statement in database writer

    Hey Mirthers,
    I've been using a Database Writer for some table updates in Oracle (or even MS SQL).

    I want to use the 'new' (since Oracle 9?) Merge statement.
    However, when I run the channel I get the following error:
    ERROR-406: JDBC Connector error
    ERROR MESSAGE: Error writing to database:
    java.lang.IllegalArgumentException: Write statement should be an INSERT, UPDATE, or DELETE SQL statement.
    at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doDispatch(JdbcMessageDispatcher.java:124)
    at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doSend(JdbcMessageDispatcher.java:162)
    at org.mule.providers.AbstractMessageDispatcher.send( AbstractMessageDispatcher.java:164)
    at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:191)
    at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:130)
    at org.mule.routing.outbound.AbstractOutboundRouter.s end(AbstractOutboundRouter.java:85)
    at org.mule.routing.outbound.FilteringMulticastingRou ter.route(FilteringMulticastingRouter.java:54)
    at org.mule.routing.outbound.OutboundMessageRouter$1. doInTransaction(OutboundMessageRouter.java:78)
    at org.mule.transaction.TransactionTemplate.execute(T ransactionTemplate.java:48)
    at org.mule.routing.outbound.OutboundMessageRouter.ro ute(OutboundMessageRouter.java:82)
    at org.mule.impl.model.DefaultMuleProxy.onCall(Defaul tMuleProxy.java:247)
    at org.mule.impl.model.seda.SedaComponent.doSend(Seda Component.java:209)
    at org.mule.impl.model.AbstractComponent.sendEvent(Ab stractComponent.java:277)
    at org.mule.impl.MuleSession.sendEvent(MuleSession.ja va:201)
    at org.mule.routing.inbound.InboundMessageRouter.send (InboundMessageRouter.java:176)
    at org.mule.routing.inbound.InboundMessageRouter.rout e(InboundMessageRouter.java:143)
    at org.mule.providers.AbstractMessageReceiver$Default InternalMessageListener.onMessage(AbstractMessageR eceiver.java:487)
    at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:266)
    at org.mule.providers.AbstractMessageReceiver.routeMe ssage(AbstractMessageReceiver.java:229)
    at com.mirth.connect.connectors.jdbc.JdbcMessageRecei ver.processMessage(JdbcMessageReceiver.java:180)
    at org.mule.providers.TransactedPollingMessageReceive r$1.doInTransaction(TransactedPollingMessageReceiv er.java:98)
    at org.mule.transaction.TransactionTemplate.execute(T ransactionTemplate.java:72)
    at org.mule.providers.TransactedPollingMessageReceive r.poll(TransactedPollingMessageReceiver.java:104)
    at org.mule.providers.PollingMessageReceiver.run(Poll ingMessageReceiver.java:76)
    at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
    at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor.runWorker(ThreadPoolExecutor.java: 1061)
    at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :575)
    at java.lang.Thread.run(Unknown Source)

    It looks like the database writer doesn't acknowledge the MERGE statement?

    My Mirth Connect version info:
    Mirth Connect Server 2.2.1.5861
    Built on February 28, 2012
    Java version: 1.7.0_17
    My (Oracle) query statement:
    merge into bs_dag_vrd_reservering
    using dual on ( bvr_artikelnr = ${bvr_artikelnr} and bvr_internetordernr = ${bvr_internetordernr})
    when matched then
    update
    set bvr_nr_org_classif = ${bvr_nr_org_classif}
    , bvr_org_clas_waarde = ${bvr_org_clas_waarde}
    , bvr_aantal = ${bvr_aantal}
    when not matched then
    insert
    ( bvr_artikelnr
    , bvr_nr_org_classif
    , bvr_org_clas_waarde
    , bvr_aantal
    )
    values
    ( ${bvr_artikelnr}
    , ${bvr_nr_org_classif}
    , ${bvr_org_clas_waarde}
    , ${bvr_aantal}
    )
    (query shortened a bit for readability).

    Is this known behaviour (although I couldn't find anything about it in the docs)?
    Any workaround?

    Thanks,
    Jurjan

  • #2
    You will need to do this as JavaScript. The DB Writer will only handle INSERT, UPDATE, or DELETE SQL statements.

    Comment


    • #3
      Cory,
      thanks, I feared as much.

      I had hopes that I could use both Database Reader and Database Writer for a lot of our needs, since more people in our organization know SQL than Javascript.
      However, (for us rather random) issues like these make that impractical.

      Comment


      • #4
        Originally posted by Jurjan View Post
        Cory,
        thanks, I feared as much.

        I had hopes that I could use both Database Reader and Database Writer for a lot of our needs, since more people in our organization know SQL than Javascript.
        However, (for us rather random) issues like these make that impractical.
        That has been fixed in 3.0; the database connectors will now allow any SQL statements (though the receiver still calls executeQuery while the dispatcher calls executeUpdate).
        Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

        Nicholas Rupley
        Work: 949-237-6069
        Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


        - How do I foo?
        - You just bar.

        Comment


        • #5
          narupley,
          that's good to hear (for the future anyway).

          Thanks for the clarification
          Any idea that you can publicly give about release timescales?

          Thanks,
          Jurjan

          Comment


          • #6
            Originally posted by Jurjan View Post
            narupley,
            that's good to hear (for the future anyway).

            Thanks for the clarification
            Any idea that you can publicly give about release timescales?

            Thanks,
            Jurjan
            Two betas have already been released (go test it out!), an RC (release candidate) is on the way, and the GA release is scheduled for sometime this summer.
            Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

            Nicholas Rupley
            Work: 949-237-6069
            Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


            - How do I foo?
            - You just bar.

            Comment

            Working...
            X