| Author |
Message
|
| m00300 |
 Posted: Fri May 07, 2004 7:03 am Post subject: Problems inserting timestamps in v5 |
 |
|
Apprentice
Joined: 01 May 2002
Posts: 31
|
We've just migrated our v2.0.2 code to v5 and all seems well apart from one problem.
In v2.0.2 we often inserted the CURRENT_TIMESTAMP into a DB2 UDB 6.1 database table - after first removing the 'TIMESTAMP' literal using a statement like :
SET EXCEPTION_TIMESTAMP = SUBSTRING(CAST(CURRENT_TIMESTAMP AS CHAR) FROM 11);
where EXCEPTION_TIMESTAMP is defined as a CHAR and referenced in a PASSTHRU statement.
This used to work fine, however in v5 (with DB2 UDB v8.1) it now complains that the date format is invalid :
2004-05-07 14:58:34.147800 2532 DatabaseException BIP2322E: Database error: SQL State '22007'; Native Error Code '-180'; Error Text '[IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007 '.
NB : database tables in v6.1 and v8.1 are defined identically.
looking at the literal value passed to the database in the trace shows the value : ''2004-05-07 14:58:33.828'' (the double single quotes appear a bit odd but traces in v2.0.2 and v5 both show these)
This can be fixed by recoding the SUBSTRING above to miss out the single quotes returned by the CURRENT_TIMESTAMP function however I dont really want to make this change (it could be in a lot of places!) unless I really need to.
Is there a setting in the database/ODBC driver/WBIMB which could effect how the timestamps are handled ? (we've looked everywhere we can think of but cant find anything relevent)
Any advice as to why this occurs or how to resolve it without re-coding would be appreciated.
Thanks,
Kevin. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri May 07, 2004 7:12 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Right, this is the issue you're running into.
When you cast a Timestamp to a Character, what you get is the literal string
| Quote: |
| TIMESTAMP 'yyyy-mm-dd hh:mm:ss.ttt' |
with single quotes.
If you want to pass this to DB2, you need to remove the single quotes, or extract the various fields and then rebuild them into the correct patterned string that DB2 expects for a timestamp.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| m00300 |
| Posted: Fri May 07, 2004 7:26 am Post subject: in that case... |
|
|
Apprentice
Joined: 01 May 2002
Posts: 31
|
the code I posted wouldnt have worked under v2.0.2 and it has been for many years.
something must have changed in the way Timestamps are handled, either in WBIMB, the ODBC driver or in DB2 v8.1
I've made no code changes to the orignal v2.0.2 ESQL yet. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri May 07, 2004 7:51 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I believe you when you say that it works under 2.0.2.
I'm telling you that converting a timestamp to a character *IN v5* will add the single quotes you are seeing, and you need to handle that when you try to pass that data to DB2.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| m00300 |
| Posted: Sun May 09, 2004 11:59 pm Post subject: |
|
|
Apprentice
Joined: 01 May 2002
Posts: 31
|
ok - thanks
I guess if thats the only change we need to make i'll count myself lucky! |
|
| Back to top |
|
|
|
|
