Archive for the ‘Database’ Category
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
- Goto Oracle website
- Accept the license agreement
- Unless you have an rpm based distribution choose the Oracle SQL Developer for other platforms(this will be assumed throughout this how-to)
- Unzip the downloaded zip archive to e.g.
/usr/local. Assuming pwd is the directory to which Oracle SQL developer was downloaded to
sudo unzip sqldeveloper-126.96.36.199.45-no-jre.zip -d /opt
- sqldeveloper can be started by the command
sh /opt/sqldeveloper/sqldeveloper.shFor 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.
- If not already downloaded, download the latest JDBC driver from sourceforge
- Unpack the archive and copy the
jtds-1.2.5.jarinto your java environment. On ubuntu 9.10 with Sun Java this is somewhere like
/usr/lib/jvm/java-6-sun-188.8.131.52/jre/lib/ext/This should enable Oracle SQL developer to locate the driver
- Start sqldeveloper. You should see the following screen
- Click the big green plus in the connections pane and the following window will pop-up
- 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
- 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
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)…”.
The first step is to download the jTDS driver from http://sourceforge.net/projects/jtds/files/. Unpack the zip archive somewhere.
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
In the data source URL type the following:
Usually the port number is 1433. Hostname and database name depends on the actual setup.
In the JDBC driver class field type in
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