How to setup datasource for MS Access MDB file using UCanAccess on Ubuntu 14.04 LTS

[Michael Aushana]

Hey Guys,

I'm looking for some help regarding the steps I have to follow to setup UCanAccess on my Ubuntu box for a small ColdFusion site that uses MS Access for a db. I have everything working in terms of lucee but I can't seem to figure out how to get the UCanAccess datasource to connect to my MS Access db. Here is what I've done so far:

1) I downloaded the ucanaccess.zip file and extracted all jar files and put them in /opt/lucee/lib/ folder.
2) I've renamed hypersonic-hsqldb.jar to hypersonic-hsqldb_old.jar
3) I tried using hsqldb.jar that came with the ucanaccess.zip file but it didn't work. So renamed it to hypersonic-hsqldb.jar.
4) I login to the Lucee server admin console and I try to setup the datasource using the following:

Type: Other - JDBC Driver
Class: net.ucanaccess.jdbc.UcanloadDriver
Connection: jdbc:ucanaccess:///home/MyUserName/www/database/MyMsAccessDB.mdb

I'm getting this error:

The UCANACCESS_HOME system variable isn't defined:
it should be:
-DUCANACCESS_HOME=

Where do I have to define this/these variables and what values should they be set to? I've searched all over the web but I can't seem to find anything. Is this even possible to do on Ubuntu or will this only work on Windows?

Any help is appreciated.

Thank you,

Mike

[Hugo Ahlenius]

Michael Aushana wrote on 2016-05-30:
> The UCANACCESS_HOME system variable isn't defined:
> it should be:
> -DUCANACCESS_HOME=

This looks like an argument to the servlet engine at startup - e.g. Tomcat (maybe check catalina.sh for the startup)

[Mark Drew]

Looks like an environment variable needs to be set

Mark Drew
- Sent by typing with my thumbs.

> --
> Win a ticket to dev.objective from Lucee via Twitter, see http://bit.ly/1UbTMWj for details, good luck and see you there...
> ---
> You received this message because you are subscribed to the Google Groups "Lucee" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
> To post to this group, send email to lu...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/198901d1bafe%24541a8a20%24fc4f9e60%24%40oxel.net.
> For more options, visit https://groups.google.com/d/optout.

[Mark Drew]

Hi Mike

A few things:

1) I downloaded the ucanaccess.zip file and extracted all jar files and put them in /opt/lucee/lib/ folder.

That should make it available, you should put it in a sub folder of it’s own I think

2) I've renamed hypersonic-hsqldb.jar to hypersonic-hsqldb_old.jar

the name of hypersonic.jar doesn’t matter, if its extension is .jar it will still be loaded, so if you don’t want it maybe rename it .old rather than old.jar

3) I tried using hsqldb.jar that came with the ucanaccess.zip file but it didn't work. So renamed it to hypersonic-hsqldb.jar.

Again, the name of the file doesn’t matter except the extension so hypersonic-hsqldb is still being loaded. 

4) I login to the Lucee server admin console and I try to setup the datasource using the following:

Type: Other - JDBC Driver
Class: net.ucanaccess.jdbc.UcanloadDriver
Connection: jdbc:ucanaccess:///home/MyUserName/www/database/MyMsAccessDB.mdb

I'm getting this error:

The UCANACCESS_HOME system variable isn't defined:
it should be:
-DUCANACCESS_HOME=

See:

http://stackoverflow.com/questions/34464565/ucanaccess-home-system-variable-not-set

Where do I have to define this/these variables and what values should they be set to? I've searched all over the web but I can't seem to find anything. Is this even possible to do on Ubuntu or will this only work on Windows?

you can set this in the opt/lucee/tomcat/bin/setenv.sh file. 

Regards

Mark drew

[Michael Aushana]

Hi Mark,

Thank you very much for the quick reply. I got it to work with the info you provided. Here is what I did just so it helps anyone else looking to do this:

1) I downloaded the UCanAccess zip file and extracted it.

2) I uploaded only the files that were in the /lib/ folder from the zip to the my Ubuntu server in the /opt/lucee/lib/ folder.

3) I setup an environment variable in /opt/lucee/tomcat/bin/setenv.sh file like so:

UCANACCESS_HOME=/opt/lucee/lib;

export UCANACCESS_HOME;

4) I restarted the server just to make sure everything gets loaded up for the environment variable.

5) In the lucee server admin panel I added the datasource with the following options:

Name: YourDataSourceName

Type: Other - JDBC Driver

UserName: Blank (Unless your db needs a username then enter it here.)

Password: Blank  (Unless your db needs a password then enter it here.)

Connection Limit: Default

Connection Timeout: 0 (Zero)

...leave all other options as defaults...

Class: net.ucanaccess.jdbc.UcanaccessDriver

Connection: jdbc:ucanaccess:///home/MyUserName/www/database/MyMsAccessDB.mdb

Verify Connection: Default

6) Click the Create button to complete setting up the datasource. You should have the datasource connecting to your db now :D

I hope this helps anyone else looking to do this.

Thanks again Mark for the info your provided. Much appreciated.

Cheers,

Mike