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

[dlr at futurechalk.com]

Hello Paul,

Is this what your looking for maybe - link to web page https://www.sqlite.org/lang_returning.html <https://www.sqlite.org/lang_returning.html>

Dan Ridinger

> On Jul 25, 2022, at 8:18 PM, 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:
> 
> $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
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com