Getting the value from nextval()

There is a SQL-clause in Postgres/Oracle called RETURNING, and OUTPUT in SQL-Server…
Here is the complete commands, which works like a SELECT, you obtain the next unique counter

; IN SQL-SERVER
; ===========

UPDATE COUNTER_TABLE SET COUNTER= COUNTER +1
OUTPUT INSERTED. COUNTER
WHERE

; ORACLE / POSTGRES
; ==============
UPDATE COUNTER_TABLE SET COUNTER= COUNTER +1
WHERE
RETURNING COUNTER

_________________________________
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: domenica 28 gennaio 2018 10:40
A: OmnisDev List – English <omnisdev-en@lists.omnis-dev.com>
Oggetto: Re: Getting the value from nextval()

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

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

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