Exclude and DoWork

Doug Easterbrook doug at artsman.com
Fri Mar 10 16:44:09 UTC 2023


Morning Daniel

about ‘$excludefrominsert’, I cannot say for sure.

however, I do use the same approach that you found to change con('@[$cinst.',iPKname,']’  to DEFAULT.    its easier to read.


you can use the same concept to do a lot of weird stuff to your insert or udpate statements.


eg, if you have timestamps like   ‘dateudpated’ you can replace with now() to use server time and also return that in your returning clause.   its a great idea that makes your data independent of the current time on the workstation.

Calculate lString as con('@[$cinst.dateupdated')
Calculate lInsertString as replace(lInsertString,lString,’now()')



you could even replace with calculations or functions, or case statements.

eg

Calculate lString as con('@[$cinst.balance')
Calculate lInsertString as replace(lInsertString,lString,’field1+field2-field3')


or 
Calculate lString as con('@[$cinst.SomeField')
Calculate lInsertString as replace(lInsertString,lString,’ (select xx from a table where ..) ‘)

or

Calculate lString as con('@[$cinst.SomeOtherField')
Calculate lInsertString as replace(lInsertString,lString,’ case when name=“bob” then 1 else 2 end ‘)


they are very contrived examples —  but we have used them to make conditional data inserts or use server functions.






Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Mar 10, 2023, at 5:59 AM, Daniel Sananes <daniel.s at kopparbergs.se> wrote:
> 
> Hi
> 
> I found this about $excludefrominsert when investigating how to make postgres autoincrement my primary key user_id.
> I cannot get that to work. Does it only work if you use the built-in $insert()?
> 
> In my overridden table $insert I have this instead:
>   Calculate lInsertString as $cinst.$insertnames()
>   Calculate lString as con('@[$cinst.',iPKname,']')
>   Calculate lInsertString as replace(lInsertString,lString,'DEFAULT')
> It replaces the primary key value with DEFAULT. This excludes it and makes postgres assign a new number.
> So the string will be:
> ("user_id","user_name","user_password","user_short") VALUES (DEFAULT,@[$cinst.user_name],@[$cinst.user_password])
> Instead of:
> ("user_id","user_name","user_password","user_short") VALUES (@[$cinst.user_id],@[$cinst.user_name],@[$cinst.user_password])
> 
> Regards
> Daniel
> 
> -----Ursprungligt meddelande-----
> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För IT
> Skickat: den 1 augusti 2022 08:22
> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Ämne: Re: Exclude and DoWork
> 
> Hi Das,
> 
> Of course, it works.
> Check your code to ensure is OK. I’m excluding from insert and from update my primaries keys in the table superclass and is working always.
> 
> Do $cinst.$cols.$sendall($ref.$excludefrominsert.$assign(kTrue);pos('_PK';$ref.$name)>0)
> Do $cinst.$cols.$sendall($ref.$excludefromupdate.$assign(kTrue);pos('_PK';$ref.$name)>0)
> 
>> El 26 jul 2022, a las 20:51, Das Goravani <goravanis at gmail.com> escribió:
>> 
>> 
>> Does anyone know for sure whether or not
>> 
>> $excludefrominsert
>> 
>> Definitely works on the $dowork and $doinserts() commands?
>> 
>> I once did the two together and got a duplicate unique value exception when I tried to do the dowork command.
>> 
>> Postgres was choking on the fact that the PK column WAS being included though I issued the exclude from command.
>> 
>> That’s how I remember it, it was awhile ago, so I thought I’d ask the opinion of others.
>> 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com Start a 
>> new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> VARNING: Det här e-postmeddelandet kommer från en extern avsändare. KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren och vet att innehållet är säkert.
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



More information about the omnisdev-en mailing list