#99 failed .addRow operations leave "phantom" records

[Gord Thompson]

I have encountered the following issue with Jackcess 2.0.1:

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)
    )

 
Now run the following:

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 {
            Database database = DatabaseBuilder.open(new File("C:\\__tmp\\jTest\\jTest.accdb"));
            Table table = database.getTable("Employee");

            while (!insertedOK) {
                try {
                    table.addRow(insertEmp_Id, newEmp_Name);
                    System.out.println(String.format("New record inserted as %s", insertEmp_Id));
                    insertedOK = true;
                } catch (Exception e) {
                    insertEmp_Id = String.format("%s_copy%s", newEmp_Id, counter++);
                }
            }
            System.out.println();
            System.out.println("Running through table:");
            for (Row row : table) {
                  System.out.println(String.format("Column 'Emp_Id' has value: %s", row.get("Emp_Id")));
            }
            System.out.println();
            System.out.println("Running through cursor backed by 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")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 
The result is

New record inserted as GT001

Running through table:
Column 'Emp_Id' has value: GT001

Running through cursor backed by primary key index:
Column 'Emp_Id' has value: GT001

 
This is fine. Now run the code again. This time it returns

New record inserted as GT001_copy1

Running through table:
Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001_copy1

Running through cursor backed by primary key index:
Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001_copy1

 
...and a third time...

New record inserted as GT001_copy2

Running through table:
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

Running through cursor backed by primary key index:
Column 'Emp_Id' has value: GT001
Column 'Emp_Id' has value: GT001_copy1
Column 'Emp_Id' has value: GT001_copy2

 
Doing a Compact and Repair on the database makes the "phantom" records go away.

Additional details (and pretty pictures) available here:

https://sourceforge.net/p/jackcess/discussion/456474/thread/c07e8b00/

[James Ahlborn]

fixed, will be in the 2.0.2 release.