Getting the value from nextval()

[Doug Easterbrook]

hi Mirko.

for years we have changed out $insert in the table vase to use the returning clause to get the default value.  its about 15% faster than any other method of setting keys.  we did a huge timing on it for large record sets.


anyway, as rocco says…    use returning.

change $insert in the table class to do three things
1) change your key feild from a bind far to DEFAULT.

eg,  if the statement looks like    insert into table (key) value (@[key])

replace @[key] with   DEFAULT   so that postgres lets the default value for that field work (which you wold set to nextval()


2)   append     returning KEY to the  end of the statement… eg


insert into table (key) value (@[key]) returning key

you can return more than one field … eg


insert into table (key) value (DEFAULT) returning key, field1,field2, etc  (where those are in the table)



3) in the $insert method of the table class..

after you send the insert statement to postgres
issue a fetch.

eg
fet chin to returnlist

you get values back for the returning clause

stick them into  $cinst

eg
calc $cinst.key as  returnList.key


(there are elegant ways to make this generic like assignrow  …   but thats up to you).


works very very very wall.


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978




see you at the third annual users conference
https://tickets.proctors.org/TheatreManager/95/online?performance=29086 <https://tickets.proctors.org/TheatreManager/95/online?performance=29086>

> On Jan 28, 2018, at 2:40 AM, Mirko Pepa <mirko.pepa at profile.ch> wrote:
> 
> Hello Das
> 
>> If it can only be done with insertion, how do we for sure fetch that just inserted row back so we can see the number assigned?
> I have a field in every table, where I put a random number in it (at insertion), an right after insertion I search that record.
> like this:
> 
> ;; iZufall is 'Random'
> Calculate iZufall as randintrng(1,1000000)
> Calculate XXISQ as con(mid($cinst.$cols.1.$name,1,3),'ISQ') ;; Field name for the random field is XX_ISQ
> Calculate $cinst.[XXISQ] as iZufall
> Do $cinst.$insert() Returns OK
> If not(OK)
> Quit method 1
> End If
> Calculate Select as con('WHERE ',XXISQ,'=@[iZufall]')
> Do $cinst.$select(Select)
> Do $cinst.$fetch(1)
> 
> Another method would be:
> 
> Do $cinst.$insert() Returns OK
> If not(OK)
> Quit method 1
> End If
> Do $cinst.$sessionobject().$newstatement Returns Stat
> Do Stat.$execdirect(con("SELECT currval(pg_get_serial_sequence('",low(iServerTableName),"','",low(XXSEQ),"'));")) Returns #F     ;; @OKSQL
> If flag false
> Do tSQLSession.$statementerror(Stat)
> End If
> Do Stat.$fetch(Row,1)
> Calculate Index as Row.C1
> Calculate Select as con('WHERE "',low(XXSEQ),'" = ',Index)
> Do $cinst.$select(Select)
> Do $cinst.$fetch(1)
> But You have to make sure, that it is in the same transaction. Otherwise some other insert could fire inbetween.
> 
> And as a third method (and probably the best):
> Insert in Postgres can give back some values
> https://www.postgresql.org/docs/9.6/static/sql-insert.html <https://www.postgresql.org/docs/9.6/static/sql-insert.html>
> Insert into blablabla returning field
> With a statement You possibly could insert and then fetch the resulting new Primary Key. But I never did it. If You manage this, I would be *very* interested in the code.
> 
> Greetings
> 
> Mirko
> ---------------------------------------------------------------------------------------------------
> Mirko Pepa, dipl. Wirtschaftsinformatiker
> Profile GmbH, Schaffhauserstr. 103, 8400 Winterthur
> Tel. 052-213 63 33 | www.profile.ch | www.helper.ch
> ---------------------------------------------------------------------------------------------------
> 
>> Am 28.01.18 um 06:17 schrieb Das Goravani <das at Goravani.com>:
>> 
>> 
>> One can issue a SELECT nextval(seq_field)
>> 
>> But how does one read the result in Omnis?
>> 
>> I am aware you can use nextval(seq_field) as a value in an INSERT INTO statement but I’m wondering if we can get the value at the beginning of data entry instead of after it’s inserted
>> 
>> If it can only be done with insertion, how do we for sure fetch that just inserted row back so we can see the number assigned?
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com