[Gambas-user] Surrogate keys and database insertions

Bruce Bruen bbruen at ...2308...
Sun Sep 25 05:44:19 CEST 2011


(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



More information about the User mailing list