Proxy to JDBC scenario

[Former Member]

My scenario is proxy sync to PI to JDBC sync

On Database, stored procedure is called with 3 input and 1 output parameter.

I defined request JDBC msg structure for stored procedure

How to define the response JDBC data type for getting back the stored procedure response from database in PI?

Any ideas, plz. Thx.

[iaki_vila · Accepted solution]

Hi Stewart,

Check this Thorsten Nordholm Søbirk blog

And the SAP.HELP documentation:

Defining an EXECUTE Statement - Advanced Adapter Engine - SAP Library

A little trick when you don't know the response structure is to do a SOAP sender and to do a dummy interface in the inbound and outbound response, then you execute the call and you can notice what is the structure. With the structure clear you can do finally the right xsd structures

Hope this helps.

Regards.

[Former Member · Accepted solution]

i ran my scenario sync proxy to sync jdbc stored procedure(SP).

in the message monitor log, i see the database request got processed

but then in response msg mapping, it is erroring saying the topmost response msg node is not getting formed in target msg. I have mapped it to the topmost node of SP response msg.

The SP should return an integer because SP has 2 input and 1 output integer field.

So does this mean SP is not returning any output?

Any idea, how to debug this further? Moreover the msg content in PO 7.4 is just showing the request msg data, how to get the response msg data in the msg log? Thx.

[Former Member · Accepted solution]

Hello,

It seems that SP is not returning anything as a result you are getting that error message.

I have two things to suggest u -

1) Check whether the SQL query which PI is creating is correct or not?

2) Cross check ur SP response structure.

If u want then u can paste ur SP request and response structure here...

Thanks

Amit Srivastava

[naveen_chichili · Accepted solution]

Hi Stewart,

Check the response structure also there might be a chance that you are not getting any response.

Thanks,

Naveen

[Former Member · Accepted solution]

i got access to dev database through squirrel software.

i can see the database in it and the SP in it.

i tried to execute EXEC <SP_NAME> <PARAMETER1>, <PARAMETER2> ,<PARAMETER3>

i can see in the below window two panes in which one pane has:

RETURN type integer value 0

the other pane has:

RETURN type integer value 1

i am not able to figure out on running the above SQL command, why I am getting 2 panes in the below window? i tried with changing the parameter values, but each time it is outputting 2 panes in the below window?

i am not able to see the code of SP, so do not know which table is actually getting the data inserted into it. any way that i can see the code of SP - through any software, any idea pls - the database is on SQL server 2008.

i have defined the response structure as:

MT_Request_Response

stmt_Response

response_1

Row_1

update_count

RETURN

Row1 is having sub-elements update_count and RETURN where RETURN is the SP output parameter name.

So is my response structure ok or not ok?

Any ideas, plz. Thx.

[Former Member · Accepted solution]

Any reply to above/below queries, pls. Thx.

Also in PI monitoring in sync message scenarios, in msg monitor, i can see 1 msg for sender system and 1 msg from reciever system. The msg from sender system in msg content has the payload of the request msg. But the second msg from receiver system in msg content is not showing the response data from the receiver system.

Does any setting need to be made to get the response data shown in response msg in msg monitor in single stack PO system?

Any idea, plz. Thx.