Upgrade MSSQL driver to v8.4.1 JRE 11

I’ve been having trouble finding a JDBC driver that will work properly under Lucee and Tomcat 9.0.37 under Java 11. I’ve tried the JTDS and the SQL Server v7.2.2.jre8, but each one of them is causing me different issues:

  1. JTDS — I can’t get XML datatypes to work. Any time a query contains a column with an XML datatype, it throws a The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 (""): Data type 0xF1 is unknown. error.
  2. v7.2.2.jre8 — This works with XML datatypes, but any query that generates multiple recordsets does not work correctly. For example, we have a lot queries that generate multiple recordsets, but only the final recordset is expected. This is not working using the SQL Server driver (although this works fine in ACF).

I’ve spent a ton of time trying to find solutions to these issues, but have come up empty.

So I thought I’d try upgrading the SQL Server version to 8.4.1 for JRE 11 to see if that would work, but I’m having issues.

I went and forked the extension-jdbc-mssql extension and did the following:

  • Gave a unique label & ID in the build.properties file
  • Replaced the org.lucee.mssql- with mssql-jdbc-8.4.1.jre11.jar.

Running the build.xml creates the LEX extension and it appears to install correctly, but it’s not working correctly. Even though the JAR gets copied to the server/lucee-server/bundles/ folder, it throws errors saying it cannot find the com.microsoft.sqlserver.jdbc.SQLServerDriver.

If I try to manually use createObject() it doesn’t find the class either, unless I specify the full path to the server/lucee-server/bundles/ folder, then it finds the class.

I can create other classes that are in JARs in the server/lucee-server/bundles/ folder, so I’m not sure why it’s not finding the files in my JAR file.

What might I be doing wrong?

For anyone coming on this, see MIcha’s comments on this ticket

The jar as shipped by MS is not compatible with Felix and must have its manifest adjusted.

Micha ( @micstriit ),

Are there any instruction on exactly how to build the MSSQL drivers in a new Lex?

I’m trying to test a build of the MSSQL drivers that has some fixes to see if it addresses my issue, but I need to get the drivers built into a LEX.

My understanding is that the way the MSSQL drivers implement OSGi causes issues with Lucee and there are some changes that need to be made. I’ve looked at the osgi-bundle-mssql/pom.xml at master · lucee/osgi-bundle-mssql · GitHub file, but I don’t see where the target JAR is being defined.

Can you provide some step-by-step instructions on what I need to do?

I’m guessing I need to use the Maven file in the osgi-bundle-mssql project to alter the MSSQL jar, then use the extension-jdbc-mssql to build the LEX file. Am I on the correct path?

In case anyone stumbles across this thread, I put together a fork of the osgi-bundle-mssql which documents how to build a new MSSQL driver extension in the readme.md file. My fork also builds mssql-jdbc v9.40:

I also added a pull request to have these changes merged into the official release:

1 Like