| Author |
Message
|
| cvag |
 Posted: Sat Jun 09, 2012 6:13 am Post subject: Failed to obtain JDBC connection |
 |
|
Centurion
Joined: 17 Mar 2008
Posts: 127
|
Hi,
Am trying to retrieve the values from Database using JDBC connection.
my JDBC properites:
| Quote: |
C:\IBM\MQSI\6.1>mqsireportproperties WBRK61_DEFAULT_BROKER -o SIMPLERROUTEDB -c
JDBCProviders -r
JDBCProviders
SIMPLERROUTEDB
connectionUrlFormat='jdbc:db2://localhost:50000/TEST:user=db2admin;password=db2admin;'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='TEST'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='Simplified Database Routing Sample Database'
jarsURL='C:\IBM\SQLLIB\java'
portNumber='50000'
securityIdentity='mySecurityIdentity'
serverName='localhost'
type4DatasourceClassName='com.ibm.db2.jcc.DB2DataSource'
type4DriverClassName='com.ibm.db2.jcc.DB2Driver'
BIP8071I: Successful command completion.
|
And the code which i have written for sample:
| Quote: |
public class DBOperation_JavaCompute extends MbJavaComputeNode {
public void evaluate(MbMessageAssembly contact admin) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbMessage inMessage = contact admin.getMessage();
// create new message
MbMessage outMessage = new MbMessage(inMessage);
MbMessageAssembly outAssembly = new MbMessageAssembly(contact admin,outMessage);
try {
// Obtain a java.sql.Connection using a JDBC Type4 datasource - in this example for a
// JDBC broker configurable service called "MyDB2"
// Connection conn = getJDBCType4Connection("SIMPLERROUTEDB",JDBC_TransactionType.MB_TRANSACTION_AUTO);
Connection conn = getJDBCType4Connection("SIMPLERROUTEDB", null);
// Example of using the Connection to create a java.sql.Statement
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs0 = stmt.executeQuery("SELECT NO, NAME FROM SAMPLE.EMP");
} catch (SQLException sqx ){
sqx.printStackTrace();
} finally {
// Clear the outMessage
outMessage.clearMessage();
}
}
}
|
Am stuck with an exceptionlist, though the connection is established.
ExceptionList:
| Quote: |
ExceptionList
RecoverableException
File:CHARACTER:F:\build\S610_P\src\DataFlowEngine\ImbDataFlowNode.cpp
Line:INTEGER:957
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:DBOperation#FCMComposite_1_1
Label:CHARACTER:DBOperation.MQInput
Catalog:CHARACTER:BIPv610
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:F:\build\S610_P\src\DataFlowEngine\PluginInterface\ImbJniNode.cpp
Line:INTEGER:1022
Function:CHARACTER:ImbJniNode::evaluate
Type:CHARACTER:ComIbmJavaComputeNode
Name:CHARACTER:DBOperation#FCMComposite_1_3
Label:CHARACTER:DBOperation.JavaCompute
Catalog:CHARACTER:BIPv610
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
RecoverableException
File:CHARACTER:MbNode.java
Line:INTEGER:1527
Function:CHARACTER:getJDBCType4Connection
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPv610
Severity:INTEGER:3
Number:INTEGER:6233
Text:CHARACTER:Failed to obtain JDBC Connection
Insert
Type:INTEGER:5
Text:CHARACTER:getJDBCType4Connection
Insert
Type:INTEGER:5
Text:CHARACTER:SIMPLERROUTEDB
Insert
Type:INTEGER:5
Text:CHARACTER:
Insert
Type:INTEGER:5
Text:CHARACTER:No Details
|
After executing the Connection statement, its going to the finally block.
Can anyone please suggest on this. 
Thanks in Advance. |
|
| Back to top |
|
 |
| cvag |
| Posted: Sat Jun 09, 2012 6:15 am Post subject: |
|
|
Centurion
Joined: 17 Mar 2008
Posts: 127
|
Connection conn = getJDBCType4Connection("SIMPLERROUTEDB",JDBC_TransactionType.MB_TRANSACTION_AUTO);
//Connection conn = getJDBCType4Connection("SIMPLERROUTEDB", null);
Sorry second line was commented. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sat Jun 09, 2012 6:28 am Post subject: Re: Failed to obtain JDBC connection |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| cvag wrote: |
| connectionUrlFormat='jdbc:db2://localhost:50000/TEST:user=db2admin;password=db2admin;' |
This is entirely wrong and entirely the source of your problem.
"Format" means "pattern".
You have specified a fully qualified connectionURL.
This field only holds a connectionURLFormat. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sat Jun 09, 2012 7:57 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
There are at least a dozen other posts on this very matter in this forum. Mr Google and the search box are your friend here.
_________________
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 |
|
|
| cvag |
| Posted: Sat Jun 09, 2012 8:25 am Post subject: |
|
|
Centurion
Joined: 17 Mar 2008
Posts: 127
|
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Jun 09, 2012 9:25 am Post subject: Re: Failed to obtain JDBC connection |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
No, it hasn't ...
| Code: |
JDBCProviders
SIMPLERROUTEDB
connectionUrlFormat='jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];'
... |
That should appear in the reported properties output as-is. Not like this:
| cvag wrote: |
| Code: |
JDBCProviders
SIMPLERROUTEDB
connectionUrlFormat='jdbc:db2://localhost:50000/TEST:user=db2admin;password=db2admin;'
... |
|
The broker should plug in the appropriate values into the Format string at run time. See also here. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sat Jun 09, 2012 10:18 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
The correct url format should look like this:
| Code: |
connectionUrlFormat='jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];'
|
What you did not seem to understand is that [serverName] does not mean replace here with server name but means this is a place holder for the configurable service to replace the value at runtime.
So your connectionUrlFormat needs to look exactly like above and your configurable service needs to reference the properties in [] with the exact name (case included) and assign them a value.
See
| Code: |
JDBCProviders
SIMPLERROUTEDB
connectionUrlFormat='jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='SROUTEDB'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='Simplified Database Routing Sample Database'
environmentParms='default_none'
jarsURL='C:\Program Files\IBM\SQLLIB\java'
portNumber='50000'
securityIdentity='default_User@default_Server'
serverName='localhost'
type4DatasourceClassName='com.ibm.db2.jcc.DB2DataSource'
type4DriverClassName='com.ibm.db2.jcc.DB2Driver'
BIP8071I: Successful command completion. |
You then still have to define your security identity profile to be substituted at runtime again.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Jun 10, 2012 2:51 am Post subject: Re: Failed to obtain JDBC connection |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| rekarm01 wrote: |
No, it hasn't ...
| Code: |
JDBCProviders
SIMPLERROUTEDB
connectionUrlFormat='jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];'
... |
That should appear in the reported properties output as-is. |
Again, this is a pattern. The parts like "[serverName]" are literal text. The BROKER is supposed to replace them, NOT YOU. |
|
| Back to top |
|
|
| cvag |
| Posted: Sun Jun 10, 2012 11:40 am Post subject: |
|
|
Centurion
Joined: 17 Mar 2008
Posts: 127
|
Thanks mqjeff and rekarm,
I have changed my details
| Quote: |
C:\IBM\MQSI\6.1>mqsireportproperties WBRK61_DEFAULT_BROKER -o SIMPLERROUTEDB -c
JDBCProviders -r
JDBCProviders
SIMPLERROUTEDB
connectionUrlFormat='jdbc:db2://[serverName]:[portNumber]/[databaseName]:use
r=[user];password=[password];'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='TEST'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='Simplified Database Routing Sample Database'
jarsURL='C:\IBM\SQLLIB\java'
portNumber='50000'
securityIdentity='default_User@default_Server'
serverName='localhost'
type4DatasourceClassName='com.ibm.db2.jcc.DB2DataSource'
type4DriverClassName='com.ibm.db2.jcc.DB2Driver'
BIP8071I: Successful command completion.
C:\IBM\MQSI\6.1>mqsisetdbparms WBRK61_DEFAULT_BROKER -n jdbc::mySecurityIdentity
-u db2admin -p db2admin
BIP8071I: Successful command completion.
C:\IBM\MQSI\6.1>mqsichangeproperties WBRK61_DEFAULT_BROKER -c JDBCProviders -o S
IMPLERROUTEDB -n securityIdentity -v mySecurityIdentity
BIP8071I: Successful command completion.
|
Still facing the same error failed to obtain the jdbc connection.
I checked for this error in older posts, but mostly problem is there with connectionurl format.
But my details seems to be OK.
Can you please suggest with any idea.
Thanks |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sun Jun 10, 2012 3:26 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
The urlformat should be on one line not on 2
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| cvag |
| Posted: Sun Jun 10, 2012 5:01 pm Post subject: |
|
|
Centurion
Joined: 17 Mar 2008
Posts: 127
|
Thanks fjb for your reply.
The url is in one line only, it was displayed in 2 lines whiles copying from command console |
|
| Back to top |
|
|
|
|
