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

[former_member197830]

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

[former_member190293 · Accepted solution]

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-procedu...

Regards, Evgeniy.

[former_member197830]

Hi Evgeniy,

Thank for your help and I have implemented Alert category method for above exception handling.

Once again thank for your help..

Thank you,

Narasaiah T.

[former_member197830]

Hi All,

In our scenario receiver is jdbc and if process is successfull it returns update count and if process fails like above case I want to send that error message to sender.

Please help me in that case how to create fault structure and how to achieve scenario.

Thank you,

Narasaiah T

[former_member190293]

Hi Narsaiah!

I've already given you the link to the blog, describing the approach for catching system faults. Just try it.

Regards, Evgeniy.

[former_member197830]

Hi Evgeniy,

Thank for the information and I have successfully resolved above issue and my interface testing results looks good .

Note : one small question i.e. is there any way to handle jdbc exception in PI side means if we send a wrong data to jdbc stored procedure then jdbc side will get exceptions and can we send that exception as a response to the sender(our scenario is SOAP to JDBC)?

ex: We got below error(I intentionally did a mistake and I got exception like below) in JDBC side and I want to send that as response to sender. is it possible ?

Thank you,

Narasaiah T.

[manoj_khavatkopp]

Use fault message type to handle such errors.

[former_member190293]

Hi Manoj!

I'm afaraid, that with fault message type we can handle only application exceptions defined by receiver system. In this case we have system fault.

Regards, Evgeniy.

[former_member190293]

Hi Narasaiah!

Yes, generaly you can handle such type of exception.

https://blogs.sap.com/2015/07/29/soap-faults-and-webservice-faults-from-3rd-party-webservices/commen...

Regards, Evgeniy.

[former_member197830]

Hi Evgeniy,

We got below issue while testing.

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'ts_ps_save_tank_move' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations

Note : what is the advantage adding CDATA tag to XML data type in message mapping ?

Thank you,

Narasaiah T

[former_member190293]

Hi Narsaiah!

CDATA is often used to mark the data inside the tag as character data that should be transferred "as is" by parser.

As for error: You (or your DB team) should perform DB side testing of the stored procedure using parameters in the same form as it's provided in your payload after mapping. It's SQL Server processing error and it should be debugged on server side.

Regards, Evgeniy.

[former_member190293]

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.

[former_member197830]

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

[former_member197830]

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

[former_member190293]

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.

[former_member197830]

Thank you so much Evgeniy..

Thank you,

Narasaiah T

[former_member190293]

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.

[former_member197830]

Hi Evgeniy,

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

Thank you,

Narasaiah T

[former_member197830]

Hi Evgeniy,

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

Thank you,

Narasaiah T

[former_member197830]

Hi,

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

Thank you,

Narasaiah T

[former_member190293]

Hi Narsaiah!

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

Regards, Evgeniy.