Getting the value from nextval()

[Mirko Pepa]

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=29086 <https://tickets.proctors.org/TheatreManager/95/online?performance=29086>
> 
>> 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