O$: SQLite, Table.$dowork() and sequence numbers

[Paul Mulroney]

Hi $All

We're investigating using SQLite for a demo version of our software.  We use sequence numbers which are automatically generated for unique record IDs. Currently we use Postgres, and in our $dowork() method of our Table superclass we have the following to generate the new sequence number for us automatically:

$dowork:
> Do $cinst.$sendall($cinst.$setNewIdent(),$ref.$status=kRowInserted)
> Do default Returns vbOK
> Quit method vbOK

and the $setNewIdent method:
> Set reference voStatement to $cinst.$statementobject
> Begin statement
> Sta: {SELECT nextval('[$cinst.$servertablenames]_[isPrimaryKeyName]_seq')}     ;; Postgres - eg SELECT nextval('fContract_seq_seq')
> End statement
> Get statement vsQuery
> 
> Do voStatement.$prepare() Returns vbOK
> If vbOK
> 	Do voStatement.$execute()
> 	Do voStatement.$fetchinto($cinst.[isPrimaryKeyName])     ;; If this is a row or a line in a list, return into this column
> Quit method vbOK

In SQLite, that doesn't exist.  Instead there's a RowID property, which is the equivalent of the sequence number.  The problem is, this is automatically generated AFTER the record is created, so we can't use the pre-emptive method that we use in Postgres.

My question is this:  is there a way to achieve the same in SQLite?    Is there a way we could add a "returning rowid" to the insert SQL command and then read that back into the table row/list instance?

Regards,
Paul.

"Debugging is like being the detective in a crime movie where you're also the murderer."
-- 
Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au       Trading as Logical Developments
www.logicaldevelopments.com.au                   ACN 161 009 374 
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102