| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| PASSTHRU SQL Query Not Working |
« View previous topic :: View next topic » |
| Author |
Message
|
| kiruthigeshwar |
 Posted: Mon Feb 10, 2014 9:07 am Post subject: PASSTHRU SQL Query Not Working |
 |
|
Acolyte
Joined: 31 Oct 2012
Posts: 50
|
Hi All,
We are using SQL database. Which we migrated from 2008 to 2012, but after that. We are not able to insert data into table, because of one DATETIME field. which was working before migration. But the same query is code is working fine when the field type in the table is changed to DATETIME2. But all other applications using the database isn't working with the same. So I have been asked to fix it.
Same happened with select query on same table, when I use the DATETIME field in WHERE clause. Then I tried to concat it inside query string which worked only when I used quotes for the value.
Is it a driver issue.
_________________
Regards,
K |
|
| Back to top |
|
 |
| Gralgrathor |
| Posted: Mon Feb 10, 2014 9:26 am Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kiruthigeshwar wrote: |
| Is it a driver issue. |
Could you provide more info? Like, for instance:
- The driver you're using
- The exact query/code snippet
- The error message you're getting
Also, have you considered *not* using PASSTHRU? |
|
| Back to top |
|
|
| kiruthigeshwar |
| Posted: Mon Feb 10, 2014 11:27 pm Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Acolyte
Joined: 31 Oct 2012
Posts: 50
|
| Gralgrathor wrote: |
| kiruthigeshwar wrote: |
| Is it a driver issue. |
Could you provide more info? Like, for instance:
- The driver you're using
- The exact query/code snippet
- The error message you're getting
Also, have you considered *not* using PASSTHRU? |
Exception with PASSTHRU
Statement is PASSTHRU('INSERT INTO chequerequest(AccountNo, AccountName,RoutingCode,ChequeNoFrom,ChequeNoTo,RequestNumber,RequestDate,RequestedUserName, RequestingBranchCode, ChequeBookMainType, ChequeBookSubType, Reserve1,Reserve2) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)', accountNumber, accountName, routingCode, startNumber, endNumber, requestNumber, requestDate, requestUserName, requestBranchCode, chequeBookMainType, chequeBookSubType, reserve1, reserve2)
| Code: |
| [IBM][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. |
Exception without PASSTHRU
Statement is INSERT INTO Database.chequerequest ( AccountNo, AccountName, RoutingCode, ChequeNoFrom, ChequeNoTo, RequestNumber, RequestDate, RequestedUserName, RequestingBranchCode, ChequeBookMainType, ChequeBookSubType, Reserve1, Reserve2 ) VALUES (accountNumber, accountName, routingCode, startNumber, endNumber, requestNumber, requestDate, requestUserName, requestBranchCode, chequeBookMainType, chequeBookSubType, reserve1, reserve2 )
| Code: |
| [IBM][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. |
Note: Variable type in ESQL (requestDate) is DATE, also tried TIMESTAMP. The field type in SQL table is DATETIME.
Below is the driver detail.
databaseProviderVersion = 10.50.4297
driverVersion = 06.00.0157 (b0108, U0090)
driverOdbcVersion = 03.52
driverManagerVersion = 03.52.0600.0129
driverManagerOdbcVersion = 03.52.0000
databaseProviderName = Microsoft SQL Server
datasourceServerName = VMSERVER35
databaseName = QuickcheckDB
odbcDatasourceName = QUICKCHECKDB
Sorry the database is SQL 2008 and the previous version is 2005
_________________
Regards,
K |
|
| Back to top |
|
|
| dogorsy |
| Posted: Tue Feb 11, 2014 12:01 am Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| kiruthigeshwar wrote: |
Then I tried to concat it inside query string which worked only when I used quotes for the value.
|
Can you please specify clearly what value are you passing to the query ? from the sentence above it seems you are passing a character string.
| Quote: |
Is it a driver issue. |
why do you think that ? Please get a user trace with the -l debug option and it is very likely you will get the answer
Last edited by dogorsy on Tue Feb 11, 2014 5:37 am; edited 1 time in total |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Tue Feb 11, 2014 12:51 am Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kiruthigeshwar wrote: |
| Variable type in ESQL (requestDate) is DATE, also tried TIMESTAMP. The field type in SQL table is DATETIME. |
As dogorsky asked: can you show the code in which you declare and assign requestDate?
Also, have any settings changed on the SQL server instance or host during migration? For instance, LANGUAGE or DATEFORMAT? Have any settings changed on the broker machine or instance? |
|
| Back to top |
|
|
| kiruthigeshwar |
| Posted: Tue Feb 11, 2014 10:40 pm Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Acolyte
Joined: 31 Oct 2012
Posts: 50
|
| Gralgrathor wrote: |
| kiruthigeshwar wrote: |
| Variable type in ESQL (requestDate) is DATE, also tried TIMESTAMP. The field type in SQL table is DATETIME. |
As dogorsky asked: can you show the code in which you declare and assign requestDate?
Also, have any settings changed on the SQL server instance or host during migration? For instance, LANGUAGE or DATEFORMAT? Have any settings changed on the broker machine or instance? |
| Code: |
DECLARE requestDate DATE ;
SET requestDate = InputRoot.MRM.Body.requestDate;
|
InputRoot.MRM.Body.requestDate is of type xsd:date in message set.
And I am said that none of the above changes were made in the SQL server. It is same as the previous one.
And DATETIME2 is working fine, while DATETIME is not working.
_________________
Regards,
K |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Wed Feb 12, 2014 12:11 am Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kiruthigeshwar wrote: |
| InputRoot.MRM.Body.requestDate is of type xsd:date in message set. |
So, you can't change the table because other applications will fail, but you can change the ESQL code of the flow you're working on to fix the problem? Does it work when you cast the xsd:date (DATE) to a TIMESTAMP? |
|
| Back to top |
|
|
| kiruthigeshwar |
| Posted: Mon Feb 24, 2014 12:46 am Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Acolyte
Joined: 31 Oct 2012
Posts: 50
|
| Gralgrathor wrote: |
| kiruthigeshwar wrote: |
| InputRoot.MRM.Body.requestDate is of type xsd:date in message set. |
So, you can't change the table because other applications will fail, but you can change the ESQL code of the flow you're working on to fix the problem? Does it work when you cast the xsd:date (DATE) to a TIMESTAMP? |
No it didnt. I tried with TIMESTAMP in ESQL too.
_________________
Regards,
K |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Mon Feb 24, 2014 11:37 am Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kiruthigeshwar wrote: |
| No it didnt. I tried with TIMESTAMP in ESQL too. |
Right, you said so. I'm sorry; I'm out of easy questions or answers.
I don't have a toolkit handy right now: might it be possible to replace the TIMESTAMP with a formatted CHARACTER field without causing an exception?
If not, then the only other thing I can think of right now is the option you mentioned earlier: format the SQL query in a string, concatting the value into the string in stead of passing it as a parameter, and use PASSTHRU.
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| cirnogenius |
| Posted: Thu Feb 27, 2014 6:17 am Post subject: |
|
|
Newbie
Joined: 22 Oct 2013
Posts: 2
|
Yes, you can create smth. like
| Code: |
PASSTHRU('INSERT INTO chequerequest(AccountNo, AccountName,RoutingCode,ChequeNoFrom,ChequeNoTo,RequestNumber,RequestDate,RequestedUserName, RequestingBranchCode, ChequeBookMainType, ChequeBookSubType, Reserve1,Reserve2) VALUES (?,?,?,?,?,?,TO_TIMESTAMP(?,''DB_TIMESTAMP''),?,?,?,?,?,?)', accountNumber, accountName, routingCode, startNumber, endNumber, requestNumber, CAST(requestDate AS CHAR FORMAT 'WMB_TIMESTAMP'), requestUserName, requestBranchCode, chequeBookMainType, chequeBookSubType, reserve1, reserve2)
|
Where
TO_TIMESTAMP is your sql database function for char->timestamp convertion,
DB_TIMESTAMP is timestamp pattern in database format for the sent string
WMB_TIMESTAMP is pattern for timestamp in broker format for the desired string, corresponding to the pattern in TO_TIMESTAMP function.
P.S. I may be wrong with qoutes surrounding DB_TIMESTAMP - try double qoutes too. |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Thu Feb 27, 2014 7:01 am Post subject: Re: PASSTHRU SQL Query Not Working |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kiruthigeshwar wrote: |
| I tried with TIMESTAMP in ESQL too. |
WMBToolkit 7.0.0.6 / WMBruntime 7.0.0.4 (running against MSSQL) at least will accept the following construct:
| Code: |
DECLARE utc INTEGER CAST((CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 01:00:00') SECOND as INTEGER);
INSERT INTO Database.SecondsSince1970
("[Created]", "[Seconds]")
VALUES
(CAST(CURRENT_TIMESTAMP as CHARACTER FORMAT 'yyyy-MM-dd HH:mm:ss'), utc); |
Whereby "Created" is defined as a "datetime". So the broker itself will accept and correctly pass on to the driver a character field for a datetime type.
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| 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
|
|
|
|
