Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 30, 2013 at 05:06 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

  • Oct 23, 2013 at 04:44 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Oct 23, 2013 at 04:47 PM

    Hi Debansu,

    Did you check how is input_field defined in the database?

    Ambrish

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 24, 2013 at 04:58 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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