Feature #69
allow accessing ID of inserted row
| Status : | Assigned | Start : | 05/12/2008 | |
| Priority : | Normal | Due date : | ||
| Assigned to : | - | % Done : | 0% |
|
| Category : | - | |||
| Target version : | - | |||
| Resolution : |
Description
The Python DB API (PEP 249) allows accessing the ID of the last modified row as the 'lastrowid' attribute of cursor objects. This is useful as you can then refer to inserted rows by ID without having to do a query to work out the ID of the new object or generating IDs manually. The attribute is allowed to be None if the database does not support it.
Perhaps we could have an getLastRowID :: IConnection conn => conn -> IO (Maybe Integer). I think that ideally, execute etc. would return IO (Integer, Maybe Integer) but that would break API.
History
05/12/2008 09:42 PM - John Goerzen
- Status changed from New to Assigned
Interesting. I had not known this was a common part of database APIs. I see that PostgreSQL has a PQoidValue, which I think -- but am not certain -- is what you're after. I see this for Sqlite: http://www.sqlite.org/c3ref/last_insert_rowid.html
I'm not sure how to get this with ODBC, but it seems reasonable. Do you have any knowledge of how this works with the database backends that you could share? Particularly, it looks like this could always be represented as an Integer (if it exists)? Do you know of any standards documents on this?
05/14/2008 11:44 AM - Dafydd Harries
I don't know of any standards documents.
PySqlite seems to use the sqlite3_last_insert_rowid() function you found:
http://oss.itsystementwicklung.de/trac/pysqlite/browser/src/cursor.c
MySQL has a similar function, mysql_insert_id():
http://dev.mysql.com/doc/refman/6.0/en/mysql-insert-id.html
PQoidValue looks very much like it does the same thing.
One difference between Sqlite/PostgreSQL and MySQL is that in Sqlite/PostgreSQL all rows have an implicit oid column, whereas (as far as I know), the ID column has to be explicitly declared as PRIMARY KEY and AUTOINCREMENT. I'm not sure if this affects the API.
05/19/2008 11:08 AM - Dafydd Harries
I've had a stab at implementing this. I've only made it work for the Sqlite3 backend.
http://rhydd.org/~daf/git/hdbc.git/
http://rhydd.org/~daf/git/hdbc-sqlite3.git/
There's one slightly weird bit: the SQLite API returns 0 if there were no qualifying inserts, but 0 is also a valid ID. If you just use Sqlite's OIDs, then the first one it generates is 1 (though you can insert a 0 oid manually). In practice, I think you can always tell from context whether getLastInsertedRow is going to return you a useful value (you made an insert that didn't fail).
sqlite> create table foo (x text, y integer);
sqlite> insert into foo values ('hi', 3);
sqlite> select oid from foo;
1
sqlite> insert into foo (oid, x, y) values (0, 'oops', 4);