Project

Profile

Help

Bug #4572

Problem loading JDBC drivers with sql:connect

Added by Radu Coravu over 1 year ago. Updated 7 months ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Saxon extensions
Sprint/Milestone:
-
Start date:
2020-06-04
Due date:
% Done:

100%

Estimated time:
Legacy ID:
Applies to branch:
10, trunk
Fix Committed on Branch:
10, trunk
Fixed in Maintenance Release:
Platforms:

Description

I do not have much experience with XSLT connecting to SQL, this is more of a discussion.

Let's say I have this JAR library "mysql-connector-java-5.1.42-bin.jar", if it's loaded in the same classpath with Saxon, then it can be used from the XSLT with sql:connect. But if I load the mysql JAR library in a separate class loader which I set on the current thread like this:

Thread.currentThread().setContextClassLoader(customClassLoader);

Saxon will not find the mysql library this way, maybe it can also use the context class loader from the current thread to try and find the driver.

History

#1 Updated by Michael Kay over 1 year ago

I was going to suggest you could customise the class loading using Configuration.setDynamicLoader; or you could preload the class and register it using Configuration.getDynamicLoader().registerKnownClasses.

But I see that Saxon doesn't actually use this for loading the JDBC driver. Instead (see SQLConnect.java line 173) it simply uses Class.forName(). But this is some kind of preloading: I don't understand what process the JDBC DriverManager then uses to find this loaded driver.

I see that the JDK7 documentation for DriverManager says "Applications no longer need to explictly load JDBC drivers using Class.forName(). Existing programs which currently load JDBC drivers using Class.forName() will continue to work without modification.".

Are you getting a failure from Class.forName()? Could you try commenting it out?

It seems that the recommended way of doing this these days is by using a named DataSource: see https://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html - we haven't updated this code in years.

Perhaps you could implement an extension function that you can use in place of Saxon's sql:connect to get a JDBC connection from a named datasource, i.e. implement

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/billingDB");
Connection con = ds.getConnection(user, password);
return con;

If that works, then we can integrate it into the product.

#2 Updated by Radu Coravu over 1 year ago

It all started from this forum thread: https://www.oxygenxml.com/forum/post58334.html

we have Oxygen users who for some time now have added the "mysql-connector-java-5.1.42-bin.jar"" in the Oxygen "lib" folder so that it's loaded in the main class loader. We don't really want this, we don't want them to change Oxygen's installation folder in any way, each Oxygen transformation scenario has an Extensions list in which you can add JAR libraries, usually for extension function calls. The jar libraries added there are loaded in a separate classloader which is set as context class loader on the current thread. I have not had time to build an XSLT which actually works with a mysql connection on my side. I did not yet manage to find the Java source for "net.sf.saxon.option.sql.SQLConnect ", it is not in the Saxon HE sources zip file: https://sourceforge.net/projects/saxon/files/Saxon-HE/9.9/

Somehow if you are calling Class.forName() on your side and it fails, can you also try as a fallback "Thread.currentThread().getContextClassLoader().loadClass(name)"?

#3 Updated by Michael Kay over 1 year ago

I'll give it a go but it may take a few days. Getting the environment right for running the SQL tests is always a bit tricky.

#4 Updated by Radu Coravu over 1 year ago

No hurry, it's just something which would be nice to have, thanks for considering this.

#5 Updated by Michael Kay 7 months ago

I've got my SQL test environment with derby back up and running...

I've confirmed that the call on Class.forName(driverClass) is no longer needed, and I propose to drop it. This moves the responsibility for dynamic loading to the JDBC infrastructure so it becomes "not our problem". Of course, it's still necessary to have the right things on the classpath.

In consequence, the "driver" parameter to the sql:connect instruction and function is no longer needed (and is ignored if specified).

#6 Updated by Michael Kay 7 months ago

  • Subject changed from Problem loading org.gjt.mm.mysql.Driver with sql:connect to Problem loading JDBC drivers with sql:connect
  • Category set to Saxon extensions
  • Status changed from New to Resolved
  • Assignee set to Michael Kay
  • Priority changed from Low to Normal
  • Applies to branch 10, trunk added
  • Fix Committed on Branch 10, trunk added

I have updated the sql:connect instruction and function to make the driver name optional (and ignored) reflecting JDBC changes made in Java 7. This means Saxon is no longer responsible for dynamic loading of the JDBC driver.

#7 Updated by Radu Coravu 7 months ago

Thanks Michael.

#8 Updated by O'Neil Delpratt 7 months ago

  • Status changed from Resolved to Closed
  • % Done changed from 0 to 100
  • Fixed in Maintenance Release 10.5 added

Bug fix applied to Saxon 10.5 maintenance release.

Please register to edit this issue

Also available in: Atom PDF