[groovy-user] How to query an MS Access accdb file with Groovy?

[citron]

Hi,

I want to query an Access db, however this code is not quite working.

Any ideas how to get it right?

Thanks!

import groovy.sql.Sql

db = Sql.newInstance("jdbc:odbc:file:///C:/Data/MSAccess.accdb")

---------------
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Datakällan hittades
inte och ingen standarddrivrutin är angiven

at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6956)

at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113)

at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3072)

at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)

at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)

at java.sql.DriverManager.getConnection(DriverManager.java:579)

at java.sql.DriverManager.getConnection(DriverManager.java:243)

at groovy.sql.Sql.newInstance(Sql.java:232)

at groovy.sql.Sql$newInstance.call(Unknown Source)

at
org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)

at
org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)

at
org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)

at ConsoleScript7.run(ConsoleScript7:3)

at
groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:266)

at groovy.lang.GroovyShell.run(GroovyShell.java:517)

at groovy.lang.GroovyShell.run(GroovyShell.java:172)

at groovy.lang.GroovyShell$run.call(Unknown Source)

at groovy.ui.Console$_runScriptImpl_closure16.doCall(Console.groovy:924)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:601)

at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)

at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)

at
org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:272)

at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:883)

at
org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:66)

at
org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:141)

at groovy.ui.Console$_runScriptImpl_closure16.doCall(Console.groovy)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:601)

at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)

at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)

at
org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:272)

at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:883)

at groovy.lang.Closure.call(Closure.java:410)

at groovy.lang.Closure.call(Closure.java:404)

at groovy.lang.Closure.run(Closure.java:488)

at java.lang.Thread.run(Thread.java:722)

-----
Project pages
AndersTool
earBuddy

--
View this message in context: http://groovy.329449.n5.nabble.com/How-to-query-an-MS-Access-accdb-file-with-Groovy-tp4974351p4974351.html
Sent from the groovy - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Rob Elsner]

> java.sql.SQLException: [Microsoft][ODBC Driver Manager] Datakällan hittades
> inte och ingen standarddrivrutin är angiven

Google tells me this is "The data source was found No, and no default
driver is specified"

If you are using ODBC, you should create a user or system datasource.
That should specify the Access driver and data base (file), so then
your JDBC connection URI is something like "jdbc:odbc:datasourceName"

Rob

[Jason Stell]

Just to throw it out there, Jackcess (http://jackcess.sourceforge.net/) can be used to read MSAccess data files without going the JDBC/ODBC route. *And it works on Linux* :)

Jason

[citron]

I downloaded Jackcess 1.2.5 from here
http://sourceforge.net/projects/jackcess/files/jackcess

and

apache-common-lang 1.0 from here (latest version didn't work with Jackcess
1.2.5)
http://archive.apache.org/dist/commons/lang/old/v1.0

and it works like a charm!!

Thanks a lot Jason!

-----
Project pages
AndersTool
earBuddy

--
View this message in context: http://groovy.329449.n5.nabble.com/How-to-query-an-MS-Access-accdb-file-with-Groovy-tp4974351p4977074.html

[Ewan Dawson]

On 8 November 2011 14:11, citron <viklund...@hotmail.com> wrote:

Hi,

I want to query an Access db, however this code is not quite working.

Any ideas how to get it right?

Thanks!

For the record, here's how to connect to a MS Access db using groovy.sql.Sql:

def dbPath = '\path\to\my\db'

def connectionString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=$dbPath;DriverID=22}"

def sql = groovy.sql.Sql.newInstance(connectionString, '', '', 'sun.jdbc.odbc.JdbcOdbcDriver')

You'll need to have the JDBC-ODBC bridge installed, obviously.

Cheers,

Ewan