Can't insert into database

[vewert]

I've created an internally used tool that reads some records from a csv file, and then inserts them into an access database. This was working before, but when I switched to using the forked version of ucanaccess (5.1.1), the data is no longer inserted. I don't see any errors, but afterwards, when I open the file in Access, the data isn't there.

This is the code I'm running:

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

try (Connection connection = DriverManager.getConnection(connectionString);
     PreparedStatement preparedStatement = connection.prepareStatement(SQL_STATEMENT)) {
  List<Transaction> transactionList = InputFileParser.parseInputFile(this.importFilePath);
  for (Transaction transaction : transactionList) {
    preparedStatement.setInt(1, transaction.getFamilyId());
    preparedStatement.setNull(2, Types.INTEGER);
    preparedStatement.setDate(3, Date.valueOf(transaction.getDonationDate()));
    preparedStatement.setDate(4, Date.valueOf(transaction.getDonationDate()));
    preparedStatement.setString(5, Transaction.DONATION_TYPE);
    preparedStatement.setBigDecimal(6, transaction.getAmount());
    preparedStatement.setString(7, Transaction.DESIGNATION);

    final int rows = preparedStatement.executeUpdate();
    connection.commit();
    log.info("Updated: {} row(s)", rows);
    importResult.incrementCount(rows);
    importResult.incrementTotalAmount(transaction.getAmount());

    this.updateMessage("Inserted: %s\n".formatted(transaction.toString()));
    log.info("Inserted: {}", transaction);
    Thread.sleep(100);
  }
} catch (final SQLException sqlException) {
  log.warn("Error inserting transactions to database.", sqlException);
  throw sqlException;
}

The connection string is: "jdbc:ucanaccess://V:\DevProj\psaft-importer\src\test\resources\database\test\test.accdb"

The SQL statement used in the prepared statement is:

INSERT INTO `Donation Detail` (`Donor_ID`, `Batch`,  `Donation Date`, `Deposit Date`, `Donation Type`, `Amount`, `Designation`) VALUES (?, ?, ?, ?, ?, ?, ?)

If I switch back to the 'old' ucanaccess (5.0.1) from net.sf.ucanaccess, the inserts work.

In case it helps, I'm using Java 22, and the UI for the tool is JavaFX (also version 22)

Any help would be appreciated.

[spannm]

Hi Victor (@vewert),

thanks for posting this issue and sorry for the late reply.

Could you share your database here? an MRE will do, it should contain the Donation Detail table and related db objects (if any), and a few sample records. I will then take a closer look.

Have a great week-end! Markus

[spannm]

I cannot reproduce your issue.

I am happy to try with your test database.

Have you checked for exceptions apart from SQLException?

Unrelated to the issue but I would recommend against using spaces in database object names and avoid possibly reserved words such as Amount and Batch.

[spannm]

pinging @vewert ...

[vewert]

Sorry, life is very crazy right now, and I haven't had time to get back into this. I might have time this week, but not sure.

[vewert]

Thanks for looking into this.

I have attached a stripped down version of database (removed most data, and all queries, forms and reports): database.zip

I tried using the following test code with this database; this code does a simple insert (no prepared statements, no data objects):

@Test
void basicInsertTest() throws Exception {
  try {
    final Path databaseFilePath = TestUtils.resourceToPath("database/test/test.accdb"); // Path to the database file
    final String connectionString = "jdbc:ucanaccess://%s".formatted(databaseFilePath.toString());
    System.out.printf("Connection String: %s\n", connectionString);
    final Connection connection = DriverManager.getConnection(connectionString);

    final String sqlString = "INSERT INTO `Donation Detail` " +
        "(`Donor_ID`, `Batch`,  `Donation Date`, `Deposit Date`, `Donation Type`, `Amount`, `Designation`) VALUES " +
        "(372, NULL, '2024-01-01', '2024-01-01', 'PAP', 500.00, '5010 - Direct Payment')";
    System.out.printf("SQL Statement: %s\n", sqlString);

    final Statement statement = connection.createStatement();
    statement.executeUpdate(sqlString);
  } catch (Exception e) {
    System.err.println(e);
    throw e;
  }
}

Running this code with "old" ucanaccess (5.0.1) works, but with the forked version (5.1.1) it doesn't work, i.e. after opening the database after running the code, the record is not there. Also, I don't see any Exceptions being thrown.

I agree that there a some non-ideal table and column names, but I inherited this database, and it has a lot of queries, forms and reports, and I don't want to risk breaking anything, by changing any names.

Thanks again for your help and any input would be appreciated.

[spannm]

Hi @vewert, Still unable to reproduce using your provided database ... you're welcome to check out my unit tests for issue #16

EDIT: Commit edited and force pushed to remove personal info from test database.

[vewert]

Very Strange. Thanks for trying it out. When I get a chance, I'll download your code and see if I can reproduce it.

btw what Java version are you using?

[spannm]

btw what Java version are you using?

The library is tested and built on Windows and Ubuntu with Java 11, 17, 21 each, using several Github workflows. It requires Java 11 or later:

    <project.build.systemJdk>11</project.build.systemJdk>
    <project.build.targetJdk>11</project.build.targetJdk>

Test GithubIssue16Test uses the database file you sent in and performs your insert, while GithubIssue16VersionTest is a parametrized test using blank MS Access database files of all versions listed in enum net.ucanaccess.type.AccessVersion.

[spannm]

The driver requires at least Java 11. If run with a JRE < 11, java.sql.DriverManager would find the driver using the ServiceLoader mechanism but encounter an UnsupportedClassVersionError attempting to load it. This error is silenty swallowed by the JDK (by design). A subsequent call to DriverManager.getConnection will throw "java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://(path)".

Looks that you are using at least Java 15 (noticed the usage of String.formatted api).

I am unable to reproduce the issue. The sample record gets inserted as expected.

Wished I knew how I could help you further. Please take another look at your end.

[vewert]

I cloned and ran your unit test and it passed. Where can I find the actual database file, after the unit test is done? The reason I ask, is because in one of my unit tests, after inserting the data, the unit test does a SELECT to check the data. That was passing, but when I actually opened the database file in Access the record wasn't there.

[spannm]

Test GithubIssue16Test also performs an SQL Select in line 39+40 following the Insert:

            checkQuery("SELECT * FROM " + tbl + " WHERE Donor_ID=1",
                recs(rec(1, null, sqlDt, sqlDt, "donation_type", amt, "designation", null)));

The test database is copied from the project to a sub directory test-ucanaccess-(loggedOnUserId) of system temp at start of test execution. Please note that in this project temporary files are deleted at JVM shutdown using calls to File#deleteOnExit in UcanaccessBaseTest. To keep the test resources you would have to comment out such lines.

I noted the TestUtils.resourceToPath("database/test/test.accdb") in your test code. I don't have this method and don't know how its implementation. Just checking whether it copies the database and leaves the original unmodified?

[spannm]

Hello @vewert, can you shed more light on this or is it time to close this issue?

[vewert]

My life is really crazy right now, and I don't have any time to look at it, so go ahead and close for now. Hopefully, things will calm down eventually, and I will attempt to look at this again.

Thanks for your patience and help.

[spannm]

I hope that things will quiet down for you in a positive way.

I'll keep this issue open for some longer.