"Row is deleted" IllegalStat...

[mike rodent]

Dear anyone,

New to Jackcess and a bit of a pity there isn't more documentation.
I have a table with 2 indices, the PK and another called "parent", index on
"parent" field.
I can delete and add rows, but occasionally following a deletion in particular
I get this "Row is deleted" exception.
This is a typical stack trace (NB I am using Jython rather than Java but that
shouldn't change anything):

Exception in thread "AWT-EventQueue-0" java.lang.IllegalStateException: Row is
deleted: 1892:18
at
com.healthmarketscience.jackcess.Table.requireNonDeletedRow(Table.java:2106)
at com.healthmarketscience.jackcess.Table.getRow(Table.java:618)
at com.healthmarketscience.jackcess.Cursor.getCurrentRow(Cursor.java:908)
at com.healthmarketscience.jackcess.IndexCursor.currentRowMatchesEntryImpl(Ind
exCursor.java:421)
at
com.healthmarketscience.jackcess.IndexCursor.access$700(IndexCursor.java:39)
at com.healthmarketscience.jackcess.IndexCursor$EntryIterator.findNext(IndexCu
rsor.java:574)
at
com.healthmarketscience.jackcess.Cursor$BaseIterator.hasNext(Cursor.java:984)
at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImp
l.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:186)

My suspicion is that the Table is left in a strange state after deletion.
Equally it might be something to do with the state of one or other of the
indices after deletion. For the latter reason, whenever deleting I update the
indices. But this hasn't stopped the (occasional) exceptions. Sometimes (not
always) the table is left in a state where even in Access (Access 2000) the
table can't be opened until I delete the "parent" index, and then recreate
it.

Anyone know what the problem is?

[James Ahlborn]

do you have some code you can share showing how you interact w/ jackcess?

[James Ahlborn]

it's hard to give guidance with out seeing some of your code.

[mike rodent]

Thanks... I've made a bit of progress since but am still wanting more info.
The situation is a very simple one: a table in a dbase has 2 indexes: the
Primary Key (on ID, unique), and an index permitting duplicates on a field
called "parent". When I locate to a given ID, using pkIndexCursor, and then
delete a row, I then update the index from which pkIndexCursor came, namely
pkIndex.
But what is the correct procedure with the other index on the table, namely
parentIndex?
First I tried parentIndex.update()... but this seems to WRITE the current
state to the dbase.
Then (below) I tried parentIndex.initialize(). Is this a way to get the index
in the .mdb file itself to recreate itself taking into account that a row has
now been defeated from the table?
I do find there's a lack of documentation for Jackcess... specially regarding
indexes...

NB the code below is Jython rather than Java... but is very easy to understand

assert frame.pkIndexCursor.findRowByEntry( indexValue ), "no entry found in pkIndexCursor for %s" % indexValue
frame.pkIndexCursor.deleteCurrentRow()
frame.pkIndex.update()
#  frame.parentIndex.update()
frame.parentIndex.initialize()

[mike rodent]

follow up:
still getting the same error when I delete a row like this... i.e.
"initialize" is not the answer...

and I have not found any examples of deleting rows by means of a jackcess
IndexCursor... particularly when there are more than 1 indexes on a given
table...

[James Ahlborn]

the last 3 lines are superfluous. you don't need to do anything to manage the
indexes directly, that is handled internally by the table update logic.

the jackcess api is pretty heavily unit tested, so you can find many examples
for using the api in the unit tests. e.g.
CursorTest.

[James Ahlborn]

without seeing more of your code, the potential issues i can imagine are that
you are using a single Database(and its child objects) across multiple threads
(an individual jackcess Database does not support use by multiple threads) or
that you have 2 separate Table instances which represent the same physical
table and this is causing the Cursors to get out of sync.

of course, i'm assuming you are using the latest version of jackcess.

[mike rodent]

Hi,

Thanks for getting back to me... I see you are one of the main developers.
Hopefully I can put together an short, self-contained code example in due
course, with a pared-down .mdb file.

so "deleteCurrentRow" on an IndexCursor should leave all indexes on a table in
a non-anomalous state, right, good to know.
- The multiple threads idea definitely does not apply. On the other hand
- this access is currently being done from the EDT
- without the "delete" command (i.e. when just navigating) everything works fine
- I was just looking at this table again and in fact it has a dependent table on one field (the ID field, not the parent field)... and this relationship enforces referential integrity (delete & update). This has never been a problem when using a JDBC arrangement and PreparedStatements, but I wonder whether this might be an issue with Jackcess? (I've now deleted this relationship and will do some testing, although I've reverted temporarily to JBDC because of this problem).
- yes, it's the latest version.
- there is only one Table instance, but what about if I have created multiple IndexCursors on the same (parent) index? They are all local variables though, used in a recursive method which is responsible for setting up a JTree (each parent field points to an ID field of another record in the same table). But they are out of scope and (probably) garbage-collected by the time a delete command is given. Again, will test with a different code arrangement where only one IndexCursor exists.

Will get back to you, thanks though.

[James Ahlborn]

Yes, a standalone code example is by far the best way to get this problem
resolved.

To re-confirm, you definitely should not need to do anything manual to the
indexes yourself in order to maintain them, all table operations should to
that internally. there should be no problem with multiple indexes, or multiple
cursors on the same table and/or index. and the cursors all support live table
modification (they aren't invalidated by other table modifications).

as for the foreign key, jackcess does not currently maintain referential
integrity. this should cause any exceptions, it just means that you can modify
info in the primary table and cause it to be out of sync with the dependent
tables. basically, you currently need to maintain those relationships yourself
(although jackcess gives you most of the tools you need, e.g. the Joiner
class).

[James Ahlborn]

Glancing through the code a bit, i did find one scenario which might result in
the problem you are seeing (especially
since you mentioned recursive calls). if you did something like the following
you could run into problems:

Iterator<Map<String, Object="">> iter = cursor.iterator();

while(iter.hasNext()) {
// ... do something here which deletes the current row...
Map<String, Object=""> row = iter.next();
}

since the table modification happens between the hasNext() call and the next()
call, the Iterator code will not pick
up the change to the underlying table/index and therefore does not adjust the
current pointer. however, even if the
iterator did recognize the change, all that would do is change the exception
thrown (you would get a
NoSuchElementException instead of the IllegalStateException). if this is
indeed the problem, the issue isn't so much
a problem in jackcess, but a violation of the Iterator semantics.