| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| MB V6 - MSSQL : DATETIME cast error |
« View previous topic :: View next topic » |
| Author |
Message
|
| scravr |
 Posted: Tue Dec 04, 2007 7:36 am Post subject: MB V6 - MSSQL : DATETIME cast error |
 |
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
I am trying to INSERT a row to MSSQL with a DATETIME field but getting cast error on MB V6.
The problems is with the TS char field. Shoud this be declared as TIMESTAMP?
I have tried without the AM/PM and still same error.
DECLARE TS1 CHARACTER;
DECLARE TS CHARACTER;
SET TS1 = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyy/MM/dd HH:mm:ss');
SET TS = SUBSTRING(TS1 FROM 1 FOR 19);
IF CAST(SUBSTRING(TS FROM 12 FOR 2) AS INTEGER) < 12 THEN
SET TS = TS || ' AM';
ELSE
SET TS = TS || ' PM';
END IF;
2007-12-03 16:42:55.835487 3580 UserTrace BIP2231E: Error detected whilst processing a message in node 'DataShare_PCS_MF_PROJ_112007.PcsDbCallsCompute'.
The message broker detected an error whilst processing a message in node 'DataShare_PCS_MF_PROJ_112007.PcsDbCallsCompute'. 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.
2007-12-03 16:42:55.835514 3580 RecoverableException BIP2488E: ('.DataShare_PCS_MF_PROJ_112007_PcsDbCallsCompute.Main', '221.3') Error detected whilst executing the SQL statement ''INSERT INTO Database.dbo.t_JobStatus ( SeqID, JobID, JobStatusCode, CreatedBy, LastUserID, CreatedDate, LastModifyDate ) VALUES (Int2, Char1, CAST(Dup AS INTEGER), 'PST', 'PST', TS, TS ) ''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2007-12-03 16:42:55.835521 3580 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.
2007-12-03 16:42:55.835529 3580 DatabaseException BIP2322E: Database error: SQL State ''22018''; Native Error Code '0'; Error Text ''[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification''.
The error has the following diagnostic information: SQL State ''22018'' SQL Native Error Code '0' SQL Error Text ''[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification''
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.
Thanks for advice,
Moshe |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Tue Dec 04, 2007 7:51 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Why not just use FORMAT to get the value you want, rather than all those substrings?
Why do you think this is with the TS rather than the (CAST Dup AS INTEGER)?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| scravr |
| Posted: Tue Dec 04, 2007 8:05 am Post subject: MB V6 - MSSQL : DATETIME cast error |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
this is not the cast(dup.... since i successfuly insert without the TS field.
what do you mean by FORMAT?
Is TS should be declared as CHAR or as TIMESTAMP (and how sould I code the FORMAT or substrings)? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 04, 2007 8:21 am Post subject: Re: MB V6 - MSSQL : DATETIME cast error |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| scravr wrote: |
DECLARE TS1 CHARACTER;
SET TS1 = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyy/MM/dd HH:mm:ss');
|
You should be able to adjust this to produce your "nn AM" format, rather than jumping through all those extra substrings.
And then decide if "nn AM" is a valid specifier for the field you're inserting.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| scravr |
| Posted: Tue Dec 04, 2007 8:58 am Post subject: MB V6 - MSSQL : DATETIME cast error |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
The only way this works is:
DECLARE TS TIMESTAMP;
SET TS = CAST(CURRENT_TIMESTAMP AS TIMESTAMP FORMAT 'yyyy/MM/dd HH:mm:ss');
This did not work:
DECLARE TS1 CHARACTER;
SET TS1 = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyy/MM/dd HH:mm:ss');
Thanks for the help |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Dec 04, 2007 9:17 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I just posted sections of the code you posted, and said "change it to produce what you want".
If the code you posted doesn't work, then have you now solved your problem?
You shouldn't need to write any code to add AM or PM, like you have. You should be able to tweak the format to do it for you.
And then you need to validate the format against what SQLServer wants, and then tweak the format again until it's valid.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| scravr |
| Posted: Wed Dec 05, 2007 6:25 am Post subject: MB V6 - MSSQL : DATETIME cast error |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
This is how I got it to work (when CreatedDate is defined as DATETIME in MSSQL) :
DECLARE TS TIMESTAMP;
...
...
SET TS = CAST(CURRENT_TIMESTAMP AS TIMESTAMP FORMAT 'yyyy/MM/dd HH:mm:ss');
...
...
INSERT INTO Database.dbo.t_JobStatus
(SeqID, JobID, JobStatusCode, CreatedBy, LastUserID, CreatedDate, LastModifyDate, DateStatus)
VALUES
(Int2, Char1, DupI, 'PST', 'PST', TS, TS, SD);
CALL CheckSQLSTATE(SQLSTATE, SQLERRORTEXT, SQLCODE, SQLNATIVEERROR);
There is no need for AM/PM.
Thanks for the advice and help,
Moshe |
|
| 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
|
|
|
|
