Date+Time not getting stored in DB table

, ,
1

Hi,

When I try to insert the date+time in a filed which is of “DATE” type, it’s not storing with date+time, only date gets stored.

Tried using built in INSERT adapter service.

Am using webMethods 7.1.2.0
Adapter version 6.5

Tried with both java.util.Date and java.sql.Date in “Input Filed Type”. Same result. Any one could you please help me on this?

Do we need to apply any fix?

Regards,
Sam

2

Hello

All databases have different date/time field types, and different rules about how they are handled. My taught’s are you need to contact your database admin or table owner to find out what format is that Date type field is expecting

thanks
Anil

3

I assume you’re using an Oracle database. This link has information about the Oracle JDBC driver behavior.

[URL]http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01[/URL]

A couple of background points (based on assumptions, not explicit knowledge of the inner workings of the JDBC adapter):

  • The adapter interacts with the JDBC driver. It relies on the driver for many things.
  • The driver maps column data to/from java.sql data types, such as java.sql.Date, java.sql.Timestamp, etc.
  • The adapter can map the underlying type to other compatible types. For example, you can select java.util.Date as the type in the adapter service and the adapter will convert java.sql.Date or java.sql.Timestamp to java.util.Date when reading and the reverse when writing.

Starting with version 9.2, Oracle introduced a new column data type of “timestamp.” With that change, the JDBC driver no longer maps date columns to java.sql.Timestamp. They map to java.sql.Date, which does not have a time component.

The JDBC adapter relies on the output of the driver–since the driver now returns a java.sql.Date, the adapter does not have any access to the time.

The reverse is also true. Even if the adapter passes a java.util.Date to the driver, the driver will map that to java.sql.Date, losing the time portions.

According to the Oracle site, 11.1 changes the driver behavior (again) back to how it behaved before 9.2.

One way to address this for 9.2 to 11.0 is to use custom SQL adapter services. With these you can control the JDBC data types used when interacting with the DB. You can set the JDBC data type for a particular field to be TIMESTAMP and set the field type to be java.util.Date (or java.sql.Timestamp) and the time will be preserved.

Using custom SQL services may not be ideal (more manual work to define things) but it works around this issue nicely.

4

As Rob said,use the JDBC customSQL template with query and change the field type it will send/insert the date+timestamp accordingly,we did faced this situation like this and tried this approach.

HTH,
RMG