| Author |
Message
|
| neerav |
 Posted: Wed Jun 07, 2006 1:50 pm Post subject: createSQLStatement SELECT with WHERE CLAUSE failing |
 |
|
Novice
Joined: 31 May 2006
Posts: 22
|
Hello,
Current Scenario:
Need to use JavaCompute and createSQLStatement to fetch records from ORACLE DB with a where clause.
Issue:
Flow disappears in the JavaCompute node with the following error in the Windows Event Log:
+++
( SDTS_BROKER.route ) An error occurred in the JNI layer during flow debugging
+++
My Code:
MbSQLStatement state = createSQLStatement(dataSourceName, "SET Environment.Variables.RoutingData.ResultSet[] = PASSTHRU(' SELECT * FROM CRM_BKR_RTNG WHERE SRVC_NAME_TXT = "+srvcName+"');");
state.setThrowExceptionOnDatabaseError(true);
MbMessageAssembly outAssembly = new MbMessageAssembly(contact admin, outMessage);
state.select(contact admin, outAssembly);
out.propagate(outAssembly);
Possibilities tried:
1. PASSTHRU+no where clause :: works
2. PASSTHRU+where clause :: JNI error (as shown above)
3. No PASSTHRU+no where clause :: JNI error
4. No PASSTHRU+where clause :: JNI error
Limitations:
1. Cannot have oracle stored procedure
2. Cannot use JDBC connection oriented java programming
3. Have to use MbSQLStatement / createSQLStatement class
Toolkit Version: 6.0.0.1
Build id: 20060106_1130
Can you please let me know where am I making a mistake?
Neerav |
|
| Back to top |
|
 |
| wschutz |
| Posted: Thu Jun 08, 2006 8:29 am Post subject: |
|
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
and I assume if you code that statement in a esql compute module, it works ok?
_________________
-wayne |
|
| Back to top |
|
|
| neerav |
| Posted: Thu Jun 08, 2006 9:08 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
Update: We did some change to the query and applied the FP.
Status: Success yet to be seen.
Code Changes :
String query1 ="SET Environment.Variables.Results.rtdb[]";
String query1a = "(SELECT RT.* FROM CRM_BKR_RTNG AS RT WHERE RT.SRVC_NAME_TXT=serviceName)";
MbSQLStatement state = createSQLStatement(dataSourceName, query1 +"="+query1a );
NOTE: serviceName is the actual value passed to the query and not a variable.
Error:
Exception List (
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbDataFlowNode.cpp'
(0x03000000):Line = 616
(0x03000000):Function = 'ImbDataFlowNode::createExceptionList'
(0x03000000):Type = 'ComIbmJniNode'
(0x03000000):Name = 'route/route#FCMComposite_1_6'
(0x03000000):Label = 'route.route.QueryDB_ProcessCanMsg_Route-JavaCompute'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x03000000):Text = 'Node throwing exception'
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlFieldRef.cpp'
(0x03000000):Line = 3063
(0x03000000):Function = 'SqlFieldReference::resolve'
(0x03000000):Type = 'ComIbmJniNode'
(0x03000000):Name = 'route/route#FCMComposite_1_6'
(0x03000000):Label = 'route.route.QueryDB_ProcessCanMsg_Route-JavaCompute'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2432
(0x03000000):Text = 'First path element must be a valid correlation name'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '1.60'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'CRM_BKR_RTNG'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'Environment, InputLocalEnvironment, OutputLocalEnvironment, InputRoot, InputBody, InputProperties, OutputRoot, InputExceptionList, OutputExceptionList, Database, InputDestinationList, OutputDestinationList'
)
)
)
)
This Code works in ESQL but doing the same functionality in Java doesn't. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 08, 2006 9:18 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| neerav wrote: |
| This Code works in ESQL but doing the same functionality in Java doesn't. |
I don't see why it would work in ESQL as written. ESQL requires the "Database" keyword as a correlation name in SELECT statements.
| Quote: |
String query1 ="SET Environment.Variables.Results.rtdb[]";
String query1a = "(SELECT RT.* FROM Database.CRM_BKR_RTNG AS RT WHERE RT.SRVC_NAME_TXT=serviceName)";
MbSQLStatement state = createSQLStatement(dataSourceName, query1 +"="+query1a );
|
And the exception list bears me out. It's complaining that "CRM_BKR_RTNG" is not a valid Correlation Name, and lists the valid possibilities - one of which is 'Database'.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| neerav |
| Posted: Thu Jun 08, 2006 9:40 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 08, 2006 10:00 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
That link shows a PASSTHRU ESQL statement. PASSTHRU statements have to be valid SQL on the database server.
Your code is running a regular ESQL SELECT, not a PASSTHRU. Regular ESQL SELECT statements have to use the 'Database' Correlation ID.
Also, your link says
| Quote: |
| passing in the ODBC datasource, a broker EQSL statement, and optionally the transaction mode to the method |
My emphasis. The ESQL has to be valid ESQL.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| neerav |
| Posted: Thu Jun 08, 2006 10:25 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
Updated JavaCode:
String query1 ="SET Environment.Variables.Results.rtdb";
String query1a = "(SELECT RT.* FROM Database.CRMDBA.CRM_BKR_RTNG AS RT WHERE RT.SRVC_NAME_TXT = serviceName)";
MbSQLStatement state = createSQLStatement(dataSourceName, query1 +"="+query1a );
Result (Text: First path element must be a valid correlation name):
-------------------------------
Exception List (
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbDataFlowNode.cpp'
(0x03000000):Line = 616
(0x03000000):Function = 'ImbDataFlowNode::createExceptionList'
(0x03000000):Type = 'ComIbmJniNode'
(0x03000000):Name = 'route/route#FCMComposite_1_6'
(0x03000000):Label = 'route.route.QueryDB_ProcessCanMsg_Route-JavaCompute'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x03000000):Text = 'Node throwing exception'
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlFieldRef.cpp'
(0x03000000):Line = 3063
(0x03000000):Function = 'SqlFieldReference::resolve'
(0x03000000):Type = 'ComIbmJniNode'
(0x03000000):Name = 'route/route#FCMComposite_1_6'
(0x03000000):Label = 'route.route.QueryDB_ProcessCanMsg_Route-JavaCompute'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2432
(0x03000000):Text = 'First path element must be a valid correlation name'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '1.118'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'serviceName'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'Environment, InputLocalEnvironment, OutputLocalEnvironment, InputRoot, InputBody, InputProperties, OutputRoot, InputExceptionList, OutputExceptionList, InputDestinationList, OutputDestinationList, RT'
)
)
)
)
-------------------------------
Valid SQL:
SELECT * FROM CRMDBA.CRM_BKR_RTNG WHERE SRVC_NAME_TXT = 'serviceName';
CRMDBA = database schema
What's the ESQL equivalent for the MbSQLStatement? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 08, 2006 10:28 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
See how the Inserts for the Exception list have changed?
See that instead of reporting "CRM_BKR_RTNG" in an Insert, it's now reporting "serviceName"?
See how your valid ESQL has 'serviceName', and your invalid MbSQLStatement has just serviceName?
| Code: |
| String query1a = "(SELECT RT.* FROM Database.CRMDBA.CRM_BKR_RTNG AS RT WHERE RT.SRVC_NAME_TXT = 'serviceName')"; |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| neerav |
| Posted: Thu Jun 08, 2006 10:50 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
this causes the broker debugger to hang. (I had to kill the debugger through TaskManager)
updated Code:
String query1 ="SET Environment.Variables.Results.rtdb[]";
String query1a = "(SELECT RT.* FROM Database.CRMDBA.CRM_BKR_RTNG AS RT WHERE RT.SRVC_NAME_TXT = 'serviceName')";
MbSQLStatement state = createSQLStatement(dataSourceName, query1 +"="+query1a );
Yes, even we realise that passing that variable to the query is causing the broker to throw an exception or die out for other combinations (as mentioned in the first message by me)
Possibilities used:
With PASSTHRU and without PASSTHRU both cases failing |
|
| Back to top |
|
|
| wschutz |
| Posted: Thu Jun 08, 2006 11:00 am Post subject: |
|
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
Might be time to open a PMR...have you put the latest service on the broker?
_________________
-wayne |
|
| Back to top |
|
|
| neerav |
| Posted: Thu Jun 08, 2006 11:05 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
| well, for now i think i have cracked the problem. Thanks to jefflowrey |
|
| Back to top |
|
|
| neerav |
| Posted: Thu Jun 08, 2006 11:08 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
But as an FYI: We cannot use the Debugger (with latest FP for toolkit, Broker and the debugger) to test this scenario.
All we did was stopped using the debugger until the last test and saw the expected result. It did work!
So i think it is a debugger problem and not the broker. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 08, 2006 11:34 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Are you at the latest/correct version of RAC?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| neerav |
| Posted: Mon Jun 12, 2006 7:13 am Post subject: |
|
|
Novice
Joined: 31 May 2006
Posts: 22
|
|
| Back to top |
|
|
|
|
