[egenix-users] how to insert new row, update in-memory object with same row from database?

[Michael Rothwell]

M.-A. Lemburg wrote:
> The best way to do this in a database independent way is to add
> an ID column to all tables you are using (+ a timestamp column if
> you want to edit them with MS Access).
> 
> ODBC only allows you to query the table for "special" columns
> which are best suited for e.g. identifying rows (see cursor.
> specialcolumns()).


Thanks. I decided to see how ADO does this, because it seems to work 
there. Upon investigation, ADO's RecordSet.AddNew() + .Update() does not 
update the in-memory object if there is not an identity field in the 
table, although the insert works. If there's a timestamp field (or an 
identity field), then a second .Update() of the new record works. If 
there's not, ADO botches the update and returns this error message:

"Key column information is insufficient or incorrect. Too many rows were 
affected by update."

So it seems that ADO relies on "special columns" too.

It would be nice if a standard part of ODBC (and database APIs in 
general) was a method to get at just-inserted rows. Inside a trigger, 
one can just look at the "inserted" table (on SQL Server, anyway).

I'll look at specialcolumns()...