Select FOR UPDATE and $update, is there a problem?

to add to stene’s fine description and to talk why the old row is a really good thing……

OldROW is invaluable when updating the database.

it gives you an opportunity to simulate what a database trigger can do by providing the opportunity to look at changed data

eg (this is a pseudo code example to give the idea)

if $cinst.MyField <> oldrow.MyField
then myField changed, so update another database table with something
end if

We use oldRow extensively in the $update statement to find out what fields have changed for the key purpose of ONLY UPDATING THOSE FIELDS and making the app faster.

why? I’ll use an extreme example to illustrate the point — Suppose
1) you have 200 database fields in one table
2) they are all binary fields containing pictures
3) they are all large (say 1 meg each – which is small for a picture)

the way the omnis update statement works out of the box, the math says you would be transmitting

200 (fields) x 1 Meg (per field) * 2 (the update statement has the data and the 2here clause has the data for comparison)

= 400 megs of data transmitted just to do an update.

imagine over an 50 m/bit cable connection to a remote server in the cloud and this statement will take

400 M bytes / 50 (mbit/sec) * 8 = 64 seconds to transmit the sql statement to the final destination.

if you only changed one picture in the database, you could optimize the statement to only change the one binary field that changed and transmit 1 meg.

or
1/50 * 8 = .16 seconds to transmit the single change.

Thats a clear example where the table class has benefit and the requirement to provide the old row for comparison purposes gives you the opportunity to optimize the SQL with possibility od significant performance gains.

it is for that reason, the TMOBJS xcomp thats out there in the wild and free to all has a static method that will provide you with the update statement containing only changed fields. Its useful.

hope that helps

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

see you at the third annual users conference
tickets.proctors.org/TheatreManager/95/online?performance=29086 <tickets.proctors.org/TheatreManager/95/online?performance=29086>

> On Mar 1, 2018, at 11:35 PM, Sten-Erik Björling <s-e.bjorling@enviro.se> wrote:
>
> Hi Das,
>
> The oldrow is a row variable that represents the row var before the row was updated with new data. It is used by Omnis Studio to search the old record in order to update it with new values – using the complete row vars data as a search data instead of the primary key. If it does not find the record representing the old row in the database then that is an indication that the record already have been updated by another user and can be used to trigger a warning to your user.
>
> In the example that I linked to earlier I use a generic table class for all tables in my systems which is used for all the standard DB operations in each of the data objects in the system. Thus – operations like insert, update, delete are all handled by the generic table class. Searches etc. are mostly done by scripted SQL – both since the SQL (especially for process objects spanning over multiple tables etc.) can be complex but also to allow analysis and optimisation of the SQL code at the server system.
>
> Take care, all the best…
>
> Stene
>
> ______
>
>> 1 mars 2018 kl. 22:51 skrev Das Goravani <das@Goravani.com>:
>>
>> OK I got it to work by putting the “old row” variable into the parenth. Of the $update statement. It seems to require that you have an “oldrow” in there. Can anyone tell me why that is…why does it need to have the “old row” in there…does it compare?
>
>
>
> Sten-Erik Björling
> Enviro Data
> Kyrkogatan 5A 2 tr
> SE-972 32 Luleå
> Sweden
>
> E-Mail: s-e.bjorling@enviro.se
> Mobile: +46-70-655 11 72
> Wire: @stenerikbjorling
> Skype: stenerikbjorling
> iChat: stene@mac.com
> FaceTime: stene@mac.com
> Hotmail / Messenger: stenerikbjorling@hotmail.com
> GMail: stenerikbjorling@gmail.com
>
> ______
>
> This email and any files transmitted with it are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, please note that any use, distribution, or reproduction of the contents of this email is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by return email and destroy all copies of the original message including any attachments thereto. Thank you.
>
> Please note that we take reasonable precautions to prevent the transmission of viruses; however, we cannot guarantee that this email or its attachments are free from viruses. We only send and receive emails on the basis that we are not be liable for any loss or damage resulting from the opening of this message and/or attachments.
>
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com