Update SQL Statement in the JDBC sender

[Former Member]

Hello!

I have a AS400/DB2 table which I am polling for my XI scenario using the JDBC sender adapter.

I would like to know if it is possible to use system variables (data/time) in the UPDATE statement field? This because we would like to timestamp the exact date/time we read a table. (We can use stored procedures, I know, but would preferr not if at all possible.) Or is there another way?

Thanks,

Nam

[former_member187587 · Accepted solution]

Hi Nam.

I don't have the full picture of your integration scenario but, you can generate any data using java functions from the simple or advanced java mapping function.You can try to create any date in the mapping and map it to the target structure in the DB or anywhere else.

You can also try graphical mapping i think they have current date there...you wont get the DB polling time but the XI processing time...maybe its good enough for you.

It is also possible to create your own developed adapter but offcours that is developing...

I thing there is no feature in the JDBC adapter adding timestamp to the XML structure and this is the closest place to the polling action..

Tell me is this enoguth for you.

Nimrod

[Former Member · Accepted solution]

Hello!

a little more detail:

We have a fairly simple scenario. We are polling the AS400/DB2 table for information using the JDBC sender with a SELECT statement in the SQL query. We take the data, map it and push it into SRM.

Bhavesh: What I meant is that for error handlig purposes we would like to update the polled record with date and time at the same time as we poll. Using the system date/time of the db system would be ideal, but XI time would be just fine to. So wondering if there is any way to pick up the system data/time (of either system) and pass it in the UPDATE statement as system variables or some such.

Nimrod: one question.... how do we do the mapping in the poll? Meaning the polling is the trigger of the whole scenario, so there is no mapping preceding it. I am fairly new to XI, especially the mapping bit, any help or advice appreciated!

Thanks!

Nam

[bhavesh_kantilal · Accepted solution]

<i>What I meant is that for error handlig purposes we would like to update the polled record with date and time at the same time as we poll. Using the system date/time of the db system would be ideal, but XI time would be just fine to. So wondering if there is any way to pick up the system data/time (of either system) and pass it in the UPDATE statement as system variables or some such.</i>

Think this should be possible. Any Update Statement should work from the JDBC sender adapter including SYSDATE as, the final SQL query is going to be executed in the Database that you are polling over.

Regards,

Bhavesh

[bhavesh_kantilal]

<i>would like to know if it is possible to use system variables (data/time) in the UPDATE statement field?</i>

Can you give an example?

Regards,

Bhavesh