JDBC stored procedure erroe

[Former Member]

Hi ,

I am mapping the source CREMAS02 segment E1LFBKM to storecProcedureaction so that when there are multiple bank accounts the storedProcedure executes multiple times.

<Statement_Name>

<storedProcedureName action="EXECUTE">

....

</storedProcedureName>

<storedProcedureName action="EXECUTE">

...

</storedProcedureName>

<storedProcedureName action="EXECUTE">

...

</storedProcedureName>

</Statement_Name>

Below is the actual XML after mapping.

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

- <ns0:MT_SP_Bank xmlns:ns0="http://test.com">

- <Statement_Name>

- <storedProcedureName action="EXECUTE">

<table>spr_Bank</table>

<BANKS type="Char">BOA</BANKS>

<BANKL type="char">44400202</BANKL>

<BANKN type="char">00022-0</BANKN>

<LIFNR type="char">9000000218</LIFNR>

<KOINH type="char">varchar</KOINH>

<BKONT type="char">01</BKONT>

<BANKA type="varchar">IS.A.</BANKA>

</storedProcedureName>

- <storedProcedureName action="EXECUTE">

<table>spr_Bank</table>

<BANKS type="Char">BOA</BANKS>

<BANKL type="char">55500207</BANKL>

<BANKN type="char">09500-2</BANKN>

<LIFNR type="char">9000000218</LIFNR>

<KOINH type="char">varchar</KOINH>

<BKONT type="char">02</BKONT>

<BANKA type="varchar">S.A.</BANKA>

</storedProcedureName>

</Statement_Name>

</ns0:MT_SP_Bank>

I keep getting the following error when i send a IDOC from SAP to SQL . In RWB it shows message is scheduled and when i process it shows system error with message below. Is anything wrong with the above XML?

Could not execute statement for table/stored proc. "spr_Bank" (structure "Statement_Name") due to 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 !)

Thank you ,

Teresa

Error JDBC Message processing failed, due to Error processing request in sax parser: Error when executing statement for table/stored proc. 'spr_Bank' (structure 'Statement_Name'): 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 !)

[Former Member · Accepted solution]

hi Teresa,

In attribute "type" of your stored procedure' sfield, be sure to use the same type without error of lower/uppercase, than you have in your Database..

in BANKS, you have "Char" (uppercase on "C" => bad), whereas for other fields you "char" (lowercase on "c" => good).

<BANKS type="Char">BOA</BANKS> 

I think it's the reason why you have this error: 'type="<SQL-type>"' attribute is missing for element 'table'

regards.

Mickael

[Former Member · Accepted solution]

I am still gettin the same error. I did change from Char to lower case char.

MP: exception caught with cause com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'spr_Bank' (structure 'Statement_Name'): 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 !)

Thanks,

Teresa

Edited by: Teresa lytle on Nov 11, 2011 7:11 PM

[rajasekhar_reddy14 · Accepted solution]

Hi,

Please correct your stored procedure structure .

Regards,

Raj

[Former Member · Accepted solution]

Hi Teresa,

Your first error is on your field <table>. Focus you on it: is it really spr_Bank ? (by respecting lowercase / uppercase)

Have a look on, [SAP help|http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm], have you respect this ?

 If you use the optional <table> element, the value specified here is used as the stored procedure name.....

Aftert that, check with your database provider these points:

- the naming of your stored procedure name (by respecting lowercase / uppercase)

- the naming of your field (by respecting lowercase / uppercase)

- the naming of field type (char or CHAR) (by respecting lowercase / uppercase)

Else in mine, on each field (not <table>) I used also the attribute: isInput=u201Dtrueu201D, in addition to type=char, but if you missed it, by default is "input", so I don't think it that.

I hope this help you to find where is your tiny error.

Regards.

Mickael

[Former Member · Accepted solution]

Thanks Mickael. I will check on all those parameters tomorrow. I am planning on redoing from scratch the creation of data types , message types and operation mapping.

Regards,

Teresa

[Former Member · Accepted solution]

Hi Mickael,

I checked everything. My XML format works when I send one bank information. But I think it is erroring out when it see two two stored procedure names and keeps giving me this error as earlier. Have you worked on this kind of scenario before?

<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'spr_Bank' (structure 'StatementName'): 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 !)</SAP:AdditionalText>

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

- <ns0:MT_SP_LFBK_N xmlns:ns0="http://test.com/Vendor">

- <StatementName>

- <storedProcedureName action="EXECUTE">

<table>spr_Bank</table>

<BANKS type="char">BOA</BANKS>

<BANKL type="char">33300202</BANKL>

<BANKN type="char">01010-0</BANKN>

<LIFNR type="char">9000000218</LIFNR>

<KOINH type="char">varchar</KOINH>

<BKONT type="char">01</BKONT>

<BANKA type="char">S.A.</BANKA>

</storedProcedureName>

- <storedProcedureName action="EXECUTE">

<table>spr_Bank</table>

<BANKS type="char">BOA</BANKS>

<BANKL type="char">44400207</BANKL>

<BANKN type="char">99500-2</BANKN>

<LIFNR type="char">9000000218</LIFNR>

<KOINH type="char">varchar</KOINH>

<BKONT type="char">02</BKONT>

<BANKA type="char">S S.A.</BANKA>

</storedProcedureName>

</StatementName>

</ns0:MT_SP_LFBK_N>

[Former Member · Accepted solution]

Hi Teresa

The message you posted does not look correct.

According to help page [Document Formats for the Receiver JDBC Adapter|http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm], you should replicate the StatementName node and inside it create a single storedProcedureName node.

Regards,

Giuseppe

[Former Member · Accepted solution]

Hi Giuseppe,

Do you have a sample XML structure? How can I replicate the Statement Name? That maps to IDOC segment and stored procedure name maps to E1LFBKM.

Thanks,

Teresa

[Former Member · Accepted solution]

Hi Teresa

According to example from your previous post, your message should become like this:

<?xml version="1.0" encoding="UTF-8" ?>
<ns0:MT_SP_LFBK_N xmlns:ns0="http://test.com/Vendor">
  <StatementName>
    <storedProcedureName action="EXECUTE">
      <table>spr_Bank</table>
      <BANKS type="char">BOA</BANKS>
      <BANKL type="char">33300202</BANKL>
      <BANKN type="char">01010-0</BANKN>
      <LIFNR type="char">9000000218</LIFNR>
      <KOINH type="char">varchar</KOINH>
      <BKONT type="char">01</BKONT>
      <BANKA type="char">S.A.</BANKA>
    </storedProcedureName>
  </StatementName>
  <StatementName>
    <storedProcedureName action="EXECUTE">
      <table>spr_Bank</table>
      <BANKS type="char">BOA</BANKS>
      <BANKL type="char">44400207</BANKL>
      <BANKN type="char">99500-2</BANKN>
      <LIFNR type="char">9000000218</LIFNR>
      <KOINH type="char">varchar</KOINH>
      <BKONT type="char">02</BKONT>
      <BANKA type="char">S S.A.</BANKA>
    </storedProcedureName>
  </StatementName>
</ns0:MT_SP_LFBK_N>

Map StatementName with E1LFBKM segment and storedProcedureName with a constant.

It should work.

Regards,

Giuseppe

[Former Member · Accepted solution]

It worked . Thank you very much . Below is the correct structure

Teresa

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

- <ns0:MT_SP_LFBK_N xmlns:ns0="http://test.com/Vendor">

- <StatementName>

- <storedProcedureName action="EXECUTE">

<table>spr_LFBK</table>

<BANKS type="char">BOA</BANKS>

<BANKL type="char">99900202</BANKL>

<BANKN type="char">99010-0</BANKN>

<LIFNR type="char">9000000218</LIFNR>

<KOINH type="char">varchar</KOINH>

<BKONT type="char">01</BKONT>

<BANKA type="char"> S.A.</BANKA>

</storedProcedureName>

</StatementName>

- <StatementName>

- <storedProcedureName action="EXECUTE">

<table>spr_LFBK</table>

<BANKS type="char">BOA</BANKS>

<BANKL type="char">22300207</BANKL>

<BANKN type="char">67800-2</BANKN>

<LIFNR type="char">9000000218</LIFNR>

<KOINH type="char">varchar</KOINH>

<BKONT type="char">02</BKONT>

<BANKA type="char">S.A.</BANKA>

</storedProcedureName>

</StatementName>

</ns0:MT_SP_LFBK_N>

[Former Member · Accepted solution]

Hi Teresa,

yes I did it by the past without error. Docs are at home....

hopefully, Giuseppe found error.

regards

mickael

[former_member240495]

Thanks!!! This is helpful.

[Former Member]

Before all, check if your Stored Procedure name is correct (usually is PACKAGE_NAME.STORED_PROCEDURE and not directly with Procedure Name)

Then check the Receiver Structure:

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

[rajasekhar_reddy14]

Hi Teresa,

first make sure that stored procedure name is correct or not and dont missing attribute type for every field(define type in caps VARCHAR,CHAR...) like that.

Request data base team to share the table structure with type, based on that create in PI .

Regards,

Raj