Stored procedure calling through PI

[former_member197830]

Hi Experts, 

I want to call stored procedure through PI to save Orders and my Stored procedure structure schema is like below.

Below is the user define table type schema(table type)

----------------------------------------------------------------------------

[dbo].[OrdersTableType] AS TABLE(

      [BATCH_ID] [nvarchar](24) NULL,

      [ACTY_TYPE] [int] NULL,

      [ACCOUNT_ID] [nvarchar](24) NULL,

      [PROD_ID] [bigint] NULL,

      [REQ_ID] [bigint] NULL,

      [START_TIME] [datetime] NULL,

      CARRIER_TO_ID] [bigint] NULL,

      [COMMENT] [nvarchar](256) NULL,

      [DEST_LOC_ID] [bigint] NULL,

      [DEST_TANK_ID] [bigint] NULL

I need to call storproc, p_save_orders, and pass in the following parameters:

--------------------------------------------------------------------------------------------------

Procedure [dbo].[p_save_orders]

    @scheduleID BIGINT = NULL,

    @tableData OrdersTableType READONLY,                 (OrdersTableType fields are given above)

    @batch_schedule_part_id NVARCHAR(64) = NULL,

    @startDate DATETIME = NULL,

    @overwrite INT = NULL

Can any one help us how to create structure at PI side using above schema ?

Thank you,

Narasaiah T

[former_member190293]

Hi Narsaiah!

If I understand you right you want to pass a table as input parameter to your stored procedure. Is it correct?

Regards, Evgeniy.

[former_member190293]

I'm not sure about others but if your DB provider is MS SQL Server you could use "XML" type parameter in SP instead of table type parameter. But this approach requires changing your stored procedure logic or developing some "wrapper" SP which will convert your XML parameter to table parameter and call your target SP.

Regards, Evgeniy.

[former_member197830]

What you said is correct. I want to pass table as input parameter to my stored procedure.

Thank you,

Narasaiah T.

[former_member197830]

If u have any example or link please share me.

Thank you,

Narasaiah T

[former_member190293]

Hi Narsaiah!

Sorry for delay with answer. I needed to check my previous suggestion .

Source message:

<MT_JDBC_Customers action="insert" insertedby="PID">

     <Customers>

          <Customer>

               <ID>500</ID>

               <Name>Item 500</Name>

          </Customer>

          <Customer>

               <ID>501</ID>

               <Name>Item 501</Name>

          </Customer>

     </Customers>

</MT_JDBC_Customers>

JDBC request message after mapping:

<ns0:MT_JDBC_StoredProc_Request xmlns:ns0="urn://train">

     <Statement>

          <dbTableName action="EXECUTE">

               <table>InsertCustomers</table>

               <XMLCustomers type="CLOB" isInput="true">![CDATA[<Customers> <Customer> <ID>500</ID> <Name>Item 500</Name> </Customer> <Customer> <ID>501</ID> <Name>Item 501</Name> </Customer> </Customers>]]</XMLCustomers>

               <ProcessedFlag type="VARCHAR" isInput="true">I</ProcessedFlag>

               <InsertedBy type="VARCHAR" isInput="true">PID</InsertedBy>

          </dbTableName>

     </Statement>

</ns0:MT_JDBC_StoredProc_Request>

MS SQL Server stored procedure code:

USE [RB_Test]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[InsertCustomers]

     @XMLCustomers as XML,

     @ProcessedFlag as nvarchar(1),

     @InsertedBy as nvarchar(10)

AS

BEGIN

     SET NOCOUNT ON;

     INSERT Customers (CustomerID, CustomerName, ProcessedFlag, InsertedBy)

          SELECT

               CUST.item.value('(ID)[1]', 'nvarchar(10)') as 'CustomerID',

               CUST.item.value('(Name)[1]', 'nvarchar(10)') as 'CustomerName',

               @ProcessedFlag as ProcessedFlag,

               @InsertedBy as InsertedBy

          FROM

               @XMLCustomers.nodes('/Customers/Customer') as CUST(item)

END

Result in DB:

Nothing difficult .

Regards, Evgeniy.

[former_member182412]

Nice one Evgeniy!

[former_member190293]

Thanks for your positive response, Praveen!

Regards, Evgeniy.

[former_member182412]

Hi Narsaiah,

Crate the structure as shown in this blog.

Regards,

Praveen.

[former_member197830]

Hi Praveen,

In my case tableData is of type OrdersTableType table data.

then how to configure tableData field in sap pi structure.

Thank you.

Narasaiah T

[former_member182412]

Hi Narsaiah,

tableType is not supported, the following sql data types are supported only.

Defining an EXECUTE Statement - Advanced Adapter Engine - SAP Library

You must specify the attribute type=<SQLDatatype> for all parameter types (IN, OUT).
It describes the valid SQL data type.
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 connection with the Oracle JDBC driver)

Regards,

Praveen.

[former_member197830]

Hi Praveen,

Any suggestions to achieve above functionality through PI.

is there any way to call stored procedure using native SQL query via sap pi ?

Thank you,

Narasaiah T

[GauravKant]

Hi Narsaiah,

Selecting native sql as msg protocol means u have to pass the sql statement as message content. this statement will be transferred to the database for processing without alteration.

PFB link for native sql.

Advanced Adapter Engine - SAP Library

one cons using native sql, If we use native SQL in receiver JDBC adapter we are limited to use only single line SQL statement (or) in case of PL/SQL block we can use multi line and we get only acknowledgement as result and not the output parameter values i.e the acknowledgement whether the PL/SQL block has executed or not i.e "SQL statement returned OK, 1 row(s) affected".

Regards,

Gaurav

[iaki_vila]

Hi Narsaiah,

May be you can do a java development to do it , but in my opinion the DB should to wrap its SP with another one with one structure according with PI restrictions.

Regards.