'Issues using Grapes to grab JDBC driver

I've read a lot of similar posts about using Grapes to load dependencies but everything I try is getting me nowhere. Using:

@Grapes([
    @Grab(group='com.microsoft', module='sqljdbc4', version='3.0')
])

class DatabaseTest {
    static void executeSql() {
        def sql = Sql.newInstance("jdbc:sqlserver://my.server.com:1433;databaseName=SOME_DB;user=USER;password=secret", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
        def rows = sql.rows("select something from some_table")
        println rows.join("\n")
    }
}

I get:

java.sql.SQLException: No suitable driver found for jdbc:sqlserver://my.server.com:1433;databaseName=SOME_DB;user=USER;password=secret

A common solution for loading JDBC drivers seems to be adding @GrabConfig(systemClassLoader = true) but that only gets me the dreaded No suitable ClassLoader found for grab error, which is peculiar because it seems like others add the @GrabConfig to resolve the no suitable class loader error.

I have also tried using this:

static loadGrapes(){
    ClassLoader classLoader = new groovy.lang.GroovyClassLoader()
    Grape.grab(group:'com.microsoft', module:'sqljdbc4', version:'3.0', classLoader:classLoader)
    printClassPath(classLoader)
}

static printClassPath(classLoader) {
    println "$classLoader"
    classLoader.getURLs().each {url->
       println "- ${url.toString()}"
    }
    if (classLoader.parent) {
       printClassPath(classLoader.parent)
    }
}

which gives me:

groovy.lang.GroovyClassLoader@740cae06
- file:/C:/Users/me/.groovy/grapes/com.microsoft/sqljdbc4/jars/sqljdbc4-3.0.jar
groovy.lang.GroovyClassLoader@23282c25
sun.misc.Launcher$AppClassLoader@55f96302
- file:/C:/dev/workspaces/utils/bin/
- file:/C:/dev/eclipse/plugins/org.codehaus.groovy_2.4.10.xx-201704192203-e45/lib/groovy-all-2.4.10.jar
- file:/C:/dev/eclipse/plugins/org.codehaus.groovy_2.4.10.xx-201704192203-e45/lib/bsf-2.4.0.jar
- file:/C:/dev/eclipse/plugins/org.codehaus.groovy_2.4.10.xx-201704192203-e45/lib/ivy-2.4.0.jar
- file:/C:/dev/eclipse/plugins/org.codehaus.groovy_2.4.10.xx-201704192203-e45/lib/servlet-api-2.4.jar
- file:/C:/Users/me/.groovy/greclipse/global_dsld_support/
- file:/C:/dev/eclipse/plugins/org.codehaus.groovy_2.4.10.xx-201704192203-e45/plugin_dsld_support/
sun.misc.Launcher$ExtClassLoader@1787f2a0
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/access-bridge-64.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/cldrdata.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/dnsns.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/jaccess.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/jfxrt.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/localedata.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/nashorn.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/sunec.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/sunjce_provider.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/sunmscapi.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/sunpkcs11.jar
- file:/C:/Program%20Files/Java/jdk1.8.0_111/jre/lib/ext/zipfs.jar
Caught: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

In this example however I can see that the sqljdbc4 JAR is on the classpath. If anyone has insight, that would be great, at this point I've exhausted about every combination of code I can think of to get this to work.

The only way I got this to run was by adding the JAR into my .groovy/lib directory, but I need to execute this from Jenkins which could run on 10 different slave nodes, so I really don't want to have to manually manage this JAR across different servers.

sql-serverjdbcgroovygroovy-grape


Solution 1:[1]

In my experience you need both the GrabConfig annotation and a traditional invocation of Class.forName

For example, this is how I load a JDBC driver in standalone Groovy scripts:

@GrabConfig(systemClassLoader=true)
@Grab('org.postgresql:postgresql:+')

import groovy.sql.Sql

Class.forName('org.postgresql.Driver')

DB = Sql.newInstance('jdbc:postgresql://127.0.0.1/', 'postgres', 'postgres')

Solution 2:[2]

If you are not using the default system class loader, then you must explicitly load a JDBC driver before you can use it. Only JDBC drivers on the default class loader will automatically get loaded by DriverManager. In this case, you will explicitly need to load the JDBC driver using Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"), or maybe even Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver", true, classLoader)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tobia
Solution 2 Mark Rotteveel

Related Questions