Connecting to a Microsoft Access database using Processing

Question

I'm trying to connect to a local Microsoft Access Database with Processing (v3.4 windows64).

According to this previous answer ( Manipulating an Access database from Java without ODBC ) I've downloaded UCanAccess libraries from here ( http://ucanaccess.sourceforge.net/site.html ) the current version is 4.0.4

It includes ucanaccess-4.0.4.jar and, under lib folder: commons-lang-2.6.jar, commons-logging-1.1.3.jar, hsqldb.jar, jackcess-2.1.11.jar

Here the simple sketch code I'm running:

import java.sql.*;

void setup() {
  size(640, 360);  // Size must be the first statement
  stroke(255);     // Set line drawing color to white
  frameRate(30);

  try{

    Connection conn=DriverManager.getConnection("jdbc:ucanaccess://D:/Dati/Profili/M030098/Documents/Database1_test.accdb");
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("SELECT * FROM tab_one");
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }

  }catch(Exception e){
     e.printStackTrace();
  }
}

I'm not sure to correctly import the 5 libraries within my sketch. Without them the catch block returns this message:

java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://C:/ ... path to my folder ... /Documents/Database1_test.accdb

If I import the 5 JARs coping them inside Processing\libraries folder and then add the line import hsqldb.jar; or import hsqldb.*; to my sketch the result is this console error:

No library found for hsqldb
Libraries must be installed in a folder named 'libraries' inside the sketchbook folder (see the Preferences window).

The only wired way I've found is to create a dedicated folder inside libraries with the same name of the jar including a library subfolder including the jar. All of them renamed removing any numbers and dash characters (see image). In this way the libraries names are available under sketch > import library and, if selected, add several new include lines (48) to the sketch.

One of them ( import org.apache.commons.lang.enum.*; ) results in an error: Syntax error on token "enum", Identifier expected.

I've just commented that line considering import org.apache.commons.lang.*; instead.

As a result a new error occurs referring to line

Statement s = conn.createStatement();

: the type Statement is ambiguous

Then I've looked for the library conflict and commented also import org.hsqldb.*;

Now it seems to work correctly, I can retrieve my table information but in a very messy way.

Is there a correct way to import just the 5 libraries?

UPDATE

As suggested by Kevin Workman drag and drop the JARs file in the sketch window is also possible and automatically creates a code folder within the sketch folder including the dropped files.

It includes all the packages and no more line of code are needed, anyway, this procedure has two drawbacks:

1. Due to the fact that behind the scenes it creates a code folder within the sketch folder import the packages only for one single sketch instead of the "global" library folder, and needs to be replicated for every sketch.

2. I've noticed two errors after importing the packages: one due to org.apache.commons.lang.enum.* which uses the dedicated word enum and org.hsqldb.* which has a definition of Statement object in conflict with another library. The drag and drop technique doesn't allow to select the single library to exclude in order to solve these errors.

Answer 1

The easiest way I know of to import a .jar file into a Processing sketch is to drag the .jar file onto the Processing editor.

Do this for your 5 .jar files. If you're curious about what it's doing behind the scenes, take a look at your sketch directory after you do the dragging.

Shameless self-promotion: here is a tutorial on using libraries in Processing.