Getting the value from nextval()

Doug Easterbrook doug at artsman.com
Mon Jan 29 08:23:22 EST 2018 

smile.

and if you have TM objs in your application for any reason at all .. you might want to look at two static methods.  $prepareInsert and $prepareupdate


they achieve two things:

1) they tell you which fields are different in the smart list.   so if you only updated one column — this makes an insert statement for you with 1 column, not the entire list of vars from the table

this alone, saves traffic to/from the database a substantial amount, especially if you have large binary objects in the table because omnis always set the value and uses it in a where statement as the default behaviour.     this causes it to be ignored  if it didn’t change.     much faster.



2) and because of that we can insert or update against a query class that joins dozens of tables, as long as we only change variables in the MAIN table.
thats a very neat trick   updating against query classes.   and having fields from other joined tables.   I can’t tell you how much that works well for us.



in $insert of the table class

TMOBJS$prepareinsert


eg..
Do TMOBJS.$prepareinsert(InsertString,otherThanStandardField,$cinst.$colcount,"DATE_UPDATED`DATE_ENTERED`EDITNUM`CHANGED_BY_E_SEQ",tempPrefix)

and same in $update

TMObjs.$prepareupdate




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 29, 2018, at 4:24 AM, Rocco Ventura [Kunvi] <r.ventura at kunvi.it> wrote:
> 
> 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 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: lunedì 29 gennaio 2018 11:45
> A: OmnisDev List - English <omnisdev-en at 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 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=2908
>> 6
>> <https://tickets.proctors.org/TheatreManager/95/online?performance=290
>> 86>
>> 
>>> 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
> 
> 
> 
> ---
> Questa e-mail è stata controllata per individuare virus con Avast antivirus.
> https://www.avast.com/antivirus
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com

More information about the omnisdev-en mailing list