Developing a synchronous proxy to JDBC scenario from SAP PI 7.0 system

[Former Member]

Hi Experts,

Hope you are doing well.

I am developing a synchronous proxy to JDBC scenario. On the database end, I have to call a stored procedure and pass on a value as an input parameter. The stored procedure will return back a set of fields which will have to returned back to the proxy.

I designed the interface as per the steps provided in the link http://scn.sap.com/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

The request message type on the database side was defined as follows:

<Message Type name>

     <Statement>

          <STORED_PROCEDURE_NAME action="EXECUTE">

               <INPUT FIELD isInput="TRUE" type="VARCHAR2">12345</INPUT FIELD>

          </STORED_PROCEDURE_NAME>

     </Statement>

</Message Type name>

The synchronous response message type from the Database side was defined as follows:

  <Statement_Response>

      <STORED_PROCEDURE_NAME>

         <row>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

         </row>

      </STORED_PROCEDURE_NAME>

   </Statement_Response>

I developed a request message mapping for mapping the Proxy structure to the DB Request structure. I also developed a response mapping for mapping the DB Response to the Proxy response. I combined the two message mappings under a single Interface mapping

While executing the interface, I am getting the following error in XI:

com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table/stored proc. '<STORED_PROCEDURE_NAME>' (structure 'Statement'): java.sql.SQLException: Unsupported parameter type 'VARCHAR2' for parameter '<INPUT FIELD>' found

Is there something that I am missing?

regards

Debansu

[Former Member · Accepted solution]

Hi Debansu

Create your SAP response structure as below

<Response Message Type to SAP>

   <Record>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

   </Record>

</Response Message Type to SAP>

Make sure the occurence of Record is 0 to unbounded.

Your data base response structure

<Response Message Type>

   <Statement_response>

         <row>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

         </row>

   </Statement_response>

</Response Message Type>

Make sure that row has occurence 0 to unbounded.

Do the one to mapping between

<Statement_response> to

<Response Message Type to SAP>

<row> to <Record>.

<OUTPUT FIELD 1> to </OUTPUT FIELD 1> 

<OUTPUT FIELD 2> to </OUTPUT FIELD 2>

<OUTPUT FIELD 3> to </OUTPUT FIELD 3>

Let me know if you still face any issues.

[Former Member · Accepted solution]

Hi Indrajit,

I modified the data type structures and mapping as per your suggestion but the same issue remains. Please note that only one row will be returned by the stored procedure. Do you have any other suggestions?

regards

Debansu

[Former Member · Accepted solution]

Hi,

I was finally able to resolve this issue. The error was in the name of the target interface. Thanks everyone.

regards

Debansu

[Former Member · Accepted solution]

Hi Debangsu,

What do you mean by error was in target interface ? Was it the node under Statement which was the problem.

My database element is defined as VARCHAR2, can I use VARCHAR on my SAP PI side or will that create a conflict.

Regards

Ravijeet

[Former Member · Accepted solution]

Hi Ravijeet,

If you are designing a synchronous proxy to JDBC scenario, the message types have to be named and designed as follows:

Request to Database

<Message Type>

     <Statement>

          <sp_stored procedure name>

          <table>

          <action>

          <Field 1>

               <isInput or isOutput>

               <type>

          </Field 1>

          <Field 2>

               <isInput or isOutput>

               <type>

          </Field 2>

          - - - - - - - - -

          - - - - - - - - -

          <Field n>

               <isInput or isOutput>

               <type>

          </Field n>

          </sp_stored procedure name>

     </Statement>

</Message Type>

Response from Database

<MT_DB_Interface name_Resquest_response>

     <Statement_response>

          <Field 1>

          <Field 2>

          - - - - - - - -

          - - - - - - - -

          <Field n>

     </Statement_response>

</<MT_DB_Interface name_Resquest_response>

Please note the response message type should have the words 'Resquest_response' at the end which will allow the system to wait for the response from the database. I had actually named the respnse differently.

Hope I have clarified your doubt.

regards

Debansu

[Former Member]

Hi

The correct response structure should be

<Statement_Response> 

             <row>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

         </row>

         </Statement_Response>

You don't need to provide the store procedure name in the resposne structure.

[Former Member]

Hi Ambrish and Indrajit,

Thanks.

I designed the structure of the response from the database as given below.

<Response Message Type>

   <Statement_response>

         <row>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

         </row>

   </Statement_response>

</Response Message Type>

Then I tried keeping the response structure as below

<Response Message Type>

   <Statement_response>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

    </Statement_response>

</Response Message Type>

In the meantime, the structure of the response back to SAP (to which the database response is mapped) were designed as provided below:

<Response Message Type to SAP>

   <Record>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

   </Record>

</Response Message Type to SAP>

I also tried designing the response to SAP as below

<Response Message Type to SAP>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

</Response Message Type to SAP>

I tried different permutation and combination of the above structures but failed to map the response back to SAP. Please note that I am getting back the response from the database and I am able to see it in SXMB_MONI of the XI development system but these values are not getting mapped to the response structure in SAP. I am getting the following error.

com.sap.aii.utilxi.misc.api.BaseRuntimeException: RuntimeException in Message-Mapping transformation: Cannot produce target element /Response Message Type to SAP. Queue does not have enougth values in context.

Please note, I am receiving a single row of data from the DB and the same needs to be mapped to the structure in SAP. It is a simple 1 to 1 mapping.

regards

Debansu

[ambrish_mishra]

Hi Debansu,

You can simply observe the response from Database and create a similar response message for SAP.

The structure below makes sense:

<Response Message Type to SAP>

   <Record>

            <OUTPUT FIELD 1>val 1</OUTPUT FIELD 1>

            <OUTPUT FIELD 2>val 2</OUTPUT FIELD 2>

            <OUTPUT FIELD 3>val 3</OUTPUT FIELD 3>

   </Record>

</Response Message Type to SAP>

You can resolve this error in message mapping easily. Just check that statement response should be mapped to Record.

You are close to the solution. 

Ambrish

[ambrish_mishra]

Hi Debansu,

Did you check how is input_field defined in the database?

Ambrish

[Former Member]

Hi,

I mapped the INPUT FIELD isInput=TRUE and type=VARCHAR but it threw up the following error.

com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table/stored proc. '<STORED_PROCEDURE_NAME>' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to '<STORED_PROCEDURE_NAME>' ORA-06550: line 1, column 7: PL/SQL: Statement ignored.

Is there anything else that neds to be done?

I will request the PL/SQL developer to change the input field type to VARCHAR from VARCHAR2.

regards

Debansu

[Former Member]

Hello,

I would suggest you to change field type to varchar in DB and then test the same. In addition to that, make sure that the number of arguments which u have defined in PI (JDBC structure) is exactly the same to what u have created in SP.

For response structure, u can refer my reply in below thread:

http://scn.sap.com/message/14285982#14285982

Thanks

Amit Srivastava

[Former Member]

From SAP help:

The following SQL data types are supported:

INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output),CLOB (input and output), CURSOR (output; only in conjunction with the Oracle JDBC driver).

So use VARCHAR instead of VARCHAR2

In addition to that, ur JDBC response structure is not correct?

[Former Member]

Amit,

Can you please suggest the correct response structure?

Ambrish,

The input_field is defined as an input parameter of type varchar2.

regards

Debansu

[former_member184720]

Change the Input paramter type in the Database from varchar2 to varchar.

[ambrish_mishra]

Hi Debansu,

First change the definition of this field to VARCHAR.

Your response structure is going to return records. You can have a data type structure like below:

Row

     FieldName1

     FieldName2

        ......................

          ..................

So this depends on the response structure which you are going to get back from database.

Hope it helps!

Ambrish