In the past I have added several different SQL databases back ends to xpages including Oracle, DB2, MYSQL and Microsoft MSSQL – so it was a bit of a surprise when setting up a new database for a client that things didn’t quite go as expected. After using the wizard to wrap the jar file downloaded from Microsoft and deploying using an updatesite – a check was made on the server with
tell http osgi ss com.microsoft.sqlserver.jdbc.driver
and checking the status of the jdbc driver is shown as resolved. So far so good.
A simple jdbc file (mydata.jdbc) was added to the WEB-INF/jdbc folder which I had created
<jdbc>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
<url>jdbc:sqlserver://192.168.1.1:1433;databaseName=DBNAME</url>
<user>testedusername</user>
<password>testedpassword</password>
</jdbc>
These credentials had been checked using squirrelsql and the same jar file.
So a simple xpage later with a view control and the connection details for the jdbc added along with the var as vardata and a simple select statement for the table I wanted to link to. The final step was to change the column value to a field from the database table – and I thought – this should be it !!
No – not quite. when the page loaded I got the “Error while reading the relational data … cannot create JDBC driver…”. Of course I checked and rechecked the settings but still couldn’t see anything wrong – compared with working servers – everything seemed correct. So what had changed ?
First the jdbc driver v4.2 jar file was fresh from Microsoft ! I became suspicious as several other servers I had set up some time ago had a different earlier V4.0 version. On that basis checked the requirements for the current jar file and found here the probable source of the issue. The version of java needed to support the 4.2 version of the jarfile is 8 !!
So the final issue in my case was how to deal with the “newer” jdbc version being loaded through the update site – without having to redo the update site. Simple – disable it in the update site – package a copy of the older 4.0 jar file. Restart the server task and check the version again with
tell http osgi ss com.microsoft.sqlserver.jdbc.driver
Now the page reloads with data from the database as required !!
One important tip to watch out for – I found that in the jdbc file this “com.microsoft.sqlserver.jdbc.SQLServerDriver” works – whereas this “com.microsoft.sqlserver.jdbc.driver” does not !!!!