Update SQL statement in JDBC sender (system fields)

[michel_dekimpe]

All,

Is it possible to update more than 1 field via the update sql statement ?

Also, is it possible to use system fields ?

Something like this

UPDATE database.table SET processed='Y', date = sy-datum

Regards, Michel

[dharamveer_gaur2 · Accepted solution]

Hi

use sysdate as suggested above

Check your generated SQL query format is correct

At runtime you can find the genereated sql statements by doing configuraitons in Receiver JDBC adapter.

In the JDBC Receiver adapter you have the Advanced Properties .

Over there enter the following

left column logSQLStatement

right column true

To see the query created ..

Login to adapter monitoring ..select the relevant jdbc adapter.

Now when any message is processed by the jdbc adapter in adapter monitoring at that time you will see a message link. When you click on that link a new window will open. In that window if you click on page down you will get to see the sql statement generated by the jdbc adapter.

[VijayKonam · Accepted solution]

Sy-Datum is available in ABAP only. In SQL date handling is different. Check SQL SP help.

VJ

[michel_dekimpe]

All,

Thanks for all your help.

I found the solution to update date and time in MySQL.

Use CURRENT_DATE() and CURRENT_TIME()

Like this, UPDATE database.table SET processed='Y', date = CURRENT_DATE(), time = CURRENT_TIME()

Regards, Michel

[Former Member]

HI

Check wheather sy-datum is understood by SQL server. Try updating some other field. Or as Unni said try with sysdate

Thanks

Gaurav

[michel_dekimpe]

Anand,

That's what I have used, the quote comes from the communication channel monitoring.

Regards, Michel

[Former Member]

Hi,

Use sysdate instead of sy-datum.

UPDATE database.table SET processed='Y', date = sysdate

regards

Unni

[Former Member]

HI Michel,

You can execute the same statement in the SQL editor itself.

And check syntax of the statement in SQL and try use the same statement in the UPDATE querty option of the JDBC Adapter.

As Gaurav said you check the generated Statement at runtime.

Regards

Goli Sridhar

[michel_dekimpe]

Hello Anand,

I would also assume it's possible but when I try it it gives following message.

"Database-level error reported by JDBC driver while executing statement 'UPDATE database.table SET processed='Y', date = sy-datum'. The JDBC driver returned the following error message: 'com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'sy' in 'field list''. For details, contact your database server vendor."

Or should I put double quotes around sy-datum ?

Regards, Michel

[Former Member]

> "Database-level error reported by JDBC driver while executing statement 'UPDATE database.table SET processed='Y', date = sy-datum'. The JDBC driver returned the following error message: 'com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'sy' in 'field list''. For details, contact your database server vendor."

syntax incorrect.. remove the quote (') and try

UPDATE database.table SET processed='Y', date = sy-datum

[Former Member]

It should be possible