R: Getting the value from nextval()
Rocco Ventura [Kunvi]
r.ventura at kunvi.it
Sun Jan 28 06:42:00 EST 2018
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 <PRIMARY-KEY>
; ORACLE / POSTGRES
; ==============
UPDATE COUNTER_TABLE SET COUNTER= COUNTER +1
WHERE <PRIMARY-KEY>
RETURNING COUNTER
_________________________________
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: domenica 28 gennaio 2018 10:40
A: OmnisDev List - English <omnisdev-en at 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 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
---
Questa e-mail è stata controllata per individuare virus con Avast antivirus.
https://www.avast.com/antivirus
More information about the omnisdev-en
mailing list