SmartList and sequence or serial primary keys

[Doug Easterbrook]

just to offer a twist on how Bas does it in postgres -- we didn't know about the $excludefrominsert

so we do it a different way in the table class.     we have a method (or methods) that return fields we don't want to actually insert -- like the unique key field.


Calculate Field as $cinst.$getUniqueKeyField


then we replace the   @[$cinst. field ]    in the insert values with  'DEFAULT'    eg

Do tStringFields.$replace(pStatement,con('@[$cinst.',Field,']'),'DEFAULT',pStatement)



this tells postgres to take the default value that the database suggests when inserting, rather than the value in the smart list.



This can be extended to a lot of things.  For example, we have timestamps on each of our tables for date entered and date updated.     So we make the default in the database for those kinds of fields to be now().

and then we replace a   @$cinst.XX_DATE_ENTERED  in the SQL statement with   'DEFAULT'

advantage of this approach is that the date entered field gets replaced server time (not local omnis workstation time -- which is generally off set in some fashion from the server).

With this same approach you can set the default value of fields to be functions if you wish...


so, a statement that omnis generates in the $insert of a table class might go from

insert into   table (key_field,date_field,funny_field,...) values (@[$cinst.key_field], @[$cinst.date_field], @[$cinst.funny_field], ......)


to

insert into   table (key_field,date_field,funny_field,...) values ('DEFAULT', 'DEFAULT', someFunction(), ......) returning key_field,date_field,funny_field...


I find this approach incredibly helpful for synchronizing the date/time on log records across multiple machines --  its all about when the sql gets to the database and the order in which it is processed -- rather than what the time is on a machine.



but like Bas suggests ...  skip omnissql -- and go to postgres and you won't regret al the workarounds you have to build for omnissql





Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 536-1205    Fax (403) 536-1210

> On Mar 12, 2015, at 9:23 PM, Bastiaan Olij <bastiaan at basenlily.me> wrote:
> 
> Hi Xavier,
> 
> I don't believe OmnisSQL allows you to retrieve the generated sequence
> number after you insert so your only option is to determine this
> yourself before inserting the record.
> 
> If you're going to move to Postgres I'd make the move sooner rather then
> later so you can make use of Postgres' specific code.
> 
> With Postgres simply change your insert query to:
> insert into FTEXTOS (TX_TIPO, TXSUBTA, TX_SUBTN, TX_DESCRIP) value
> (....) returning TX_SEQ
> 
> You can then fetch the new TX_SEQ value.
> Note that if you do a:
> calculate $cinst.$cols.TX_SEQ.$excludefrominsert as kTrue
> in the $construct of your table class to make sure the column isn't
> inserted (else the sequence logic in Postgres doesn't work)
> 
> Then you override your $insert like so:
> ----
> Calculate lvSQL as con('insert into ',$cinst.$servertablenames,'
> ',$cinst.$insertnames(),' returning TX_SEQ')
> if $cinst.$statementobject.$execdirect(lvSQL)
>  do $cinst.$statementobject.$fetch(lvList)
>  calculate $cinst.TX_SEQ as lvList.1.TX_SEQ
>  quit method kTrue
> else
>  do $cinst.$sqlerror(ktableInsertError,
> $cinst.$statementobject.$errorcode, $cinst.$statementobject.$errortext)
>  quit method kfalse
> end if
> ----
> 
> Add a little bit more source to find out the primary key by looking at
> the schema class and you can move all this code in your table base class.
> 
> Cheers,
> 
> Bas
> 
> On 12/03/2015 9:03 pm, IT wrote:
>> Hi,
>> 
>> This is based in OmnisSQL but I will move to PostgreSQL as soon as possible
>> 
>> I have an squema with some columns (TX_SEQ, TX_TIPO, TX_SUBTA, TX_SUBTN, TX_DESCRIP).
>> 
>> TX_SEQ is primary key, omnis sequence.  As soon I move to Postgresql i will use a serial field server side generated.
>> 
>> 
>> Do $clib.$tables.tSC.$sqlclassname.$assign('sFTEXTOS')
>> Do ivList.$definefromsqlclass('tSC')
>> Do ivList.$smartlist.$assign(kTrue)
>> Enter data
>> If flag true
>> 	Do ivList.$dowork()
>> Else
>> 	Do ivList.$clear()
>> End If
>> Do $cwind.$redraw()
>> 
>> I get all rows inserted in the database, but ivList has TX_SEQ as NULLs.
>> A  subsecuent edit of the ivList fails because the primary keys are NULLs and the smart list is unable to update the rows unless you reread the rows after inserting to recover the TX_SEQ.
>> 
>> Something similar happens with a row if you do an ivRow.$insert(). You don't get back the TX_SEQ value.
>> 
>> 
>> Is $dowork() not so useful as I thought and I will need to loop the list and insert rows one by one, recovering the primary key manually and updating it's value in the ivList ?
>> Am I missing something ?
>> 
>> 
>> regards
>> 
>> 
>> x_____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> 
>> 
> 
> 
> --
> Kindest Regards,
> 
> Bastiaan Olij
> e-mail: bastiaan at basenlily.me
> web: http://www.basenlily.me
> Skype: Mux213
> http://www.linkedin.com/in/bastiaanolij
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com