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

[Paul Mulroney]

Hi Dan,

Thanks for your email.  That will work for Studio versions > 10.0.  In earlier versions, the version of SQLite doesn't support the Returning clause.  In older versions of Omnis, the $session.$lastrowid has the value that we want to use.

The other aspect is how we get that record id back into the row/list that is defined by the table object.  I think it's a matter of overriding the right $dowork(), $doinsert(), $insert() methods, but I'm still figuring that bit out.  This is what I have so far:

The $insert() method in the table class is something like this:

> if Postgres
>    do $setNewIdent_Postgres() returns vnRecordID		;; calls Postgres nextval() to get our record id
>    calculate $cinst.[isPrimaryKeyName] as vnRecordID		;; set the the primary key prior to insert
> end if
> do default
> if SQLite
>    do $setNewIdent_SQLite() returns vnRecordID			;; reads $session.$lastrowid to get our record id
>    calculate $cinst.[isPrimaryKeyName] as vnRecordID   	;; get the primary key that was used to isnert
> end if

The $dowork() method in the table class is something like this:

> if Postgres
>    	Do $cinst.$sendall($cinst.$setNewIdent(),$ref.$status=kRowInserted)     
> end if
> do default

The $doinsert() method in the table class is something like this:

> do default
> if SQLite
>    calculate vnRecordID as voSession.$lastrowid
>    calculate $cinst.[pnline].[isPrimaryKeyName] as vnRecordID   	;; get the primary key that was used to insert.
> end if

I'm wondering if anyone else is using SQLite and table classes to do their work?

Regards,
Paul.


> On 26 Jul 2022, at 12:10 pm, dlr at futurechalk.com wrote:
> 
> 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 
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 


Say what you want about deaf people...
-- 
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