spannm / ucanaccess Goto Github PK

UCanAccess>create table zzz (id text(50) primary key, int_col integer null default 0);
UCanAccess>No rows affected

UCanAccess>insert into zzz (id) values ('int_col omitted');
UCanAccess>1 row(s) affected

UCanAccess>select * from zzz;

·-----------------+---------·
| id              | int_col |
·-----------------+---------·
| int_col omitted |       0 |
·-----------------+---------·

UCanAccess>insert into zzz (id, int_col) values ('int_col explicit NULL', NULL);
UCanAccess>1 row(s) affected

UCanAccess>select * from zzz;

·-----------------------+---------·
| id                    | int_col |
·-----------------------+---------·
| int_col explicit NULL |       0 |
| int_col omitted       |       0 |
·-----------------------+---------·

UCanAccess>insert into zzz (id, int_col) values ('int_col some value', 123);
UCanAccess>1 row(s) affected

UCanAccess>select * from zzz;

·-----------------------+---------·
| id                    | int_col |
·-----------------------+---------·
| int_col explicit NULL |       0 |
| int_col omitted       |       0 |
| int_col some value    |     123 |
·-----------------------+---------·

                if (t2 != null && t != null && !tn.startsWith("~")) {
                    
                    TableMetaData tableMetaData = t2.getDatabase().getTableMetaData(tn);
                    if (tableMetaData.getType() == TableMetaData.Type.LINKED_ODBC) {
                        logger.log(Level.WARNING, "Table {0} is linked to an ODBC table. Skipped ...", tn);
                        unresolvedTables.add(tn);
                    }else{
                        createTable(t);
                        loadingOrder.add(t.getName());
                    }
                }
...

[DEBUG] 2024-06-26 18:09:05.803 [pool-2-thread-3] MdbWriter - CreateDb Url:jdbc:ucanaccess:///home/gm/GM4C/Bin/./index.mdb;newDatabaseVersion=V2003,SQL:CREATE TABLE PICINDEX ( IDX INTEGER NOT NULL PRIMARY KEY, POLEID VARCHAR(50), SETLOC VARCHAR(50), KM DOUBLE, ST VARCHAR(50), TUNNEL VARCHAR(50), DETECTDATE VARCHAR(50), MDirection VARCHAR(50), SPEED DOUBLE, CAMERANO VARCHAR(50), PATH VARCHAR(50), POLENUM VARCHAR(50), FRONT DOUBLE, BACK DOUBLE, FILEPRE VARCHAR(50), AllReadState VARCHAR(50), CorrectState VARCHAR(50), OldPoleNum VARCHAR(50), DataState VARCHAR(50));

[DEBUG] 2024-06-26 18:09:05.803 [pool-2-thread-2] MdbWriter - CreateDb Url:jdbc:ucanaccess:///home/gm/GM4C/Bin/./FaultIndex.mdb;newDatabaseVersion=V2003,SQL:CREATE TABLE FaultLevel ( LevelID VARCHAR(50) NOT NULL PRIMARY KEY, LevelName VARCHAR(50), DelFlag VARCHAR(50), Memo VARCHAR(100));

[ERROR] 2024-06-26 18:09:05.846 [pool-2-thread-2] MdbWriter - CreateDb Failed,file:/home/gm/GM4C/Bin/./FaultIndex.mdb,Err:UCAExc:::4.x.x java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: PICINDEX

UCanAccess-5.0.1.bin
    lib
        commons-lang3-3.8.1.jar  
        commons-logging-1.2.jar  
        hsqldb-2.5.0.jar  
        jackcess-3.0.1.jar
    licenses
        apache-license-2.0.txt  
        hsqldb_lic.txt  
        hypersonic_lic.txt  
        lgpl-2.1.txt  
        licenses.txt
    loader
        ucanload.jar
    console.bat  
    console.sh  
    copyright.txt
    ucanaccess-5.0.1.jar  
    version.txt

    d) in your maven project pom.xml, add dependencies to 
        - ucanaccess
	      https://central.sonatype.com/artifact/io.github.spannm/ucanaccess
        - jackcess-encrypt (used by our code)
	      https://mvnrepository.com/artifact/com.healthmarketscience.jackcess/jackcess-encrypt
    e) add maven-shade-plugin to pom.xml
    f) view the dependency tree of the project via maven:
	    > mvn dependency:tree
    g) inspect the dependency tree, and from top to bottom, add the artifacts that need to be included in your pom.xml
    h) download these artifacts if your IDE hasn't automatically done so
    i) inspect each artifact, determine the patterns that include the entire contents of the artifact (other than META-INF), and relocate them to jdbc.ucanaccess.shaded.* in your pom.xml
    j) in your pom.xml, ensure that the project version (line 10) matches the version of ucanaccess that was added as a dependency in step d (line 15). (See FMEENGINE-63096)
    k) package a JAR by running:
        > mvn clean package

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.1.1 Cannot invoke "org.hsqldb.Statement.getTableNamesForRead()" because "<parameter1>.sessionContext.currentStatement" is null
    at net.ucanaccess.jdbc.UcanaccessSQLException.wrap(UcanaccessSQLException.java:123)
    at net.ucanaccess.util.Try.orThrow(Try.java:298)
    at net.ucanaccess.jdbc.UcanaccessStatement.tryCatch(UcanaccessStatement.java:406)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:165)
    at my.namespace.MyTest.TestUCanAccessWithHSQLDBv272(MyTest.java:*)

@Test
public void TestUCanAccessWithHSQLDBv272() throws SQLException, IOException {
    try(var conn = openNewDatabase(); var stmt = conn.createStatement()) {
        // create new table and add some data
        stmt.executeUpdate("CREATE TABLE Tbl (ID INT NOT NULL PRIMARY KEY, Txt VARCHAR(255))");
        stmt.executeUpdate("INSERT INTO Tbl (ID, Txt) VALUES (1, 'One'), (2, 'Two'), (3, 'Tre')");
        
        // query data
        try(var row = stmt.executeQuery("SELECT ID, Txt FROM Tbl ORDER BY ID")) {
            assertTrue(row.next()); assertEquals(1, row.getInt(1)); assertEquals("One", row.getString(2));
            assertTrue(row.next()); assertEquals(2, row.getInt(1)); assertEquals("Two", row.getString(2));
            assertTrue(row.next()); assertEquals(3, row.getInt(1)); assertEquals("Tre", row.getString(2));
        }
        // update data
        stmt.executeUpdate("UPDATE Tbl SET Txt = 'Row ' || Txt");
        try(var row = stmt.executeQuery("SELECT Txt FROM Tbl ORDER BY ID")) {
            assertTrue(row.next()); assertEquals("Row One", row.getString(1));
            assertTrue(row.next()); assertEquals("Row Two", row.getString(1));
            assertTrue(row.next()); assertEquals("Row Tre", row.getString(1));
        }
        
        // remove data
        stmt.executeUpdate("DELETE FROM Tbl");
        try(var row = stmt.executeQuery("SELECT * FROM Tbl")) {
            assertFalse(row.next());    // no data
        }
    }
}

private static Connection openNewDatabase() throws SQLException, IOException {
    var tempDbFile = Path.of(System.getProperty("java.io.tmpdir"), "TestDatabase.accdb");
    var dbFileLocation = tempDbFile.toString().replace('\\', '/');
    
    tempDbFile.toFile().delete();    // delete from previous test run
    
    return DriverManager.getConnection("jdbc:ucanaccess://" + dbFileLocation + ";newdatabaseversion=V2016");
}