JDBC receiver error - stored procedure

[Former Member]

Hi Experts,

My scenario is File to JDBC, calling stored procedure using JDBC connection.

Getting below error in JDBC receiver communication channel, below is target structure. Can you please help on what is causing error

Error:

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'Storedprocedurename' (structure 'Statement'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'table' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

Target Structure:

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

<ns0:MT_Receiver xmlns:ns0="Namespace">

<Statement>

  <Storedprocedurename action="EXECUTE">

   <table>Storedprocedurename</table>

   <Delivery_Number isInput="true" type="CHAR">1234</Delivery_Number>

   <Order_Number isInput="true" type="CHAR">30296310</Order_Number>

   <Cust_PO_Number isInput="true" type="CHAR">474187</Cust_PO_Number>

   <Origin_Name isInput="true" type="CHAR">3011</Origin_Name>

   <Origin_Code isInput="true" type="CHAR">3011</Origin_Code>

   <Shipped_Datetime isInput="true" type="CHAR">20140114042121</Shipped_Datetime>

   <Seal_Numbers isInput="true" type="CHAR">-</Seal_Numbers>

   <Mode_Code isInput="true" type="CHAR">TRUCK</Mode_Code>

   <Manual_Carrier_Desc isInput="true" type="CHAR">WATSON POWELL INC</Manual_Carrier_Desc>

   <Tractor_ID isInput="true" type="CHAR">7</Tractor_ID>

   <Trailer_ID isInput="true" type="CHAR">5</Trailer_ID>

   <UOM isInput="true" type="CHAR">UOM</UOM>

   <Product_Code isInput="true" type="CHAR">PrdCd</Product_Code>

   <Gross isInput="true" type="CHAR">123</Gross>

  </Storedprocedurename>

</Statement>

</ns0:MT_Receiver>

Thank you.

[nabendu_sen]

Hi Sruthi,

Most probably there is mismatch between DataType which you defined for Stored Procedure and actual table. The attribute for the field 'table' is defined wrong as per the error.

SAP Library - SAP NetWeaver Exchange Infrastructure

<StatementName5>

<storedProcedureName action=” EXECUTE”>

    <table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

  </StatementName5>

Check:

Regards,

Nabendu.

[Former Member]

Hi Nabendu,

Thanks for your reply.

I have craeted data type by refering table and stored procedure in SQL server management studio. So i think it will not be a actual problem.

Passed actual stored procedure name to table field in mapping.

>>>>The attribute for the field 'table' is defined wrong as per the error.

we won't create attribute for table right? we will just pass stored procedure name.

i have referred these links, still not getting what is wrong... any other ideas?

Thank you.

[nabendu_sen]

Hi Sruthi,

Try like this one:

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

- <ns0:MT_DB_STOREDPROCEDURE xmlns:ns0="urn:pg-siemens-com:POCA0013:sample1">

- <StatementName>

- <XI_KDATA_KLOGDATA action="EXECUTE">

  <EMPNO isInput="TRUE" type="NUMERIC">901064</EMPNO>

  <EMPNAME isInput="TRUE" type="CHAR">Sateesh</EMPNAME>

  <ADDRESS isInput="TRUE" type="CHAR">Hyderabad</ADDRESS>

  </XI_KANLOGDATA>

  </StatementName>

- <StatementName>

- <XI_KANLOGDATA action="EXECUTE">

  <EMPNO isInput="TRUE" type="NUMERIC">901000</EMPNO>

  <EMPNAME isInput="TRUE" type="CHAR">Ashish</EMPNAME>

  <ADDRESS isInput="TRUE" type="CHAR">Bangalore</ADDRESS>

  </XI_KANLOGDATA>

  </StatementName>

  </StatementName>

  </ns0:MT_DB_STOREDPROCEDURE>

Instead of (without <table>)

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

- <ns0:MT_DB_STOREDPROCEDURE xmlns:ns0="urn:pg-com:POCA0013:sample1">

- <StatementName>

- <XI_KDATA_KLOGDATA action="EXECUTE">

<table>XI_KDATA_KLOGDATA</table>

  <EMPNO type="CHAR">901064</EMPNO>

  <EMPNAME type="CHAR">Sateesh</EMPNAME>

  </XI_KDATA_KLOGDATA>

- <XI_KDATA_KLOGDATA action="EXECUTE">

  <table>XI_KANLOGDATA_KLOGDATA</table>

  <EMPNO type="CHAR">901000</EMPNO>

  <EMPNAME type="CHAR">Ashish</EMPNAME>

  </XI_KDATA_KLOGDATA>

  </StatementName>

  </ns0:MT_DB_STOREDPROCEDURE>

Regards,

Nabendu.

[Former Member]

Hi Nabendu,

Thanks for your reply, its working.

Now got another error.

Unable to execute statement for table or stored procedure. 'NAME' (Structure 'Statement') due to com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function NAME has too many arguments specified.

can you help on this.

Thank you.

[former_member184720]

May be your target xml is still not in the expected format. Did you check the therad shared by Nabendu? It also addressed simillar error.

If your strcture is correct then cross check the number of parameters that your SP is expecting.

[Harish]

Hi Sruthi,

It seems to be issue with the data which you are passing to stored procedure. Please check the below discussion

regards,

Harish

[nabendu_sen]

Hi Sruthi,

As mentioned by in the thread provided by Harish:

"please compare the lenth of the value and cross check with DB field lenth."

Regards,

Nabendu.