Connecting to an MSSQL Server (and others) with OpenOffice.org

I have wanted to try out OpenOffice.org Base for some time now, but I have never taken the time to figure out how to make it connect to a relational database system. And as it turns out there doesn't seem to be all that much documentation to help. Anyway I needed to figure out a way to look over the structure of a Microsoft SQL database and I didn't have access to a Windows machine, let alone Enterprise Manager.

So OpenOffice.org uses JDBC to connect to various databases, but sadly it does not come bundled with any of the libraries required to connect and communicate. So after researching a little I came across jTDS. And with a little messing around I got it to work. Below is what I did.

  • First you have to download jTDS at http://sourceforge.net/project/showfiles.php?group_id=33291.
  • Second, uncompress the file and store it somewhere out of the way on your computer (such that you don't accidentally move or delete it). You can store the whole folder or just the jtds-x.x.x.jar file.
  • Third, open up OpenOffice.org and go to Tools, Options..., OpenOffice.org (or NeoOffice), Java and then click on Class Path... and then Add Archive... (EDIT: Seems the options menu has changed) open up LibreOffice (OpenOffice.org) Tools, Options..., LibreOffice, Advanced, Class Path..., Add Archive.... Choose the jtds-x.x.x.jar file and hit Open. Then keep hitting Ok until all preference windows are closed.
  • Fourth, you need to close and reopen OpenOffice.org for the change to take effect.

  • Fifth, open up Base and when the wizard comes up choose Connect to an existing database, choose JDBC from the dropdown and then hit Next.
  • Sixth, the Datasource URL is of the form below.

    jtds:sqlserver://[host]:[port]/[database]

    Replace [host] with the hostname of the server, replace [port] with the port that the database connects over (the default is usually 1433) and replace [database] with the name of the database.

  • Seventh, for the JDBC driver class put in the below.

    net.sourceforge.jtds.jdbc.Driver

    You can hit Test class if you want to. It should give you a promising message. After all of the fields are filled out hit Next.

  • Eight, on this screen you can fill out the authentication information. This should be pretty straight forward and you can test it to see that it is connecting properly. And then on the last screen you can set some final options and then save the local connected copy somewhere.

After that you should be able to see and browse the tables, build queries and have fun.

- - - - -

You can also use this jTDS driver to connect to Sybase. All you have to change the Datasource URL to this form.

jtds:sybase://[host]:[port]/[database]

- - - - -

For connecting to MySQL you can follow these same steps except you need to get the MySQL JDBC driver class from the MySQL website (http://www.mysql.com/products/connector/j).

The Datasource URL is:

mysql://[host]:[port]/[database]

And the JDBC driver class is:

com.mysql.jdbc.Driver

24 Responses to “Connecting to an MSSQL Server (and others) with OpenOffice.org”

  1. photoguy76 Says:

    Thank-you so much for your instructions. I have been using Virtual PC and Access to access our Sql2005 DB. I followed your instructions, and bingo, in I went without a hitch.

    Peace.

  2. Kleverson Says:

    Tks a bunch. It’s ALIVE :)

  3. Loic Duros Says:

    Very nice. It worked perfectly!

    Thanks.

  4. Joe Says:

    Thanks a ton!

  5. Sergio Says:

    Gracias!!!!

  6. Duane Says:

    Using OOo 3.2.0 on an imac and have followed the steps however at step 6 it will not allow me to change “jdbc:” to “jtds:” . Any suggestions?

  7. Duane Says:

    I got it!!! I just added it after the jdbc and it took it…. Very nice! Thank you very much!

  8. jordoncm Says:

    @Duane I am glad you figured it out and posted your solution. I have not been keeping this article up to date with new versions.

  9. Jorge Eduardo Says:

    Jordan, your post is very usefull, I just needed 6 min to connect. What I saw is that OO base doesn´t do a good job as a MSSQL GUI. I found that the connection is useful to generate variable data, and so, on. I´m still looking forward for a linux app that replaces the MSSE, something that lets me create, edit, change owners, and all the stuff of a DBA, but remotely, from my ubuntu laptop.

  10. jordoncm Says:

    @Jorge Yeah OpenOffice is not going to contend as a serious DB admin GUI. But it can be useful in a pinch.

  11. Tracie Tuley Says:

    I attempted to run this program – I could not get

  12. fd Says:

    I got an error with the net.sourceforge.jtds.dbc.Driver that could not be loaded… Any solution?
    Thanks in advance!

    FD

  13. jordoncm Says:

    @fd It has been a long time since I have tried this but I would not be surprised if the package name of the Driver class has changed. You may to poke around inside the jar file to find the new path.

  14. jordoncm Says:

    @fd I looked at the source of jtds and it doesn’t look like it changed. However looking at your error message closer I noticed that did not enter the class name correctly. It is “net.sourceforge.jtds.jdbc.Driver” not “net.sourceforge.jtds.dbc.Driver”; you missed the j in jdbc.

  15. rexus Says:

    I did exactly as described, but all the time i catch “the JDBC driver cannot be loaded”, when i click “Test class” button.
    Please advice ..

  16. rexus Says:

    ooops, my fault, i did not extracted jar from the zip, sorry

  17. BadTomatoe Says:

    If you are using SQL Express and are running with the default SQLEXPRESS instance, then you have to add ;instance=SQLEXPRESS to the end of the MS SQL datasource URL above

  18. Eleanor Ellis Says:

    I am trying to connect Base to a MS SQL server. When I get to 7 and hit “Test class” I get the error “The JDBC driver could not be loaded.” I have tried this on both OpenOffice base and LibreOffice base with the same result.

    Can anyone help please?

  19. Dana Armstrong Says:

    This will not load the driver with Open Office 4.0.0 or 4.0.1. Microsoft also puts out a JDBC driver and it doesn’t work either. It’s too bad that they don’t make sure this feature works with new versions.

  20. jordoncm Says:

    @Eleanor

    It is likely the namespace (path) to the driver has changed in the newer versions. you should be able to figure out what the new path is by searching the web.

  21. jordoncm Says:

    @Eleanor

    After playing with a new version of LibreOffice I figured out that the steps changed for setting up Java to find the jtds jar file.

    You need to go to Tools -> Options… -> LibreOffice -> Advanced -> Class Path… -> Add Archive…

    Then you should be able to load the jtds jar file, hit ok and save for everything and then close and reopen and this should work.

  22. Vassilis Says:

    I’m trying with Libreoffice 3.5.1 and still the same problem “The JDBC driver could not be loaded.”. Correct syntax, correct everything (old menu: Tools-> Options -> Java etc). I feel like a fool.

  23. zxMarce Says:

    An alternative I found by following this article is how to use Microsoft’s own JDBC driver. You can get it from Microsoft sites for free. Note that they have two versions, one is for older JREs, and the other needs JRE 7 (maybe my terms are not strictly correct, but I think the audience will follow).
    So, once you downloaded the right one depending on the JRE you have, you must “register” it in Base as said in the article (Tools->Options->Java/Class Path-> etc…) and select the correct .jar file (‘sqljdbc.jar’ or ‘sqljdbc4.jar’, depending on the JRE version you have). You should now “OK” your way out and close and reopen OOO/LO for the changes to stick.

    The URL syntax style is of the form ‘sqlserver://:;databaseName=’. Default port is 1433.

    The the JDBC driver class should be “com.microsoft.sqlserver.jdbc.SQLServerDriver”.

    If you selected your .jar correctly, then you should receive a “The JDBC driver was loaded successfully” message shortly after clicking “Test class”, and if the DB URL is correct and reachable you should be able to connect successfully after entering the logon credentials in the next wizard page.

  24. zxMarce Says:

    Errata: Looks like the blog removed some parts of the URL syntax style, which is pretty important. Here it goes again:

    The URL syntax style is of the form ‘sqlserver://[ServerNameOrIP]:[TCPPort];databaseName=[DBName]‘. Default port is 1433.

Leave a Reply