JDBC Receiver adapter Stored procedure

[Former Member]

We are using stored procedure to insert/update the data into SQL database, for this I define data type  as below

But the JDBC call is failing with error " com.sap.engine.interfaces.messaging.api.exception.MessagingException:
Error processing request in sax parser: Error when executing statement for
table/stored proc. 'insert_IDOCRec' (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 !)"

Can you please help me why the JDBC call is failing with the above error.

[nikhil_bose]

Data Type seems wrong.

DT_INSPLOT_JDBC_REC_REQ

->STATEMENT

-->INSERT_IDOCREC // this can be stored procedure name or keep as 'TABLE' itself.

-->Action  // this is an attribute to above 'Element'.

--->Table // 'element' 0..unbounded this is input parameters to stored procedure.

---->Parameter1 //element 1-1

---->IsInput //this is attribute to Parameter1

check message mapping test result. If the issue is not resolved, paster message mapping result

[Former Member]

I think your struct is wrong,the right is following:

DT_INSPLOT_JDBC_REC_REQ

          STATEMENT

                    insert_IDOCRec     Element     1..unbounded

                    action                    Attribute     xsd:string     required

                    table                     Element          xsd:string     1

                    field1                      Element    1

                         isInput               Attribute     xsd:boolean     required

                         type                 Attribute     xsd:boolean     required

                    field2                      Element    1

                         isInput               Attribute     xsd:boolean     required

                         type                 Attribute     xsd:boolean     required

from the log you send to us, the struct miss the table element.

[engswee]

Hi Srinivas

The screenshot is very small, can't really see your Data type definition.

Can you also please provide the payload that failed?

From first glance, it might be the action attribute is missing from the payload. You need to map it with UPDATE_INSERT. You can check the following link for more details on the correct XML structure required for JDBC calls.

Defining XML Documents for Message Protocol XML SQL Format - Configuring the JDBC Adapter in the Int...

Rgds

Eng Swee

[Former Member]

We are able to insert a single record in the database using Stored Procedure

But we need to insert multiple records in a single database call

I defined my datatype as below

DT_INSPLOT_JDBC_REC_REQ

          STATEMENT

                    insert_IDOCRec     Element     1..unbounded

                    action                    Attribute

                    field1                      Element    1

                         isInput               Attribute     xsd:boolean     required

                         type                 Attribute     xsd:boolean     required

                    field2                      Element    1

                         isInput               Attribute     xsd:boolean     required

                         type                 Attribute     xsd:boolean     required

To execute the stored procedure i'm passing 'EXECUTE' in the action attribute

Do we need to repeat the STATEMENT for every record.

Thanks

Srini

[former_member312864]

Hi,

Each statement will act as a row of record . I think you need to change the occurances of statement to 1..Unbounded to update multiple rows of records.

Regards,

[Former Member]

Hi

Please check the following help link to create the structure.

https://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards

Osman

[Former Member]

I know with multiple "STATEMENT" we can insert multiple records.

Using a single "STAEMENT" is there any way in the store procedure to insert multiple records.

Is it possible with the store procedure to handle multiple records with in a single 'STATEMENT'?

Thanks

Srini