| Author |
Message
|
| Vitor |
 Posted: Tue Mar 29, 2016 10:17 am Post subject: |
 |
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| rahulibm wrote: |
| 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. |
You sure? That URL doesn't look like I'd expect either.
Not that I'm an expert in JDBC you understand, but still.....
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Tue Mar 29, 2016 10:36 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| mqjeff wrote: |
| The connectionUrlFormat is wrong. |
| rahulibm wrote: |
| modified only the username, password, database name and service name. |
You put them directly into the connectionUrlFormat.
That's wrong.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Mar 29, 2016 10:38 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
You put them directly into the connectionUrlFormat.
That's wrong. |
I'd expect it to be a format string
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| rahulibm |
| Posted: Tue Mar 29, 2016 1:46 pm Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
Below is how the standard JDBC service properties for a Oracle looks like in the Web User Interface for the Integration Node:
type4DatasourceClassName oracle.jdbc.xa.client.OracleXADataSource
type4DriverClassName oracle.jdbc.OracleDriver
databaseType Oracle
jdbcProviderXASupport true
portNumber 1521
connectionUrlFormatAttr5
connectionUrlFormatAttr4
serverName default_Database_Server_Name
connectionUrlFormatAttr3
connectionUrlFormatAttr2
connectionUrlFormatAttr1 <SID>
environmentParms default_none
maxConnectionPoolSize 0
description default_Description
jarsURL <ORACLE HOME>\jdbc\lib
databaseName default_Database_Name
databaseVersion default_Database_Version
securityIdentity default_User@default_Server
connectionUrlFormat jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1]
I just updated the database details, user id and password in the service i created using the above properties. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Mar 29, 2016 11:41 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
If you care to search this forum for
| Quote: |
connectionUrlFormat
|
you will see a clear set of help that say in no uncertain terms,
DO NOT EDIT the connectionUrlFormat string.
It is a pattern and must not be changed.
[/code]
_________________
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 |
|
|
| mqjeff |
| Posted: Wed Mar 30, 2016 5:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| rahulibm wrote: |
| connectionUrlFormat jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1] |
To repeat again.
You changed this.
THAT IS WRONG.
Use the rest of the properties of the configurable service to provide the necessary information.
DO NOT CHANGE THE CONNECTIONFORMATURL.
Get it?
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| rahulibm |
| Posted: Wed Mar 30, 2016 5:22 am Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
I see what you are saying. If i remember i did not update the connectionurlformat directly. I rather updated the other properties and as I updated the properties, the connection url format was getting updated automatically.
I would probably give an another try in creating the service and provide a feedback to you guys.
Thank you. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Mar 30, 2016 5:28 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Delete the Configurable service and re-create it. Don't try to edit the existing one.
If it would help, post the mqsicreateconfigurableservice command here.
Then post the output from the display of the params.
_________________
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: Wed Mar 30, 2016 4:49 pm Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
I deleted the configurable service and created a new one using the below command:
mqsicreateconfigurableservice TESTNODE_Admin_RahulSin -c JDBCProviders -o testdb -n databaseName,connectionUrlFormat
Attr1,serverName -v testdb,xe,localhost
BIP8071I: Successful command completion.
Below is how the service definition looks like. I see the connectionUrl is empty.
mqsireportproperties TESTNODE_Admin_RahulSin -c JDBCProviders -o testdb -r
JDBCProviders
testdb
connectionUrlFormat=''
connectionUrlFormatAttr1='xe'
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='testdb'
databaseSchemaNames='useProvidedSchemaNames'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='default_Description'
environmentParms='default_none'
jarsURL='default_Path'
jdbcProviderXASupport='jdbcProviderXASupport'
maxConnectionPoolSize='0'
portNumber='default_Port_Number'
securityIdentity='default_User@default_Server'
serverName='localhost'
type4DatasourceClassName='default_Type_Four_Datasource_Class_Name'
type4DriverClassName='default_Type_Four_Driver_Class_Name'
BIP8071I: Successful command completion.
I restarted the Integration Node/Broker after creating the service and when i tried to deploy the flow having a database retrieve node , deployment failed. Below is what i see in the Windows Event Viewer's Application Log:
( TESTNODE_Admin_RahulSin.testintsrvr2 ) Invalid configuration message containing attribute value ''empty string'', which is not valid for target attribute ''connectionUrlFormat'', object ''JDBCProvider: testdb''; valid values are ''well formatted url''.
The integration node received a configuration message containing the attribute value ''empty string'', which is not valid for the target attribute ''connectionUrlFormat'', on object ''JDBCProvider: testdb''. Valid values are ''well formatted url''. This can be caused by a mismatch in levels between the Integration Toolkit and the integration node. Or it can be caused as a result of a node defined by a user, or written by a third party, where the implementation library installed at the integration node does not match the node definition held at the Integration Toolkit.
Ensure that the levels of code installed at the Integration Toolkit and integration node are consistent. If they are, identify the supplier of the target object and report the problem to them. If this is IBM, contact your IBM support center.
Please advise. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 30, 2016 7:23 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
The conection URL Format is not a connection url. It represents a connection URL template, Form which the runtime will Bild the connection URL. Check out in the Infocenter the section in jdbc configurable services. There are examples giving the connection URL Format for multiple databases. Find the one hat matches your DB type and Copy it. AS is.
You will then need to populate the parameters in the configurable service that will be substituted by the runtime in the URL Format.
Habe fun 
Errors due to German language autocorrect...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rahulibm |
| Posted: Wed Mar 30, 2016 8:34 pm Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
I recreated the service and it looks like below:
JDBCProviders
testdb
connectionUrlFormat='jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1]'
connectionUrlFormatAttr1='xe'
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='testdb'
databaseSchemaNames='testdb'
databaseType='Oracle'
databaseVersion='11'
description='default_Description'
environmentParms='default_none'
jarsURL='<ORACLE HOME>\jdbc\lib'
jdbcProviderXASupport='true'
maxConnectionPoolSize='0'
portNumber='1521'
securityIdentity='default_User@default_Server'
serverName='localhost'
type4DatasourceClassName='oracle.jdbc.xa.client.OracleXADataSource'
type4DriverClassName='oracle.jdbc.OracleDriver'
Restarted the node, deployed the flow and when the message attempts to pass through Database Retrieve Node , it throws an exception with respect to driver.
ExceptionList
RecoverableException
File:CHARACTER:F:\build\slot1\S000_P\src\DataFlowEngine\MessageServices\ImbDataFlowNode.cpp
Line:INTEGER:1239
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:databasenode_test#FCMComposite_1_1
Label:CHARACTER:databasenode_test.TESTIN
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
Insert
Type:INTEGER:14
Text:CHARACTER:databasenode_test.TESTIN
RecoverableException
File:CHARACTER:F:\build\slot1\S000_P\src\DataFlowEngine\PluginInterface\ImbJniNode.cpp
Line:INTEGER:1273
Function:CHARACTER:ImbJniNode::evaluate
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:Caught exception and rethrowing
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)]
The driver name mentioned in the exception tree is the value defined as the Driverclassname in the service properties. Could be it something that the required class file is missing in my machine ? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 30, 2016 11:17 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
I hope that in the jarsURL you replace <Oracle Home> with the actual path leading to oracle home...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rahulibm |
| Posted: Thu Mar 31, 2016 6:06 am Post subject: |
|
|
Novice
Joined: 26 Mar 2016
Posts: 19
|
Thank you and a big thank to each one of you who provided their comments 
I could get the jdbc connection and Database Retrieve node to work now.
I just had to substitute the ORACLE_HOME with the actual path and define the userid and password for the JDBC connection using the mqsisetdbparms command.
Cheers  |
|
| Back to top |
|
|
|
|
