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

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