import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@hostname:1521:testdb", "user", "pass", "oracle.jdbc.driver.OracleDriver")
def query = "select * from tablename where name='Beta 1'"
row = sql.firstRow(query)
println row
However, despite various attempts, I couldn’t even establish a DB connection because of this annoyingly persistent exception:
Caught: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver at sql1.run(sql1.groovy:13)
So of course, I immediately googled the rather-descriptive error message, and quickly learnt that Groovy didn't know where to find the oracle jdbc driver. With a few more searches, I discovered that the missing driver was located inside a jar that was called something like “ojdbc14.jar” or something.
I found where the jar was on my file system:
”C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0” contained ojdbc14-10.2.0.1.0.jar that seemed like a good candidate for the jar containing the required oracle jdbc driver. Seemed like I’m making progress, good.
Now how could I tell Groovy/Java to search this path? I figured that I had to include the jar’s file-system path into my Java CLASSPATH, so that groovy would know where to find missing classes.
Okay, so I added “C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0” to my CLASSPATH environment variable, but that didn’t seem to work.
Still no good :-(
Hmmm, “groovy –h” indicates that I can specify a classpath on the command line with the –cp or –classpath parameters. So let’s try that:
$ groovy -classpath C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0 sql1.groovy Caught: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver at sql1.run(sql1.groovy:2)
Dammit work already! :P
I realized that I must be missing something very basic. It had to be a total n00b Java/Groovy problem. C’mon it can’t really be so hard to specify a path can it?!?
So I started googling about setting the java CLASSPATH correctly and then I stumbled on why it wasn’t’ working for me:
JARs on the classpath
Java compiler and run-time can search for classes not only in separate files, but also in `JAR' archives. A JAR file can maintain its own directory structure, and Java follows exactly the same rules as for searching in ordinary directories. Specifically, `directory name = package name'. Because a JAR is itself a directory, to include a JAR file in the class search path, the path must reference the JAR itself, not the directory that contains the JAR. This is a very common error.
Suppose I have a JAR jarclasses.jar in directory /jarclasses. The Java compiler look for classes in this jar, we need to specify:
javac -classpath /jarclasses/jarclasses.jar
and not merely the directory jarclasses.
Thank you, http://www.roseindia.net/java/java-classpath.shtml, for saving me from a lot of frustration!
Voila! Finally it works!
$ groovy -classpath C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0\ojdbc14-10.2.0.1.0.jar sql1.groovy [NAME:Summary, PATH:Snapshot, MAPPINGKEY:26, VERSION:v9]
It’s even easier with the CLASSPATH environment variable set correctly like this:
CLASSPATH=C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0\ojdbc14-10.2.0.1.0.jar;
Now I don’t even have to type the –classpath parameter out on the command line each time:
$ set | findstr CLASSPATH CLASSPATH=C:\Users\ambars\.m2\repository\com\oracle\ojdbc14\10.2.0.1.0\ojdbc14-10.2.0.1.0.jar;C:\Program Files\Java\jre6\lib\ext\QTJava.zip;. $ groovy sql1.groovy [NAME:Summary, PATH:Snapshot, MAPPINGKEY:26, VERSION:v9]
