JDBC Receiver Adapter Stored Procedure multiple records at time

[former_member334911]

Hello Experts,

I have created scenario for synchronous Proxy to JDBC in which receiver JDBC calls stored procedure. In stored procedure, I am trying to insert multiple records at a time. I have referred following thread:

I am passing my input structure as XML.

When I test scenario, In PI log I get as Database request executed successfully. I don't get any error in PI. But its not updating/Calling ( Not sure ) stored procedure. In stored procedure response, I always get blank output ( Empty Payload ). It should return number of rows changed or exception raised as code for this is written in stored procedure.

Can you please help me with ,

1. Stored Procedure response parameter name I have given as 'return_value', Is there any specific name to be used ? Because my DB stored procedure developer says, there is no parameter name given for return value in stored procedure. So I have kept it as 'return_value' with type xsd:string.

2. Is there any way to confirm from PI that stored procedure is called and executed ? because always I get success message but nothing is happening in DB.

3. Can DB guys can get XML which they are receiving from SAP PI ?

Here, third party DB is SQL Server 2014.

Thanks and Regards,

Gaurav Khandelwal

[former_member334911]

Hello Everyone,

Can you please help in above regards ?

Thanks and Regards,

Gaurav Khandelwal

[former_member194786]

Hi Gaurav,

I had similar scenario where date formats were not correct and even though the call from PI was showing successful, nothing happened in DB. Try executing the stored proc using same data that you are using to test the stored procedure.

Another thing to check would be to confirm that you have used right data types as per link below:

Defining an EXECUTE Statement - Advanced Adapter Engine - SAP Library

Regards,

Sanjeev

[former_member334911]

Hi Sanjeev,

Thanks for your answer but as I mentioned I am passing input structure as XML and target structure contains only field with VARCHAR type. Now, here date format, do you mean at Source structure ?

Thanks and Regards,

Gaurav Khandelwal

[former_member194786]

Hi Gaurav,

Apologies, I missed that part.In my scenario, we are not passing the data as CLOB.

After reading the thread you mentioned again, I noticed that the data type used in that thread is "CLOB".

Can you try using that DT and see if that helps? Also, I would suggest testing your data in the DB stored procedure and see what happens.

Regards,

Sanjeev

[former_member334911]

Hi Sanjeev,

Thanks for highlighting type and I have changed type to CLOB. Now my XML looks like,

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

<ns0:MT_GL_Posting_In_XML xmlns:ns0="http://test">

   <Statement>

      <usp_SAP_GL_Posting_XML action="EXECUTE">

         <TABLE>usp_SAP_GL_Posting_XML</TABLE>

         <GL_Posting type="CLOB" isInput="true"><![CDATA[<GL_Posting>

      <GL>

         <acct_type_code>00</acct_type_code>

      </GL>

      <GL>

         <acct_type_code>00</acct_type_code>

      </GL>

   </GL_Posting>]]></GL_Posting>

      </usp_SAP_GL_Posting_XML>

   </Statement>

</ns0:MT_GL_Posting_In_XML>

If I take content between CDATA and test with stored procedure,

<GL_Posting>

      <GL>

         <acct_type_code>00</acct_type_code>

      </GL>

      <GL>

         <acct_type_code>00</acct_type_code>

      </GL>

   </GL_Posting>

Stored procedure gets executed successfully and returns value.

But using PI, same issue I am getting, stored procedure is not getting executed and at the same time, no response I am receiving. My response structure looks like,

Thanks and regards,

Gaurav Khandelwal

[former_member194786]

Hi Gaurav,

Under your statement node, try changing the table to lower case and see if that works.

Regards,

Sanjeev.

[former_member334911]

Hi Sanjeev,

I tired but no luck

Thanks and Regards,

Gaurav Khandelwla