failed .addRow operations leave "phantom" records visible from ADO/DAO connections

[Gord Thompson]

Okay, this is weird. In Access 2010, create a new database named "jTest.accdb" and then run the following query to create a table

CREATE TABLE Employee (
    Emp_Id TEXT(255) NOT NULL PRIMARY KEY,
    Emp_Name TEXT(255)
    )

 
Close Access 2010. Run the following Java code

import java.io.File;
import com.healthmarketscience.jackcess.*;

public class jackcessTest {

    public static void main(String[] args) {
        String newEmp_Id = "GT001";  // test data
        String newEmp_Name = "Gord";

        boolean insertedOK = false;
        int counter = 1;
        String insertEmp_Id = newEmp_Id;
        try {
            Table table = DatabaseBuilder.open(new File("C:\\__tmp\\jTest\\jTest.accdb")).getTable("Employee");
            while (!insertedOK) {
                try {
                    table.addRow(insertEmp_Id, newEmp_Name);
                    insertedOK = true;
                    System.out.println(String.format("New record inserted as %s", insertEmp_Id));
                } catch (Exception e) {
                    insertEmp_Id = String.format("%s_copy%s", newEmp_Id, counter++);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 
It displays the following

New record inserted as GT001

 
So far, so good. Now run the Java code again. It displays

New record inserted as GT001_copy1

 
Launch Access and open the [Employee] table. All looks normal:

Close Access. Now run the following VBScript

Option Explicit
Dim con, rst
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\__tmp\jTest\jTest.accdb;"
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT Emp_Id, Emp_Name FROM Employee", con
Do Until rst.EOF
    WScript.Echo rst("Emp_Id").Value & ": " & rst("Emp_Name").Value
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing

 
It returns

C:\__tmp\jTest>cscript /nologo adoTest.vbs
GT001: Gord
GT001: Gord
GT001_copy1: Gord

 
Note that 'GT001' is the primary key, so there is no way that it should appear twice.

Run the Java code again.

New record inserted as GT001_copy2

 
Run the VBScript again

C:\__tmp\jTest>cscript /nologo adoTest.vbs
GT001: Gord
GT001: Gord
GT001_copy1: Gord
GT001: Gord
GT001_copy1: Gord
GT001_copy2: Gord

 
However, if we open the database in Access the [Employee] table looks fine:

Now do a "Compact and Repair" on the database in Access. Run the VBScript

C:\__tmp\jTest>cscript /nologo adoTest.vbs
GT001: Gord
GT001_copy1: Gord
GT001_copy2: Gord

 
The "phantom records" have disappeared.

I first noticed this when working with a DAO connection in Access itself, but this VBScript replicates the issue with a completely independent ADO connection.

I am using jackcess-2.0.1.jar under JavaSE-1.7.

[Gord Thompson]

Additional information:

Before performing the Compact and Repair, the "phantom records" are also visible to Jackcess itself if I run through the table directly...

for (Row row : table) {
      System.out.println(String.format("Column 'Emp_Id' has value: %s", row.get("Emp_Id")));
}

 
...which gives me...

Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001_copy1
Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001_copy1
Column 'Emp_Id' has value: GT001_copy2

 
...but not if I cycle through the records using cursor backed by the Primary Key index...

for (Row row : CursorBuilder.createCursor(table.getPrimaryKeyIndex())) {
      System.out.println(String.format("Column 'Emp_Id' has value: %s", row.get("Emp_Id")));
}

 
...yielding

Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001_copy1
Column 'Emp_Id' has value: GT001_copy2

 
If you would like me to create a bug ticket for this just let me know.

[James Ahlborn]

yeah, that would be great if you could file an issue.

basically, jackcess adds the row to the table before updating the indexes. so, if the unique constraint fails, the row will already be in the table (although it won't be in the index). jackcess really needs to pre-check the indexes before writing the row to the table.

[Gord Thompson]

Either that, or perhaps go back and flag the record as "deleted" if something bad happens while updating the indexes.

Anyway, I have created the ticket. Thanks for your reply (and for Jackcess, too!).

[James Ahlborn]

Bug filed here: https://sourceforge.net/p/jackcess/bugs/99/

Will be fixed in the 2.0.2 release.