Eclipse/Hibernate – Installation – Day/01

 

Outline

  1. Download & Install Eclipse
  2. JDBC Driver
    • Microsoft JDBC Driver
      • Download Microsoft JDBC Driver
  3. Configure Database
    • Microsoft SQL Server
      • Provision Database
        • WideWorldImporters ( Sample Database )
          • Restore Database
      • Provision Principal
        • Create SQL Server Principal
        • Grant Principal access to database
        • Grant principal access to database objects
  4. Configure Eclipse
    • Install Components
      • JBoss Tools
        • Hibernate

Tasks

Download & Install Eclipse

Please peruse this post per installing Eclipse :-

Eclipse IDE – Installation On MS Windows
Link

JDBC Drivers

Our database back-end is Microsoft SQL Server and so we will reach for Microsoft SQL Server JDBC Drivers.

Microsoft

SQL Server

JDBC Driver
Links
  1. Microsoft JDBC Driver for SQL Server
    Link
  2. Download Microsoft JDBC Driver for SQL Server
    Link
Available

Here are all the drivers available as of 2019-May-6th :-drivers.microsoft.jdbc.20190506.1231PM

We are on 7.0.

And, we will cover that in this post!

Database

Again, our database back-end is Microsoft SQL Server.  There are a few sample databases on that platform; the latest official is WorldWideImporters.

Microsoft

SQL Server

Worldwide Importers
Links
  1. SQL Samples
    Link
  2. Wide World Importers
    • Wide World Importers sample database v1.0
      Link
Brochure

Brochure

microsoft.sample.20190506.1246PM

Choice

We can go with traditional database backup files ( bak ).

Or with bacpac.

We chose :-

SQL Server 2016 SP1 (or later) Any Edition aside from LocalDB; SQL Server 2016 RTM (or later) Evaluation/Developer/Enterprise Edition
WideWorldImporters-Full.bak

Restore database

Launched SQL Server Management Studio and restored the backup file


USE [master]
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\'
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\'
go

RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\downloads\Microsoft\SQLServer\Sample\WideWorldImporters\WideWorldImporters-Full.bak'
WITH
       FILE = 1
    ,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters.mdf'
    ,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_UserData.ndf'
    ,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\WideWorldImporters.ldf'
    ,  MOVE N'WWI_InMemory_Data_1' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_InMemory_Data_1'
    ,  NOUNLOAD
    ,  STATS = 5
    ,  REPLACE

GO
Create Database Account

Let us create SQL Server Account


use [WideWorldImporters]
go

/*
    SQL Server Principal - helloDB
*/
if suser_id('helloDB') is null
begin

    create login [helloDB]
    with password = 'Nu3Hu5RuSt'
       , DEFAULT_DATABASE = [WideWorldImporters]
       , CHECK_EXPIRATION = OFF
       , CHECK_POLICY = OFF

end
go

/*
    SQL Server Principal - helloDB
*/
if user_id('helloDB') is null
begin

    create user [helloDB]
    from login [helloDB];

end
go

grant select, insert, update, delete on schema::[Application] to [helloDB]
go

grant select, insert, update, delete on schema::[Purchasing] to [helloDB]
go

grant select, insert, update, delete on schema::[Sales] to [helloDB]
go

grant select, insert, update, delete on schema::[Warehouse] to [helloDB]
go

jboss.org

Available Software

In the Work With entry box, please enter :-

http://download.jboss.org/jbosstools/photon/development/updates

Once entered please click the “Add…” button

install.AvailableSoftware.02.WorkWith.20190505.0225PM.PNG

Add Repository

Please give a Name for the Repository we are adding.

In our case we chose jboss.

install.AvailableSoftware.03.WorkWith.20190505.0225PM.PNG

 

Check the items that you wish to install

Using Web Services, jboss.org is reached.

Available Items are returned and displayed.

install.AvailableSoftware.05.AddRepository.20190505.0228PM.PNG

Item – jBoss Data Services Development

We selected the following items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.06.AddRepository.20190505.0229PM.PNG

Item – jBoss Data Services Development – Install Details

Please review the selected items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.07.AddRepository.20190505.0230PM.PNG

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

Item – Installing Software

 

Item – Security Warning

Security Warning – The authenticity or validity of the software cannot be established

Even Hibernate.org is shipping software without SSL Certificates?

 

It is good, please click “Install anyway”.

Restart

Please restart Eclipse ID to effect the changes.

Restarted

Upon restart, we are asked to opt in for anonymous usage statistics.

Summary

Enough shenanigans for Day 01.

 

 

Eclipse – Data Source Explorer – SQL Server – Configuration ( Day 01 )

Background

Ran into a blocking issue while playing around with Eclipse.

The issue surfaced while trying to configure Hibernate to connect to SQL Server.

 

Data Source Explorer

I tried using the Hibernate Data Tools to connect to my back-end database; SQL Server in this case.

Finally gave up and wanted to see if I can reproduce the error using other data tools.

Process

Outline

  1. Perspective
    • Change to Data Development Perspective
  2. Driver Templates
    • Does Not Work
      • Microsoft SQL Server 2016 JDBC Driver
        • Specify a Driver Template and Definition Name
          • Driver Name :- Microsoft SQL Server 2016 JDBC Driver
          • Driver Type :- Microsoft SQL Server 2016 JDBC Driver
        • Tab :- JAR List
          • Removed
            • Removed sqljdbc.jar
          • Added
            • Added mssql-jdbc-7.0.0.jre10.jar
        • Tab :- Properties
          • Filled out properties tab
        • Tested Connection
          • Errors Out
            • java.lang.UnsupportedClassVersionError: com/microsoft/sqlserver/jdbc/SQLServerDriver has been compiled by a more recent version of the Java Runtime (class file version 54.0), this version of the Java Runtime only recognizes class file versions up to 52.0
    • Works
      • Microsoft SQL Server 2008 JDBC Driver
        • Specify a Driver Template and Definition Name
          • Driver Name :- Microsoft SQL Server 2008 JDBC Driver
          • Driver Type :- Microsoft SQL Server 2008 JDBC Driver
        • Tab :- JAR List
          • Removed
            • Removed sqljdbc.jar
          • Added
            • Added mssql-jdbc-7.0.0.jre8.jar
        • Tab :- Properties
          • Filled out properties tab
        • Tested Connection
          • Ping Successful
  3. Data Explorer
    • Review Data Explorer

Perspective

Database Development

Image

perspective.OpenPerspective.01.20190505.1033PM.PNG

Connection Profile

Driver Templates

Driver Templates – v 2016

Image
Image – Connection Profile

connectionProfile.01.20190505.1052PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Name/Type

DriverTemplateAndDriverName.Tab.NameAndType.01.20190505.1059PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 01

DriverTemplateAndDriverName.Tab.JARList.01.20190505.1100PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 02

Removed sqljdbc.jar

DriverTemplateAndDriverName.Tab.JARList.02.20190505.1101PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 04

Added mssql-jdbc-7.0.0.jre10.jar

DriverTemplateAndDriverName.Tab.JARList.03.20190505.1121PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 01

Review Properties Tab

DriverTemplateAndDriverName.Tab.Properties.01.20190505.1104PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 02

Completed Properties Tab

DriverTemplateAndDriverName.Tab.Properties.02.20190505.1105PM.PNG

Image – Specify a Driver and Connection Details

Review Driver and Connection Details ….

connectionProfile.jre10.01.SpecifyADriverAndConnectionDetails.02.20190505.1118PM.PNG

Image – Ping failed!

Ping failed.

pingFailed.20190505.1120PM.PNG

Ping failed.

java.lang.UnsupportedClassVersionError: com/microsoft/sqlserver/jdbc/SQLServerDriver has been compiled by a more recent version of the Java Runtime (class file version 54.0), this version of the Java Runtime only recognizes class file versions up to 52.0
	at java.lang.ClassLoader.defineClass1(Native Method)
	at java.lang.ClassLoader.defineClass(Unknown Source)
	at java.security.SecureClassLoader.defineClass(Unknown Source)
	at java.net.URLClassLoader.defineClass(Unknown Source)
	at java.net.URLClassLoader.access$100(Unknown Source)
	at java.net.URLClassLoader$1.run(Unknown Source)
	at java.net.URLClassLoader$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at java.net.FactoryURLClassLoader.loadClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:327)
	at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
	at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
	at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
	at org.eclipse.datatools.enablement.msft.internal.sqlserver.connection.JDBCSQLServerConnectionFactory.createConnection(JDBCSQLServerConnectionFactory.java:27)
	at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
	at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
	at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
	at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Driver Templates – v 2008

Image
Image – Connection Profile

connectionProfile.01.20190505.1052PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Name/Type

Please choose “Microsoft SQL Server 2008 JDBC Driver

ConnectionProfile.SpecifyADriverAndDefinitionName.Tab.NameAndType.01.20190505.1112PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 01

DriverTemplateAndDriverName.Tab.JARList.01.20190505.1100PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 02

Removed sqljdbc.jar

DriverTemplateAndDriverName.Tab.JARList.02.20190505.1101PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 03

Using the OS “Select the file” functionality reach for mssql-jdbc-7.0.0.0.jre8.jarpingSucceedded.20190505.1124PM.PNG.

DriverTemplateAndDriverName.Tab.JARList.SelectTheFile.03.20190505.1122PM.PNG

 

Image – Specify a Driver Template and Definition Name – Tab – JAR List – 04

Added mssql-jdbc-7.0.0.jre8.jar

DriverTemplateAndDriverName.Tab.JARList.04.20190505.1123PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 01

Review Properties Tab

DriverTemplateAndDriverName.Tab.Properties.01.20190505.1104PM.PNG

Image – Specify a Driver Template and Definition Name – Tab – Properties – 02

Completed Properties Tab

DriverTemplateAndDriverName.Tab.Properties.02.20190505.1105PM.PNG

Image – Specify a Driver and Connection Details

Review Driver and Connection Details ….

connectionProfile.jre10.01.SpecifyADriverAndConnectionDetails.02.20190505.1118PM.PNG

Image – Test Connection

Tested Connection

pingSucceedded.20190505.1124PM.PNG

 

Issues

Java Runtime Compatibility ( JRE )

Hibernate

Compatibility Matrix

Hibernate Compatibility Matrix is available here.

Image

compatibilityMatrix.01.20190506.1250AM

Explanation
  1. Hibernate ORM
    • v6.0
      • Java 8 or 11
    • v5.4
      • Java 8 or 11

Microsoft JDBC Driver

mssql-jdbc-7.0.0.jre10.jar

Naming Convention

The name, mssql-jdbc-7.0.0.jre10.jar, is telling :-

  1. mssql
  2. jdbc
  3. 7.0.0
    • Version of MSSQL/JDBC is 7
  4. jre10
    • Requires Java Runtime 10
  5. jar
    • Jar File
Implication
  1. mssql-jdbc-7.0.0.jre10.jar requires Java JRE 10
    • Hibernate does not support JRE 10
      • It appears we have an incompatibility

mssql-jdbc-7.0.0.jre8.jar

Naming Convention

The name, mssql-jdbc-7.0.0.jre8.jar, is telling :-

  1. mssql
  2. jdbc
  3. 7.0.0
    • Version of MSSQL/JDBC is 7
  4. jre8
    • Requires Java Runtime 8
  5. jar
    • Jar File
Implication
  1. mssql-jdbc-7.0.0.jre8.jar requires Java JRE 8
    • Hibernate does support JRE 8
      • It appears we are good with compatibility

 

 

Data Explorer

Images

Image – Data Explorer – 01

DataSourceExplorer.02.20190505.1126PM.PNG

Dedicate

Always a dedicated piece.

This time it is Kenji Hasunuma.

  1. Kenji Hasunuma
    • How to connect to SQL Server 2016 by Eclipse DTP (en)
      Link

DBeaver – Database Connection Configuration – IBM – DB2 / UDB JDBC Driver

Background

As always coming back to DBeaver.

This time to connect to a UDB Instance running on Linux.

BTW, UDB means Universal Database.

UDB JDBC Driver

Artifacts

DB2 JDBC Driver Versions and Downloads

The URL for DB2 JDBC Drivers is Link.

Here is a current snapshot.

artifacts_db2drivers_20180816_1236PM

v11.1 FP0 ( GA )

We chose the v11 FP0 ( GA ) which is a baseline install; specifically 4.21.29.

Others might choose v11 M3 FP3; which includes the latest patches.

The URL is Link.

Here is a screen shot:

artifacts_db2drivers_v11_20180816_0110PM

IBM Data Server Driver for JDBC and SQLJ ( JCC Driver )

As all we need is the JDBC Driver, we chose “IBM Data Server Driver for JDBC and SQLJ (JCC Driver)“.

One needs membership account to actually download, so please login in with one if you have already registered.  Else, please create one, as it is free.

Download

In short, we downloaded “IBM Data Server Driver for JDBC and SQLJ (JCC Driver)“.

Extracted

Image

zip_extracted_20180816_0216PM.PNG

Textual

  1. db2jcc.jar
    • JDBC v3.0
  2. db2jcc4.jar
    • JDBC 4.0

Preparation

Network

Port Number

Let us determine the Network Port Number that our DB/2 Instance is listening on.

db2 get dbm cfg

Syntax


db2 get dbm cfg

Sample


db2 get dbm cfg | grep SVCE

Output

db2getdbmcfg_20180816_0230PM.PNG

Databases

Outline

Let us determine the list of databases that are available on our UDB Instance.

db2 list database directory

Syntax


 db2 list database directory

Sample


 db2 list database directory | grep "Database name"

Output

listDatabases_20180816_0242PM.PNG

 

DBeaver

Database

Connections

Steps

Select new connection type
Image

SelectNewConnectionType_20180816_0644AM.PNG

Textual
  1. DB2
    • DB2 LUW
Driver settings – Download driver files
Image

downloadDriverFiles_20180816_0647AM.PNG

Textual
  1. Click on the “Add JARs” button
Driver settings – Edit Driver – Add File – Post
Image

EditDriver_AddFile_After_20180816_0647AM.PNG

Textual
  1. JAR file “db2jcc4.jar” added
Create new connection – DB2 Connection Settings – Tab – General
Image

EditDriver_Tab_Database_20180816_0709AM ( Edited ).PNG

Textual
  1. Host
  2. Port
  3. Database
  4. username
  5. Password
Create new connection – DB2 Connection Settings – finish connection creation
Image

finishConnection_20180816_0719AM ( Edited ).PNG

 

References

  1. IBM
    • Home > DB2 for Linux UNIX and Windows 9.5.0 > Database administration > Administrative interfaces > Administrative SQL routines and views > Supported routines and views > Snapshot routines and views
      • APPLICATIONS administrative view – Retrieve connected database application information
        Link

 

DBeaver – Database Connection Configuration – Oracle – Oracle JDBC Driver

Background

Towards a couple of sample Oracle Database sample exercises that we have in mind wanted to make sure that our all purpose database client, DBeaver, is able to communicate with an Oracle Database.

 

Preparation

Oracle

Java Version

Objective

The version of JDBC Client that we need will be based on the version of JRE that is installed or will be installed on the computer.

And, so let us establish that first; that is determine the JRE Version Number.

Outline

Using Control Panel, find and launch the Java applet.

Once Java Control Panel window appears, please access the “General” tab.

Within the “General” tab, click the “About” button

Review the Version Numbers listed and exit the “About” panel, once satisfied.

Next access the “Java” tab and review the System and User settings.

Within the Java tab, we are able to enable and disable each installed JRE package.  They are listed by Version #.

Image

Image – About

controlPanel_java_about__20171013__0831PM

 

Explanation
  1. The version number is “Version 8 Update 40” ( build 1.8.0_40)
    • We have Version 8
    • Build 1.8
Image – Java Runtime Environment Settings
Image – Java Runtime Environment Settings – System

Image – Java Runtime Environment Settings – User

Image – Java Runtime Environment Settings – System

Download

Download URL

Please visit Oracle’s JDBC download web site.

The URL is Link.

And, the current version# is 12.1.0.2.

 

Image

Matrix

Jar Package Description Detail
ojdbc7.jar JDBC driver classes except classes for NLS support in Oracle Object and Collection types. NLS Support which enables Internalization are excluded
ojdbc7_g.jar Same as ojdbc7.jar except compiled with “javac -g” and contains tracing code. Tracing & Debugging support included
ojdbc7dms.jar Same as ojdbc7.jar, except that it contains instrumentation to support DMS and limited java.util.logging calls. Instrumentation & Logging Supported included
ojdbc7dms_g.jar Same as ojdbc7_g.jar except that it contains instrumentation to support DMS. Instrumentation included

 

Explanation

We do not need support for NLS, National Language, english is sufficient.

And, we do not need tracing and logging as will not be developing code and thus need to capture code related errors and instrumentation.

In short, the base package, ojdbc7.jar, is sufficient.

Download

Please download the Jar file and move it to a location where you will like to have it available to our Client, DBeaver.

Configuration

Please launch DBeaver and let us review and configure the list of JDBC Drivers.  And, also configure connections to our various Oracle Database hosts.

Outline

  1. Driver Manager
  2. Connection

 

Driver Manager

The list of Drivers currently availed is accessible through the menu items Database \ Driver Manager.

Outline

  1. Please click the menu item Database / Driver Manager
  2. In the “Driver Manager” window
    • Choose Oracle
    • Click the “Edit” button
  3. In the “Edit Driver Oracle” window
    • Tab – Libraries
      • Review the list of libraries registered
      • Add
        • If the ojdbc driver is missing
          • Please click the “Add File” button
          • Navigate to the folder where the JDBC Driver is kept
          • And, select the jdbc jar file
          • Ensure that jar file is listed
      • Driver Class
        • Click the “Find Class” button
        • In the “Driver Class” dropdown, the Driver classes are availed
          • Driver Class
            • oracle.jdbc.OracleDriver
            • oracle.jdbc.driver.OracleDriver
          • The original driver class is oracle.jdbc.driver.OracleDriver
          • And, the modern one is oracle.jdbc.OracleDriver
          • Please choose the modern one
      • Please press the OK button once you are satisfied

Images

Driver Manager – Oracle

Edit Driver “Oracle” – Before Adding Jar File

Edit Driver “Oracle” – After adding Jar file

Edit Driver “Oracle” – Choose Driver Class – Choosing

Edit Driver “Oracle” – Choose Driver Class – Chosen

 

Connection

New Connection

Please click the “Database” / “New Connection” menu item to create a new connection.

Outline

  1. In the “Select new connection type” panel
    • Please select the Oracle driver
  2. In the “Oracle Connection Settings” panel
    • Tabs
      • Tab – General
        • Group Box
          • Group Box – Connection Type
            • In the “Basic” group box
              • Host
                • Please enter the Oracle DB Server Hostname
                  • In our case localhost
              • Port
                • Please enter the Port Number for the Oracle Instance
                  • In our case 1521
              • Database
                • Please enter the database name
                  • As we are local, we clicked on the database name scroll bar and we chose XE from the discovered local instances
              • Service or SID
                • Chose SID
            • Test Connection
              • Please click the “Test Connection” button to validate your connection settings
                • If connection attempt fails, you will get an error message similar to
                  • I/O error :- The Network adapter could not establish a connection
      • Tab – Oracle Properties
        • Group Box
          • Group Box – Session Settings
            • Language
              • Please choose Default from the list of languages
            • Territory
              • Please choose Default from the list of territories
            • NLS Date Format
              • We left the “NLS Date Format” textbox empty
                • As there is no need for Internalization
          • Group Box – Content
            • Hide empty schemas
              • Please unclick the default setting of hiding empty schemas
                • Hiding empty schemas could create potential for not seeing the entire schema footprint
            • Always Show DBA Objects
              • Left unchecked as we will are not currently interested in seeing metadata information availed through the DBA schema
          • Tab – Driver Properties
            • Will not expand on this tab at this time
    • Click the Next button
  3. In the “Network” panel
    • No need for SSH Tunnel / Proxy

Image

Select new connection type

Oracle Connection Settings
Oracle Connection Settings – General – Initial

Oracle Connection Settings – General – Completed – Incorrect

Oracle Connection Settings – General – Completed – Correct

 

Create new Connection – Network

Create new Connection – Test Connection

When test connection fails…

Create new Connection – Test Connection – IO Error : The Network Adapter could not establish the connection – Image

Explanation
  1. Oracle services not started
Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Image

 


Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Textual

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Explanation

When test connection fails…

Create new Connection – Test Connection – When things are good

Create new Connection – Finish Connection Creation
Create new Connection – Finish Connection Creation – Initial

Create new Connection – Finish Connection Creation – Completed

 

Usage

Sample Query

Session Information

Query


SELECT 
         SYSDATE AS currentDate 
       , SYS_CONTEXT ('USERENV', 'SESSION_USER') AS currentUser
       , SYS_CONTEXT('USERENV','HOST') AS host       
       , SYS_CONTEXT('USERENV','TERMINAL') AS terminal
       
FROM SYS.DUAL

Output

References

  1. Oracle Docs
  2.  Oracle-Base
    • Identifying Host Names and IP Addresses
      Link

Java – JDBC – Microsoft Drivers – Named Instance/ “Un-Default” Port Issues

Perplexingly, there are various issues one may run into while attempting to connect to a MS SQL Server Instance, especially the partner node of a mirrored-database.

This problem seems to be more common when MS SQL Server is running on something other than its default port 1433.

The problem is also well chronicled

  1. SQL 2005 JDBC Driver and Database Mirroring
    http://blogs.msdn.com/psssql/archive/2008/12/31/sql-2005-jdbc-driver-and-database-mirroring.aspx
  2. Running SQL Server ‘Default’ instance on a non-default (or non-standard) TCP port: : tips for making application connectivity work
    http://blogs.msdn.com/dataaccesstechnologies/archive/2010/03/03/running-sql-server-default-instance-on-a-non-default-or-non-standard-tcp-port-tips-for-making-application-connectivity-work.aspx
  3. The Failover Partner connection string attribute cannot connect to a mirror partner server by using the SQL Native Client OLE DB provider and the SQL Native Client ODBC driver in a .NET Framework-based application (http://support.microsoft.com/kb/941526)

 

Sample JDBC URL

Assumptions:

a) MS SQL Server Name is EPH
b) MS SQL Server Failover Partner is EphMirr
c) Database Name is intra

a)
DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr;
( works well)

b)DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr\\MSSQL2008;
( works well)

c)
DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=HOLYSPIRIT,2060;
( does not work)

d)DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=DBMIRR\\MSSQL2008
(
works — if DBMirr is resolvable thru DNS or
on MS Windows c:\windows\system32\drivers\etc\hosts

note that is is not enough to have used cliconfg.exe to create an alias
)

Issues:

The big problem seems to revolve around the fact that MS SQL Server does not properly resolve the port number for the partner node

Review of our various connection strings:

a) DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr;

LIFE IS GOOD

  • Since the partner is listening on MS SQL Server default port of 1433, no need to specify or indicate otherwise

b)

DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr\\MSSQL2008;

LIFE IS GOOD

  • It appears that the JDBC Driver (running on the client) correctly attempts to  engage the “SQL Server Browser” service on the database server – Note that this service, by default is listening on UDP Port 1434, and so Server\ DBA\Firewall Admins should ensure that this service is set to running and service mode set to “Automatic”
  • Also note that we have “escaped” the \ (the separator between the server and alias name) and have it as \\ (two backslashes)

c)

DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=HOLYSPIRIT,2060;


LIFE IS NOT SO GOOD

d) If one were try to create a SQL Client Alias

DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=DBMIRR\\MSSQL2008

  • Note that creating a SQL Client alias is not useful — Especially, as SQL Client Alias are MS OS Specifics, and does not translate to Unix
  • Also any alias created will have to resolvable at the Network\OS level (DNS or c:\windows\system32\drivers\etc\hosts)
  • Also, note that if MS SQL Server is not listening on its default port or its a named instance then make sure that “SQL Server Browser” is running on the server

Microsoft – MSSQLServer / JDBC 3.0 Driver Released

Microsoft – MSSQLServer / JDBC 3.0 Driver Released

For those who develop\support Oracle\Sun Java based Applications that interacts with MS SQL Server, you may want to consider MS latest JDBC Driver – Microsoft SQL Server JDBC Driver 3.0. 

It is available @ http://www.microsoft.com/downloads/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en