| Author |
Message
|
| madi |
 Posted: Mon May 08, 2006 10:29 am Post subject: database timestamp formats[solved] |
 |
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
Hi All
i need to insert Timestamp into a database in a particular format which is not supported by broker.
so I create a string manually formatting it to fit the database timeformat
but then the database says
| Quote: |
| A Database Exception has occured for MsgId: X'414d5120574d5144514d3031202020204410b6f621bbca8b'--[IBM][CLI Driver][AS] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007 |
so is there a way to do a CAST depending on the format that is accepted by the database??
im think of a way to do a SQL statement that is executed in the database like using PASSTHRU or something like that
is that possible??
--madi
Last edited by madi on Mon May 08, 2006 1:41 pm; edited 1 time in total |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon May 08, 2006 10:32 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Does the insert statement you built work just fine from the database command line (i.e. outside of broker)?
Does it work in PASSTHRU?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| madi |
| Posted: Mon May 08, 2006 12:53 pm Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
i have tried all sorts of stuff but failed to enter data into the timestamp fields of the database even through command line!!
anyway in the error message that i got the msgId of the message was X'414D5120574D5154434330312020202095CEFC4302750420' does that mean the that the actual msgId is 414D5120574D5154434330312020202095CEFC4302750420
because when i went into the queue that is what it shows
what is X' on front of the Id??
--madi[/b] |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon May 08, 2006 12:57 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If you can't enter the date from the command line, then it's not formatted correctly for the database, somehow.
What is the format you're trying to create, what does the string you are creating look like, what is the database you are trying to use?
The X is part of the normal string representation of BLOB data. It's used in part to identify the data as BLOB data.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| madi |
| Posted: Mon May 08, 2006 1:06 pm Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
we are trying to insert into DB2 database on AS400 which has some weird timestamp formats!! we finally found out the format it needs but its too much trouble in broker to convert the format from what i am getting from the xml.
the way i convert timestamps is extract all the diff year,month,day....etc and create a string which represents the new timestamp format.
is there a better way to do this in broker??
--madi |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon May 08, 2006 1:09 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If I recall, you're using v5, aren't you?
There are new functions in v6 that let you specify format strings for date and time data.
So, if you're using v5, then "not as far as I know".
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| madi |
| Posted: Mon May 08, 2006 1:18 pm Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
yeah im using v5
most of my coding for this project is done anyway!!
u've been a real help for the most part 
thanks jeff
--madi |
|
| Back to top |
|
|
| dsriksha |
| Posted: Mon May 08, 2006 1:24 pm Post subject: |
|
|
Voyager
Joined: 27 Feb 2005
Posts: 95
|
| madi wrote: |
i have tried all sorts of stuff but failed to enter data into the timestamp fields of the database even through command line!!
--madi[/b] |
Post in any DB forums..  |
|
| Back to top |
|
|
| madi |
| Posted: Mon May 08, 2006 1:35 pm Post subject: |
|
|
Chevalier
Joined: 17 Jan 2006
Posts: 475
|
| Quote: |
| we finally found out the format it needs but its too much trouble in broker to convert the format from what i am getting from the xml. |
thanks for your interest
--madi |
|
| Back to top |
|
|
|
|
