Anders Andreasen Blog

Just another WordPress.com weblog

Posts Tagged ‘jtds

Using Oracle SQL developer as a GUI front-end for MS SQL server

with 3 comments

In my previous post I described how to use OpenOffice Base as a MS SQL server GUI front-end. On some occasions I have experienced OObase to hang when executing queries or otherwise communicating with the SQL server. For a more professional experience the Oracle SQL developer can be used instead. Oracle SQL developer can be downloaded free-of-charge from the Oracle website.

Download and install Oracle SQL developer

  1. Goto Oracle website
  2. Accept the license agreement
  3. Unless you have an rpm based distribution choose the Oracle SQL Developer for other platforms(this will be assumed throughout this how-to)
  4. Unzip the downloaded zip archive to e.g. /opt or /usr/local. Assuming pwd is the directory to which Oracle SQL developer was downloaded to
    sudo unzip sqldeveloper-2.1.1.64.45-no-jre.zip -d /opt
  5. sqldeveloper can be started by the command
    sh /opt/sqldeveloper/sqldeveloper.sh For convenience an alias can be added to .bashrc e.g.
    alias sqldeveloper='sh /opt/sqldeveloper/sqldeveloper.sh'

Install jTDS JDBC driver

As with OOBase the jTDS JDBC driver is required for connecting to MS SQL server. Here’s how-to make it work.

  1. If not already downloaded, download the latest JDBC driver from sourceforge
  2. Unpack the archive and copy the jtds-1.2.5.jar into your java environment. On ubuntu 9.10 with Sun Java this is somewhere like /usr/lib/jvm/java-6-sun-1.6.0.15/jre/lib/ext/ This should enable Oracle SQL developer to locate the driver
  3. Start sqldeveloper. You should see the following screen
  4. Click the big green plus in the connections pane and the following window will pop-up
  5. If, for some reason, the (MS) SQLserver pane/entry is not there (only the Oracle) them the jTDS JDBC driver was not successfully loaded (perhaps copied to the wrong destination). The JDBC driver can be installed manually by choosing Preferences in the Tools menu
    Click “Add enty” and located the jtds-1.2.5.jar file on your system
  6. Enter the address of the MS SQL server and your log-in credentials and you’re ready to go. Read more in the documentation on the Oracle website
Advertisements

Written by aandreasen

April 6, 2010 at 9:54 pm

Connecting to MS SQL server from OpenOffice Base using jTDS JDBC driver on linux

with one comment

I have been using the Microsoft Enterprise Manager to connect to MS SQL server in order to test SQL queries before being used in applications etc. Clearly this is not an option on a linux platform. However, different methods for connecting to Microsoft SQL server exists. From Python e.g. the freeTDS driver can be used (not covered here). Sometimes a gui administration tool is nice to have. For this purpose I will cover how to connect to a MS SQL server using the jTDS JDBC driver in OpenOffoce Base. According to the website: “jTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005 and 2008)…”.

Download driver

The first step is to download the jTDS driver from http://sourceforge.net/projects/jtds/files/. Unpack the zip archive somewhere.

Install driver

Open OpenOffice base (on Ubuntu install it with sudo apt-get install openoffice.org-base). Create a new database and call it e.g. “test”

Pres next and choose not to register the db in openoffice base and press finish. Give the db a name. This db is simple used in order to access the ooBase gui in order to add the jTDS JDBC driver. Choose options in the “Tools” menu

Go to the java options and press the Class Path button.

Add the jtds-1.2.5.jar archive file by pressing the “Add archive” button.

Exit OpenOffice Base

Connect to database

Open OpenOffice Base. Choose to connect to existing database using JDBC

Click next.

In the data source URL type the following:
jtds:sqlserver://hostname:portnumber/databasename
Usually the port number is 1433. Hostname and database name depends on the actual setup.
In the JDBC driver class field type in
net.sourceforge.jtds.jdbc.Driver
Press the Test Class button. If you get a message that says “The JDBC driver was loaded successfully” the driver is OK. Click next to setup authentication.

Test the connection by clicking “Test connection”. If the connection was successful the following message box is displayed

Written by aandreasen

April 5, 2010 at 9:55 pm