JDBC Sender update SQL Statement Question.

[Former Member]

I was wondering if there is a way to have the update SQL statement line in the JDBC sender update by time stamp, this would be very helpful. Does anyone know a method of doing this?

[Former Member · Accepted solution]

In the SAP documentation of the adapter it has this example for using the update SQL statement after the query statement:

SQL statement for query: SELECT * FROM table WHERE processed = 0;

SQL statement for update: UPDATE table SET processed = 1 WHERE processed = 0;

What I want is to be able to put processed = (the current date) instead of processed =1 in the update statement, like this example:

SQL statement for query: SELECT * FROM table WHERE processed = 0;

SQL statement for update: UPDATE table SET processed = (the current date) WHERE processed = ' ';

I seems like you can only use a fixed value for the update statements in the JDBC Sender though, I would like to know if you can use a time stamp or variable there.

[Former Member · Accepted solution]

Paul,

You can use the oracle SQL functions like SYSDATE to set the datetime stamp.

SQL statement for update: UPDATE table SET processed = SYSDATE WHERE processed = ' ';

As the Update/Select queries have to be database specific, sender JDBC adapter can not provide you with dynamic values.

hope this helps..

praveen

[justin_santhanam · Accepted solution]

Paul,

Please go ahead with the same as Praveen suggested. But I never worked like that, but i'm sure it will work, coz the query is executed on the DB side. There is nothing to do with XI. It simply passes the query and get executed over there am I right, please correct me if i'm wrong.

Try the same query in your DB, if there is no issues, then surely it will work.

Best regards,

raj.

[Former Member]

well, from what I can see in our situation the solution praveen gave me wont work for it, I'm going to have to try and do dynamic time stamping in a Reciever...

That solution would work though for simpler update, so I'll give you guys points.

[Former Member]

HI Paul,

>>> <i>from what I can see in our situation the solution praveen gave me wont work for it, I'm going to have to try and do dynamic time stamping in a Reciever...

That solution would work though for simpler update, so I'll give you guys points.</i>

This should not be a problem at all. Praveen's suggestion will work. You just need to use a function corresponding to your database for getting the current date and time.

We have used a similar update statement in one of our interfaces and found no issues. If I am missing any thing, let me know.

Regards,

Jai Shankar

[justin_santhanam]

Paul,

Can u make it little bit more clearer. Please give an example , it will be great helpful.

Best regards,

raj.