ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
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

Post new topic  Reply to topic Goto page 1, 2  Next
 SQL Server JDBC Issue in IIB « View previous topic :: View next topic » 
Author Message
Phanikumar
PostPosted: Tue Apr 24, 2018 10:02 pm    Post subject: SQL Server JDBC Issue in IIB Reply with quote

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
View user's profile Send private message Send e-mail
abhi_thri
PostPosted: Wed Apr 25, 2018 12:13 am    Post subject: Reply with quote

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
View user's profile Send private message
Phanikumar
PostPosted: Wed Apr 25, 2018 12:35 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
abhi_thri
PostPosted: Wed Apr 25, 2018 12:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Wed Apr 25, 2018 12:55 am    Post subject: Reply with quote

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
View user's profile Send private message
fjb_saper
PostPosted: Wed Apr 25, 2018 2:28 am    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20695
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
View user's profile Send private message Send e-mail
Phanikumar
PostPosted: Wed Apr 25, 2018 4:32 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
Phanikumar
PostPosted: Wed Apr 25, 2018 4:46 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
Vitor
PostPosted: Wed Apr 25, 2018 5:04 am    Post subject: Reply with quote

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
View user's profile Send private message
Phanikumar
PostPosted: Wed Apr 25, 2018 5:58 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
fjb_saper
PostPosted: Wed Apr 25, 2018 4:47 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20695
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
View user's profile Send private message Send e-mail
Phanikumar
PostPosted: Thu Apr 26, 2018 4:13 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
fjb_saper
PostPosted: Thu Apr 26, 2018 5:23 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20695
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
View user's profile Send private message Send e-mail
Phanikumar
PostPosted: Tue May 01, 2018 8:34 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
Vitor
PostPosted: Wed May 02, 2018 5:43 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic 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
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.