Announcement

Collapse
No announcement yet.

Insert into a MS SQL DB table.

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

  • Insert into a MS SQL DB table.

    Hi,
    All I am trying to do is to insert a value( date time value ) into an SQL DB.

    The only issue is that I am unable to insert a value into the column. The value that I am trying to insert is 2010-11-09 10:34:39.936.

    this is the code:

    var Transactiondata = channelMap.get('TransactionData');
    var TransactionPostedDate = channelMap.get('TransactionPostedDate');
    var TransactionQueuedDate = channelMap.get('TransactionQueuedDate');
    var TransactionTransmittedDate = channelMap.get('TransactionTransmittedDate');
    var TransactionDetails = channelMap.get('TransactionDetails');

    var params = new Packages.java.util.ArrayList();
    var sql = "INSERT IGNORE INTO TRANSACTIONS ( "+
    " Transaction_Sequence_Number, Transaction_Name, Transaction_TimePosted,"+
    " Transaction_TimeQueued, Transaction_TimeTransmitted, Transaction_State,"+
    " Transaction_Data, CREATED_BY, CREATED_DT) "+
    " VALUES (? ,? ,? ,? ,? ,? ,? ,? ,? )";
    logger.error(TransactionPostedDate);
    params.add($('Transaction_Key'));
    params.add('Test');
    params.add(TransactionPostedDate);// 2010-11-09 10:34:39.936 field value
    params.add(TransactionQueuedDate);
    params.add(TransactionTransmittedDate);
    params.add($('Transaction_State'));
    params.add(TransactionDetails);
    params.add('MIRTH');
    params.add(DateUtil.getCurrentDate('yyyy-MM-dd HH:mm:ss'));

    Please help !!

    The error that I am getting is this:
    ERROR-406: JDBC Connector error
    ERROR MESSAGE: Error writing to database:
    org.mozilla.javascript.WrappedException: Wrapped com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported. (50c36832-172d-46b8-a63f-f865d494c6bb#61)
    at org.mozilla.javascript.Context.throwAsScriptRuntim eEx(Context.java:1781)
    at org.mozilla.javascript.MemberBox.invoke(MemberBox. java:183)
    at org.mozilla.javascript.NativeJavaMethod.call(Nativ eJavaMethod.java:247)
    at org.mozilla.javascript.optimizer.OptRuntime.call2( OptRuntime.java:76)
    at org.mozilla.javascript.gen._50c36832_172d_46b8_a63 f_f865d494c6bb_68._c_doDatabaseScript_7(50c36832-172d-46b8-a63f-f865d494c6bb:61)
    at org.mozilla.javascript.gen._50c36832_172d_46b8_a63 f_f865d494c6bb_68.call(50c36832-172d-46b8-a63f-f865d494c6bb)
    at org.mozilla.javascript.optimizer.OptRuntime.callNa me0(OptRuntime.java:108)
    at org.mozilla.javascript.gen._50c36832_172d_46b8_a63 f_f865d494c6bb_68._c_script_0(50c36832-172d-46b8-a63f-f865d494c6bb:66)
    at org.mozilla.javascript.gen._50c36832_172d_46b8_a63 f_f865d494c6bb_68.call(50c36832-172d-46b8-a63f-f865d494c6bb)
    at org.mozilla.javascript.ContextFactory.doTopCall(Co ntextFactory.java:426)
    at org.mozilla.javascript.ScriptRuntime.doTopCall(Scr iptRuntime.java:3157)
    at org.mozilla.javascript.gen._50c36832_172d_46b8_a63 f_f865d494c6bb_68.call(50c36832-172d-46b8-a63f-f865d494c6bb)
    at org.mozilla.javascript.gen._50c36832_172d_46b8_a63 f_f865d494c6bb_68.exec(50c36832-172d-46b8-a63f-f865d494c6bb)
    at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doDispatch(JdbcMessageDispatcher.java:94)
    at com.mirth.connect.connectors.jdbc.JdbcMessageDispa tcher.doSend(JdbcMessageDispatcher.java:160)
    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:61)
    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:225)
    at com.mirth.connect.connectors.vm.VMMessageReceiver. getMessages(VMMessageReceiver.java:223)
    at org.mule.providers.TransactedPollingMessageReceive r.poll(TransactedPollingMessageReceiver.java:108)
    at org.mule.providers.PollingMessageReceiver.run(Poll ingMessageReceiver.java:97)
    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)
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.
    at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDriverError(SQLServerException.java:171)
    at com.microsoft.sqlserver.jdbc.DataTypes.throwConver sionError(DataTypes.java:1117)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.setObject(SQLServerPreparedStatement.java:97 7)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.setObjectNoType(SQLServerPreparedStatement.j ava:912)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.setObject(SQLServerPreparedStatement.java:92 1)
    at com.mirth.connect.server.util.DatabaseConnection.e xecuteUpdate(DatabaseConnection.java:142)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.mozilla.javascript.MemberBox.invoke(MemberBox. java:161)
    ... 37 more
    Last edited by Sree; 11-12-2010, 09:43 AM. Reason: Wrong code and error copied

  • #2
    Does

    params.add(TransactionPostedDate.toString());

    fix the problem?

    Comment


    • #3
      I really don't like to necro a thread but I'm having pretty much the same issue and yes adding .toString() fixes the problem but I can't do that for every parameter because you can't call toString() on a null value. Would the solution be to loop through the values checking if it's null or undefined (I assume the same is true for undefined) or is there a more streamlined process?

      Comment

      Working...
      X