SmartList and sequence or serial primary keys

Bastiaan Olij bastiaan at basenlily.me
Thu Mar 12 23:23:09 EDT 2015


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




More information about the omnisdev-en mailing list