JDBC to IDoc Scenario: Update the Source table field with System Date and Time

[Former Member]

Hi Masters,

currently im working on a JDBC to IDoc scenario and this gonna be my first time.

I need to fetch the ORDERNO and update STAT the with 'P' and update OUTDAT and OUTTIM with the current date and time respectively.

Table Name: TAB1

Table Field          Description                         Format

     ORDERNO     Sales Order Number

     STAT             Status

     OUTDAT        Date Processed               CCYYMMDD

     OUTTIM         Time Processed               HHMMSS

Based on my "googling" you could update these fields via SQL statement on the sender adapter like:

UPDATE TAB1 SET STAT to 'P' where STAT = 'N' .

My problem is with the OUTDAT and OUTTIM,
1. How can I get the Tables own sytem time?

2. How can I pass the retrieved value back to the said fields? (so far the examples i have seen were updating table fields with hardcoded values)

[former_member184681 · Accepted solution]

Hi,

This should be possible with the SQL functions that return date and time, e.g.:

UPDATE TAB1 SET STAT to 'P', OUTDAT = CURDATE(), OUTTIM = CURTIME() WHERE STAT = 'N'

See more details about the date functions in SQL e.g. here:

Date Functions in SQL Server and MySQL

Regards,

Greg

[amankumarmangla · Accepted solution]

Hey Greg,

Do we have an option to change the date and time format as per our need and update the modified format in the table?

Say, from YYYY-MM-DD (standard format) to DDMMYYYY.

Regards,

Aman

[former_member184681 · Accepted solution]

Dear Aman,

Yes it should be possible. Example functions for that purpose are also mentioned under the link I included in my previous post, e.g. it can be DATE_FORMAT() for MySQL, or CONVERT() for SQL. Bear in mind that particular implementation will highly depend on the database engine you are using as a sender, so you need to adjust your UPDATE statement depending on that.

Regards,

Greg

[former_member191435 · Accepted solution]

Hi,

Please check below link

http://stackoverflow.com/questions/4259729/updating-a-date-format-in-mysql

[Former Member]

Thanks Greg.

As I have mentioned, my requirement i have to select ORDERNO then update STAT, OUTDAT, OUTTIM.
what i forgot to mention is I also need to return the DB current date and current time on my mapping for further processing after updating it on the table.

If i get it correctly, sender JDBC adapter works sequentially:

1st step: select the data needed

2nd step: Update the fields

So how can i get the values which i have just updated on the table?