how to handle stored procedure response having multiple queries

[Former Member]

Hi Friends,

While working in JDBC to RFC scenario,I faced an issue that my stored procedure is having multiple SQL queries in it. First Select and then update and again some select options.So,how to handle the response of the stored procedure. I read that while using sender JDBC

" db.processDBSQLStatement=<SQL-Select-Statement>

Either specify a valid SQL SELECT statement to select the data to be sent from the specified database, or specify an SQL EXECUTE statement to execute a stored procedure that contains exactly one SELECT statement "

So, please suggest me is there any other way to catch the output of the stored procedure.Because, if select statement is working fine but if any other quires fails then data inconsistencies can happen.Kindly help me out.

Thanks and Regards,

Nutan

[former_member5086 · Accepted solution]

Hi Nutan,

I have some similar scenarios with a Ms SQL Server.

I can do multiple SQL queries and multiple update, but with some conditions:

• You must do the select statements at first, using union (same number of columns for each select) or a XML format (different number of columns for each select)

• update statements must be the last statement and must be executed at time with:

declare @sSql nvarchar(2000)

set @sSql= N' update statement 1

update statement 2....'

EXECUTE sp_executesql @sSql

Regards,

Carme

[Former Member · Accepted solution]

Hi,

Thanks for your valuable replies.

As per Suraj's suggestion the error has to be handled by SP.Already exception is handled in SP.But,issue is that select will never fail so, sender adapter will get the resultset from select and continue process.But if later any other query fails in SP adpter wont be getting any response.

Carme can you please explain the query please.

I need to try something like creating a temporary table and inserting the resultset of slect statement in that. and perform all other operations and after successful completion of all the queries.Again i want to get all the values from the temporary table. So,whether I can write such query in the sender communication channel.Please suggest me for this.

Thanks and Regards

Nutan

[former_member187339 · Accepted solution]

Hi nutan,

>>Already exception is handled in SP.But,issue is that select will never fail so, sender adapter will get the resultset from select and continue process.But if later any other query fails in SP adpter wont be getting any response.

Along with exception you need to handle the case when some other query fails. A SP is like a procedure which will do a certain list of activities before providing the output. So during this activity if some query fail then you can send back the response with a message!!!! And in XI handle this error (by routing it to some error receiver etc)

>>I need to try something like creating a temporary table and inserting the resultset of slect statement in that. and perform all other operations and after successful completion of all the queries.Again i want to get all the values from the temporary table. So,whether I can write such query in the sender communication channel.Please suggest me for this.

Approach looks ok, but think of the delay for JDBC sender adapter. IT will invoke your SP and will wait for it to fill a table and do all the processing. I guess this may become a issue for you.

Check on the frequency of this interface and message size before taking this design approach

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

Instead of temporaray table I am creating a temporary table variable and inserting data into that. After that trying to get the output from it.So,performance issue will not be there.But,when I tried that at database level it's working fine but at adapter level it's not getting any resultset from database. Whether adapter can not handle this or my query problem.

Query is:

DECLARE @test Table (PROC_MESS_ID_TMP int,PLANT char(4),PROC_MESS_CATEGORY char(8),TEST_FLAG char(1),SENDER_NAME char(32)) EXECUTE sProcP1_O_GoodProd_Header 0,'MESSAGE' SELECT * FROM @test

Thanks

Nutan

[Former Member]

I created a new SP which will use create an internal table to store the values from select query through joining all the tables and then perform all the DMl operations.After completion of other queries again at the end I am trying to select the data from the internal table. It worked fine. Thanks for your help.

[former_member187339]

Hi,

When you call a SP from JDBC adapter, you need to pass all the input parameter (no matter whether they are getting used in first select or second update) and finally its response shoudl have the fields that you need in PI.

>>Either specify a valid SQL SELECT statement to select the data to be sent from the specified database, or specify an SQL EXECUTE statement to execute a stored procedure that contains exactly one SELECT statement "

When you use SP on the receiver side yoo need to have an Execute with only one select query. Since here you are calling SP from the sender JDBC adapter, you can have multiple selects/updates.

>>if select statement is working fine but if any other quires fails then data inconsistencies can happen.Kindly help me out.

This you need to handle it in SP, if any statement fails then appropriate empty values shoudl be sent to PI

Regards

suraj