File to JDBC - JDBC database not getting updated using UPDATE_INSERT

[Former Member]

Hi ,

I have a done a file to JDBC Scenario.File is successfully picked up by sender file channel and recieved by reciever JDBC channel.There is no error in receiver communication channel.I used UPDATE_INSERT action in mapping which has to update the database with all records it recieve.But,i found there is no update in Database table.I donot have access to database,so i made a sender jdbc channel querying all data in it to see what new value is populated.

I made a dummy sender jdbc channel to check what are the values updates on the run of above interface.But , i found no update occur.Please help me out of this problem.

Thanks

Deepak Jaiswal

[justin_santhanam · Accepted solution]

Deepak,

Did you find any errors in the JDBC receiver comm.channel? Also can you enable logSQLStatement true in your receiver JDBC channel to see what SQL query got created?

raj

[Former Member · Accepted solution]

Hi Raj,

where i found any parameter to log sql statement.I have seen receiver channel and find no such option there.

Thanks

Deepak

[Former Member · Accepted solution]

Hi Deepak,

In JDBC Receiver Adapter, just check advanced mode

and give parameter as logSQLStatement and its value as true

This way you will be able to see the query that is getting generated in audit log of the adapter.

Regards,

Gautam Purohit

[Former Member · Accepted solution]

Hi,

I am getting following type of log in reciever channel for each record in file:

UPDATE TABLE1 SET FIELD1=NULL, FIELD2=A1, FIELD2 =2010.

but still when i try to fetch all data from same table using sender jdbc channel(dummy channel made for testing) i am not getting any such records,mean no update happens.

what will be the reason?

Thanks

Deepak Jaiswal

[Former Member · Accepted solution]

this update statement contains 2 errors: FIELD2 is referenced 2 times, and the value A1 must be surrounded with ', otherwise it is no valid SQL, at least not for Oracle and probably also not for other DBMS.

CSY

[Former Member · Accepted solution]

Hi,

FIELD2 IS Referenced 2 times .This is my mistake it will be FIELD3.It is just an example.And most of the values are numeric for FIELD1... to FIELDn. Some of the fixed values i get in sql logstatement is not enclosed with ' characters.Should this will be error and how to add ' character using reciever channel for character values.

Thanks

Deepak

[justin_santhanam · Accepted solution]

Deepak,

Can you post the complete Audit log that you see in the comm.channel monitoring for this receiver channel?

raj.

[Former Member · Accepted solution]

Hi Raj,

The audit log is like below type for each record:

UPDATE SAP_TABLE_DEMO SET COSTCENTER=NULL, COMP_CODE=IN01, FISCYEAR=2009, FISCPER3=001, AC_DOC_NO=5000000, GL_ACCOUNT=0000001, CREDITOR=X00001, LOC_CURRCY=USD, ZPorto=NULL, ZCLTY=NULL, VALUE_LC=100.00

Thanks

Deepak Jaiswal

[justin_santhanam · Accepted solution]

Deepak,

I'm asking the complete Audit log (not only the SQL query)

raj.

[Former Member · Accepted solution]

What database is it ? In Oracle this update would definitely fail. Probably also on most other DBMS, because of the missing ' for the alphanumeric values.

And one more question, you do not use a where clause ?! You update the whole table ? (locking issues)

CSY

Edited by: Christian Sy on Apr 14, 2010 10:35 AM

[Former Member · Accepted solution]

Hi Raj,

I can't provide whole audit log as it has customer specific information.

I am using mysql server database.

Thanks

Deepak

[Former Member · Accepted solution]

Hi ,

I am using my sql server database and using UPDATE_INSERT action in mapping. How to include ' for alphanumeric values and whether any configuration required to achieve this and where.

I am updating whole table thats why not using where statement.

Thanks

Deepak

[justin_santhanam · Accepted solution]

The reason I ask , because you will see whether the message got successfully delivered to the target system.

I want to see information like below

2010-04-14 14:31:35 Success Message successfully received by messaging system. Profile: XI URL: http://server:port/MessagingSystem/receive/AFW/XI Credential (User): PIISUSER

2010-04-14 14:31:35 Success Using connection JDBC_http://sap.com/xi/XI/System. Trying to put the message into the receive queue.

2010-04-14 14:31:35 Success Message successfully put into the queue.

2010-04-14 14:31:35 Success The message was successfully retrieved from the receive queue.

2010-04-14 14:31:35 Success The message status set to DLNG.

2010-04-14 14:31:35 Success Delivering to channel: Communuication channel

2010-04-14 14:31:35 Success MP: Entering module processor

2010-04-14 14:31:35 Success MP: Processing local module localejbs/CallSapAdapter

2010-04-14 14:31:35 Success Receiver JDBC adapter: processing started; QoS required: ExactlyOnce

2010-04-14 14:31:35 Success JDBC adapter receiver channel CC: processing started; party , service DB_BS

2010-04-14 14:31:35 Success Database request processed successfully

2010-04-14 14:31:35 Success MP: Leaving module processor

2010-04-14 14:31:35 Success The message was successfully delivered to the application using connection JDBC_http://sap.com/xi/XI/System.

2010-04-14 14:31:35 Success The message status set to DLVD.

raj.

[Former Member · Accepted solution]

Hi Raj,

I am getting all the step you have mention in audit log as successful and last statement as" The message status set to DLVD".

But i am not seeing anything like " 2010-04-14 14:31:35 Success MP: Leaving module processor " as in your statement.May be this is also ok.

Thanks

Deepak

Edited by: deepak jaiswal on Apr 14, 2010 4:54 PM

[justin_santhanam · Accepted solution]

Deepak,

As pointed out by our friend Christian - you don't have any Where clause in your query? Is this ur reqmt?

[Former Member · Accepted solution]

Hi Raj,

yes as per my requirement i donot use where clause.Can you give me the exact data type for reciever JDBC side with element ,type,attribute and occurence property,may be that will be having error.For defining table name in mapping,do we have to define Table field as an attribute( with required/optional) or as an element of type xsd:string.Might be this also cause error.

Thanks

Deepak

[stefan_grube · Accepted solution]

Hi Deepak,

the adapter monitor does not show error, the SQL log seems fine, so you should look for the sender channel.

In my opinion the entries into the databas table works well, but you are not able to verify this.

Ask for access to the database directly, it does not make sense to do system integration without having access to the receiving system.

[Former Member · Accepted solution]

Thanks to everyone helping me to resolve this issue.I have tried with action DELETE and INSERT action in mapping to flush the database and INSERT the records.It is working fine.The problem earlier was database is not set properly and when i was running scenario using action UPDATE_INSERT.Every records are overwrite by next records and finally last records only get loaded to database.This last record i am able to fetch from JDBC sender Channel(Made only to check the entries).The Log which was generated in audit log for Sql query was fine which i was thinking their might be some wrong as alphanumeric values are not enclosed within '.

Thanks

Deepak

[stefan_grube]

Another idea, which sounds crazy, but have you considered, that the database entries and the file data are not different, so that the update would not have any impact?

[stefan_grube]

Check runtime workbench -> message monitor for adapter framework for errors.