MQSeries.net :: View topic - SQL Server JDBC Issue in IIB

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » SQL Server JDBC Issue in IIB

Goto page 1, 2 Next

SQL Server JDBC Issue in IIB

« View previous topic :: View next topic »

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

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.

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.

abhi_thri

Posted: Wed Apr 25, 2018 12:42 am Post subject:

Knight

Joined: 17 Jul 2017
Posts: 516
Location: UK

hi...noticed that you are trying to connect to 2016 SQL Server but iib currently support upto 2014 only,

https://www.ibm.com/software/reports/compatibility/clarity-reports/report/html/softwareReqsForProduct?deliverableId=1376392102308&osPlatform=Windows#sw-4

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. '

fjb_saper

Posted: Wed Apr 25, 2018 2:28 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20729
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

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.

Phanikumar

Posted: Wed Apr 25, 2018 4:46 am Post subject:

Apprentice

Joined: 29 Aug 2016
Posts: 25
Location: Kuwait

fjb_saper wrote:

Can you please be specific where it is wrong?
_________________
Saying what you feel. It's not being rude, it's called being real.

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.

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.

fjb_saper

Posted: Wed Apr 25, 2018 4:47 pm Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20729
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.

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

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.

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.

fjb_saper

Posted: Thu Apr 26, 2018 5:23 pm Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20729
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

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:

Hope that helps

Not working

_________________
Saying what you feel. It's not being rude, it's called being real.

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.

Display posts from previous:

Goto page 1, 2 Next

Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » SQL Server JDBC Issue in IIB

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP