1 Reply Latest reply on Jan 20, 2017 4:56 AM by vijaysaradhi1

Microsoft Sqlserver insert query on apache camel doesn't work

leo_analyst Jan 10, 2017 4:08 PM

I developed an Apache Camel route in RedHat Jboss Fuse (karaf version 6.3). When i try to execute an insert query, that has to call a sequence, into the datasource (Microsoft Sqlserver 2012) i get an sql error related to the number of the parameters.

Apache Camel query:

<to id="insertStartToDB" uri="sql:INSERT INTO dbo.conart_transactions (PK_ID,TRANS_ID,Date_insert,Date_start_flow,Date_end_flow,Flow_name,direction,status,status_descr,aux_1, aux_2,aux_3,aux_4,BLOB) VALUES (NEXT VALUE FOR transaction_pkid_seq,:#TRANS_ID,:#DATE_INSERT,:#DATE_START,:#DATE_END,:#FLOW_NAME,:#DIRECTION,:#STATUS,:#STATUS_DESCR,:#AUX_1,:#AUX_2,:#AUX_3,:#AUX_4,:#BLOB)?dataSource=sqlServerDS"/>

Error returned:

["org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Number of parameters mismatch. Expected: 14, was: 13; nested exception is java.sql.SQLException: Number of parameters mismatch. Expected: 14, was: 13","\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)[142:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1]","\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[142:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1]","\tat org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[142:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1]","\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)[142:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1]","\tat org.apache.camel.component.sql.SqlProducer.process(SqlProducer.java:107)[123:org.apache.camel.camel-sql:2.17.0.redhat-630187][org.apache.camel:camel-sql:2.17.0.redhat-630187]","\tat org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:468)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:196)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.Pipeline.process(Pipeline.java:121)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.Pipeline.process(Pipeline.java:83)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.ChoiceProcessor.process(ChoiceProcessor.java:117)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:468)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:196)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.Pipeline.process(Pipeline.java:121)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.Pipeline.process(Pipeline.java:83)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:196)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:109)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:91)[120:org.apache.camel.camel-core:2.17.0.redhat-630187][org.apache.camel:camel-core:2.17.0.redhat-630187 com.googlecode.concurrentlinkedhashmap:concurrentlinkedhashmap-lru:1.4.2]","\tat org.apache.camel.component.jms.EndpointMessageListener.onMessage(EndpointMessageListener.java:112)[121:org.apache.camel.camel-jms:2.17.0.redhat-630187][org.apache.camel:camel-jms:2.17.0.redhat-630187]","\tat org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:555)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:515)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:485)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1103)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1095)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:992)[143:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jms:3.2.16.RELEASE_2]","\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)[:1.8.0_111]","\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)[:1.8.0_111]","\tat java.lang.Thread.run(Thread.java:745)[:1.8.0_111]","Caused by: java.sql.SQLException: Number of parameters mismatch. Expected: 14, was: 13","\tat org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy.populateStatement(DefaultSqlPrepareStatementStrategy.java:153)[123:org.apache.camel.camel-sql:2.17.0.redhat-630187][org.apache.camel:camel-sql:2.17.0.redhat-630187]","\tat org.apache.camel.component.sql.SqlProducer$2.doInPreparedStatement(SqlProducer.java:137)[123:org.apache.camel.camel-sql:2.17.0.redhat-630187][org.apache.camel:camel-sql:2.17.0.redhat-630187]","\tat org.apache.camel.component.sql.SqlProducer$2.doInPreparedStatement(SqlProducer.java:107)[123:org.apache.camel.camel-sql:2.17.0.redhat-630187][org.apache.camel:camel-sql:2.17.0.redhat-630187]","\tat org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)[142:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1][org.apache.servicemix.bundles:org.apache.servicemix.bundles.spring-jdbc:3.2.16.RELEASE_1]","\t... 29 more"]

I guess that is something related to first values of the insert ("NEXT VALUE FOR transaction_pkid_seq"), but i'm not able to understand why is not working.

Thanks in advance.

1. Re: Microsoft Sqlserver insert query on apache camel doesn't work

vijaysaradhi1 Jan 20, 2017 4:56 AM (in response to leo_analyst)

Please be aware, that SQL Driver has SQL parsing problem for INSERT INTO.
So if you issue:
INSERT INTO your_table(whatever) VALUES(whatever) —— > without space after table name

The funny thing is that in most cases it will work!

BUT if Spring will call stmt.getParameterMetaData()on prepared statement (which happens sometimes)- then it will fail with very strange exception which is not related to the query (guess its a bug in sql server). I recently had such strange issue and fixed it.
To avoid the problem, please make sure you will always place a space after table name for INSERT INTO.

But not sure if that is the case for you- just posted it incase it may help you. cheers.
1 of 1 people found this helpful
Actions