Issues using Grapes to grab JDBC driver

Question

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.

This question is not related to sql-server, please use tags properly — FLICKER, Dec 21 '17 at 20:26
@FLICKER It is, the OP is trying to connect to SQL Server, which is relevant for the question. — Mark Rotteveel, Dec 22 '17 at 11:34
@MarkRotteveel, when we choose a tag, we actually choose the audience of the question. to answer this question, no SQL Server knowledge needed. so sql server selecting sql-server tag only shows this up in the list of people who are not able to answer and interested to see this question. — FLICKER, Dec 22 '17 at 17:53
@FLICKER The fact this is about connecting to SQL Server might actually be important (eg if somebody would use a connection string that is incorrect for SQL Server). — Mark Rotteveel, Dec 23 '17 at 08:52

score 2 · Answer 1 · answered May 30 '18 at 09:13

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')

score 0 · Answer 2 · answered Dec 22 '17 at 11:37

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)

Issues using Grapes to grab JDBC driver

2 Answers2