web stats
oracle / ms sql merge statement in database writer - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 04-11-2013, 12:45 AM
Jurjan Jurjan is offline
OBX.1 Kenobi
 
Join Date: Feb 2011
Posts: 37
Jurjan is on a distinguished road
Question 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
Reply With Quote
  #2  
Old 04-11-2013, 06:08 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,213
cory_cole is on a distinguished road
Default

You will need to do this as JavaScript. The DB Writer will only handle INSERT, UPDATE, or DELETE SQL statements.
Reply With Quote
  #3  
Old 04-11-2013, 06:12 AM
Jurjan Jurjan is offline
OBX.1 Kenobi
 
Join Date: Feb 2011
Posts: 37
Jurjan is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 04-11-2013, 07:18 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,099
narupley is on a distinguished road
Default

Quote:
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.
Reply With Quote
  #5  
Old 04-11-2013, 11:01 PM
Jurjan Jurjan is offline
OBX.1 Kenobi
 
Join Date: Feb 2011
Posts: 37
Jurjan is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 04-12-2013, 07:08 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,099
narupley is on a distinguished road
Default

Quote:
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.
Reply With Quote
Reply

Tags
database writer, merge, ms sql, oracle

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 08:08 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation