JDBC receiver adapter - stored procedure response

[Former Member]

I am on PI 7.11 and have the following scenario:

RFC->PI->JDBC

whereby the JDBC receiver access an Oracle db (unsure of that version) using a stored procedure "GET_ICBC_ID".

• My issue is that I never seem to have any data back in PI on the JDBC response coming back from Oracle. ***

Here is the definition of that stored procedure in Oracle:

Stored Procedure Name: GET_ICBC_ID

Parameters

ICBCID OUT VARCHAR2 <- Returned RACF ID (output)

REQUESTOR IN VARCHAR2 default 'SAPHR' <- Optional Requestor ID.

COMMUNITY IN VARCHAR2 default 'EMP' <- Optional Community ID.

Here is my JDBC call in to the stored procedure (sxi_monitor), which seems to be fine:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns1:MT_racf_req xmlns:ns1="http://sap.com/xi/Z_SkillSoft">

- <Statement>

- <GET_ICBC_ID action="EXECUTE">

<REQUESTOR isInput="true" type="VARCHAR">SAPHR</REQUESTOR>

<COMMUNITY isInput="true" type="CHAR">EMP</COMMUNITY>

</GET_ICBC_ID>

</Statement>

</ns1:MT_racf_req>

It appears as though the call into the JDBC is working, as the Oracle guys have shown me the logs (showing success) on the Oracle side.

This is my sxi_monitor entry (of that response message):

<?xml version="1.0" encoding="utf-8" ?>

- <ns1:MT_racf_req_response xmlns:ns1="http://sap.com/xi/Z_SkillSoft">

<Statement_response />

</ns1:MT_racf_req_response>

Also when I look at some of the message properties I see:

<SAP:MessageSizePayload>0</SAP:MessageSizePayload>

<SAP:MessageSizeTotal>13243</SAP:MessageSizeTotal>

<SAP:PayloadSizeRequest>0</SAP:PayloadSizeRequest>

<SAP:PayloadSizeRequestMap>0</SAP:PayloadSizeRequestMap>

<SAP:PayloadSizeResponse>179</SAP:PayloadSizeResponse>

<SAP:PayloadSizeResponseMap>149</SAP:PayloadSizeResponseMap>

which I believe is saying that something came back in to PI as a response?? Not sure.

My Message Type definition is:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://sap.com/xi/Z_SkillSoft" targetNamespace="http://sap.com/xi/Z_SkillSoft">

<xsd:element name="MT_racf_req_response" type="DT_racf_req_response" />

<xsd:complexType name="DT_racf_req_response">

<xsd:sequence>

<xsd:element name="Statement_response" type="DT_statement_response" />

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="DT_statement_response">

<xsd:sequence>

<xsd:element name="ICBCID" type="xsd:string" />

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

I have also tried inserting <row> between <Statement_response> and the ICBCID element. Still no luck.

I suspect something is wrong with my response message type definition, but am not seeing it. Also, am unsure how I can debug this - it appears as though Oracle side is fine, but nothing back into PI.

Any thoughts appreciated.

Keith

[Former Member]

Hi,

Please refer the SAP Note 941317 for JDBC adapter incompatibilities. Also, please note that PI only supports Stored Procs for Oracle DB version 10.2.x or higher. If you Oracle DB is of a lower version, you might need to revisit the design.

Also, for the stored procedure response, this should be in the form of a resultset else PI would not be able to recognize the response structure and would would get a blank response in your return.

Try to execute the stored proc on the DB itself and confirm that the response is in the proper format. Also, ensure that there is no other return line, say something like "10 rows updated" before the return data as the jdbc adapter would not recognise the response in this case.

Regards

[Former Member]

Thank you all for your response. Some more info.:

- The stored procedure is a SELECT, so am thinking I shouldn't need the <update_count> tags mentioned. Let me know if this assumption is incorrect.

- Because one field (ICBCID) is to be returned in the reponse, does it need to be passed in on the jdbc request ? Looking at the blogs I did not see them do that. If so, with its IsOutput=true, or should it be IsInput=false or other? Also if it is to be passed in on the request, I guess I would set its value to a space so that the xml tag gets produced?

- Does the order of the request elements passed matter, i.e. if the ICBCID is to be passed on the request, should it be first or last in the list? Does order of the 2 IsInput=true parameters matter (i.e. in relation to the Stored Procedure)?

- The Oracle version is 11.1, so pretty up-to-date. Stored Procedures should work fine.

- What specifically is meant be 'resultset'? Also, does <row> need to be added as a tag within the response xml?

Regards,

Keith

[Former Member]

If you do not need the update/insert count, then this would not be required.

Also, the order of the request elements should not matter.

You can refer this link for a JDBC receiver with stored procedure to select data.

/people/luis.melgar/blog/2008/05/13/synchronous-soap-to-jdbc--end-to-end-walkthrough

[Former Member]

AFAIK the order of the element name (i.e structure ) in PI should match the order of the parameter name in the SP. When the values are sent from PI to SP they are sent position wise....(corrections are always welcome)

as mentioned in the blog try to add "row" in the response structure and chk the result...

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

[Former Member]

Amit is right in stating that the order in the Data type and stored proc should match. What I meant by order not important is the order in the DB table.

However, it would be better to keep the order same.

[Former Member]

Hi all - thanks for your helpful responses. I have set the question now to 'Answered'. I got it working by the following (structure item level indicated):

Request message type (to stored procedure):

1.MT_racf_req

2. Statement

3. GET_ICBC_ID

4. action (set to 'EXECUTE')

4. ICBCID

5. isOutput (set to '1')

5. type (set to 'VARCHAR')

Response message type (from Stored Procedure):

1.MT_racf_req_response

2. Statement_response

3. ICBCID

Regards,

Keith

[Former Member]

cross chk ur JDBC response structure in case of stored procedures:

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

[former_member854360]

Hi,

Your response structure will have this fields,

<StatementName_response>

<update_count>count</update_count>

<insert_count>count</insert_count>

</StatementName_response>

Refer this

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

http://help.sap.com/SAPhelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm