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

[M.-A. Lemburg]

Michael Rothwell wrote:
> I'm trying to achieve the following:
> 
> * INSERT a blank record into a table.
> 
> * update a result set with that inserted row's data (which may have 
> default values and identity fields filled in, now, by the database)
> 
> * edit the result set
> 
> * UPDATE the row with the new data
> 
> 
> ... the trick is selecting the newly inserted row. Does anyone know if 
> there is a way to do this without resorting to a database- and 
> table-specific method (such as using identity fields and slecting 
> @@IDENTITY)?

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

Another method is using database cursors for this task. Note that
mxODBC does not support updateable result sets; however you can
achieve the same effect with server side cursors. See you DBs docs
for details.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/