| Author |
Message
|
| afroz11031 |
 Posted: Thu Jan 05, 2017 10:31 pm Post subject: Timestamp issue - data fetching from Oracle DB using esql |
 |
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
Hi IIB experts,
We are facing an issue while fetching the records from Oracle DB of a specified timestamp using esql code. Please note we are using IIB V9. We need your assistance in this. Please help!. Below are the details.
Code:
DECLARE MyTimeStamp TIMESTAMP CURRENT_TIMESTAMP;
DECLARE sqlQuery CHARACTER 'SELECT DISTINCT(CUSTOMER_ID) FROM CUSTOMER_DETAILS cd WHERE cd.TIMESTAMP<=?' ;
SET OutputLocalEnvironment.Variables.RESULTSET[] = PASSTHRU(sqlQuery VALUES(MyTimeStamp));
Error returned:
[IBM][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1.' (CHARACTER)
Trace details:
ExceptionList:( ['MQROOT' : 0x29c5f3d0]
(0x01000000:Name):RecoverableException = (
(0x03000000:NameValue):File = 'F:\build\slot1\S900_P\src\DataFlowEngine\SQLNodeLibrary\ImbComputeNode.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 497 (INTEGER)
(0x03000000:NameValue):Function = 'ImbComputeNode::evaluate' (CHARACTER)
(0x03000000:NameValue):Type = 'ComIbmComputeNode' (CHARACTER)
(0x03000000:NameValue):Name = 'com/mobi/esb/Golden_BSCS_Integration#FCMComposite_1_13' (CHARACTER)
(0x03000000:NameValue):Label = 'com.mobi.esb.Golden_BSCS_Integration.ComputeRoute' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2230 (INTEGER)
(0x03000000:NameValue):Text = 'Caught exception and rethrowing' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 14 (INTEGER)
(0x03000000:NameValue):Text = 'com.mobi.esb.Golden_BSCS_Integration.ComputeRoute' (CHARACTER)
)
(0x01000000:Name ):RecoverableException = (
(0x03000000:NameValue):File = 'F:\build\slot1\S900_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 650 (INTEGER)
(0x03000000:NameValue):Function = 'SqlStatementGroup::execute' (CHARACTER)
(0x03000000:NameValue):Type = 'ImbESQLManager' (CHARACTER)
(0x03000000:NameValue):Name = 'ImbESQLManager' (CHARACTER)
(0x03000000:NameValue):Label = 'ImbESQLManager' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2488 (INTEGER)
(0x03000000:NameValue):Text = 'Error detected, rethrowing' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'com.mobi.esb.Golden_BSCS_Integration_ComputeRoute.Main' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = '9.9' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'SET OutputLocalEnvironment.Variables.RESULTSET[ ] = DEFAULTPASSTHRU(sqlQuery, MyTimeStamp);' (CHARACTER)
)
(0x01000000:Name ):DatabaseException = (
(0x03000000:NameValue):File = 'F:\build\slot1\S900_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 3246 (INTEGER)
(0x03000000:NameValue):Function = 'ImbOdbcStatement::checkRcInner' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2321 (INTEGER)
(0x03000000:NameValue):Text = 'Root SQL exception' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '-1' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 14 (INTEGER)
(0x03000000:NameValue):Text = 'odbc32.dll' (CHARACTER)
)
(0x01000000:Name ):DatabaseException = (
(0x03000000:NameValue):File = 'F:\build\slot1\S900_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 3424 (INTEGER)
(0x03000000:NameValue):Function = 'ImbOdbcStatement::checkRcInner' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2322 (INTEGER)
(0x03000000:NameValue):Text = 'Child SQL exception' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = '22001' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '0' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = '[IBM][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1.' (CHARACTER)
)
)
)
)
)
) |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Jan 05, 2017 11:22 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Oracle is a bit 'iffy' in my experience when it comes to timestamps and IIB
I'd start by changing this code
| Code: |
DECLARE MyTimeStamp TIMESTAMP CURRENT_TIMESTAMP;
DECLARE sqlQuery CHARACTER 'SELECT DISTINCT(CUSTOMER_ID) FROM CUSTOMER_DETAILS cd WHERE cd.TIMESTAMP<=?' ;
SET OutputLocalEnvironment.Variables.RESULTSET[] = PASSTHRU(sqlQuery VALUES(MyTimeStamp));
|
into something like this
| Code: |
DECLARE MyTimeStamp TIMESTAMP CURRENT_TIMESTAMP;
declare cTime CHAR;
set cTime = cast(MyTimeStamp as char format 'yyyy-MM-dd HH:mm:ss');
DECLARE sqlQuery CHARACTER 'SELECT DISTINCT(CUSTOMER_ID) FROM CUSTOMER_DETAILS cd WHERE cd.TIMESTAMP<=to_date(?,'insert oracle data format conversion string here')' ;
SET OutputLocalEnvironment.Variables.RESULTSET[] = PASSTHRU(sqlQuery VALUES(cTime));
|
Note that I am using the 'to_date' Oracle function to make sure that oracle can understand the date/time format. The input to the 'to_date' function is a char and I can't remember the exact format of the date/time conversion string for oracle. Just google for
'oracle to_Date format' and you should get it.
You can test the select string outside IIB using a tool like SQLDeveloper. That how I do this sort of thing with Oracle.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Fri Jan 06, 2017 5:08 am Post subject: Timestamp issue - data fetching from Oracle DB using esql |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
Thanks for your valuable input, however I have tried as per your suggestion but it did not work then I have changed the code from to_date to to_timestamp as below:
| Quote: |
DECLARE MyTimeStamp TIMESTAMP CURRENT_TIMESTAMP;
declare cTime CHAR;
set cTime = cast(MyTimeStamp as char format 'yyyy-MM-dd HH:mm:ss');
DECLARE sqlQuery CHARACTER 'SELECT DISTINCT(CUSTOMER_ID) FROM CUSTOMER_DETAILS cd WHERE cd.TIMESTAMP<=to_timestamp(?,'||'''YYYY-MM-DD HH:MI:SS'''||')'
SET OutputLocalEnvironment.Variables.RESULTSET[] = PASSTHRU(sqlQuery VALUES(cTime));
|
However after changing the code it did not work too.
Trace log :
| Quote: |
DatabaseException = (
(0x03000000:NameValue):File = 'F:\build\slot1\S900_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp' (CHARACTER)
(0x03000000:NameValue):Line = 3424 (INTEGER)
(0x03000000:NameValue):Function = 'ImbOdbcStatement::checkRcInner' (CHARACTER)
(0x03000000:NameValue):Type = '' (CHARACTER)
(0x03000000:NameValue):Name = '' (CHARACTER)
(0x03000000:NameValue):Label = '' (CHARACTER)
(0x03000000:NameValue):Catalog = 'BIPmsgs' (CHARACTER)
(0x03000000:NameValue):Severity = 3 (INTEGER)
(0x03000000:NameValue):Number = 2322 (INTEGER)
(0x03000000:NameValue):Text = 'Child SQL exception' (CHARACTER)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = 'HY000' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 2 (INTEGER)
(0x03000000:NameValue):Text = '1002' (CHARACTER)
)
(0x01000000:Name ):Insert = (
(0x03000000:NameValue):Type = 5 (INTEGER)
(0x03000000:NameValue):Text = '[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01002: fetch out of sequence' (CHARACTER) |
but when we pass directly the value of timestamp in query as below it is working fine.
DECLARE sqlQuery CHARACTER 'SELECT DISTINCT(CUSTOMER_ID) FROM CUSTOMER_DETAILS cd WHERE cd.TIMESTAMP<=to_timestamp('2017-01-04 11:34:10','YYYY-MM-DD HH:MI:SS')'
Not sure what is went wrong when we pass the value as cTime. Trace log is given above. please suggest.
Thanks
Afroz |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jan 06, 2017 5:15 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Code: |
| set cTime = cast(MyTimeStamp as char format 'yyyy-MM-dd HH:mm:ss'); |
Is a string, not a timestamp
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Fri Jan 06, 2017 6:20 am Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
Hi,
@mqjeff, yes that is a string and when we pass the value in sql , we are converting it to timestamp to match the format. do you have any other solution. Please suggest. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Jan 06, 2017 7:51 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
This page
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm
says:-
| Quote: |
TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
|
See, the format control in ORacle is different to the one use in IIB.
As I said, test the select in SQLDeveloper first. Get the format control right and it should work.
I'm referring to this
'DD-MON-RRHH24:MI:SS.FF'
This might not be the correct format but you can see what I'm talking about. You can't just use the same format as you used to convert the ESQL Timestamp value to a CHAR.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Sat Jan 07, 2017 11:20 pm Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
@smdavies99:
 yes i misunderstood in your previous comments, however i re-tried as per your suggestion and all looks good. Many Thanks.
Thanks
Afroz |
|
| Back to top |
|
|
|
|
