MQSeries.net :: View topic

ethirajesh · Posted: Tue Mar 19, 2013 7:56 pm Post subject:

I am seeing some strange behaviour while invoking stored procedure(This is just retriving data, no update,insert, or delete).

Requirement :

I am invoking one SP more than once with different input parameters and storing result sets in environment variables. I have one CREATE PROCEDURE statement and multiple CALL statements which invokes the same SP with different input parameters. All this is happening in same compute node

Problem facing :

When the SP is run directly from any database client or thru datasource explorer it returns only one row for each call we make.

But when call is made through MB it returns 1 row for first call, two rows for second call, three rows for third call and so on.. In all the calls only the 1st row has complete data and other rows are having null for most of the columns.

Workaround I did :

1) I have introduced two separate compute nodes to invoke the SP separately(propagated from existing compute node based on IF condition), declaration and invocation of SP has been taken care in these nodes but still the same results.
2) I have created separate routine in the existing compute node in subflow, this routine will be called with function code, input data ID and key as the input param. Internally I had one common declaration and Invocation of the SP, the idea is every time I call the routine the variables initialized earlier will be overwritten/nullified automatically. But still the same results.
3) In 3rd option I have created two SP declaration(one for brand and one for size) with a difference in their variable names(in CREATE, DECLARE and CALL statements), this also did not help.

Finally, I have put COMMIT; right after CALL statement and found this is working fine after that. Means, It retruned exactly one row for each call as it is supposed to be.

Is it something to do with DB end, if not what could be the reason for this behaviour?