Inserting a record does NOT move the cursor

  • Article

Inserting a record into a table doesn't change the location of the JET_TABLEID used to insert the record. At one level this makes sense because a JET_TABLEID is an iterator on a table and inserting a record into a  table shouldn't change the position of its iterators. On the other hand this clearly doesn't make sense because a lot of people are surprised by this behaviour. The basic problem is that the JET_TABLEID is performing two functions. It acts a cursor which keeps track of a location in a table, using the navigation APIs (JetMove, JetSeek). A JET_TABLEIS is also a context for a table update, using the DML APIs (JetPrepareUpdate, JetSetColumn, JetUpdate). Sometimes the functions overlap, but a lot of the time they are surprisingly orthgonal.

Creating a new JET_TABLEID positions it on the first record in the table. This position is 'virtual' -- the first record in the table isn't determined until the cursor is actually used. That means opening an empty table and then inserting a record will leave the JET_TABLEID positioned at the new record (which must be the first one in the table). A second insertion doesn't change the cursor location however, the cursor will still be on the first record. This surprises people because the following code breaks at the second assert:

Call(JetOpenTable(sesid, dbid, "table", null, 0, 0, &tableid));
 
int i;
 
Call(JetBeginTransaction(sesid));
Call(JetPrepareUpdate(sesid, tableid, JET_prepInsert));
i = 10;
Call(JetSetColumn(sesid, tableid, columnid, &i, sizeof(i), 0, NULL));
Call(JetUpdate(sesid, tableid, NULL, 0, NULL));
Call(JetCommitTransaction(sesid, 0));

Call(JetRetrieveColumn(sesid, tableid, columnid, &i, sizeof(i), NULL, 0, NULL));
assert(10 == i); <= the cursor is on the first record, which is the one we just inserted

Call(JetBeginTransaction(sesid));
Call(JetPrepareUpdate(sesid, tableid, JET_prepInsert));
i = 99;
Call(JetSetColumn(sesid, tableid, columnid, &i, sizeof(i), 0, NULL));
Call(JetUpdate(sesid, tableid, NULL, 0, NULL));
Call(JetCommitTransaction(sesid, 0));

Call(JetRetrieveColumn(sesid, tableid, columnid, &i, sizeof(i), NULL, 0, NULL));
assert(99 == i); <= The cursor is still on the first record