java.sql.SQLException: Invalid parameter index 3. Query: INSERT INTO [test].[dbo].[HL7Raw] ([LName] ,[FName] ,[Doctor] ,[PCP] ,[MRN] ,[VID] -- ,[PrimaryKey] ,[DTofArrival] -- ,[DTofMessageInsertion] -- ,[DTofMessage] ,[StatusCode] ,[Insurance1] ,[Insurance2] ,[Sex] ,[ReceivingFacility] ,[DOB]) VALUES ('LastName' ,'FirstName' ,'Doctor' ,'PCP' ,? ,? -- ,<PrimaryKey, numeric(18,0),> ,CONVERT(datetime,SUBSTRING("?",1,4)+ --year '-' + SUBSTRING('?',5,2) + --month '-' + SUBSTRING('?',7,2) + --day ' ' + SUBSTRING('?',9,2) + --24hh ':'+ SUBSTRING('?',11,2) + --min ':00.000', 121) -- ,? -- ,? ,'?' ,'?' ,'insuranceelement2' ,'?' ,'?' ,CONVERT(datetime,SUBSTRING('?',1,4)+ --year '-' + SUBSTRING('?',5,2) + --month '-' + SUBSTRING('?',7,2) + --day ' ' + SUBSTRING('?',9,2) + --24hh ':'+ SUBSTRING('?',11,2) + --min ':00.000', 121) ) Parameters: [0732043, 000038426102, 200810100000, 200810100000, 200810100000, 200810100000, 200810100000, 200810100000, 200810100000, A08, , F, 1, 19610302, 19610302, 19610302, 19610302, 19610302]

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • Invalid parameter index 3 - Mirth Community
    via by Unknown author,
  • cant get it working [#1428232] | Drupal.org
    via by Unknown author,
  • GitHub comment 25#52516950
    via GitHub by StigLau
    ,
  • [mule-dev] MSSQL with JDBC Transport
    via by Juan Guzman,
  • I'm trying to perform an insert on an Oracle Database. The problem I have is with null parameters to be passed to the statement. The jdbc dispatcher performs a: int nbRows = new QueryRunner().update(con, writeStmt, paramValues); Looking at the QueryRunner code I can see that the update method invokes the fillStatement operation: protected void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException { if (params == null) { return; } for (int i = 0; i < params.length; i++) { if (params[i] != null) { stmt.setObject(i + 1, params[i]); } else { stmt.setNull(i + 1, Types.OTHER); } } } When a null value is found, the stmt.setNull(i + 1, Types.OTHER); is called and the following exception is thrown: java.sql.SQLException: Invalid column type Query: INSERT INTO FPA.ESPECIES (rest of the query here)... at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330) at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:399) at org.mule.providers.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:98) at com.lumina.integration.mule.providers.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:61) at org.mule.providers.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:196) Apparently the error is because of the Types.OTHER sql type. I know that this is an issue involving Jakarta dbutils package but maybe someone has already got this problem and found a workaround to this. At the Jakarta mailing list a workaround is mentioned by subclassing the QueryRunner class. In order to do that, I'll need to subclass the JDBC provider completely (the dispatcher and factory at least). Perhaps a custom QueryRunner could be injected to the JdbcMessageDispatcher in a future version. Regards, Juan Manuel
    via by Juan Manuel Sanmarco,
  • I'm trying to perform an insert on an Oracle Database. The problem I have is with null parameters to be passed to the statement. The jdbc dispatcher performs a: int nbRows = new QueryRunner().update(con, writeStmt, paramValues); Looking at the QueryRunner code I can see that the update method invokes the fillStatement operation: protected void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException { if (params == null) { return; } for (int i = 0; i < params.length; i++) { if (params[i] != null) { stmt.setObject(i + 1, params[i]); } else { stmt.setNull(i + 1, Types.OTHER); } } } When a null value is found, the stmt.setNull(i + 1, Types.OTHER); is called and the following exception is thrown: java.sql.SQLException: Invalid column type Query: INSERT INTO FPA.ESPECIES (rest of the query here)... at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330) at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:399) at org.mule.providers.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:98) at com.lumina.integration.mule.providers.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:61) at org.mule.providers.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:196) Apparently the error is because of the Types.OTHER sql type. I know that this is an issue involving Jakarta dbutils package but maybe someone has already got this problem and found a workaround to this. At the Jakarta mailing list a workaround is mentioned by subclassing the QueryRunner class. In order to do that, I'll need to subclass the JDBC provider completely (the dispatcher and factory at least). Perhaps a custom QueryRunner could be injected to the JdbcMessageDispatcher in a future version. Regards, Juan Manuel
    via by Juan Manuel Sanmarco,
    • java.sql.SQLException: Invalid parameter index 3. Query: INSERT INTO [test].[dbo].[HL7Raw] ([LName] ,[FName] ,[Doctor] ,[PCP] ,[MRN] ,[VID] -- ,[PrimaryKey] ,[DTofArrival] -- ,[DTofMessageInsertion] -- ,[DTofMessage] ,[StatusCode] ,[Insurance1] ,[Insurance2] ,[Sex] ,[ReceivingFacility] ,[DOB]) VALUES ('LastName' ,'FirstName' ,'Doctor' ,'PCP' ,? ,? -- ,<PrimaryKey, numeric(18,0),> ,CONVERT(datetime,SUBSTRING("?",1,4)+ --year '-' + SUBSTRING('?',5,2) + --month '-' + SUBSTRING('?',7,2) + --day ' ' + SUBSTRING('?',9,2) + --24hh ':'+ SUBSTRING('?',11,2) + --min ':00.000', 121) -- ,? -- ,? ,'?' ,'?' ,'insuranceelement2' ,'?' ,'?' ,CONVERT(datetime,SUBSTRING('?',1,4)+ --year '-' + SUBSTRING('?',5,2) + --month '-' + SUBSTRING('?',7,2) + --day ' ' + SUBSTRING('?',9,2) + --24hh ':'+ SUBSTRING('?',11,2) + --min ':00.000', 121) ) Parameters: [0732043, 000038426102, 200810100000, 200810100000, 200810100000, 200810100000, 200810100000, 200810100000, 200810100000, A08, , F, 1, 19610302, 19610302, 19610302, 19610302, 19610302] at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:359) at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:428) at com.webreach.mirth.connectors.jdbc.JdbcMessageDispatcher.doDispatch(JdbcMessageDispatcher.java:130) at com.webreach.mirth.connectors.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:157) at org.mule.providers.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:166) at org.mule.impl.MuleSession.sendEvent(MuleSession.java:191) at org.mule.impl.MuleSession.sendEvent(MuleSession.java:130) at org.mule.routing.outbound.AbstractOutboundRouter.send(AbstractOutboundRouter.java:85) at org.mule.routing.outbound.FilteringMulticastingRouter.route(FilteringMulticastingRouter.java:99) at org.mule.routing.outbound.OutboundMessageRouter$1.doInTransaction(OutboundMessageRouter.java:78) at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:48) at org.mule.routing.outbound.OutboundMessageRouter.route(OutboundMessageRouter.java:82) at org.mule.impl.model.DefaultMuleProxy.onCall(DefaultMuleProxy.java:247) at org.mule.impl.model.seda.SedaComponent.doSend(SedaComponent.java:209) at org.mule.impl.model.AbstractComponent.sendEvent(AbstractComponent.java:277) at org.mule.impl.MuleSession.sendEvent(MuleSession.java:201) at org.mule.routing.inbound.InboundMessageRouter.send(InboundMessageRouter.java:161) at org.mule.routing.inbound.InboundMessageRouter.route(InboundMessageRouter.java:128) at org.mule.providers.AbstractMessageReceiver$DefaultInternalMessageListener.onMessage(AbstractMessageReceiver.java:493) at org.mule.providers.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:272) at org.mule.providers.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:231) at com.webreach.mirth.connectors.file.FileMessageReceiver.processFile(FileMessageReceiver.java:219) at com.webreach.mirth.connectors.file.FileMessageReceiver.poll(FileMessageReceiver.java:138) at org.mule.providers.PollingMessageReceiver.run(PollingMessageReceiver.java:118) at org.mule.impl.work.WorkerContext.run(WorkerContext.java:290) at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) at java.lang.Thread.run(Unknown Source)

    Users with the same issue

    Unknown visitor1 times, last one,