| Author |
Message
|
| prasannanb |
 Posted: Wed Mar 28, 2012 4:48 am Post subject: MYSQL JDBC Configurable service |
 |
|
Apprentice
Joined: 17 Apr 2009
Posts: 35
|
Hi,
Anybody please let me know the connection url format of mysql in configurable service?
Broker is not giving any template from mysql. I tried different combinations which did not work, giving error "Problem encountered obtaining JDBC connection"
However, Direct connection from java code(without using configurable service) is working fine.
Please help. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Mar 28, 2012 4:52 am Post subject: Re: MYSQL JDBC Configurable service |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| prasannanb wrote: |
| Broker is not giving any template from mysql. I tried different combinations which did not work, giving error "Problem encountered obtaining JDBC connection" |
Do the WMB supplied drivers support MySQL? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 28, 2012 5:05 am Post subject: Re: MYSQL JDBC Configurable service |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| prasannanb wrote: |
| Broker is not giving any template from mysql. I tried different combinations which did not work, giving error "Problem encountered obtaining JDBC connection" |
Do the WMB supplied drivers support MySQL? |
At least in theory at least at some levels, the Unix ODBC project drivers do. |
|
| Back to top |
|
|
| prasannanb |
| Posted: Wed Mar 28, 2012 5:10 am Post subject: Re: MYSQL JDBC Configurable service |
|
|
Apprentice
Joined: 17 Apr 2009
Posts: 35
|
| mqjeff wrote: |
| Vitor wrote: |
| prasannanb wrote: |
| Broker is not giving any template from mysql. I tried different combinations which did not work, giving error "Problem encountered obtaining JDBC connection" |
Do the WMB supplied drivers support MySQL? |
At least in theory at least at some levels, the Unix ODBC project drivers do. |
I am using external JDBC jar files (given by mysql), since WMB ODBC are not supporting MySQL. And trying to connect using java compute node |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Mar 28, 2012 5:11 am Post subject: Re: MYSQL JDBC Configurable service |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| prasannanb wrote: |
| mqjeff wrote: |
| Vitor wrote: |
| prasannanb wrote: |
| Broker is not giving any template from mysql. I tried different combinations which did not work, giving error "Problem encountered obtaining JDBC connection" |
Do the WMB supplied drivers support MySQL? |
At least in theory at least at some levels, the Unix ODBC project drivers do. |
I am using external JDBC jar files (given by mysql), since WMB ODBC are not supporting MySQL. And trying to connect using java compute node |
Your statement that MySQL does not support ODBC is incorrect:
http://dev.mysql.com/downloads/connector/odbc/
You can configure WMB to connect to MySQL through ODBC.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 28, 2012 5:16 am Post subject: Re: MYSQL JDBC Configurable service |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| prasannanb wrote: |
I am using external JDBC jar files (given by mysql), since WMB ODBC are not supporting MySQL. And trying to connect using java compute node |
Your statement that MySQL does not support ODBC is incorrect: |
Your statement that his statement is incorrect is incorrect.
He didn't say that MySQL doesn't use ODBC. He said that Broker ODBC doesn't support MySQL.
Which is also slightly incorrect. In theory. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Mar 28, 2012 5:19 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Ok, I'll concede that point. Indirectly, I was suggesting that (1) WMB connects to databases through ODBC and (2) MySQL has ODBC drivers and (3) WMB has connected for others through ODBC.
If he cannot get his config to work, he should post the log that states the problem.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| prasannanb |
| Posted: Wed Mar 28, 2012 6:19 am Post subject: |
|
|
Apprentice
Joined: 17 Apr 2009
Posts: 35
|
As the broker does not ship ODBC drivers for mySQL, I thought better to use standard JDBC (as mentioned in some other related posts also).
I downloaded the jdbc jar files and successfully connected to DB using direct java code in JCN.
Now my problem is, I want to use configurable service instead of giving url,username,password in code itself. Since Broker is not giving any template for mysql configurable service, I tried different options but without success. As stated earlier, getting error "Problem encountered obtaining JDBC connection" |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 28, 2012 6:31 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The usual difficulty that people have creating JDBC configurable services is that they try to put a fully expanded and set up URL in the ConnectionUrlFormat field.
I.e. they put in something like "jdbc://mysqlHostName:user@password/mydatabase" rather than using the proper formatting indicaters like "[hostname]" and '[user]' and etc.
In general, as long as you have a working JDBC connection string, you can use it in a JDBC Configurable Service by altering it to use the '[hostname]' shortcuts.
If you post the definition of your configurable service, you may get more information about how to alter it to work correctly. |
|
| Back to top |
|
|
| prasannanb |
| Posted: Wed Mar 28, 2012 9:20 pm Post subject: |
|
|
Apprentice
Joined: 17 Apr 2009
Posts: 35
|
| mqjeff wrote: |
The usual difficulty that people have creating JDBC configurable services is that they try to put a fully expanded and set up URL in the ConnectionUrlFormat field.
I.e. they put in something like "jdbc://mysqlHostName:user@password/mydatabase" rather than using the proper formatting indicaters like "[hostname]" and '[user]' and etc.
In general, as long as you have a working JDBC connection string, you can use it in a JDBC Configurable Service by altering it to use the '[hostname]' shortcuts.
If you post the definition of your configurable service, you may get more information about how to alter it to work correctly. |
I am using connectionUrlFormat='jdbc:MySQL://172.00.3.28:3306/esbdb:user=root;password=abc123!;
mqsireportproperties BROKER -c JDBCProviders -o WebServiceDatabase -r
JDBCProviders
WebServiceDatabase
connectionUrlFormat='jdbc:MySQL://172.00.3.28:3306/esbdb:user=root;password=abc123!;'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='esbdb'
databaseType='MySQL'
databaseVersion='5.5.10'
description='default_Description'
environmentParms='default_none'
jarsURL='C:\JDBC_Jars'
maxConnectionPoolSize='0'
portNumber='3306'
securityIdentity='default_User@default_Server'
serverName='172.00.3.28'
type4DatasourceClassName='default_Type_Four_Datasource_Class_Name'
type4DriverClassName='com.mysql.jdbc.Driver'
BIP8071I: Successful command completion. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 28, 2012 9:38 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| prasannanb wrote: |
I am using connectionUrlFormat='jdbc:MySQL://172.00.3.28:3306/esbdb:user=root;password=abc123!;
|
As mqjeff already said the connectionUrlFormat IS DIFFERENT from the connection URL.
In your case it should look something like:
| Code: |
| 'jdbc:[databaseType]://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password];' |
or something like it.
so you will need to define all the substitutions in your jdbc configurable service... and of course set user and password on the identity profile specified in the configurable service...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Mar 28, 2012 9:54 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
To empahasise the point, there are many threads in this forum about the misuse of
connectionUrlFormat
It is a format. Just like in the following example
| Code: |
set cDate = CAST(CURRENT_TIMESTAMP as CHAR FORMAT 'yyyyMMdd');
|
It describes the way data is to be presented to the JDBC subsystem.
You should use the other params for the connection property values that are used under the covers.
_________________
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 |
|
|
|
|
