New to using Ucanaccess

[kdcantcodeit]

Hello, I'm kind of a programming newbie and was hoping for some info on how to use this. I downloaded . zip of the code, but how do I proceed? For some background, I received a legacy jar file from work that relied on the old "sun.jdbc.odbc.JdbcOdbcDriver" method for connection. I tried to change it to ucanaccess by adding the ucanaccess-5.1.2-uber.jar to my classpath, but when I tried to run, I get the error:

Exception in thread "main" java.lang.NoClassDefFoundError: javax/script/Bindings
at shaded.io.github.spannm.jackcess.impl.DatabaseImpl.getEvalContext(DatabaseImpl.java:906)
.....
at net.ucanaccess.converters.LoadJet$TablesLoader.createTables(LoadJet.java:1050)
.....
Caused by: java.lang.ClassNotFoundException: javax.script.Bindings
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)
....

I figured maybe because I am using a newer version of Access and I need to somehow incorporate your updates?

here is what I am running:

public static Connection getConnection(String strFileName) {
Connection con = null;

	      try {
	    	System.out.println("jdbc:ucanaccess://" + strFileName);
	        con = DriverManager.getConnection("jdbc:ucanaccess://" + strFileName);
	      } catch (Exception var4) {
	         System.out.println("Error: " + var4);
	      }

	      return con;
	   }

where strFileName is the filepath to my .accdb. Any ideas on what could be my issue? Or do I just need to update to this new version, and if so, how do I do that? Any guidance is much appreciated!

[kdcantcodeit]

I have continued to try to debug, and upon more research, it seems like I am using the latest ucanaccess version, but I haven't sourced the .jar correctly. Any ideas how I can do that?

[gordthompson]

So are you trying to run your code from within an IDE? If so, which one? Eclipse? NetBeans? …?

[kdcantcodeit]

Hi Gord, I have been following your reference guide on stack exchange, thanks so much for that.

The original program was meant to be run in command prompt. There is a batch file that I use to run the .jar file. When I try to run it there, I get:
Error: java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://C:/.../mydb.accdb

I am switched to running within Eclipse to see if I got better results. I have now gotten it to a point where it is utilizing the ucanaccess driver as expected. My new issue is accessing my tables. I am running and getting:

net.ucanaccess.exception.UcanaccessSQLException: UCAExc:::5.1.2 unexpected token : INTO required: FROM

The line that throws this is:
DBLayer.insertDataStatement(sh1, sqlHH1);

sh1 and sql1HH1 are defined previously as:
Statement sh1 = con.createStatement();
String sqlHH1 = "SELECT logis.clelogis, logis.nbpers, logis.typelogis, logis.ztlogis07, logis.FacLog, logis.hhincome, logis.hworker, 0 as hh1per, 0 as hh2per, 0 as hh3per, 0 as hh4per, 0 as hh5per, 0 as hh45per, 0 as hh6per, 0 as hhsizemis, 0 as grndhh, 0 as apts, 0 as hhtypemis, 0 as hh1inc, 0 as hh2inc, 0 as hh3inc, 0 as hh4inc, 0 as hhincmis, 0 as hh0work, 0 as hh1work, 0 as hh2work, 0 as hh3work INTO HHDerived FROM LOGIS";:

net.ucanaccess.exception.UcanaccessSQLException: UCAExc:::5.1.2 user lacks privilege or object not found: HHDERIVED

I guess my previous method of creating the table did not work?

[spannm]

UCanAccess is in the first place a pure Java JDBC driver.

The uber jar ist provided for history and convenience reasons as well as special usage scenarios.

Very release also ships with a source jar (amongst other) if that's what you're looking for.
We also publish snapshots (betas) of upcoming versions.

Link to the Sonatype Maven Central Release Repo

The statement above SELECT [..] INTO HHDerived FROM LOGIS is SQL dialect (Transact-SQL).
Please don't expect this to work with UCanAccess / HSQLDB / MS Access, but stick to ANSI SQL. Create the table first, then insert into it.

[gordthompson]

Hi @spannm .

I'm pretty sure that SELECT … INTO new_table FROM my_table works with Access ODBC (on Windows), but it certainly doesn't work with UCanAccess. I was just messing around with UCanAccess, and the corresponding query in HSQLDB syntax

CREATE TABLE new_table AS (SELECT col_a, col_b FROM my_table) WITH DATA;

does create the table, but the WITH DATA clause seems to be ignored, so new_table is empty. I then had to do

INSERT INTO new_table SELECT col_a, col_b FROM my_table;

to get the rows in there.