Connecting to SQLServer using JDBC-ODBC Bridge

Question

I'm writing an applicationt hat was prototyped on MySQL and is now connecting to an Oracle database.

All I had to do to connect to the oracle database (having built up the table structure) was change the connection string.

What is the format to connect to a SQL Server DB on another machine?

I've read some tutorials which tell you to use the SQL Server JDBC adaptor but I'd rather configure the application so that it's database agnostic, and just have the connection string specify the protocol etc.

Any references I've seen which tell you how to use the bridge with SQL Server require the ODBC Data Source to be installed, this is less than ideal as my app may run on Linux or windows.

I'm not doing anything complicated just inserts.

Answer 1

You should not use the JDBC-ODBC bridge in a production environment. It is much slower than other JDBC drivers and only necessary when a JDBC driver is not available.

SQL Server has a JDBC driver available from Microsoft. If you use it then you will get the required result.

With the ODBC bridge you have no choice but to install the ODBC driver.

This article describes the connection string you will need to use to connect to the SQL Server.

Answer 2

These days its quite easy to use Factory pattern and then load JDBC drivers to work with given databse. This architecture gives best of both worlds i.e. Flexibility and efficiency. The one downside of this is bit configuration/programming to handle dynamic loading but i hope so if you want to make it database agnostic that's the way to go.

Answer 3

Do NOT use the JDBC-ODBC bridge driver. That was meant purely for testing, not for production. You can still make your application database agnostic using drivers that are optimized for the database you want to connect to. Just externalize the username, password database driver name and connect string, and don't use any DB-specific SQL and you should be fine.

For connecting to SQL Server, use the jTDS driver http://jtds.sourceforge.net/ The connect string format looks like this:

jdbc:jtds:sqlserver://localhost/my_database

There are a few other parameters you can include, separated by semicolons, but I think this is all that's required. Obviously when you connect, you'll need to supply a username and password.