Getting the value from nextval()

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@kunvi.it – Mobile: +39 348 5186051

—–Messaggio originale—–
Da: omnisdev-en [mailto:omnisdev-en-bounces@lists.omnis-dev.com] Per conto di Mirko Pepa
Inviato: lunedì 29 gennaio 2018 11:45
A: OmnisDev List – English <omnisdev-en@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@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@artsman.com
> www.artsman.com
> Phone (403) 650-1978
>
>
>
>
> see you at the third annual users conference
> tickets.proctors.org/TheatreManager/95/online?performance=2908
> 6
> <tickets.proctors.org/TheatreManager/95/online?performance=290
> 86>
>
>> On Jan 28, 2018, at 2:40 AM, Mirko Pepa <mirko.pepa@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
>> www.postgresql.org/docs/9.6/static/sql-insert.html
>> <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@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 lists.omnis-dev.com
>>
>> _____________________________________________________________
>> Manage your list subscriptions at lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com

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

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com