MS SQL Server connection via JDBC

MS SQL Server connection via JDBC

I recently had to pull out some data from a Microsoft SQL Server database to feed the type ahead of an XPages Edit Box.  In the past, I already got this working for DB2, so how hard could it be? Well… a little more complicated than I imagined, so I decided to add it to my little private-public collective-individual memory: my blog :-).

The drivers

There are 2 choices here: or you use Microsoft’s own stuff, or you go open source and use the jTDS drivers.  I prefer the latter option, because there’s only a single jar to deploy and the open source boys claim it’s snappier than Microsoft’s own toys.  Also, it’s open source, so copyright-wise, I can bundle it as a plug-in without too much worries.

The tests

Before creating the JDBC-connection in an XPage, we want to make sure we can connect to our SQL Server database via JDBC.  We want to be able to check if we can connect (no firewall issues?), that we have the right access (security issues?), etc etc.  Once we’re in SSJS or in a view panel, there’s nothing much we can debug there, so we want to make sure everything is in place beforehand.

So, grab the JDBC-driver from the jTDS project, unzip to discover the jar-file, or download the jtds.jar directly.  You can now test your connection by using the Eclipse Quantum plugin.  You can follow the instructions in the article, except for the used drivers (jar-file).

The crucial part to make the magic happen, is a working connection string.  It can be as simple as:

jdbc:jtds:sqlserver://yourserver/yourdatabase

or as complicated as:

jdbc:jtds:sqlserver://yourserver/yourdatabase;instance=yourinstance;portnumber=yourportnumber;domain=yourdomain

You get the syntax help from jTDL project FAQ’s page, while you may need to get the correct property values from your database administrator.

What if the Quantum plugin doesn’t give you your results? We need to take an extra step back and use a basic external tool like f.e. DbVisualizer.  DbVisualizer Free is, well, free and has enough power to visualize your data without too much of a hassle.

JDBC Driver in Domino

The XPages Extension Library book explains step-by-step how to deploy the JDBC driver in Domino.  But there are A LOT of steps to follow, and beware if you miss one step… Luckily enough, there are some code knights out there who created the XPages JDBC Driver Wrapper: it does all the magic for you :-).  Select the JDBC-driver, choose an export directory, and tadaaa: the result is an update site file.  If you feel lazy, I prepared an update site file for you.  Download, unzip and import in your Domino Eclipse updatesite.nsf.

For deploying the JDBC driver the only thing you have to do is, (let somebody) HTTP restart your server.

JDBC in your project

Great, Domino knows your driver, but how to start using it in your project? There has to be a JDBC connection file.

For that, in Domino Designer:

  • Switch to Package Explorer, open your project and expand the WebContent\WEB-INF folder.
  • Create a folder jdbc (right-click WEB-INF, New – Other – Folder – “Next ” – Folder name: jdbc)
  • Create a file connectionname.jdbc in this folder (right-click jdbc, New – Other – File – “Next ” – File name: connectionname.jdbc).
    jdbc-connection-file
  • The structure of the file should be something like this:


    <jdbc>
      <driver>net.sourceforge.jtds.jdbc.Driver</driver>
      <url>jdbc:jtds:sqlserver://yourserver/yourdatabase</url>
      <user>yourusername</user>
      <password>yourpassword</password>
    </jdbc>
    The value for url is the previously mentioned (and tested) connection string.

Windows credentials

As you may notice in the JDBC connectionfile, username and password are added, and there’s a property domain available, that you can add to the connection string (url).  But sometimes, in SQL Server connections the Windows credentials are used to knock on the database’s door.  If that’s the way it has to be, there’s a little extra effort needed.

In fact, this uses SSO (Single Sign-on) and to make this possible, on your (Windows) Domino server, you’ll have to address the ntlmauth.dll.  The dll can be found in the jTDL download, and you’ll find a 32-bit and 64-bit variant.  The dll must be pasted in the Lotus\Domino\jvm\bin directory.  After rebooting the server, it is available and ready.

 The connection file can be as easy as below now:

<jdbc>
  <driver>net.sourceforge.jtds.jdbc.Driver</driver>
  <url>jdbc:jtds:sqlserver://yourserver/yourdatabase</url>
</jdbc>

Pulling data in

There are different options to milk the data cow, but here’s an easy sample script to test the connection in an XPage:

//"sqlserver" is the name of the connection file here, without the suffix "jdbc"
con=@JdbcGetConnection("sqlserver");
var query="SELECT blabla FROM blibli WHERE blublu=bleble";
rs=@JdbcExecuteQuery(con,query);
var resultArray=new Array();
resultArray[0]= "";
var i=1;
while (rs.next()) {
resultArray[i]=rs.getString("ColumnName");
    i++;
}
return resultArray;

As always: good luck!

1 Comment

  1. “But there are A LOT of steps to follow, and beware if you miss one step…”, you’re right there. It took me a few attempts to get it right creating a MySQL one, I missed steps and had to restart a few times. So I’ll thank you in advance for creating this one. Someone did post a MySQL one online once but there was some licensing issue, so they had to remove it before I could download. I have therefore taken the opportunity of downloading yours in case of a similar situation.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

+ 2 = 12

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>