Menu ▾ ▴

Can I use UCanAccess in a Java application that uses hsqldb (latest version) as an embedded db?

Help
Carl Kjellberg
2017-06-15
2017-06-15

  • Carl Kjellberg

    Carl Kjellberg - 2017-06-15

    hsqldb-2.4.0
    UCanAccess-4.0.2

    Overview of this topic
    - Problem
    - Test
    - Source code
    - Successful execution when only ucanload.jar in the CLASSPATH
    - Failed execution when hsqldb.jar (latest version) is added to the CLASSPATH

    Problem

    Hello,
    Can I use UCanAccess in a Java application that uses hsqldb (latest version) as an embedded db?

    The hsqldb.jar (latest version) seems to be in conflict with the hsqldb.jar (older version) from the UCanAccess distribution.

    For the embedded db, I plan to add hsqldb.jar (latest version) to the CLASSPATH. This seems to cause problems for UCanAccess.

    Thanks for any comments on this.
    Carl

    Test

    As a test, after removing hsqldb.jar (latest version) from the CLASSPATH, I have successfully used UCanAccess according to the 2 ways described here:

    https://stackoverflow.com/questions/34464565/ucanaccess-home-system-variable-not-set/34469789#34469789

    In this example I just added the ucanload.jar to the CLASSPATH and set a Java system property named UCANACCESS_HOME.

    It seems to work fine regardless of setting the -Dproperty=value or not ( maybe this is relevant for my question ).
    In "Eclipse (GUI) / Run Configurations / (x)= Arguments / VM arguments:" I put the following:
    -DUCANACCESS_HOME=C:\xyz\Carl\workspace\myProject2\UCanAccess-4.0.2-bin

    When hsqldb.jar (latest version) is added to the CLASSPATH (for the embedded db to work) UCanAccess seems to have a problem.

    Please find below a source code example and the output from a successful and a failed execution depending on what is added to the CLASSPATH.

    The database file is attached. In this example the database file is stored in the folder:
    C:\xyz\Carl\db1carlsTest.accdb

    Source code

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class Drive {
    public Drive() {
        // display new properties
        System.getProperties().list(System.out);
        String url = "jdbc:ucanaccess://C:/xyz/Carl/db1carlsTest.accdb";
        Connection con;
        Statement stmt;
        String query = "SELECT TOP 10 T2.id, T1.M_NB, T1.M_FL_DATE, T1.M_FL_UNIT, T2.MAX_DATE, T2.MAX_UNIT FROM TBL_KEY_COLS T1 INNER JOIN TBL_KEY_COLS_II T2 ON T1.M_NB = T2.M_NB;";
        try {
            //Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");    //When having five .jar files (from UCanAccess) in the CLASSPATH.
            Class.forName("net.ucanaccess.jdbc.UcanloadDriver");    //When having only ucanload.jar (from UCanAccess) in the CLASSPATH.
            con = DriverManager.getConnection(url, "", "");
            stmt = con.createStatement();
            DatabaseMetaData dbmd = con.getMetaData();
            ResultSet rs = stmt.executeQuery(query);
            ResultSetMetaData rsmd = rs.getMetaData();
            String keysCSV = ",";

            // Loop over columns and collect primary keys. BELOW
            for ( int column = 1; column <= rsmd.getColumnCount(); column++ ) {
                String catalog = rsmd.getCatalogName(column);
                String schema = rsmd.getSchemaName(column);
                String table = rsmd.getTableName(column);
                ResultSet rsKey = dbmd.getPrimaryKeys(catalog, schema, table);

                    // Loop over primary keys related to a table identified by a column from the SQL resultset. BELOW
                    while (rsKey.next()) {
                        if ( !(keysCSV.indexOf(","+rsKey.getString("COLUMN_NAME")+",") > -1) ) {
                            keysCSV = keysCSV + rsKey.getString("COLUMN_NAME") + ",";
                        }
                    }
                    // Loop over primary keys related to a table identified by a column from the SQL resultset. ABOVE

                rsKey.close();
            }
            // Loop over columns and collect primary keys. ABOVE

            System.out.println("");
            System.out.println("Key columns found: " + keysCSV);
            System.out.println("");
            System.out.println("\n\nPrint below from the resultset from MS Access");
            while (rs.next()) {
                String id = rs.getString("id");
                String nb = rs.getString("M_NB");
                String flDate = rs.getString("M_FL_DATE");
                String flUnit = rs.getString("M_FL_UNIT");
                String maxDate = rs.getString("MAX_DATE");
                String maxUnit = rs.getString("MAX_UNIT");
                System.out.println(id + "\t" + nb + "\t" + flDate + "\t" + flUnit + "\t" + maxDate + "\t" + maxUnit);
            }
            rs.close();
            stmt.close();
            con.close();
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            ex.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new Drive();
    }
}

    Successful execution when only ucanload.jar in the CLASSPATH

    -- listing properties -- (filtered by Carl)
    .
    .
    .
    UCANACCESS_HOME=C:\xyz\Carl\workspace\myP...
    java.library.path=C:\Program Files\Java\jre1.8.0_131\bi...
    .
    .
    .

    Key columns found: ,ID,M_NB,M_FL_DATE,M_FL_UNIT,

    Print below from the resultset from MS Access
    1 3 1901-01-01 00:00:00.000000 lll 1901-01-01 00:00:00.000000 lll
    2 3347385 2011-02-16 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2011-03-16 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2011-08-16 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2011-09-15 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2011-11-16 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2011-12-15 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2012-01-17 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2012-05-16 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR
    2 3347385 2012-11-15 00:00:00.000000 EUR 2017-06-15 00:00:00.000000 EUR

    Failed execution when hsqldb.jar (latest version) is added to the CLASSPATH

    -- listing properties -- (filtered by Carl)
    .
    .
    .
    UCANACCESS_HOME=C:\xyz\Carl\workspace\myP...
    java.library.path=C:\Program Files\Java\jre1.8.0_131\bi...
    .
    .
    .
    WARNING:Function already added: CREATE FUNCTION SLN(par0 DOUBLE,par1 DOUBLE,par2 DOUBLE) RETURNS DOUBLE LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.Functions.sln'
    WARNING:Function already added: CREATE FUNCTION STRING(par0 INTEGER,par1 LONGVARCHAR) RETURNS LONGVARCHAR LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.Functions.string'
    WARNING:Function already added: CREATE FUNCTION PPMT(par0 DOUBLE,par1 INTEGER,par2 INTEGER,par3 DOUBLE,par4 DOUBLE,par5 DOUBLE) RETURNS DOUBLE LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.Functions.ppmt'
    WARNING:Function already added: CREATE FUNCTION formulaToNumeric(par0 TIMESTAMP,par1 LONGVARCHAR) RETURNS DOUBLE LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.Functions.formulaToNumeric'
    WARNING:Function already added: CREATE FUNCTION DATEPARTWA(par0 LONGVARCHAR,par1 LONGVARCHAR,par2 INTEGER,par3 INTEGER) RETURNS INTEGER LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.Functions.datePart'
    WARNING:Function already added: CREATE FUNCTION EQUALS(par0 OBJECT,par1 OBJECT) RETURNS BOOLEAN LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.Functions.equals'
    .
    .
    .

    The listing of warnings goes on...

    .
    .
    .

    SQLException: UCAExc:::4.x.x java.lang.ArrayIndexOutOfBoundsException: 1
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.x.x java.lang.ArrayIndexOutOfBoundsException: 1
    at net.ucanaccess.jdbc.UcanaccessDatabaseMetadata.getPrimaryKeys(UcanaccessDatabaseMetadata.java:854)
    at myProject2.Drive.<init>(Drive.java:92)
    at myProject2.Drive.main(Drive.java:139)
Caused by: java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 1
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessDatabaseMetadata.executeQuery(UcanaccessDatabaseMetadata.java:175)
    at net.ucanaccess.jdbc.UcanaccessDatabaseMetadata.getPrimaryKeys(UcanaccessDatabaseMetadata.java:852)
    ... 2 more
Caused by: org.hsqldb.HsqlException: java.lang.ArrayIndexOutOfBoundsException: 1
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 6 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: 1
    at org.hsqldb.persist.RowStoreAVL.searchCost(Unknown Source)
    at org.hsqldb.RangeVariableResolver.searchCost(Unknown Source)
    at org.hsqldb.RangeVariableResolver.reorderRanges(Unknown Source)
    at org.hsqldb.RangeVariableResolver.reorder(Unknown Source)
    at org.hsqldb.RangeVariableResolver.processConditions(Unknown Source)
    at org.hsqldb.QuerySpecification.setRangeVariableConditions(Unknown Source)
    at org.hsqldb.QuerySpecification.resolveTypesPartThree(Unknown Source)
    at org.hsqldb.QueryExpression.resolve(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    ... 8 more
     

    Last edit: Carl Kjellberg 2017-06-15
    db1carlsTest.zip

  • Gord Thompson

    Gord Thompson - 2017-06-15

    I can reproduce the issue using my own test database. It seems to be directly related to the .getPrimaryKeys call. With both ucanload.jar and hsqldb-2.4.0.jar on my Eclipse build path I can successfully do a normal SELECT on a table in the Access database (once I get past all of the WARNING messages), but when I try to do

    ResultSet rsKey = ucaConn.getMetaData().getPrimaryKeys(null, null, "airports");

     
    I get

    net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.x.x java.lang.ArrayIndexOutOfBoundsException: 1
    at net.ucanaccess.jdbc.UcanaccessDatabaseMetadata.getPrimaryKeys(UcanaccessDatabaseMetadata.java:854)
    at com.example.ucanaccessdemo.UCanAccessDemoMain.main(UCanAccessDemoMain.java:48)

     

  • Carl Kjellberg

    Carl Kjellberg - 2017-06-15

    Hi Gord,
    Thank you for the feedback.
    Do you know if there are any plans to use the latest version of hsqldb.jar for UCanAccess?

    I guess that would solve my problem and make UCanAccess even more useful.

    However, a suggestion on a workarround is also welcome.

    Best Regards
    Carl

     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.