Does $insert() etc not work in Postgres? It's WORKING now.

[Paul Mulroney]

Hi Das,

> On 23 Jan 2018, at 1:00 am, omnisdev-en-request at lists.omnis-dev.com wrote:
> 
> what does DML stand for?

DML = Data Manipulation Language.  That's the official title for the Omnis commands used to manage data in an Omnis native datafile.

> that too i?m curious about, that problem, that reality, of record locking, how it?s reported, and how to handle it, are things I?m not informed about, but thinking about, as it makes you structure the ordering of commands around a data entry moment or session

Record locking is a completely different animal in SQL than in Omnis native datafile.  There's a number of different ways to approach record locking in SQL, but generally speaking you don't really want to "SELECT ... FOR UPDATE", because you can potentially leave the database in a locked state.  It's not like an Omnis datafile where if the user quits, the locked is released automatically. Instead you rearrange how the database works so that you don't need to do record locking - let the SQL database engine handle it for us.  

For example, one common thing we do is have a unique number that isn't a sequence number, for invoices.  In Postgres, there's a special data type called "Sequences", and these handle all the hard work for us.  We can do the following as a SQL in Postgres: "select nextval('sequence name')" and it will return the next number, guaranteed to be unique and not taken by anyone else.

Another example, if you have running totals stored in a record (and for SQL you probably shouldn't - that's part of the database normalisation that Alex was talking about), in Omnis we would say something like "calculate total as some new value".  In SQL you'd say instead "calculate total as total + some increment" - that way if two people are updating the same record, it doesn't matter which order the total is calculated since you're only adding the difference that you're adding to the total.

Another example, if you're doing data entry, if you're using $definefromsqlclass to define a row var containing your data, I believe that the $dowork() method that you execute at the end when you want to save the data, only saves the changes made to the row (only the changed fields, not all fields). In that setup, the only time you need to deal with conflict is when two people update the same field at the same time.  It's better to design the code so that scenario doesn't happen, sometimes by social engineering, changing business processes etc.

Admittedly this is a new area for us too - we've been doing Omnis Native data for many many years, and only 3-4 years of SQL for our biggest app.

Good luck!

Regards,
Paul.


I bought my friend an elephant for his room. He said “Thanks” I said “Don’t mention it”
-- 
Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au       Trading as Logical Developments
www.logicaldevelopments.com.au                   ACN 161 009 374 
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102