| Author |
Message
|
| rahulibm |
 Posted: Sat Mar 26, 2016 1:50 pm Post subject: JDBC Error when using the Database Retrieve Node |
 |
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
Hi,
I am trying to explore the usage of Database Retrieve node by creating a simple message flow. I have defined the JDBC driver connection and when i check the status in Data Source Explorer, it shows as connected. But when i deploy the flow and trigger a message to flow, i get the below error:
RecoverableException
File:CHARACTER:F:\build\slot1\S000_P\src\DataFlowEngine\MessageServices\ImbDataFlowNode.cpp
Line:INTEGER:1239
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmDatabaseRetrieveNode
Name:CHARACTER:databasenode_test#FCMComposite_1_4
Label:CHARACTER:databasenode_test.Database Retrieve
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
Insert
Type:INTEGER:14
Text:CHARACTER:databasenode_test.Database Retrieve
DatabaseException
File:CHARACTER:Undefined
Line:INTEGER:-1
Function:CHARACTER:JDBCConnection::createConnection
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:6232
Text:CHARACTER:Problem encountered obtaining JDBC connection
Insert
Type:INTEGER:5
Text:CHARACTER:Broker 'TESTNODE_Admin_RahulSin'; Execution Group 'testintsrvr2'; Message Flow 'databasenode_test'; Node 'Database Retrieve'; Node Type 'DatabaseRetrieve
Insert
Type:INTEGER:5
Text:CHARACTER:oracle.jdbc.OracleDriver
Insert
Type:INTEGER:5
Text:CHARACTER: error message: No suitable driver, SQLState value: 08001, stack trace: [com.ibm.broker.jdbcnodes.JDBCConnection.createConnection(Unknown Source)
com.ibm.broker.jdbcnodes.JDBCDatabaseManager.getThreadOwnedConnections(Unknown Source)
com.ibm.broker.jdbcnodes.DatabaseRetrieveNode.evaluate(Unknown Source)
com.ibm.broker.plugin.MbNode.evaluate(Unknown Source)]
For a moment i thought the JDBC driver is not set up correctly, but that cannot be the case as i have an another flow which uses Database Input node that also uses the same JDBC connection. I can see this flow does get triggered when i make an update on the event table. so the connection does work. But im not sure why the database retrieve node is throwing this error.
Please help.  |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sat Mar 26, 2016 10:47 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
Not all Database nodes use JDBC. Some use ODBC.
Have you verified that you do not need the ODBC setup? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rahulibm |
| Posted: Sat Mar 26, 2016 10:52 pm Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
As per the documentation , i understand that for Database nodes to work, we have to set up a JDBC connection and not ODBC.
I could use the DatabaseInput node to work with the JDBC connection, but when i attempt to use the DatabaseRetrieve or Database Route node i get the below error. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Mar 27, 2016 12:13 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
From my understanding of the nodes the correlation is as follows
ODBC connection
Database
Database Input
JDBC connection
Database Route
Database Retrieve
At least this is what the Node properties want when dropped onto a flow.
_________________
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 |
|
|
| rahulibm |
| Posted: Sun Mar 27, 2016 12:33 pm Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
| Yes, even the databaseinput node works on ODBC. But the issue here is with the DatabaseRetrive node throwing error with the JDBC driver. In the Data Source explorer I can see the connection status for the JDBC profile as "Connection Active". Is there a way to identify the issue for this error ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Mar 29, 2016 5:15 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The error you've shown is from the runtime, not the toolkit.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| rahulibm |
| Posted: Tue Mar 29, 2016 5:36 am Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
| Yes, it is from runtime. I do not have any compile time errors. I captured this information from the exception tree when i posted a message to the flow. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Mar 29, 2016 6:32 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So.
You have configured Toolkit properly.
And you are getting an error from Runtime.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| rahulibm |
| Posted: Tue Mar 29, 2016 6:58 am Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
| Yes. As i stated above, I have created a JDBC profile and when I view the profile in the Data Source Explorer , i can see the Connection is Active and when i expand the profile it also shows me the schema and the tables present under that schema. I believe if there was an issue with JDBC connection, then i wouldn't have been able to create this profile successfully in first place and not be able to view those tables. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Mar 29, 2016 7:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You keep talking about the Toolkit.
You are getting an error from the Runtime.
These are not the same thing. They do not use the same configuration.
Stop looking at the Toolkit.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| rahulibm |
| Posted: Tue Mar 29, 2016 7:43 am Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
| OK. So what do i need to do to check if the runtime can see the jdbc profile that needs to be used for this flow and it if it is working ? |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Mar 29, 2016 8:11 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| rahulibm wrote: |
| OK. So what do i need to do to check if the runtime can see the jdbc profile that needs to be used for this flow and it if it is working ? |
How did you create the configurable service?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| rahulibm |
| Posted: Tue Mar 29, 2016 8:33 am Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
Using the Web User interface for the Integration Node. Below are the properties for the service.
C:\Program Files\IBM\IIB\10.0.0.1>mqsireportproperties TESTNODE_Admin_RahulSin -c JDBCProviders -o testdb -r
JDBCProviders
testdb
connectionUrlFormat='jdbc:oracle:thin:testdb/test1234@localhost:1521:xe'
connectionUrlFormatAttr1='xe'
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='testdb'
databaseSchemaNames='useProvidedSchemaNames'
databaseType='Oracle'
databaseVersion='10'
description='default_Description'
environmentParms='default_none'
jarsURL='<ORACLE HOME>\jdbc\lib'
jdbcProviderXASupport='true'
maxConnectionPoolSize='0'
portNumber='1521'
securityIdentity='testdb@localhost'
serverName='localhost'
type4DatasourceClassName='oracle.jdbc.xa.client.OracleXADataSource'
type4DriverClassName='oracle.jdbc.OracleDriver' |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Mar 29, 2016 9:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The connectionUrlFormat is wrong.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| rahulibm |
| Posted: Tue Mar 29, 2016 9:37 am Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
| When creating the service, i created a clone of the standard JDBC service and modified only the username, password, database name and service name. Rest of them are the values as exported from the standard JDBC properties. |
|
| Back to top |
|
|
|
|
