Very strange error : IDOC to jdbc scenario

[Former Member]

Hi

I am getting a very strange error when I see the log for the idoc to jdbc scenario that I am building.

The mapping test shows the header record and line item record properly in the mapping result.

insert, access nodes, fields with access nodes are all formed correctly.

However the communication channel log shows below error.

Could not execute statement for table/stored proc. "tblTestItem" (structure "Item") due to com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

JDBC Message processing failed, due to Error processing request in sax parser: Error when executing statement for table/stored proc. 'tblTestItem' (structure 'Item'): com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

The message status was set to WAIT.

The query that I see in the log is :INSERT INTO tblTestItem () VALUES ()

However mapping tool shows that all nodes are correctly formed.

Please assist.

[Former Member]

use "logSQLStatement" and its value as "true" under JDBC receiver adapter (advanced tab) and chk the sql statement formed at runtime..

[Former Member]

Hi

The stuff I have pasted is from the log.

Also the INSERT statement has not considered anything from the Payload. When I paste the payload in message mapping tool everything looks ok.

This has suddenly stopped working which is strange.

[Former Member]

Hi Gary,

So do a CPAcache refresh to refresh the cache of your Adapters...

Regards

Mickael

[Former Member]

Hi

Please can you guide me as to how to do a CPAcache refresh.

[Former Member]

Within your browser: http:// your pi system :port /CPACache/refresh?mode=full

Regards,

Konrad

[Former Member]

Just to add to the previous post.

In my outgoing payload I can see that the nodes are properly formed.

However the nodes are not getting converted into the corresponding SQL statement.

For the item table the converted payload is as below

<ns0:MT_XXXXX">

- <Item>

- <dbTableName action="INSERT">

<table>tblItem</table>

- <access>

<IDocDate>20120126</IDocDate>

<IDocTime>171926</IDocTime>

<InvoiceNo>0012345</InvoiceNo>

<InvoiceItem>00010</InvoiceItem>

<PONo>,,,,...</PONo>

- </access>

- </dbTableName>

- </Item>

</ns0:MT_XXXXX>

However the query that I see in the communication channel log is below

INSERT INTO tblItem () VALUES ()

Dont know where the process is going wrong : while converting the target xml structure into SQL query?

What could be the reasons for this?

[baskar_gopalakrishnan2]

Your jdbc reciever strucure seems correct. That'swhy you get communication error log for insert correct. Check at the backend you do insert operation correct and you have access to insert on that particular table or the insert can do only for the first time for a particular primary key. If you try the same data further you get insert constraint error? Do you see such error? Also I would recommend you to reactivate the jdbc receiver and stop and restart the channel.

[Former Member]

Hi

I am still getting this issue even after the below steps so kind of stuck - actually very much stuck.

1. Restart of the dev environment

2. Tried with a new extension of the idoc

In the communication channel log for the JDBC receiver channel I still see

INSERT INTO tblItem () VALUES ()

The payload that I see in the message content tab is as follows

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

- <ns0:MT_XXX_XXXX xmlns:ns0="http://capita.com/pi/XXXX">

- <Statement>

- <dbTableName action="INSERT">

<table>tblItem</table>

</dbTableName>

- <access>

<InvoiceNo>0000000009293467</InvoiceNo>

</access>

</Statement>

</ns0:MT_XXX_XXXX>

How do I debug the jdbc adapter to see how it converts the above xml output from the mapping step (note that this output is from an actual run and not test tool) to a sql query.

Very strange that the column names are present in the target payload but it is not reflecting in the query.

I have other tables being populated with the same jdbc receiver communication channel which makes this more and more confusing.

PLEASE ADVICE.

[Former Member]

u have to close dbTableName tag after access...just cross chk the structure

http://help.sap.com/saphelp_nwpi711/helpdata/en/44/7b67fffde93673e10000000a114a6b/content.htm

[GabrielSagaya]

Hi,

I think you are populating numeric values to the target structure.

check for the datatype in Receiver JDBC structure whether it is numeric or varchar?

If number/date field then you need to use the attribute, hasQuot= NO

[Former Member]

hi Gary,

I don't think it's strange, as you talk to an "INSERT" query in your log, whereas you talk also to "EXECUTE" a stored procedure.

That's NOT the same instruction. Please refer you to [SAP help|http://help.sap.com/saphelp_nwpi711/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm].

Regards.

Mickael

[Former Member]

Hi Gary,

Please paste the XML payload and we can check what is missing.

I assume that the field names and field values are not populated and it shows empty braces.

Regards

Krish