R: Getting the value from nextval()

Rocco Ventura [Kunvi] r.ventura at kunvi.it
Mon Jan 29 06:24:55 EST 2018


Thanks Doug,

That's your bag 😉

Bye

Rocco 

_________________________________
Distinti saluti. Best regards.

Rocco Dr. Ventura
WMS Analist and Software Developer

KUNVI srl - Experiences Holding - www.kunvi.it
Email: r.ventura at kunvi.it - Mobile: +39 348 5186051

-----Messaggio originale-----
Da: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] Per conto di Mirko Pepa
Inviato: lunedì 29 gennaio 2018 11:45
A: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
Oggetto: Re: Getting the value from nextval()

Thank You all for the code.
I will implement that!!

Best regards

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 12:58 schrieb Doug Easterbrook <doug at artsman.com>:
> 
> 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=2908
> 6 
> <https://tickets.proctors.org/TheatreManager/95/online?performance=290
> 86>
> 
>> 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
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com



---
Questa e-mail è stata controllata per individuare virus con Avast antivirus.
https://www.avast.com/antivirus




More information about the omnisdev-en mailing list