Trying to use UCanAccess JDBC driver for MS-Access working w/Railo

[Leonard Chan]

Hi,

I've recently migrated a client site from an old Adobe CF shared hosting account to a Railo install on a Windows VPS. Everything is 64 bit.

We've worked through a few incompatibilities... but I'm stuck on a script we which uses an Access (.mdb) database to bulk-upload updates to several tables at once. We're using the JDBC-ODBC driver. Intermittently, but frequently, we get: "[Microsoft][ODBC Driver Manager] Invalid string or buffer length"

I don't think it's specific to Railo, other JDBC-ODBC users get this error with the JDBC-ODBC bridge and the 64 bit Access ODBC driver, e.g. https://forums.oracle.com/thread/2318875

I was hoping to try the newish UCanAccess JDBC driver for Access available here: http://sourceforge.net/projects/ucanaccess/ to skip the ODBC bridge driver.

I'm in over my head though... I'm not 100% sure where the JAR files go, nor what to use in the "Other - JDBC Driver" setup. And I'm not 100% if UCanAccess is 32 bit or 64 bit (or if that even matters for a JDBC driver).

I've tried:
- All the JAR files from the UCanAccess zip file were put into c:\railo\lib - there are multiple JARs, UCanAccess itself uses a few libraries.
- In the Other-JDBC driver setup:
-- Class: net.ucanaccess.jdbc.UcanaccessDriver
-- Connection String: jdbc:ucanaccess://C:\railo\tomcat\webapps\ROOT\data\client.mdb [actually, I've tried many variations on this]

When I enter these into the Railo datasource setup, I get:
"No suitable driver found for jdbc:ucanaccess://C:/railo/tomcat/webapps/ROOT/data/client.mdb"

And then for some reason the Class is automagically changed to: net.sourceforge.jtds.jdbc.Driver

We're on Railo 4.1.0.009 beta on Apache Tomcat/7.0.37 - started from the normal install bundle on the Railo site a couple months ago.

I'm not sure where to start debugging this. Railo and Tomcat and to a large extent, Java, are new to me.

How do I check to see if Railo (or Tomcat?) even sees UCanAccess JARs?
How can I test a JDBC connection outside of Railo?

Thanks,

Leonard

[marco amadei]

Hi Leonard,
I'm sorry to intrude in this group, this is a very interesting use case for my UCanAccess.
You can test UCanAccess outside Railo simply opening your mdb with the UCanAccess command line console (console.bat) and executing some test queries (UCanAccess is a pure java implementation and works with both 32-bit and 64-bit).
If all works fine, it means you have a configuration problem with Railo.
If not, please let me know.
Cheers Marco
P.S. A problem could be the hypersonic-hsqldb.jar (old hsqldb driver) in the railo lib folder: it's a conflicting (and unsupported) version of the hsqldb.jar used by UCanAccess.

[Leonard Chan]

Hi Marco,

Thank you for the hints, it narrowed down my testing a lot. 

It took some work, but I finally have a test page working.

So, to get the command line interface working, I had to put the UCanAccess libraries under c:\railo\lib\lib; however, for Railo, I had to move all 5 JAR files into c:\railo\lib (where I had them originally). 

I'm not sure, but I may have stumbled on a bug in the version of railo we are using. The class wouldn't stick in the Railo GUI, as I initially posted, it changes to net.sourceforge.jtds.jdbc.Driver and this is somehow related to the problem.

I created a new test file and used the new hard-coded datasources with a minimal application.cfc, per:  http://blog.getrailo.com/post.cfm/railo-4-1-explicit-datasources-in-application-cfc - and I got my select to work!

For anyone who wants to try this...

This is the contents of the test application.cfc that worked for me:

<cfcomponent>

<cfscript>

this.name = hash( getCurrentTemplatePath() ); // unique app name

this.datasources['client-jdbc']={

class:'net.ucanaccess.jdbc.UcanaccessDriver'

,connectionString:'jdbc:ucanaccess://C:/railo/tomcat/webapps/ROOT/data/client.mdb'

};

</cfscript>

</cfcomponent>

The "this.datasources" bit is from the Railo web GUI, BUT, I had to fix the "class" line.

Also: I did remove the  hypersonic-hsqldb.jar file, per your suggestion, but now that I type this, I'm not 100% it was necessary. I tried this before the explicit datasource attempt.

Ironically, MS-Access support was the key reason we went with a Windows VPS, rather than a Linux one... that probably wouldn't matter now that we will be using UCanAccess.

So... I'm closer. I still need to find a way to apply this to the production code (the site uses the older application.cfm file)... but that will be in a separate post to this group. :)

Thanks.

LC

[marco amadei]

Okay Leonard, great!
hsqldb.jar old version might cause problems but it depends on the order in which the jars are loaded by the classloader (if   the newest version is loaded  first,  you won't  have any problems).
Should you need any  assistance about UCanAccess, don't hesitate  to contact me in the UCanAccess forum (http://sourceforge.net/p/ucanaccess/discussion/help/) or by email.
Best, Marco