How to send a tabletype as Input paramter using JDBC adapter

[Former Member]

Hi All,

I have the following requirement :

I need to invoke a stored procedure from PI using the EXECUTE action and send the following data as input paramter wherein there is a tabletype "p_appl_obj"

Sample XML//:

<Statement>

<Requestmessage action="EXECUTE">

<table>XXX.yyy</table>

<p_prem_crm_guid type="VARCHAR" isInput="true">XYZ</p_prem_crm_guid>

<p_mast_ship type="VARCHAR" isInput="true">XYZ</p_mast_ship>

<p_appl_obj type="OBJECT" isInput="true">

<l_ucrserv_crm_guid_id type="VARCHAR"isInput="true">123</l_ucrserv_crm_guid_id>

<l_ucrserv_mast_ship type="VARCHAR"isInput="true">123</l_ucrserv_mast_ship>

<l_ucrserv_srvc_code type="VARCHAR"isInput="true">ABC</l_ucrserv_srvc_code>

<l_ucrserv_styp_code type="VARCHAR" isInput="true">ABC</l_ucrserv_styp_code>

</p_appl_obj>

<IsOutputPremID type="VARCHAR"isOutput="true">Constant</IsOutputPremID>

<IsOutputErrNo type="VARCHAR" isOutput="true">Constant</IsOutputErrNo>

<IsOutputErrMsg type="VARCHAR" isOutput="true">Constant</IsOutputErrMsg>

</Requestmessage>

</Statement>

THe problem I am facing is that I am not able to find a suitable datatype for sending the tabletype.. i tried STRUCt, OBJECT,SQLData and all are failing.

Can you please let me know whether something like this is technically possible ?If yes, How ?

Regards,

AC

[Former Member]

isInput was already set to TRUE.

FYI this is the XML going from PI

<?xml version="1.0" encoding="UTF-8"?>

<ns0:Appliance_Banner_in xmlns:ns0="****">

<Statement>

<Requestmessage action="EXECUTE">

<table>UIMSMGR.bgs_pi_poc_package.bgs_pi_poc_proc </table>

<p_prem_crm_guid type="VARCHAR" isInput="true">123</p_prem_crm_guid>

<p_mast_ship type="VARCHAR" isInput="true">123</p_mast_ship>

<p_appl_obj isInput="true">

<l_ucrserv_crm_guid_id type="VARCHAR" isInput="true">333</l_ucrserv_crm_guid_id>

<l_ucrserv_mast_ship type="VARCHAR" isInput="true">333</l_ucrserv_mast_ship>

<l_ucrserv_srvc_code type="VARCHAR" isInput="true">abc</l_ucrserv_srvc_code>

<l_ucrserv_styp_code type="VARCHAR" isInput="true">abc</l_ucrserv_styp_code>

</p_appl_obj>

<IsOutputPremID type="VARCHAR" isOutput="true">Constant</IsOutputPremID>

<IsOutputErrNo type="VARCHAR" isOutput="true">Constant</IsOutputErrNo>

<IsOutputErrMsg type="VARCHAR" isOutput="true">Constant</IsOutputErrMsg>

</Requestmessage>

</Statement>

</ns0:Appliance_Banner_in>

[Former Member]

The suggestions provided would not work as my question is not for how to create the element for main TABLE but rather to create the same for a Table type within in the Main table !!!

When i leave the type as BLANK and send an XML I get the following error:

java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'p_appl_obj' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

Please note that the table type is a part of the Table structure and I am facing issues in passing this tabletype.

My request message structure looks like the following :

Requestmessage

action

table

p_prem_crm_guid

p_mast_ship

p_appl_obj

---> l_ucrserv_crm_guid_id

---> l_ucrserv_mast_ship

---> l_ucrserv_srvc_code

---> l_ucrserv_styp_code

p_appl_obj is a structure within the table structure and I am facing issues in send this across as there are no standard SQLdatatype which would support sending this as a part of the XML

Regards,

AC

[baskar_gopalakrishnan2]

>>When i leave the type as BLANK and send an XML I get the following error:

java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'p_appl_obj' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

'p_appl_obj' - You can leave the type as blank but you have to declare attribute isInput = true in next line for it.

[GabrielSagaya]

No need to specify the data type for table type. leave it blank.

Please check this thread.

/people/peng.shen2/blog/2010/12/16/pi-jdbc-stored-procedure-test-scenario

[baskar_gopalakrishnan2]

The is the format you need to use ...

<StatementName>
<storedProcedureName action=u201D EXECUTEu201D>
<table>realStoredProcedureeName</table>
<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>
</storedProcedureName >
  </StatementName>

Follow help link

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/content.htm

p_appl_obj - Give category as Element and Type leave as blank

Edited by: Baskar Gopal on Apr 18, 2011 12:30 PM