| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| SOLVED - datetime value syntax error |
« View previous topic :: View next topic » |
| Author |
Message
|
| scottm |
 Posted: Mon Jan 10, 2005 10:50 am Post subject: SOLVED - datetime value syntax error |
 |
|
Apprentice
Joined: 13 May 2004
Posts: 44
Location: SE Tennessee
|
I am having a terrible time trying to solve this one.
We are running a WMQI 2.1 CSD04 broker on AIX v5.1 running against a DB2 database at 7.1.0.98 (according to lslpp).
I have a new flow that takes data from an XML message and stores it into a db2 table. I was having problems with the date/time fields so I simplified it, and now simply supply the data directly. Here is my esql:
| Code: |
INSERT INTO Database.ediodbc.EDI_RPSTRY(EDTN_KEY,
ISA06_SND_ID,
ISA13_CNTL_NO,
GS02_SND_CD,
GS06_CNTL_NO,
EDI_CUR_DT,
TRN01_01,
TRN02_01,
TRN03_01,
TRN04_01,
TRN01_02,
TRN02_02,
TRN03_02,
TRN04_02,
RESPONSE_DTTM,
EDI_DATA,
BHT03_SBRTRN_NO,
BHT04_CREATE_DT,
BHT05_CREATE_TM,
REF02_PATCNTL_NO,
EDTN_FORW_BX,
FORW_DTTM,
RES_DTTM,
ROUTE_CODE)
VALUES
('11111C999999',
'993',
CAST('000635174' as INTEGER),
'BITSA',
CAST('635174' as INTEGER),
CURRENT_TIMESTAMP,
'1',
'22222C888888',
'9ABCDE TRN',
'2901012155',
'',
'',
'',
'',
'',
'ISA*00*00*00*',
'UDNKEOLVYCPD',
DATE '2004-11-09',
TIME '10:10:00',
'599119',
'',
'',
'',
'5'); |
The result in the trace log is:
| Quote: |
UserTrace BIP2537I: Node 'EMF_BLUE.Database1': Executing statement 'DATABASE(INSERT INTO ediodbc.EDI_RPSTRY(EDTN_KEY, ISA06_SND_ID, ISA13_CNTL_NO, GS02_SND_CD, GS06_CNTL_NO, EDI_CUR_DT, TRN01_01, TRN02_01, TRN03_01, TRN04_01, TRN01_02, TRN02_02, TRN03_02, TRN04_02, RESPONSE_DTTM, EDI_DATA, BHT03_SBRTRN_NO, BHT04_CREATE_DT, BHT05_CREATE_TM, REF02_PATCNTL_NO, EDTN_FORW_BX, FORW_DTTM, RES_DTTM, ROUTE_CODE) VALUES('11111C999999', '993', ?, 'BITSA', ?, ?, '1', '22222C888888', '9ABCDE TRN', '2901012155', '', '', '', '', '', 'ISA*00*00*00*', 'UDNKEOLVYCPD', {d '2004-11-09'}, {t '10:10:00'}, '599119', '', '', '', '5'), CAST('000635174' AS INTEGER), CAST('635174' AS INTEGER), CURRENT_TIMESTAMP);' at (1, 1).
UserTrace BIP2538I: Node 'EMF_BLUE.Database1': Evaluating expression 'CAST('000635174' AS INTEGER)' at (40, 9).
UserTrace BIP2539I: Node 'EMF_BLUE.Database1': Finished evaluating expression 'CAST('000635174' AS INTEGER)' at (40, 9). This resolved to 'CAST('000635174' AS INTEGER )'. The result was '635174'.
UserTrace BIP2538I: Node 'EMF_BLUE.Database1': Evaluating expression 'CAST('635174' AS INTEGER)' at (42, 9).
UserTrace BIP2539I: Node 'EMF_BLUE.Database1': Finished evaluating expression 'CAST('635174' AS INTEGER)' at (42, 9). This resolved to 'CAST('635174' AS INTEGER )'. The result was '635174'.
UserTrace BIP2538I: Node 'EMF_BLUE.Database1': Evaluating expression 'CURRENT_TIMESTAMP' at (43, 9).
UserTrace BIP2540I: Node 'EMF_BLUE.Database1': Finished evaluating expression 'CURRENT_TIMESTAMP' at (43, 9). The result was 'TIMESTAMP '2005-01-10 12:51:51.530292''.
UserTrace BIP2544I: Node 'EMF_BLUE.Database1': Executing database SQL statement 'INSERT INTO ediodbc.EDI_RPSTRY(EDTN_KEY, ISA06_SND_ID, ISA13_CNTL_NO, GS02_SND_CD, GS06_CNTL_NO, EDI_CUR_DT, TRN01_01, TRN02_01, TRN03_01, TRN04_01, TRN01_02, TRN02_02, TRN03_02, TRN04_02, RESPONSE_DTTM, EDI_DATA, BHT03_SBRTRN_NO, BHT04_CREATE_DT, BHT05_CREATE_TM, REF02_PATCNTL_NO, EDTN_FORW_BX, FORW_DTTM, RES_DTTM, ROUTE_CODE) VALUES('11111C999999', '993', ?, 'BITSA', ?, ?, '1', '22222C888888', '9ABCDE TRN', '2901012155', '', '', '', '', '', 'ISA*00*00*00*', 'UDNKEOLVYCPD', {d '2004-11-09'}, {t '10:10:00'}, '599119', '', '', '', '5')' derived from (1, 1); expressions 'CAST('000635174' AS INTEGER), CAST('635174' AS INTEGER), CURRENT_TIMESTAMP'; resulting parameter values '635174, 635174, TIMESTAMP '2005-01-10 12:51:51.530292''.
UserTrace BIP2231E: Error detected whilst processing a message 'EMF_BLUE.Database1'.
The message broker detected an error whilst processing a message in node 'EMF_BLUE.Database1'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
RecoverableException BIP2519E: (1, 1) : Error executing SQL statement 'INSERT INTO ediodbc.EDI_RPSTRY(EDTN_KEY, ISA06_SND_ID, ISA13_CNTL_NO, GS02_SND_CD, GS06_CNTL_NO, EDI_CUR_DT, TRN01_01, TRN02_01, TRN03_01, TRN04_01, TRN01_02, TRN02_02, TRN03_02, TRN04_02, RESPONSE_DTTM, EDI_DATA, BHT03_SBRTRN_NO, BHT04_CREATE_DT, BHT05_CREATE_TM, REF02_PATCNTL_NO, EDTN_FORW_BX, FORW_DTTM, RES_DTTM, ROUTE_CODE) VALUES('11111C999999', '993', ?, 'BITSA', ?, ?, '1', '22222C888888', '9ABCDE TRN', '2901012155', '', '', '', '', '', 'ISA*00*00*00*', 'UDNKEOLVYCPD', {d '2004-11-09'}, {t '10:10:00'}, '599119', '', '', '', '5')' against datasource 'FEDIDARM' with parameters '635174, 635174, TIMESTAMP '2005-01-10 12:51:51.530292', '.
The following error occurred during execution of a database SQL statement against datasource 'FEDIDARM'. The SQL statement was 'INSERT INTO ediodbc.EDI_RPSTRY(EDTN_KEY, ISA06_SND_ID, ISA13_CNTL_NO, GS02_SND_CD, GS06_CNTL_NO, EDI_270_CUR_DT, TRN01_01, TRN02_01, TRN03_01, TRN04_01, TRN01_02, TRN02_02, TRN03_02, TRN04_02, RESPONSE_DTTM, EDI_DATA, BHT03_SBRTRN_NO, BHT04_CREATE_DT, BHT05_CREATE_TM, REF02_PATCNTL_NO, EDTN_FORW_BX, FORW_DTTM, RES_DTTM, ROUTE_CODE) VALUES('11111C999999', '993', ?, 'BITSA', ?, ?, '1', '22222C888888', '9ABCDE TRN', '2901012155', '', '', '', '', '', 'ISA*00*00*00*', 'UDNKEOLVYCPD', {d '2004-11-09'}, {t '10:10:00'}, '599119', '', '', '', '5')'. The parameters passed were '635174, 635174, TIMESTAMP '2005-01-10 12:51:51.530292', '.
DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
DatabaseException BIP2322E: Database error: SQL State '22007'; Native Error Code '-180'; Error Text '[IBM][CLI Driver][DB2/6000] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
'.
The error has the following diagnostic information: SQL State '22007' SQL Native Error Code '-180' SQL Error Text '[IBM][CLI Driver][DB2/6000] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
'
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
|
This is a new flow and I know it's reaching the database because if I enter the date just as a string, it tells me that the field is expecting DATE type, but received a CHAR type. So, I figured that if it can tell me if the fields are of the wrong type, the db2 connection is fine.
But, we have other flows that were developed a long time ago that use the same code and they work - I even tested on today so I know they still work. I've also tried all variations using the function CAST as well. But the error is always the same.
The field that uses CURRENT_TIMESTAMP is defined in the DB2 table as type TIMESTAMP. The DATE field is defined as type DATE in DB2 and the TIME field is defined as type TIME.
Any ideas?
Thanks - Scott
Last edited by scottm on Tue Jan 11, 2005 1:28 pm; edited 1 time in total |
|
| Back to top |
|
 |
| kirani |
| Posted: Mon Jan 10, 2005 3:52 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try to replace your DATE and TIME values with CURRENT_DATE and CURRENT_TIME to make sure your other variables are correct. Also, there is a section in ESQL manual explaining the mapping between DB and ESQL data types.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| scottm |
| Posted: Tue Jan 11, 2005 1:27 pm Post subject: SOLVED - Datetime syntax problem |
|
|
Apprentice
Joined: 13 May 2004
Posts: 44
Location: SE Tennessee
|
Thanks Kirani, but I had already tried those suggestions and nothing worked.
But, I have discovered the problem. And of course it was a user error and misunderstanding.
All along I was figuring that the datetime value syntax problem was with the three fields that I was trying to format. But, in reality it dealt with the three TIMESTAMP fields that were null. My xml came in with no data between the tags for RESPONSE_DTTM, FORW_DTTM and RES_DTTM, so my esql passed over '' for the data. This is not valid for a TIMESTAMP field, if you want NULL, you must use the word NULL. So, I changed my code in the INSERT for those three fields to be:
| Code: |
CAST(NULLIF(Body.EDI_RPSTRY.RESPONSE_DTTM,'') as TIMESTAMP),
CAST(NULLIF(Body.EDI_RPSTRY.FORW_DTTM,'') as TIMESTAMP),
CAST(NULLIF(Body.EDI_RPSTRY.RES_DTTM,'') as TIMESTAMP),
|
This way, if it is '', it returns NULL, and if it has real data, it will CAST it appropriately.
So far this has only been an issue with DATE, TIME & TIMESTAMP fields.
Scott |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
