Querying MS Sql Server from a Jenkins Pipeline

Question

I've been using Jenkins (2.289.3) in a docker container (https://hub.docker.com/r/jenkins/jenkins). The next update to Jenkins 2.312 migrates the docker container from Java 8 to Java 11.

I have some pipelines that use the sourceforge jdbc driver to query SQL server (http://jtds.sourceforge.net/)

Example:

import java.sql.DriverManager
import groovy.sql.Sql

con = DriverManager.getConnection('jdbc:jtds:sqlserver://servername', 'user', 'password');
stmt = con.createStatement();

To make this work, in the Docker container on Java 8 I ran this on the docker container

cp jtds-1.3.1.jar ${JAVA_HOME}/jre/lib/ext

Which loads the jar for use inside Jenkins. This method no longer exists with Java 11.

It seems pipelines have added the @Grab syntax, eg

@Grab(group='net.sourceforge.jtds', module='jtds', version='1.3.1')

If I add this to my pipline, I can see the Jars are downloaded in /var/jenkins_home/.groovy/grapes/ but it doesn't seem to actually load the jar

java.lang.ClassNotFoundException: net.sourceforge.jtds.jdbc.Driver or java.sql.SQLException: No suitable driver found for jdbc:jtds:sqlserver://servername depending on which commands I run. Either way, it appears to be due to the jar not being loaded.

All the groovy examples use

@GrabConfig(systemClassLoader=true)

But this appears to not be supported in pipelines.

I've considered using a command line client, but I need to parse the results of queries and I haven't seen a tool that works well for this (ie, one that would load results into a json file or similar)

I've also tried setting the -classpath argument in the docker container, eg

ENV JAVA_OPTS=-classpath /var/jenkins_home/test/jtds-1.3.1.jar

Running ps in the docker container, I can see that the java process runs with the classpath command line option specified, but it doesn't seem to actually load the jar for use.

I'm a bit lost on how to get this working, can anyone help? Thanks.

user_367341 · Answer

Well, I've found a workaround. It doesn't seem ideal, but it does work

The original code

import java.sql.DriverManager
import groovy.sql.Sql

con = DriverManager.getConnection('jdbc:jtds:sqlserver://servername', 'user', 'password');
stmt = con.createStatement();

Assuming we have the jar saved in /var/jenkins_home/test/jtds-1.3.1.jar it can be updated to:

import java.sql.DriverManager
import groovy.sql.Sql

def classLoader = this.class.classLoader
while (classLoader.parent) {
    classLoader = classLoader.parent
    if(classLoader.getClass() == java.net.URLClassLoader)
    {
        // load our jar into the urlclassloader
        classLoader.addURL(new File("/var/jenkins_home/test/jtds-1.3.1.jar").toURI().toURL())
        break;
    }
}

// register the class
Class.forName("net.sourceforge.jtds.jdbc.Driver")

con = DriverManager.getConnection('jdbc:jtds:sqlserver://servername', 'user', 'password');
stmt = con.createStatement();

Once this code has been run once, the jar seems to be accessible globally (even in other pipelines that don't load the jar).

Based on this, it seems like a good way to handle this is on the Jenkins initialization, rather than in the script at all. I created /var/jenkins_home/init.groovy with these contents:

def classLoader = this.class.classLoader
while (classLoader.parent) {
    classLoader = classLoader.parent
    if(classLoader.getClass() == java.net.URLClassLoader)
    {
        classLoader.addURL(new File("/var/jenkins_home/jars/jtds-1.3.1.jar").toURI().toURL())
        break;
    }
}

Class.forName("net.sourceforge.jtds.jdbc.Driver")

And after that, the scripts seem to behave similar to how I think it should work with the Jar in the classpath.

Querying MS Sql Server from a Jenkins Pipeline

1 Answers