I am trying to query DB2 database using a parameterized dynamic sql. One of the parameter fields is a timestamp in db2 database and webMethods somehow does not like it. This is the error I am getting
"[ART.114.505] Error while invoking adapter service com.wm.adapter.wmjdbc.services.DynamicSQL. JDBC Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff "

I tried changing to the format (‘yyyy-mm-dd hh:mm:ss.fffffffff’) but no luck. I tried changing the field format type from String to java.util.date and java.sql.date but no luck.

DB2 works with format “yyyy-MM-dd-HH.mm.ss.SSSSSS” and if change the sql to a variable substitution it works but we do not want to go that route.

any suggestions or ideas would be helpful.

Can you post the dynamic sql specifics and how you’re calling it?

This is how the sql looks like.

Select * from tmp
where id in ?

ID is a timestamp field in tmp table in the db2 database. The ID value comes from a different adapter service call which we are trying to pass it to this call. We get the value in the format (yyyy-MM-dd-HH.mm.ss.SSSSSS) when we use function char on the id so we can use it for subsequent calls. I tried to using this format as well (yyyy-mm-dd hh:mm:ss.fffffffff ) but no luck.

Tried changing the input parameter field type from string to java.sql.date and java.util.date but still ended with errors.

Thx in advance for your help.

Shot in the dark. Try:

select * from tmp where id in ‘?’

well we would have to use ‘’ for date field and I am using it already. just did not write it in here.

What are the types specified for the field on the 2 adapter services that you mention?

both are strings…

I got this working and wanted to share it. Do not put quotes (‘’) around the date in any format.

Call it this way. This goes with char variables as well. I was assuming earlier to put quotes around char/timestamp fields as we would do in a regular sql.

Select * from tmp
where id in ? with parameter being yyyy-MM-dd-HH.mm.ss.SSSSSS Good.

Do not try calling it this way.
Select * from tmp
where id in ? with parameter being ‘yyyy-MM-dd-HH.mm.ss.SSSSSS’ BAD.

or

Select * from tmp
where id in ‘?’ with parameter being yyyy-MM-dd-HH.mm.ss.SSSSSS BAD.

Below query works fine when this is executed at the database but fails through db2 jdbc adapter service.

select sales_order_id from sales_order
where created_date = TIMESTAMP_FORMAT(‘2014-07-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)

But this fails from db2 jdbc CustomSql adapter service with following error:
[SQL0171] Argument 01 of function TIMESTAMP_FORMAT not valid.

I tried with suggested ‘yyyy-MM-dd-…SS.fff…’ format, but it does not work.

Joy,

Have you tried this format as suggested GOOD?

Select * from tmp
where id in ? with parameter being yyyy-MM-dd-HH.mm.ss.SSSSSS Good.

HTH,
RMG