Using Date for request and response

Hi,

We are using date fields in our webMethods documents and to use date we set Content Type = date or dateTime :

Example : <xsd:element name="dateCreation" nillable="true" type="xsd:dateTime"/>

What would the best pratice to deal with date in webMethods when using Oracle Jdbc Adapter ?

Would you recommend

  • To use the same format as Oracle returns its DATE data, and using this format in webMethods (yyyy-MM-dd)
  • To never user Oracle DATE but ask the Adapter to returns a String to use in webMethods (with a user global var defined format)
  • To keep using the Adapter DATE format but convert it in String in webMethods ? (using the same global var)
  • other ?

There are many ways to do it…

Regards

Hi,

I really depends on what you are trying to accomplish and the limitations on the source database.

If (and that’s a big if) I’m certain the database holds all values on UTC, I convert to/from java.sql.timestamp (? not sure of the exact class) to the java.date object until the last moment so as not to corrupt the values.

I usually have to resort to my own java services to be able to use the standard Java library which deals with all the intricacies of timezone conversions and datetime calculus.

Best regards,

Thank you for your reply,

If I try to simplify our process from the low to high level :

1/ we get the date field from Oracle SELECT (it is the Oracle date format)

2/ get the date field from the webMethods JDBC Adapter
conversion to string or keep date as it is ?

3/ get the date field in my webMethods service to store it in a document webMethods
keep the current date as it is, or convert it with java service in a specific format ?

4/ expose the WSD web service to returns the date field (in dateTime)

maybe the better way is to invariably convert date from the JDBC Adapter with a java library before to store it in our final webMethods document ?

Regards

I prefer to keep the date values as Date objects as long as possible and only convert them to string when they have to. This way, the question about the formatting does not even arise (a date can be represented as string in different formats, and different services might expect them in different formats).

Thank you,
Ok, it’s maybe for us, the better way to do too…

Regards

When interacting with the DB (via JDBC adapter services) using Date objects as fml2 notes is the way to go.

When interacting with anything else, and in publishable doc types, we try to use the W3C format always.

Avoid Java manipulation whenever possible. It usually isn’t necessary.

Good news if we could keep date format along the whole service.

Here are the steps to try to get date with using ContentType :

In the ContentType editor, I leave as it is :
dateTime {http://… }

If I leave the DATE format in the Adapter JDBC, the date returned is like :
26/06/2018 12:40:14 CEST
(using the output type java.util.Date or java.slq.Date)

To transmit this date to the DateTime field of the webMethods document, how to do pleae ?
the mapping of the DATE field (Jdbc Adapter) to the Document does not give any value (the field is automatically droped)

(if I try with a string format form the JDBC Adapter, the mapping works and the result value is well displayed)

Any idea ?

Regards

In the adapter service you’ll see the JDBC Type will be DATE or TIMESTAMP (depending on the type of DB and the column definition).

In the Output Field Type, if you specify java.lang.String you’ll get string back in the format defined by the JVM that is running IS. You likely don’t want that (and it can change without your knowledge).

If you specify java.util.Date (avoid java.sql.Date) your service gets back an object, not a string. The DB driver and the JDBC adapter do the work to create a proper Date object. If you’re looking at the variable in the debugger or in results what you’re seeing is a toString() result of that object. The string format used is the default of your JVM. The value represented by this object will never vary so its usually what you want to use for DB queries. As you’ve seen, you cannot map this object to a string field – because it is not a string.

To map the date object to the string field of the document, you call pub.date:formatDate passing the object retrieved via the adapter service and specify this pattern: yyyy-MM-dd’T’HH:mm:ssXXX

Be sure to handle the case where the date object is null (DB column value is null).

Thanks a lot Rob for your reply, and sorry for the delay.

Can you tell me I have to use the inescapable format “yyyy-MM-dd’T’HH:mm:ssXXX”, or I can use any format of my choice to match with the DateTime element below ? (in french, we could use commonly “dd-MM-yyyy HH:mm:ss” but I can keep the english standard)


 <xsd:element name="dateCreation" nillable="true" type="xsd:dateTime"/>  

In Date and Time Formats, I have noticed the suggested formats are not exactly the same that “yyyy-MM-dd’T’HH:mm:ssXXX”. Is it not a problem ?

The XSD type definition for dateTime is specific. From a page on the web (search for xsd datetime):

“Its value space is described as a combination of date and time of day in Chapter 5.4 of ISO 8601.”

The XSD dateTime type supports a subset of what is defined by ISO 8601.

The W3C format is also derived from ISO 8601.

The format must be as specified if the element is to conform to the xsd:dateTime type. You cannot use any other format.

Thanks Rob, it’s clear.