Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

14 Answers

  • Best Answer
    Nov 06, 2016 at 06:34 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 06, 2016 at 03:50 AM

    Hi Narsaiah!

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

    Regards, Evgeniy.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 06, 2016 at 06:27 AM

    Hi,

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

    Thank you,

    Narasaiah T

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 06, 2016 at 04:50 PM

    Hi Evgeniy,

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

    Thank you,

    Narasaiah T

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 07, 2016 at 01:04 PM

    Hi Evgeniy,

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

    Thank you,

    Narasaiah T

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 08, 2016 at 02:03 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 08, 2016 at 03:19 AM

    Thank you so much Evgeniy..

    Thank you,

    Narasaiah T

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 09, 2016 at 07:00 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 10, 2016 at 02:40 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 10, 2016 at 04:32 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded