MQSeries.net :: View topic

meena05 · Posted: Fri May 20, 2016 1:24 am Post subject:

Hi,
I am calling SQL Server procedure using following way in my ESQL module:

CALL getData(param1, param2, param3, param4, param5, param6, param7, param8, Environment.Variables.ResultSet[]);

CREATE PROCEDURE getData(IN PARAM1 CHARACTER,IN PARAM2 CHARACTER,IN PARAM3 CHARACTER,IN PARAM4 CHARACTER,IN PARAM5 CHARACTER,IN PARAM6 CHARACTER, IN PARAM7 CHARACTER,IN PARAM8 CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL
NAME "dbo.proc1";

SQL Server Driver is DataDirect 7.1 SQL Server Wire Protocol "

Once the code is deployed and executed, the CALL function is invoked and EG is getting restarted automatically and I am not receiving any SOAP response back.

Below is the trace
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2016-05-19 15:29:06.039104 17008 UserTrace BIP12080I: Preparing a database statement for ''SQL_DSN''.
Preparing a database statement against data source ''SQL_DSN''.
2016-05-19 15:29:06.039355 17008 UserTrace BIP12081I: Prepared a database statement for ''SQL_DSN''.
Prepared a database statement against data source ''SQL_DSN''.
2016-05-19 15:29:06.063778 17008 UserTrace BIP12074I: Executing a database statement for ''SQL_DSN''.
Executing a database statement against data source ''SQL_DSN''.
2016-05-19 15:29:15.004922 25491 Information BIP2152I: Configuration message received from broker.
An execution group received a command from the Broker.
No user action required.
2016-05-19 15:29:15.005062 25491 Information BIP2153I: About to ''Start'' an execution group.
An execution group is about to perform an action.
No user action required.
2016-05-19 15:29:15.273763 25491 UserTrace BIP3487W: ''Dispatcher org.apache.axis2.engine.SOAPActionBasedDispatcher is now deprecated.''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.273949 25491 UserTrace BIP3487W: ''Please edit axis2.xml and replace with the same class in org.apache.axis2.dispatchers package''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.276928 25491 UserTrace BIP3487W: ''Dispatcher org.apache.axis2.engine.SOAPMessageBodyBasedDispatcher is now deprecated.''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.277018 25491 UserTrace BIP3487W: ''Please edit axis2.xml and replace with the same class in org.apache.axis2.dispatchers package''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
2016-05-19 15:29:15.277865 25491 UserTrace BIP3487W: ''Dispatcher org.apache.axis2.engine.SOAPActionBasedDispatcher is now deprecated.''
An embedded component has written the 'WARNING' level diagnostic message included here.
Refer to the appropriate message in the embedded component's documentation.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Then I tried using PASSTHRU call
SET Environment.Variables.ResultSet[] = PASSTHRU('call dbo.Proc1(?,?,?,?,?,?,?,?)',param1,param2,param3,param4,param5,param6,param7,param8);

and received below SOAPFault
<faultstring>Id Number:BIP2322E:Child SQL exception:60:0:[IBM][ODBC 20101 driver]1254</faultstring>

Trace is
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2016-05-19 17:16:49.491064 55117 RecoverableException BIP2488E: ('FlowName.getData_Compute.CopyEntireMessage', '22.2') Error detected whilst executing the SQL statement ''SET Environment.Variables.ResultSet[] = DEFAULTPASSTHRU('call dbo.Proc1(?,?,?,?,?,?,?,?)', param1,param2,param3,param4,param5,param6,param7,param8);''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2016-05-19 17:16:49.491068 55117 DatabaseException BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''/opt/ibm/IE02/2.0.1/lib/libodbcinterface.so''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
2016-05-19 17:16:49.491068 55117 DatabaseException BIP2322E: Database error: SQL State ''60''; Native Error Code '0'; Error Text ''[IBM][ODBC 20101 driver]1254''.
The error has the following diagnostic information: SQL State ''60'' SQL Native Error Code '0' SQL Error Text ''[IBM][ODBC 20101 driver]1254''

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I am working to verify the issue. Meanwhile, please let me know any solutions to fix this issue.