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

Martin Obongita martin.obongita at yahoo.com
Tue Jul 26 08:57:41 UTC 2022

 Hi Paul,
Sorry, I don't wish to be rude, but could I interject and ask why you use vbOK variable instead of using the inbuild #F variable to return the status of a flag?
Kind regards, Martin.
    On Tuesday, July 26, 2022 at 06:18:36 AM GMT+3, Paul Mulroney via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:  
 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:

> 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?


"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

Manage your list subscriptions at https://lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 

More information about the omnisdev-en mailing list