Problem executing a JDBC Insert on PI 7.1

[Former Member]

Hi,

I'm trying to do a simple insert to a database in 7.1. but it keeps failing.

The database table has been defined to allow nulls in every field.

The mapping has been tested and maps correctly resulting in the following XML:

<?xml version="1.0" encoding="UTF-8"?> <root> <StatementName1> <dbTableName action="insert"> <table>customer</table> <access> <forename>Smith</forename> <surname>John</surname> <address1>1 streets ville</address1> <address2>park avenue</address2> <address_town>MyTown</address_town> <address_county>Any County</address_county> </access> </dbTableName> </StatementName1> </root>

The error is:

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'null' (structure 'Surname'): java.lang.IndexOutOfBoundsException: Index: 1, Size: 1

I have checked the logs on the database side and no statement has been logged.

I have logSQLStatement as true on the communication channel. Again no statement logged.

Any help much appreciated.

Kind regards,

John

[Former Member]

Apparently I'm too used to creating the Receiver Determination in XI and PI is slightly different.

Missing an Interface determination and the message was passed through without going through the mapping.

John

[RaghuVamseedhar]

Hi John Lambert,

I understand, when you logon to database, you are able successfully execute

insert into customer (FORENAME,SURNAME) values ('john', 'smith');

Now let's debug why XML structure is not working.

Step 1:- In Interface Determination, do not refer to Operational Mapping (delete only OM from there, not the Receiver Message Interface). By doing so, you are not call the OM (which refer to Graphical Mapping (MM refer to Data Type (which may be wrong)). Note: As there is no OM, we need to send the exact payload (XML) required by receiver JDBC from Runtime Work Bench, for testing purpose.

Step 2:- So now, you have to construct equalant XML structure to String "insert into customer (FORENAME,SURNAME) values ('john', 'smith');".

SAP Help [Link1|http://help.sap.com/saphelp_nwpi711/helpdata/en/44/7b67fffde93673e10000000a114a6b/frameset.htm]

<StatementName>
  <dbTableName action=u201DINSERTu201D>
    <table>customer</table>
      <access>
         <FORENAME>john</FORENAME>
         <SURNAME>smith</SURNAME>
       </access>
</StatementName>

Step 3:- Now use the XML you have constructed, to test the scenario from RWB. If it is successful.

Step 4:- Now design your Message Mapping in such way that it will give output as above XML.

Step 4:- Invert the changes done in Step 1. Include OM in Interface Determination.

Regards,

Raghu_Vamsee

[Former Member]

Quick update:

Decided to move away from MySQL so I created another table on an Oracle Server.

Getting exactly the same error.

John

[baskar_gopalakrishnan2]

Declare all your elements uppercase in the target message.

I mean <SURNAME>value</SURNAME>

if that does not work follow the below pattern, this would work for sure.

<root>

<stmt>

<Customers action="SQL_DML">

<access> UPDATE Customers SET CompanyName=u2019$NAME$u2019, Address=u2019$ADDRESS$' WHERE CustomerID='$KEYFIELD$u2019

</access>

<key>

<NAME>Company</NAME>

<ADDRESS>Street 3 </ADDRESS>

<KEYFIELD>CO</KEYFIELD>

</key>

</Customers>

</stmt>

</root>

[former_member200962]

It would appear that it is this XML that has the problem as it has 'Surname'. The insert xml has 'surname'

this should not be an issue.

Are you sure that the mappping is generating surname (as it is maintained in the DB table) and not Surname?

<dbTableName action="insert">

did you try with INSERT instead of insert

The structure looks good

Edited by: abhishek salvi on Jan 27, 2011 3:08 PM

[Former Member]

I have tried 'INSERT' even though the statement is not getting as far as the database.

If I log into the database I can use:

insert into customer (FORENAME,SURNAME)

values ('john', 'smith');

insert into customer (forename,surname)

values ('john', 'smith');

or even

INSERT into customer (FOREname,SURname)

values ('john', 'lambert');

Kind regards,

John

[Former Member]

Guys,

Thank you for taking the time to look at my question:

Fields are all more than long enough and all, as mentioned previously, allow null values.

An export of the table shows it's definition:

CREATE TABLE `customer` ( `forename` varchar(40) default NULL, `surname` varchar(45) default NULL, `address1` varchar(45) default NULL, `address2` varchar(45) default NULL, `address_town` varchar(45) default NULL, `address_county` varchar(45) default NULL, `account_no` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`account_no`) )

The following is the data from which the insert XML is mapped:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?><!-- Call Adapter --> <Customer_Details xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> <Surname>John</Surname> <Forename>Smith</Forename> <Address> <Line1>1 streets ville</Line1> <Line2>park avenue</Line2> <Town>MyTown</Town> <County>Any County</County> </Address> </Customer_Details>

It would appear that it is this XML that has the problem as it has 'Surname'. The insert xml has 'surname'

As I mentioned previously. Database tracking is turned on and no statement would appear to be issued to the database.

Thanks again for the help,

John

[baskar_gopalakrishnan2]

Use describe command and see the table structure using toad or some database client tool. You send surname value as john but you have to check that column size as well mandatory or not.

Use the same values and do insert using toad or sqlplus and see what you get? I think surname column size is 1.

[former_member208856]

The insert statement is correct, only the problem is Field (SURNAME) in the table.

That field is Mandatory in the table & you are sending Null (Blank) value in that field during insertion.

Also check this from JDBC database administrator that Field Surname is Mandatory.