doug at artsman.com
Wed Feb 9 17:49:12 UTC 2022
if you have created a ’serial’ column in your table (sounds like you have — called te_id)…
you are using omnis table classes to handle your inserts
over ride your $insert and make the following change to the sql
assuming omnis creates the sql with bind variables.
insert into te (te_id,val) values ([$cinst.te_id], [$cinst.val])
you need to change that into one of the two below.
option 1: change the bind variable for [$cinst.te_id] and replace with DEFAULT
insert into te (te_id,val) values (DEFAULT, [$cinst.val]) returning te_id
option 2: remove te_id from the statement altogether
insert into te (val) values ([$cinst.val]) returning te_id
note also, the returning te_id
that will cause postgres to give that back to you. within the $insert to have to do an additonal fetch and replace into $cinst.
eg steps are
manipulate the sql
add the returning
prepare the statement
send the statement
fetch the result into returnList
calc $cinst.te_id as returrnlist.te_id
we have found this to be about 20% faster than getting a seq # independently and then sticking it into a te_id.
for what it is worth, here is what we have put in our $insert in our base table class used for many many years that handles this case.
yes, I have a function called $prepareinsert which is part of TMObjs and assumes all variable names are prefixed with a key for the table (eg ’te_' and that all variables in the table have underscores. such as
te_id and te_val (i n your case). the purpose of the function is quickly identify ONLY FIELD THAT HAVE CHANGED …. its saves traffic sending large binaries if they are empty.
you can build your own equivalent — or if you currently use TMobjs (its free), then you can use the statement
the key in the code is that it does all the above in my sample, including the fetch so you. can see it in acton.
parameter pCheckDataYN default kfalse
otherThanStandardField int default 0
returncols char default xx_id
# If the PrimaryKey hasn't been assigned, set it now.
# Set the Standard Column values.
# Check the Data if the optional parameter was set to kTrue to check the data before $insert.
# All going well, we can proceed with the insert using "Do default".
# -> pCheckDataYN ## If the calling method says kFalse, skip calling $CheckData
# Update the record with any final settings before inserting
# Set the Standard Column values while we are doing insert
Do $cinst.$preUpdateSettings ## Will never fail. Old row is empty
# Set the BLOB data type columns to NULL
Calculate TableName as $cinst.$servertablenames
If pos(',',TableName)>0 ## A comma means more than one table is in the FROM portion of the insert. This means we are inserting from a query.
# Insertion from query, must do special behaviour. Ignore all columns that are not from the main table
Calculate TableName as $cinst.$getMainSQLTable ## V81000 DM - Allow inserting from queries
Calculate tempPrefix as con(iFieldNamePrefix,pick(iFieldNamePrefix='CD','','_')) ## V1006112 workaround for bug in $prepareinsert where two tables start with similar prefixes for credit cards
Calculate InsertString as $cinst.$insertnames()
# Ensure that we have an insert string before trying to send it to the server. If not, then we know it will fail.
Calculate returnCols as ''
# add the returning clause for fields that have server side defaults or that the table class indicates as being returned
Calculate Statement as con('insert into ',TableName,' ',InsertString)
Calculate Statement as con(Statement,' returning ',returnCols)
# All going well, we can proceed with the insert by calling the default method.
Do $cinst.$sendSQLdirect(Statement) Returns StatusOK
Calculate StatusOK as kFalse
# Retrieve the last key set
# Do any updates that are required to the fields after the database is updated.
# Also if we updated any child records, then update the parent as well.
Do $cinst.$setVariablesWhenRecordInserted ## only called on insert
Do $cinst.$postUpdateSettings ## called on insert and update
# V100677 set the cache after insertint the record - if same schema, will add to cache record
If iSetCacheAfterUpdate¬(tUpgradeInProgress) ## see if we poke the cache record (never when update in progress)
Do tDataCache.$UpdateCacheRecord($cinst.$getUniqueKeyField,$cinst.$getUniqueKey,$cinst) Returns #F
If flag false
Breakpoint what happened
Quit method kTrue
# Insert was not OK? Need to add code to elegantly figure out why and report to the user.
Do $cinst.$msgOk($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',con('Developer Message:',kCr,kCr,'Insert of record has failed. We need to complete a generic method for giving the user more information about why the insert failed.')))
Calculate #S1 as iStatementRef.$sqltext
Calculate #S2 as iStatementRef.$obpreparedstatement
Calculate #S3 as iStatementRef.$obexecutedstatement
Calculate #S4 as iStatementRef.$errortext()
Calculate #S5 as iStatementRef.$nativeerrortext()
Quit method kFalse
Calculate pCheckDataYN as pCheckDataYN
Arts Management Systems Ltd.
mailto:doug at artsman.com
Phone (403) 650-1978
> On February 8, 2022, at 3:09 PM, Mike Rowan <michael.rowan3 at gmail.com> wrote:
> I could really do with a strong hint on this one.
> Omnis 10.2 on Mac
> Postgresql 14.1
> I have not until now used sequences preferring to roll my own $inserts in
> my tables. However, I think I now should in order to fully utilise all
> the Postgresql offers.
> So, I have created new table te with two columns te_id (serial) and val
> In pgAdmin4 I can insert new rows with auto-generated te_id. So, the pg
> part works fine.
> In Omnis I have a test window with a list (iList) and a row (iRow) both
> defined from a table class 'tte'.
> In the window $construct I run Do iList.$select() and then Do
> iList.$fetch(kfetchall) to populate the list. The select and fetch work
> correctly, so I know the table class is constructed correctly.
> In the table class I have left $insert untouched, so it uses the built-in
> On the window, for iRow, only iRow.val is displayed as an entry field.
> te_id is not displayed.
> When I attempt to save a new value (42), the values in iRow are (null,42).
> An error is thrown by the table class $insert ('Statement could not be
> I don't see how to find out why this is happening, unless some kind soul
> can enlighten me. After three days trying this and that, I'd be so glad.
> I have a feeling this is to do with the contents of iRow.te_id. If not
> null, what should it be? A real-life example here would be very useful.
> 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