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

Bastiaan Olij bastiaan at
Sun Jan 21 23:25:41 EST 2018

Sounds like you're making great progress Das!

Getting the hang of OO in Omnis Studio is going to be a life saver. I
know there are still 4GL commands you can fall back on but not many. I
would really stress learning how to use the notation approach so don't
settle too quickly on the 4GL commands.

Assuming your row variable is an instance variable and say it is called
ivMyDataRow then you're command should be:
Do ivMyDataRow.$definefromsqlclass('tMyTableClass')

As for the topic of Normalization, I can only echo what others have
already repeated. Stay FAR away from storing Omnis lists into the
database. Having a table with a record per invoice, and having another
table with a record per line on the invoice, will save you a lot of
hassle in the long run.

Equally so, learn about protecting your data using primary keys and
foreign keys. Omnis does not have build in logic to create tables with
the correct bits of logic in place so you're going to need to either
handle table changes outside of Omnis or start adding some magic to
Omnis (I did the later here at Instinct Systems, I did the former at
FIQAS, both have pro's and con's).

Just to keep in line with your invoice example, take this SQL script as
an example:

create sequence sq_invoices;

create table invoices (
  inv_id integer not null default nextval('sq_invoices'),
  inv_client_id integer not null,
  inv_number varchar(25) not null,
  constraint pk_invoices primary key (inv_id),
  constraint fk_inv_client_id foreign key (inv_client_id) references
clients (client_id)

create sequence sq_invoice_details;

create table invoice_details (
  idet_id integer not null default nextval('sq_invoice_details'),
  idet_inv_id integer not null,
  idet_text text not null,
  idet_amount numeric(15,2),
  constraint pk_invoice_details primary key (idet_id),
  constraint fk_idet_inv_id foreign key (idet_inv_id) references
invoices (inv_id)

create index idx_idet_inv_id on invoice_details (idet_inv_id);

"create sequence" is a command that creates a sequence object that
creates unique numbers you can use as a primary key. By using the
nextval function you can ensure that an insert into a table
automatically uses a new unique number. In Omnis you have to set the
property $excludefrominsert to true on the primary key column before you
call $insert. You'll need to do a little extra in the $insert as well to
get this new value. If you're not yet comfortable with those I'd say,
keep using your own code for giving your record a unique number.

If you're using combined primary keys or alphanumeric primary keys, my
best advise is to stop doing so. Life becomes so much easier if you use
a simply unique integer value as the primary key for your tables.
Also make sure you set $useprimarykeys to true in your table class for
more effective update queries.

The real magic here is in property identifying both inv_id and idet_id
as the primary key by adding a primary key constraint to your table.
Again, Omnis does not do this for you.
You can see that in both tables I define foreign keys. In the invoice
table I define which column is the foreign key to my client table, in my
detail table I define a column that is the foreign key to my invoice table.

PostgreSQL will now start protecting you against common developer
mistakes such as deleting a client that has invoices, or creating an
invoice detail while there is no invoices yet (or the creation of the
invoice header failed).

So all in all, more stuff to google and read up on :)



On 22/1/18 2:30 pm, Das Goravani wrote:
> Hi Bastiaan,
> Now it works.  I’m now using a different wizard made window with the same design as the first, but this one works, its on a different table.  Dont know why the other one wouldn’t work.  They declare the session variable into the row, and I’m aware of doing that to give it the functions of the hidden table class.  And I used the Define from SQL class command rather than notation because i dont know the right form to use that command, what to put before it, I’ve tried various.
> I would like to read the use of that command, $definefromSQLclass… if you can post it for me, just one line of code, i need to see what precedes it, how it’s used, syntax, example basically.  Similarly for the one called $prepareforupdate  as i assume that locks records since it issues a SELECT .. FOR UPDATE command which I was told locks records
> and if theres an error message number we can rely on which means record locked so we can respond gracefully… 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
> back in the day i did multi user omnis, so i’m aware of how to code for it in a general way, it’s the SQL way it’s reported, and when, on what commands, and ideas how to handle that, I just realized how to handle many SQL error messages is to be sure to have an interface that could be called "Never unfinished business when calling commands that could have errors", or, ever ready to start over buttons on windows that just had a procedure die on them. I think I’m hearing rollback and transactional as words whisping in the air about the hollow wherein i lurk and work
> weird new way, of handling data, weird new mature more sure and stronger realities around database design responsibility and the overall more serious nature of the SQL world as compared to using the Native Datafile (what does DML stand for?) serious, what’s it stand for?
> I was happy to find out that the way I’ve always done key ID numbers for parent records that are stored in child records, like customer to invoice to line items there’s a chain of numbers tying them together stored in them, just one number each, the parent, and then it has it’s own unique number except the bottom of stack needs it not
> found out, that that’s the way recommended in "Normalization of Database Data" major circles.  Kindof yeah "being on the standard but not knowing it yet".   
> so I’m off now, connected to a server on my Mac, using the Omnis tools also the server log, SQL, and I’ve fore sure entered data, retrieved it, worked with the server tables programmatically successfully in two ways, feeling secure that I’m on my way now, i’m connected and doing it
> thanks for your contribution which is huge to the community of us all, 
> kindest regards,
> das

More information about the omnisdev-en mailing list