| Author |
Message
|
| Phanikumar |
 Posted: Tue Apr 24, 2018 10:02 pm Post subject: SQL Server JDBC Issue in IIB |
 |
|
Apprentice
Joined: 29 Aug 2016
Posts: 25
Location: Kuwait
|
I've configured JDBC connection and i've added all the necessary jars but still i am getting an exception"No Suitable Driver found"
Below is my JDBC configuration and exception details. Appreciate your help.
$ mqsireportproperties ABKAIX01T -c JDBCProviders -o ODSBETAJDBC -r
JDBCProviders
ODSBETAJDBC
connectionUrlFormat='jdbc:sqlserver://[10.*.***.167]:1433;DatabaseName=[TESTDB];user=[****];password=[******]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='TESTDB'
databaseSchemaNames='useProvidedSchemaNames'
databaseType='Microsoft SQL Server'
databaseVersion='2016'
description='ODS_Database'
environmentParms='default_none'
jarsURL='/home/iibadmin/Deployments/SQLjar/'
jdbcProviderXASupport='false'
maxConnectionPoolSize='0'
portNumber='1433'
securityIdentity='ODSBETAJDBC'
serverName='10.*.***.167'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
BIP8071I: Successful command completion.
Exception:-
[BIPmsgs:6233]BIP6233E: An error occurred in node: Broker 'ABKAIX01T'; Execution Group 'DEBUG_TST'; Message Flow 'GetAccountStatementODS_MF'; Node 'Invoke_ODS_Stored_procedure'; Node Type 'AccountStatementODS_Java There was a problem establishing a connection to the given database URL: jdbc:sqlserver://[10.*.***.167]:1433;DatabaseName=[TESTDB];user=[*****];password=[*****] Exception details: error message: No suitable driver, SQLState value: 08001, stack trace: [com.ibm.broker.jdbctype4.jdbcdbasemgr.JDBCType4Connection.createNonXAConnection(Unknown Source)
com.ibm.broker.jdbctype4.jdbcdbasemgr.JDBCType4Connection.getT4NodeConnection(Unknown Source)
com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandler.getConnection(Unknown Source)
com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandler.getConnection(Unknown Source)
com.ibm.broker.jdbctype4.connfact.JDBCType4ConnectionFactory.getConnection(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
java.lang.reflect.Method.invoke(Method.java:620)
com.ibm.broker.plugin.MbNode.getJDBCType4Connection(Unknown Source)
AccountStatementODS_Java.evaluate(AccountStatementODS_Java.java:72)
com.ibm.broker.javacompute.MbRuntimeJavaComputeNode.evaluate(Unknown Source)
com.ibm.broker.plugin.MbNode.evaluate(Unknown Source)]
The node was unable to establish a connection to the given database URL.
Contact your IBM Integration Bus administrator. : Problem encountered obtaining JDBC connection |
|
| Back to top |
|
 |
| abhi_thri |
| Posted: Wed Apr 25, 2018 12:13 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
| hi...try checking whether the broker user id have the right permissions to read the jars files, also worth restarting the node if you haven't already done so. |
|
| Back to top |
|
|
| Phanikumar |
| Posted: Wed Apr 25, 2018 12:35 am Post subject: |
|
|
Apprentice
Joined: 29 Aug 2016
Posts: 25
Location: Kuwait
|
| I've given 777 permissions to that directory and broker user is the owner as well. |
|
| Back to top |
|
|
| abhi_thri |
| Posted: Wed Apr 25, 2018 12:42 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
|
| Back to top |
|
|
| abhi_thri |
| Posted: Wed Apr 25, 2018 12:55 am Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
You may be able to work around the issue by using a pure java type4 driver...
'Where a JDBC provider is not listed in the supported databases section above IIB will provide limited support subject to the following restrictions:
- The driver must be a JDBC Type 4 (Pure Java) driver
- Discoverability in the Graphical Data Mapper is not supported
- Automatic determination of field types in Graphical Data Maps is only supported if the driver correctly and fully implements java.sql.PreparedStatement.getParameterMetaData() see Infocenter topic http://www-01.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/br40814_.htm for more details.
- Global transaction coordination (XA) is not supported. ' |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Apr 25, 2018 2:28 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
Your connection url format is all wrong. This is meant as a template to fill in the parameters via the configurable service and not as the actual connection url.
Review the manual for the setup of this template. Have fun. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Phanikumar |
| Posted: Wed Apr 25, 2018 4:32 am Post subject: |
|
|
Apprentice
Joined: 29 Aug 2016
Posts: 25
Location: Kuwait
|
I've tried with java type 4 but its not working
String oDSN = getUserDefinedAttribute("POWERCARD_DSN").toString();
oConn = getJDBCType4Connection(oDSN,JDBC_TransactionType.MB_TRANSACTION_AUTO);
So I've decided to pass the URL in the code and it's working.
_________________
Saying what you feel. It's not being rude, it's called being real. |
|
| Back to top |
|
|
| Phanikumar |
| Posted: Wed Apr 25, 2018 4:46 am Post subject: |
|
|
Apprentice
Joined: 29 Aug 2016
Posts: 25
Location: Kuwait
|
| fjb_saper wrote: |
Your connection url format is all wrong. This is meant as a template to fill in the parameters via the configurable service and not as the actual connection url.
Review the manual for the setup of this template. Have fun. |
Can you please be specific where it is wrong?
_________________
Saying what you feel. It's not being rude, it's called being real. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 25, 2018 5:04 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Phanikumar wrote: |
| Can you please be specific where it is wrong? |
Everywhere.
As my worthy associate points out, the URL is a template into which IIB will insert values as configured. It shouldn't contain specific values for any given connection.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Phanikumar |
| Posted: Wed Apr 25, 2018 5:58 am Post subject: |
|
|
Apprentice
Joined: 29 Aug 2016
Posts: 25
Location: Kuwait
|
| Vitor wrote: |
| Phanikumar wrote: |
| Can you please be specific where it is wrong? |
Everywhere.
As my worthy associate points out, the URL is a template into which IIB will insert values as configured. It shouldn't contain specific values for any given connection. |
IBM Template for SQL server jdbc is below
$ mqsireportproperties ABKAIX01T -c JDBCProviders -o Microsoft_SQL_Server -r
JDBCProviders
Microsoft_SQL_Server
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='default_Database_Name'
databaseType='Microsoft SQL Server'
databaseVersion='default_Database_Version'
description='default_Description'
environmentParms='default_none'
jarsURL='default_Path'
jdbcProviderXASupport='true'
maxConnectionPoolSize='0'
portNumber='1433'
securityIdentity='default_User@default_Server'
serverName='default_Database_Server_Name'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
I've configured as per IBM template.
Give me an example of how the url should be
_________________
Saying what you feel. It's not being rude, it's called being real. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Apr 25, 2018 4:47 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
| Phanikumar wrote: |
| Vitor wrote: |
| Phanikumar wrote: |
| Can you please be specific where it is wrong? |
Everywhere.
As my worthy associate points out, the URL is a template into which IIB will insert values as configured. It shouldn't contain specific values for any given connection. |
IBM Template for SQL server jdbc is below
$ mqsireportproperties ABKAIX01T -c JDBCProviders -o Microsoft_SQL_Server -r
JDBCProviders
Microsoft_SQL_Server
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='default_Database_Name'
databaseType='Microsoft SQL Server'
databaseVersion='default_Database_Version'
description='default_Description'
environmentParms='default_none'
jarsURL='default_Path'
jdbcProviderXASupport='true'
maxConnectionPoolSize='0'
portNumber='1433'
securityIdentity='default_User@default_Server'
serverName='default_Database_Server_Name'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
I've configured as per IBM template.
Give me an example of how the url should be |
This looks much better although I doubt very much that the default database name is right, your security identity is not filled in and your server name is definitely not default_database_server_name.
You need to fill in all the parameters below ConnectionURLFormatAttr5 with real name values. They will be substituted at runtime with the values from the configurable service.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Phanikumar |
| Posted: Thu Apr 26, 2018 4:13 am Post subject: |
|
|
Apprentice
Joined: 29 Aug 2016
Posts: 25
Location: Kuwait
|
| fjb_saper wrote: |
| Phanikumar wrote: |
| Vitor wrote: |
| Phanikumar wrote: |
| Can you please be specific where it is wrong? |
Everywhere.
As my worthy associate points out, the URL is a template into which IIB will insert values as configured. It shouldn't contain specific values for any given connection. |
IBM Template for SQL server jdbc is below
$ mqsireportproperties ABKAIX01T -c JDBCProviders -o Microsoft_SQL_Server -r
JDBCProviders
Microsoft_SQL_Server
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='default_Database_Name'
databaseType='Microsoft SQL Server'
databaseVersion='default_Database_Version'
description='default_Description'
environmentParms='default_none'
jarsURL='default_Path'
jdbcProviderXASupport='true'
maxConnectionPoolSize='0'
portNumber='1433'
securityIdentity='default_User@default_Server'
serverName='default_Database_Server_Name'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
I've configured as per IBM template.
Give me an example of how the url should be |
This looks much better although I doubt very much that the default database name is right, your security identity is not filled in and your server name is definitely not default_database_server_name.
You need to fill in all the parameters below ConnectionURLFormatAttr5 with real name values. They will be substituted at runtime with the values from the configurable service. |
Should i pass same url for all connection formats?
I've tried passing proper database name but not working.
_________________
Saying what you feel. It's not being rude, it's called being real. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Apr 26, 2018 5:23 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
Your configurable service should look like this
IBM Template for SQL server jdbc is below
| Code: |
$ mqsireportproperties ABKAIX01T -c JDBCProviders -o Microsoft_SQL_Server -r
JDBCProviders
Microsoft_SQL_Server
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='MYDB'
databaseType='sqlserver'
databaseVersion='sqlserver version'
description='whatever you find appropriate to qualify the datasource'
environmentParms='default_none'
jarsURL='The URL path for your slqserver driver jars'
jdbcProviderXASupport='true'
maxConnectionPoolSize='10'
portNumber='1433'
securityIdentity='the identity you define(d) with setmqsidbparms'
serverName='your sqldbserver'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
|
Hope that helps
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Phanikumar |
| Posted: Tue May 01, 2018 8:34 pm Post subject: |
|
|
Apprentice
Joined: 29 Aug 2016
Posts: 25
Location: Kuwait
|
| fjb_saper wrote: |
Your configurable service should look like this
IBM Template for SQL server jdbc is below
| Code: |
$ mqsireportproperties ABKAIX01T -c JDBCProviders -o Microsoft_SQL_Server -r
JDBCProviders
Microsoft_SQL_Server
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='MYDB'
databaseType='sqlserver'
databaseVersion='sqlserver version'
description='whatever you find appropriate to qualify the datasource'
environmentParms='default_none'
jarsURL='The URL path for your slqserver driver jars'
jdbcProviderXASupport='true'
maxConnectionPoolSize='10'
portNumber='1433'
securityIdentity='the identity you define(d) with setmqsidbparms'
serverName='your sqldbserver'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
|
Hope that helps
|
Not working 
_________________
Saying what you feel. It's not being rude, it's called being real. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 02, 2018 5:43 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Same error? Different error? Empty result set?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
