Problem with a UCanAccess Java program to read an Access database. HELP!

[Paul S Poole]

UCanAccess problem(s) Help!!!

I am very excited to figure out how to use UCanAccess to access (no pun intended) an Access database!
But, I can't seem to get the planets lined up. What I would like is to get the simplest possible
example of a Java program using UCanAccess to read a few rows of an Access .accdb database.

Below is my attempt at a simple Java program to read access db access1.accdb.
I have no idea if it is calling UCanAccess correctly! It probably won't run as is,
even though it compiles.

My environment is Windows 7 (don't laugh! :) and Java version 1.8.0_60
I want to get this to work in a plain vanilla way first, not using, Ecllipse, Ant, etc.
I will worry about that later. (Note: I am a hard-core C developer, but a Java newbie/novice!)

Per the UCanAccess site (http://ucanaccess.sourceforge.net/site.html) I downloaded UcanAccess
and the jars that UCanAccess says it needs. I don't know if any other jars are required.
The test program is in C:\Ucan\Ucan.java
My simple Access database is in C:\Ucan\access1.accdb
The external jars are in C:\Ucan\lib

Directory of C:\Ucan\lib
commons-lang3-3.4.jar
commons-logging-1.2.jar
hsqldb.jar
jackcess-2.1.2.jar
ucanaccess-3.0.0.jar

Here's the pitiful program. (I resisted calling it "UCant.java"! i.e. 't' for "test":)

C:\Ucan\Ucan.java

// Attempt to read Access database access1.accdb using UCanAccess
import java.io.;
import java.sql.;

public class Ucan {
public static void main(String[] args){
try {
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://C:/Ucan/access1.accdb");
System.out.println("OK");
} catch (SQLException | ClassNotFoundException e) {
System.out.println("fail");
e.printStackTrace();
}
}
}

It will compile:

C:\Ucan>javac -cp .;c:\Ucan\lib*.jar Ucan.java

...but when I try to run it, JRE spits it out with the dreaded ClassNotFoundException error!

C:\Ucan>java -cp .;c:\Ucan\lib*.jar Ucan
fail

java.lang.ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at Ucan.main(Ucan.java:9)

I have invested many hours on this problem, but at this point I am
stuck. From the Google research I have done, the problem seems to be
that the JRE loader can't find the class(es) that it needs to load. I
have no idea where to put the third-party jars that UCanAccess needs
or if there are others that I don't know about! I used the -cp switch
on the java command line to try to point to the jars in C:\Ucan\lib.
It looks like the javac loader cannot find class "forName". I have no
idea what jar that is in!

I have been developing software (not Java) for a long time, and am
used to down-and-dirty program debugging, but Working with classes and
jars in Java is a mystery wrapped in an enigma, Vastly more
complicated that it needs to be in my humble opinion! I even tried
extracting one class (jar xf) that Java said it could not find
from the jar that it was in and copied that single class to the directory
that my test program is in. Java still couldn't find it! Why does
the Java build path have to be a complete mystery? The compiler should
just output what paths it is searching to find the called classes!!

Please advise what to do next. I just want a starting point, i.e.,
the simplest Java / UCanAccess program possible to read a few rows
of an Access db table. I will take it from there!

* Attention UCanAccess and Java Gurus! *

Getting UCanAccess running quite literally means a job for me helping a
company migrate from Access to MySQL!

Programmatic access (no pun intended) to databases via UCanAccess is all the
more important, now that Oracle has pulled the plug on odbc support in Java 8!

Any help will be greatly appreciated!!
Not only that, but I am going to give you the rest of the day off!! :)

Best Regards, Paul Poole aka CCapTN: CCapTN@gmail.com

[Gord Thompson]

Okay, so in "C:\Ucan\" I have

C:\Ucan\access1.accdb
C:\Ucan\Ucan.java

and in "C:\UCan\lib\" I have

C:\UCan\lib\ucanaccess-3.0.0.jar
C:\UCan\lib\commons-lang-2.6.jar
C:\UCan\lib\commons-logging-1.1.1.jar
C:\UCan\lib\hsqldb.jar
C:\UCan\lib\jackcess-2.1.2.jar

My "access1.accdb" database file contains a table named Table1 that has one row in it.

My "Ucan.java" looks like this:

import java.sql.*;

public class Ucan {

    public static void main(String[] args) {
        String tableName = "Table1";
        try (
                Connection conn = DriverManager.getConnection(
                        "jdbc:ucanaccess://C:/UCan/access1.accdb");
                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(String.format(
                        "SELECT COUNT(*) AS n FROM [%s]", 
                        tableName))) {
            rs.next();
            System.out.println(String.format(
                    "%d row(s) found in table [%s]", 
                    rs.getInt("n"), 
                    tableName));
        } catch (Exception e) {
            e.printStackTrace(System.err);
        }
    }
}

I compile "Ucan.java" into "Ucan.class" with the following commands at a Windows command prompt:

c:
cd \Ucan
"\Program Files\java\jdk1.8.0_51\bin\javac.exe" Ucan.java

Then I can run the application ("Ucan.class") with the following command:

java -cp C:/Ucan;C:/UCan/lib/ucanaccess-3.0.0.jar;C:/UCan/lib/commons-lang-2.6.jar;C:/UCan/lib/commons-logging-1.1.1.jar;C:/UCan/lib/hsqldb.jar;C:/UCan/lib/jackcess-2.1.2.jar Ucan

which returns

1 row(s) found in table [Table1]

[Paul S Poole]

Gord, first of all, let me thank you for your post to help me with my UCanAccess problem!
I really appreciate it!

I used your "Ucan.java" code, but had to fix a few typos before it would compile, as follows:

changed "try (" to "try {"
changed "tableName))) {" to "tableName));"

Your "Ucan.java" with the fixes:

import java.sql.*;

public class Gord {

public static void main(String[] args) {
    String tableName = "Table1";
    try {
            Connection conn = DriverManager.getConnection(
                    "jdbc:ucanaccess://C:/UCan/access1.accdb");
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(String.format(
                    "SELECT COUNT(*) AS n FROM [%s]", 
                    tableName));
        rs.next();
        System.out.println(String.format(
                "%d row(s) found in table [%s]", 
                rs.getInt("n"), 
                tableName));
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }
}

}

However, trying to run it gives the following error, see below.
(Note that the java command accepted "*.jar" to find the jar files in C:/Ucan/lib)

C:\Ucan>java -cp .;C:/Ucan/lib/*.jar Ucan

java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://C:/UCan/access1.accdb
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at Gord.main(Gord.java:8)

My interpretation of the above error is that the ClassNotFoundException error seems to be resolved, i.e., all the necessary jars and classes have been found by the class loader.
That is great and a lot farther along than I was!

If the CLASSPATH issue is resolved, then I am thinking that the error is the actual Access database connection string, "jdbc:ucanaccess://C:/UCan/access1.accdb". Does that sound right?

If so, what would be the correct connection string for UCanAccess to connect to an Access .accudb database??

Any ideas? Thanks again for your help, Gord!

[Gord Thompson]

Hi Paul.

try ( ... ) {

is not a typo. It's a "try with resources", a feature that was introduced in Java 7.

As for "*.jar" in the classpath (-cp) specification, I just tried it and it didn't work for me. (I got the same error as you.) Try specifying all of the .jar files explicitly like I did in my previous reply.

[Paul S Poole]

Gord, thanks for hanging with me on this! I appreciate your help and expertise SO much.
Excuse my ignorance on the "try with resources" Java feature!
I hope that you won't mention it to the millions of other readers of SourceForge forums! :)

I got a compile with your code, just as you posted it with the try with resources.
Then, per your suggestion, I went back to the java cmd with explicit reference to the jars in C:\Ucan\lib. Evidently I was rong about java -cp accepting "*.jar" for the jar reference.

One thing I noticed was that I had the incorrect jar file names on the .jar on the java command versus the actual newer-version jar names in the /lib directory. I fixed that, but to no avail! I still got a "core-dump" from the java command, see below. My so-called system must be really hosed!

p.s. I did a post on the Jackcess help forum where I tried a test run of Jackcess from netbeans as you had posted almost two years ago! I tried to exactly duplicate what you had described there:

http://stackoverflow.com/questions/19450755/jackcess-exception?rq=1

but that didn't work for me either! :( I don't really understand the difference between Jackcess and UCanAccess, but thought I would try Jackcess as well.) It seems impossible to get the planets lined up on this! But I am not giving up. At least if I go crazy, it will be a short trip! Why does Java hate me?!

Thanks for your help and patience, Gord!
Best Regards, Paul

Here is the "core dump" from the command line attempt to run Ucan.

C:\Ucan>java -cp C:/Ucan;C:/UCan/lib/ucanaccess-3.0.0.jar;C:/UCan/lib/commons-lang3-3.4.jar;C:/UCan/lib/commons-logging-1.2.jar;C:/UCan/lib/hsqldb.jar;C:/UCan/lib/jackcess-2.1.2.jar Ucan

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/lang/builder/CompareToBuilder
at com.healthmarketscience.jackcess.impl.RowIdImpl.compareTo(RowIdImpl.java:106)
at com.healthmarketscience.jackcess.impl.IndexData$Entry.compareTo(IndexData.java:2039)
at com.healthmarketscience.jackcess.impl.IndexData$Entry.compareTo(IndexData.java:1847)
at java.util.Collections.indexedBinarySearch(Unknown Source)
at java.util.Collections.binarySearch(Unknown Source)
at com.healthmarketscience.jackcess.impl.IndexData$DataPage.findEntry(IndexData.java:2570)
at com.healthmarketscience.jackcess.impl.IndexData.findEntryPosition(IndexData.java:844)
at com.healthmarketscience.jackcess.impl.IndexData.access$3700(IndexData.java:47)
at com.healthmarketscience.jackcess.impl.IndexData$EntryCursor.updatePosition(IndexData.java:2335)
at com.healthmarketscience.jackcess.impl.IndexData$EntryCursor.restorePosition(IndexData.java:2273)
at com.healthmarketscience.jackcess.impl.IndexData$EntryCursor.restorePosition(IndexData.java:2256)
at com.healthmarketscience.jackcess.impl.IndexData$EntryCursor.beforeEntry(IndexData.java:2218)
at com.healthmarketscience.jackcess.impl.IndexCursorImpl.findPotentialRow(IndexCursorImpl.java:376)
at com.healthmarketscience.jackcess.impl.IndexCursorImpl.findFirstRowByEntryImpl(IndexCursorImpl.java:282)
at com.healthmarketscience.jackcess.impl.IndexCursorImpl.findFirstRowByEntry(IndexCursorImpl.java:153)
at com.healthmarketscience.jackcess.impl.DatabaseImpl$DefaultTableFinder.findRow(DatabaseImpl.java:2074)
at com.healthmarketscience.jackcess.impl.DatabaseImpl$TableFinder.findObjectId(DatabaseImpl.java:1953)
at com.healthmarketscience.jackcess.impl.DatabaseImpl.readSystemCatalog(DatabaseImpl.java:858)
at com.healthmarketscience.jackcess.impl.DatabaseImpl.<init>(DatabaseImpl.java:518)
at com.healthmarketscience.jackcess.impl.DatabaseImpl.open(DatabaseImpl.java:389)
at com.healthmarketscience.jackcess.DatabaseBuilder.open(DatabaseBuilder.java:248)
at net.ucanaccess.jdbc.DefaultJackcessOpener.open(DefaultJackcessOpener.java:32)
at net.ucanaccess.jdbc.DBReference.<init>(DBReference.java:160)
at net.ucanaccess.jdbc.DBReferenceSingleton.loadReference(DBReferenceSingleton.java:51)
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:96)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at Ucan.main(Ucan.java:12)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.lang.builder.CompareToBuilder
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
... 28 more

[Gord Thompson]

You are trying to use commons-lang 3.x but UCanAccess (Jackcess, actually) requires commons-lang 2.x. Marco has included the correct versions of all the required additional .jar files in the lib\ folder of the UCanAccess distribution, so just copy them into your Ucan\lib folder.

[Paul S Poole]

Gord, THAT WORKED!!!!! YOU DA MAN! I HAVE JUST LET OUT A WAR WHOOP!!!
NOW I AM GOING OUTSIDE TO RUN UP AND DOWN THE STREET!

PLUS, I AM GOING TO GIVE YOU THE DAY OFF TOMORROW!
EVEN IF YOU DON'T CELEBRATE LABOR DAY THERE IN CANADA, EH?!
I AM GOING TO TAKE YOU OUT FOR WHATEVER YOU DRINK,
THE NEXT TIIME YOU ARE IN TENNESSEE!

MANY THANKS, KEMO SABE!

CCapTN