Problem loading JDBC drivers with sql:connect
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:
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.
Updated by Michael Kay about 2 years 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.
Updated by Radu Coravu about 2 years 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)"?
Updated by Michael Kay over 1 year 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).
Updated by Michael Kay over 1 year 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.
Please register to edit this issue