[Gambas-user] Surrogate keys and database insertions

[Benoît Minisini]

> (This drove me bananas in gambas2.)  Is there now (in gambas 3) a way to
> get the key of the row just inserted into the database when the key is a
> surrogate key e.g. a SQLite "serial"?
> For example, in the following, the "items" table has a surrogate key
> called "id" which is a simple serial.
> 
> Private Sub CreateItem(item As Todoitem)
> 
>   Dim hRslt As Result
> 
>   hRslt = hConn.Create("items")
>   If hRslt.Available Then
>     Marshall(item, hRslt) 'Moves the item data into the result fields
>     hRslt.Update
> 
>     '    So what's the value of the id field???  According to hRslt!id
> it is still blank.
> 
>   Endif
> 
> End
> 
> I've tried several ways to read the table and try to determine which row
> was just added, but there are too many problems like
> a) someone else may have inserted another row between the update and the
> re-read, so I cant just look at the "highest" id
> b) I or someone else may be inserting copies of an existing row, i.e.
> all the non-key fields now exist in more than one row.
> c) In postgresql, it is possible to create a pseudo query function that
> adds the new row and returns the key of it, but
>     1) this is postgresql specific
>     2) it means I have to use hConn.Exec instead of the nice simple
> gambas routines
>     3) it is messy code in the database and the function has to be
> created and maintained for each table
> 
> Surely someone has come up with an answer to this?
> 
> Bruce

This feature is missing, mainly because there is no standard way to do that in 
SQL, and I don't know if there is a function for that in all SQL DBMS.

-- 
Benoît Minisini