Uncategorized SQLException for SQL

Question

I am using Spring Integration in my project. As per my requirement, I have to call two stored procedures simultaneously. Here is my code:

    <int:service-activator ref="msgHandler" method="buildRequestBasedDataSource" />

    <int-jdbc:stored-proc-outbound-gateway
                    id="PQIssueHistory-StoredProcedure-PQCOMMENTLOOKUP"
                    auto-startup="true"
                    data-source="routingDataSource"
                    stored-procedure-name="${PQIssueHistory-StoredProcedure-PQCOMMENTLOOKUP}"
                    skip-undeclared-results="true"
                    ignore-column-meta-data="true"  
                    use-payload-as-parameter-source = "false"
                    expect-single-result="true" >

                            <int-jdbc:sql-parameter-definition name="P_CRDATTIM" direction="IN" type="VARCHAR" />
                            <int-jdbc:sql-parameter-definition name="P_RECORDCD" direction="IN" type="VARCHAR" />
                            <int-jdbc:sql-parameter-definition name="P_CRNODE" direction="IN" type="VARCHAR" />

                            <int-jdbc:parameter name="P_CRDATTIM" expression="#xpath(payload, '//CRDATTIM')" />
                            <int-jdbc:parameter name="P_RECORDCD" expression="#xpath(payload, '//RECORDCD')" />
                            <int-jdbc:parameter name="P_CRNODE" expression="#xpath(payload, '//CRNODE')" />

            <int-jdbc:returning-resultset name="rowMapper" row-mapper="com.dsths.cs.awd.utils.ResultSetRowMapper"/>

    </int-jdbc:stored-proc-outbound-gateway>

    <int:service-activator ref="msgHandler"  method="buildMessageFromExtSysResponseTestOne" />

    <int:header-enricher default-overwrite="true"  should-skip-nulls="true"  >
        <int:header name="${headerNames.originalPayload}" expression="payload" />  
    </int:header-enricher>    



</int:chain>    

    <int:channel id="test" />
<int:chain input-channel="test"  output-channel="PQIssueHistory-XsltTransformInputChannel" >

<int-jdbc:stored-proc-outbound-gateway
                    id="PQIssueHistory-StoredProcedure-PQHISTORYLOOKUP"
                    auto-startup="true"
                    data-source="routingDataSource"
                    stored-procedure-name="${PQIssueHistory-StoredProcedure-PQHISTORYLOOKUP}"
                    skip-undeclared-results="true"
                    ignore-column-meta-data="true"  
                    use-payload-as-parameter-source = "false"
                    expect-single-result="true" >

                            <int-jdbc:sql-parameter-definition name="P_CRDATTIM" direction="IN" type="VARCHAR" />
                            <int-jdbc:sql-parameter-definition name="P_RECORDCD" direction="IN" type="VARCHAR" />
                            <int-jdbc:sql-parameter-definition name="P_CRNODE" direction="IN" type="VARCHAR" />

                            <int-jdbc:parameter name="P_CRDATTIM" expression="#xpath(payload, '//CRDATTIM')" />
                            <int-jdbc:parameter name="P_RECORDCD" expression="#xpath(payload, '//RECORDCD')" />
                            <int-jdbc:parameter name="P_CRNODE" expression="#xpath(payload, '//CRNODE')" />

            <int-jdbc:returning-resultset name="rowMapper" row-mapper="com.dsths.cs.awd.utils.ResultSetRowMapper"/>

    </int-jdbc:stored-proc-outbound-gateway>

<int:service-activator ref="msgHandler"  method="buildMessageFromExtSysResponseTestTwo" />

</int:chain>

I could able to hit the stored procedures without any issue when I execute using the junit test case.But, however, When I try to execute from GUI(i.e. after integrating with UI), I am getting the following error:

org.springframework.integration.MessageHandlingException: error occurred in message handler [org.springframework.integration.handler.MessageHandlerChain#201$child.PQIssueHistory-StoredProcedure-PQHISTORYLOOKUP.handler]
    at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:79)
    at org.springframework.integration.handler.MessageHandlerChain.handleMessageInternal(MessageHandlerChain.java:131)
   ................................
............................
    at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:598)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
    at java.lang.Thread.run(Thread.java:662)
Caused by: org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call ZSPPQHISTORYLOOKUP(?, ?, ?)}]; SQL state [000RZ]; error code [930027]; No record found in the VOC file for "BP". ; nested exception is java.sql.SQLException: No record found in the VOC file for "BP".
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1036)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1070)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:387)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:350)
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
    at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedure(StoredProcExecutor.java:335)
    at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedureInternal(StoredProcExecutor.java:325)
    at org.springframework.integration.jdbc.StoredProcExecutor.executeStoredProcedure(StoredProcExecutor.java:294)
    at org.springframework.integration.jdbc.StoredProcOutboundGateway.handleRequestMessage(StoredProcOutboundGateway.java:56)
    at org.springframework.integration.handler.AbstractReplyProducingMessageHandler.handleMessageInternal(AbstractReplyProducingMessageHandler.java:142)
    at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:73)
    ... 146 more
Caused by: java.sql.SQLException: No record found in the VOC file for "BP".
    at com.ibm.u2.jdbc.UniJDBCMsgFactory.createException(UniJDBCMsgFactory.java:101)
    at com.ibm.u2.jdbc.UniJDBCExceptionSupport.addException(UniJDBCExceptionSupport.java:87)
    at com.ibm.u2.jdbc.UniJDBCProtocolU2Impl.addServerError(UniJDBCProtocolU2Impl.java:2799)
    at com.ibm.u2.jdbc.UniJDBCProtocolU2Impl.serverExecuteSql(UniJDBCProtocolU2Impl.java:2130)
    at com.ibm.u2.jdbc.UniJDBCProtocolU2Impl.executeSql(UniJDBCProtocolU2Impl.java:2169)
    at com.ibm.u2.jdbc.UniJDBCProtocolU2Impl.doExecute(UniJDBCProtocolU2Impl.java:2208)
    at com.ibm.u2.jdbc.UniJDBCProtocolU2Impl.executeStatementQuery(UniJDBCProtocolU2Impl.java:606)
    at com.ibm.u2.jdbc.UniJDBCPreparedStatementImpl.execute(UniJDBCPreparedStatementImpl.java:407)
    at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299)
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1072)
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1070)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1020)
    ... 156 more
12:26:25,972 INFO  [STDOUT] 12:26:25,971 DEBUG [org.springframework.integration.channel.DirectChannel.preSend] preSend on channel 'cs-exceptionHandlingChannel', message: [Payload=org.springframework.integration.MessageHandlingException: error occurred in message handler [org.springframework.integration.handler.MessageHandlerChain#201$child.PQIssueHistory-StoredProcedure-PQHISTORYLOOKUP.handler]][Headers={timestamp=1401778585971, id=60a1966f-e5ed-8bc0-d9d0-76ee7f6856d8, errorChannel=org.springframework.integration.core.MessagingTemplate$TemporaryReplyChannel@1c8968f, replyChannel=org.springframework.integration.core.MessagingTemplate$TemporaryReplyChannel@1c8968f}]

From the above exception, it is clear that the control is going to the error channel. Here I just wanted to know the sql query which is causing the issue. I have turned on "DEBUG". It would be great if any one can share their ideas on how to proceed further to find/repair the problem.

Answer 1

Ashok, I can only share ideas, since I may know less springjdbc. But I think what caused the problem was a statement added by the driver such as getUpdateCount.

First, you could create a class to extend JdbcTemplate but copy everything and change constructors so that it is identical to JdbcTemplate. I assume you can see the source code of JdbcTemplate.

Second, at line 1020, find out what it is and how it was called. The exception was translated from SQLException to something else which you didn't show us. Please show us so we know what the top level exception is. what version of springjdbc were you using?

Third, now you can modify your code to use your own subclass instead of JdbcTemlate and you can modify the code. Comment something like handlewarnings. Step by step, you comment from the last line in a try { } block where the exception was caught and one by one. Then you should be able to find out what extra SQL statement the driver added was causing the problem.

This is only an idea. Please let us know what your result is and we can continue.

Answer 2

If you mean you want to see the actual contents of the query sent to the DB, use a spy driver; just google; there are a number of them. Or use WireShark or similar to get a network trace.