Skip to Content
0

how to call SQL server table type read only parameter in sap pi stored procedure calling?

Nov 05, 2016 at 06:38 PM

317

avatar image

Hi All,

can one explain me how to call SQL server table type read-only parameter in sap pi stored procedure calling(i.e, receiver JDBC Mapping structure of table type read only parameter).

how to map table type read-only at stored procedure receiver mapping i.e, to processing bulk/batch records through stored procedure.

Thank you,

Narasaiah T

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

14 Answers

Best Answer
Evgeniy Kolmakov Nov 06, 2016 at 06:34 AM
0

Hi Narasaiah!

Table type parameters are not supported in PI by default but you can try this approach:

https://blogs.sap.com/2016/09/30/using-recordset-as-input-parameter-for-ms-sql-server-stored-procedure-call/

Regards, Evgeniy.

Share
10 |10000 characters needed characters left characters exceeded
Evgeniy Kolmakov Nov 06, 2016 at 03:50 AM
0

Hi Narsaiah!

Are you talking about passing table type parameter to stored procedure or getting table type parameter as stored procedure response?

Regards, Evgeniy.

Share
10 |10000 characters needed characters left characters exceeded
NARSAIAH THOTTEMPUDI Nov 06, 2016 at 06:27 AM
0

Hi,

I am talking about passing table type parameter to stored procedure.

Thank you,

Narasaiah T

Share
10 |10000 characters needed characters left characters exceeded
NARSAIAH THOTTEMPUDI Nov 06, 2016 at 04:50 PM
0

Hi Evgeniy,

Thanks for your help and I will try to implement interface as for your sugessions.

Thank you,

Narasaiah T

Share
10 |10000 characters needed characters left characters exceeded
NARSAIAH THOTTEMPUDI Nov 07, 2016 at 01:04 PM
0

Hi Evgeniy,

Can you please help me how to add/wrapped CDATA tag for Customers using graphical mapping.

Thank you,

Narasaiah T

Share
10 |10000 characters needed characters left characters exceeded
Evgeniy Kolmakov Nov 08, 2016 at 02:03 AM
0

Hi Narsaiah!

You can use "Concat" standard function in graphical mapping or use UDF like this:

public String putInCDATA(String srcStr, Container container) throws StreamTransformationException{
if (srcStr == null || srcStr.trim().equals(""))
return "";
else
return "![CDATA[" + srcStr.trim() + "]]";
}

Regards, Evgeniy.

Share
10 |10000 characters needed characters left characters exceeded
NARSAIAH THOTTEMPUDI Nov 08, 2016 at 03:19 AM
0

Thank you so much Evgeniy..

Thank you,

Narasaiah T

Share
10 |10000 characters needed characters left characters exceeded
NARSAIAH THOTTEMPUDI Nov 09, 2016 at 07:00 PM
0

Hi Evgeniy,

As per your suggestion, We have changed stored procedure structure as Table type to XML type, while creating JDBC structure I found some difficulties and please see below snapshots.

your JDBC structure:

Please help how to create jdbc receiver structure data type and while mapping how use return as xml.

Note : I have create JDBC structure as per your suggestion like below.

in mapping I have done like below:

while testing in mapping

But I got two source item into two into two table type in receiver JDBC structure.

my jdbc structure is like below.

Please help how add my two items into receiver JDBC structure as like your jdbc structure.

Thank you,

Narasaiah T


jdbc1.png (43.7 kB)
recv1.png (31.9 kB)
mm1.png (40.9 kB)
mm2.png (46.1 kB)
mm3.png (45.9 kB)
mm3.png (47.4 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Narasaiah!

First, you should take XML tree of parent element of your recordset - "tank_movements" element. Thus, you'll get your recordset inside one parameter.

Second, I'd suggest to remove XML header from your tree:

Regards, Evgeniy.

0
NARSAIAH THOTTEMPUDI Nov 10, 2016 at 02:40 AM
0

Hi Evgeniy,

As per above suggestion, I have changes my message mapping structure and removed XML header.

test mapping results:

Please suggest me if any thing required and I found you have something like SetLeafNode function in your message mapping, is there any advantage of using SetLeafNode function in message mapping.

Note: in my test results I found XML begin and end tags as '<' and '>' and is it OK or can we need to change as <>.

Thank you,

Narasaiah T


mm1.png (21.6 kB)
mm2.png (40.4 kB)
Share
10 |10000 characters needed characters left characters exceeded
Evgeniy Kolmakov Nov 10, 2016 at 04:32 AM
0

Hi Narasaiah!

At first sight your structure seems to be correct. As further step you need to perform end-to-end testing to check for possible errors.

I use SetLeafNode function to rename target nodes in my mapping :-)

Why? JDBC document structure requires hard-coded element names for parameters. This mean, that you have to create different structures for calling different SPs, for example. I don't want to do that. So I designed one "common" structure where I use repeatable node named "parameter" for setting SP's parameters values. And in mapping I use above function to rename needed element "parameter" according to real parameter name in SP.

Regards, Evgeniy.

Share
10 |10000 characters needed characters left characters exceeded