Action field changing in database scenario

[Former Member]

I am using receiver JDBC adapter. When I get the data, I have to update or inert into the sql database.

The problem is when i get a record, there is employee number in the record. I have to go to database, check if the record for employee number is there.

If the record is there, I have to update. If the record is not there, I have to insert.

Employee number is a unique key. Using that I have to find out from database if record exists or not

How do I do this?

[Former Member · Accepted solution]

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. 'FROM_ABCD' (structure 'Statement'): java.sql.SQLException: FATAL ERROR document format: structure 'Statement', key element 'EmployeeNumber' contains no values

I am getting this error now. However, I can see the key element EmployeeNumber value in the target payload

[former_member184720 · Accepted solution]

>>>I am getting this error now. However, I can see the key element EmployeeNumber value in the target payload

Do you see the value under both Access and Key segments? Can you paste the xml?

[Former Member · Accepted solution]

this is my payload

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

<ns0:MT_XYZ xmlns:ns0="xxxx">

<Statement><ABCD action="UPDATE_INSERT">

<table>ABCD</table></ABCD>

<Access>

<Employeename>aravind</Employeename>

<Employeenumber>m1234</Employeenumber>

</Access>

<Key>

<Employeenumber>m1234</Employeenumber>

</Key>

</Statement>

<Statement>

<ABCD action="UPDATE_INSERT">

<table>ABCD</table></ABCD>

<Access>

<Employeename>anil</Employeename>

<Employeenumber>m3452</Employeenumber>

</Access>

<Key>

<Employeenumber>m3452</Employeenumber>

</Key>

</Statement>

</ns0:MT_XYZ>

However, I notice that there is no closing tag for the table name in it.

Under statement, I gave the actual table name and mapped it with CONSTANT[] empty.. this should appear before the statement closing tag and it is not appearing.. i think thats the reason for error

action attribute was UPDATE_INSERT

table was mapped with the actaual table name using a constant

[former_member184720 · Accepted solution]

Problem is with your structure..

You have defined <ABCD> and <Access> at the same level.

Access should be one level down to <ABCD>

like

<Statement>

<ABCD action="UPDATE_INSERT">

<table>ABCD</table>

<Access>

<Employeename>aravind</Employeename>

<Employeenumber>m1234</Employeenumber>

</Access>

<Key>

<Employeenumber>m1234</Employeenumber>

</Key>

</ABCD>

</Statement>

refer to sap help documentation..

Document Formats for the Receiver JDBC Adapter - SAP NetWeaver Exchange Infrastructure - SAP Library

sample structure with screenshots:

SAP PI Adapter Series: JDBC Adapter Configuration

[Former Member · Accepted solution]

now structure is coming fine..

this is the querry that I am getting in com channel

UPDATE ABCD SET Employeename=aravind, Employeenumber=m1234, WHERE (Employeename=aravind)

INSERT INTO  ABCD (id, Employeenumber) VALUES (aravind)

i think that is fine..

now i am getting another error

Unable to execute statement for table or stored procedure. 'ABCD' (Structure 'Statement') due to com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'STREET', table 'sap-interface.dbo.ABCD'; column does not allow nulls. INSERT fails.

street is a non mandatory field in PI structure, but it is under access tab.. i have made it non mandatory but databse is not accepting that

[iaki_vila · Accepted solution]

Hi Midhun,

Unable to execute statement for table or stored procedure. 'ABCD' (Structure 'Statement') due to com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'STREET', table 'sap-interface.dbo.ABCD'; column does not allow nulls. INSERT fails

You only have two option, talk with the DB team and try to convert the STREET field not mandatory, or to try to pass a blank value (<STREET> </STREET>) if the field street is not in PI. You can do it in the message mapping with the functions IF-THEN.

For example:

Regards

Message was edited by: Iñaki Vila

[Former Member · Accepted solution]

thanks a lot Inaki..

it's working fine now...

successfully delivered to jdbc

[Former Member · Accepted solution]

where is that = function? I do not see it in single stack is it same aas EqualS

[Former Member · Accepted solution]

From Text functions you'll get equalS. You can use that.

Please go through this link you'll understand standard functions.

Standard Functions in PI 7.0 - Process Integration - SCN Wiki

Regards,

Suhale Shaik.

[Former Member]

Now I am getting new error

INSERT INTO  ABCD () VALUES ()

Unable to execute statement for table or stored procedure. 'ABCD' (Structure 'Statement') due to com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

[rathansekhar]

Hi,

Check the structure.

it should be like below.

Regards,

Rathan.

[Former Member]

This is how my structure looks like

[iaki_vila]

Hi Midhun,

If you are using UPDATE_INSERT action you need to determine the KEY tag, the KEY is like the clause WHERE in SQL.

In the Access you have to set the values.

For example you have shared this XML:

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_XYZ xmlns:ns0="urn:dfgh">
<Statement>
<ABCD Action="INSERT">
<table></table></ABCD>
<Access>
<Employeename>aravind</Employeename>
<Employeenumber>m1234</Employeenumber>
</Statement>

It's totally wrong, MT_XYZ tag is not closed, Acces is not close, the table tag is without the table, you have set INSERT instead of UPDATE_INSERT, in UPDATE_INSERT you would need the Key structure, and </ABCD> should be just up to </Statement>.

Please check my below XML structure or the other structures shared for the SCN members in this thread.

Regards.

[Former Member]

this is my current xml

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

<ns0:MT_XYZxmlns:ns0="xxxxx">

<Statement><ABCD action="UPDATE_INSERT">

<table>ABCD</table>

<Access>

<Employeename>aravind</Employeename>

<Employeenumber>m1234</Employeenumber>

</Access>

<Key>

<Employeenumber>m1234</Employeenumber>

</Key>

</ABCD>

</Statement>

</ns0:MT_XYZ>

JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'ABCD' (structure 'Statement'): java.sql.SQLException: FATAL ERROR document format: structure 'Statement', key element 'Employeenumber' contains no values

[praveen_sutra]

Hi Midhun,

I would still suggest please enable logs and u can see what SQL statement is getting formed and u can directly verify it with DB team.

how to enable logging please follow below blog.

SAPTechnical.COM - How to enable the log to track the SQL statement in PI/XI

This is easier to debug.

thanks and regards,

Praveen T

[Former Member]

I activated the logSQLstatement to true

but I am NOT seeeing any additional log in PI single stack communication channel monitoring logs

[Former Member]

Please use logSQLStatement. You are using logSQLstatement.

Check it once.

Regards,

Suhale Shaik.

[praveen_sutra]

Hi Midhun,

Please restart the channel.

After restart it should work.

thanks and regards,

Praveen T

[Former Member]

I started and restarted the communication channel!

I am unable to see the log regarding the sql statement.

I am on sngle stack PI. I selected the respective com channel. I clicked on the message id. It took me to the message monitoring log and there I am not able to see the sql statement that PI has created!

[iaki_vila]

Hi Midhun,

You have to use the UPDATE_INSERT statement. Check here Defining an UPDATE_INSERT Statement - Configuring the JDBC Adapter in the Integration Directory/PCK ...

Regards.

[Former Member]

Thanks a lot Inaki

In the mapping, my structure is like this

MT_XYZ

   Statement

        dbTableName

               action

               <TABLENAME>

          access

               field 1

               field 2

               field 3

I mapped MT_XYZ to the sender message type

Statement to the row under sender mesage type

action is attrbute and given UPDATE_INSERT

I mapped dbTableName and access to CONSTANT [] Is that correct?

I used the real table name under action in dbTableName node. Should I use Table and map it to a constant with the table name?

[praveen_sutra]

Hi Midhun,

AFAIK you need not map MT_XYZ, even if you do its fine.

Yes action is attribute for node dbTableName.

And <table>realDbTableName</table> you should map with a constant field with table name to it.

hope this helps.

thanks and regards,

Praveen T

[Former Member]

I am getting the below error

Exception caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. 'dbTableName' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'dbTableName'.

[iaki_vila]

Hi Midhun,

You could use this XML or similar:

<?xml version="1.0" encoding="UTF-8"?>
<MT_XYZ>
    <STATEMENT>
        <my_dbTableName ACTION="UPDATE_INSERT">
            <TABLE>my_dbTableName</TABLE>
            <ACCESS>
                <field1>value1</field1>
                <field2>value2</field2>
                <field3>value3</field3>
                <Employee_number>Value_EmpNum</Employee_number>
            </ACCESS>
            <KEY>
                <Employee_number>Value_EmpNum</Employee_number>
            </KEY>
        </my_dbTableName>
    </STATEMENT>
</MT_XYZ>

I think you don't need to create the tag <my_dbTableName> if you set the name of the table in <TABLE>, but i should do it to avoid problems.

Pay attention that the attribute ACTION and the tag TABLE can be filled by constants.

Regards.

[Former Member]

JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. '' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'.

Now I am getting this error

[praveen_sutra]

Hi Midhun,

Please use below blog to check the logs of what SQL statement is getting formed.

http://www.saptechnical.com/Tips/XI/SQL/Log.htm

and then show us the screenshot.

hope this helps.

thanks and regards,

Praveen T

[iaki_vila]

Hi Midhun,

Could you share your output XML?, you can change the values for privacy. The problem seems to be in the KEY structure.

Regards.

[Former Member]

This is the structure of the receiver database table when I imported from ECC

ABCD.resultSet                                          Message

      ABCD.resultSet                                   Element

               row                                             Element

                    Field1                                    Element

                    Field2                                   Element

This is the structure I created in mapping

MT_XYZ

     Statement

          ABCD

               Action

               table

          Access

               Field1

               Field2

               Field3

Is action case sensistive? I used Action in PI structure

Also, I see resultSet and row in the structure in database but I am not usng them in pi side

Its a receiver adapter scenario

[Former Member]

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

<ns0:MT_XYZ xmlns:ns0="urn:dfgh">

<Statement>

<ABCD Action="INSERT">

<table></table></ABCD>

<Access>

<Employeename>aravind</Employeename>

<Employeenumber>m1234</Employeenumber>

</Statement>

this is the output xml

[Former Member]

Hi Midhun,

Use like this

For Action Map Constant enter the value UPDATE_INSERT.

For Table Map Constant enter the value Table name like ABCD.

Regards,

Suhale Shaik.

[Former Member]

See I have a node like this:

Statement

     ABCD

          Action

          table

I mapped action to UPDATE_INSERT and table to ABCD

Now what should I map ABCD to?

I mapped it to a constant with no value

[Former Member]

Make your Structure like this first.

StatementName

   dbTableName------->Map Constant

       action--------> Map Constant UPDATE_INSERT

       table--------> Map Constant Table Name

       access

          field1

          field2

As Inaki mentioned a link Please follow that.

If you want to know the error

Receiver Adapter->Advanced tab->Check Advanced Mode-> in the Additional Parameters section

Enter logSQLStatement and value as true.

Regards,

Suhale Shaik.

[rathansekhar]

Hi,

Now what should I map ABCD to?

No need to Map ABCD.

For Example.

As per your structure.

Statement --Need to Map Main Node like (DATA 0-unbound or 0-1)

ABCD -- Mapping is not required leave it blank.

Action-- (UPDATE_INSERT)

table-- (ABCD. Actual table name which is maintained in DB)

Reagrds,

Rathan.