Trying to go generic on update

Fri Aug 7 11:25:43 EDT 2020

As ever Doug, most generous with your time, and sharing of code....

regards
Phil Potter
Based in Chester in the UK.

On 07/08/2020 15:40, Doug Easterbrook wrote:
> hi Das:
>
> First: you asked for help.   thats what the list tries to do.   From responses you get, a number of us on the list have been trying to follow your stream of consciousness on multiple emails to help you solve a number of your issues.     Its like firing a shot i nthe dark.  you are deep in your code and we are only being exposed to glimpses of it.
>
> I do read all your emails out of intereste and am rooting for your success..  but just so you know, it is difficult to see the entire picture.
>
>
>
>
> Second:  which leads me to …. I can’t read your mind, nor are we (the list) reading the entirety of your code.  At the moment, I speak only for myself - I can only interpret what I read in your posts and your words.     The way you are using execdirect seemed to imply you were building your own SQL code and giving it to execdirect.
>
> The standard, out of the box, table class, means you don't need to do write any SQL code.  if you over-ride the  $insert, $update, $delete, then you will need to use $execDirect
>
> So this was a legitimate question:  are you using a table class or not?
>
> Since you say you are: creating a standard SQL statement shouldn’t be hard since I’m sure a number of us have extended the table class for some great benefits.  We have. done so.
>
> so, a recap of that
> you can use built in omnis $insert (which gives a standard sql statement with no work on your part - its just not always efficient)
> you can over ride it and add your own features.
>
>
> I offered a suggestion to break down the code into a couple of steps so you could interpret the code and get the exact SQL that you were sending.   Dan Ridinger added in his post that he does it that way to.   Dan’s done a lot of coding in the past and teaches at university.    He’s smarter than I.
>
> If Dan thinks it is a good idea, then so do I.      Did you try that and what were the results?
>
>
>
>
> Third:  you mention having tried ORM.    I assume by ORM, you mean object relational modelling..     Are you now telling me that you are building table classes within objects?     I.e:  are you telling me that you tell an object class to make update and that fires off a lot of changes to other tables?
>
> Thats how I might interpret that statement, which adds some, but not much, complexity.
>
> in other words, can you please define, for clarity, what you mean by  "I can do ORM on INSERT and DELETE and I’m sure execdirect would work there too”.
>
> My understanding is that ORM is a design pattern for a database, not a coding method in omnis.
>
> Since you are doing ORM on inserts, I truly do not have an full notion what you are up to..
>
>
>
> Finally:   there are not many of us that use SQLLite.     The original advice was to use postgres.   That gives tools and logging that quite a few of us know (And it works,)
>
> All we can do it try to help with coding approaches, while at the same time,  try to discern if there is a sql-lite specific problerm or an omnis coding problem
>
>
> as I said at the top of this, we use exce direct and we have generic code. (I’ll copy my $update below so you can interpret it from what we’ve done to enhance things to make $update super powerful).   You can use it for ideas if you want, vut it will not work exactly.
>
> so thats how advice works.    I use my experience to try to help you find your problem and others can chime in.
>
>
> but ultimately I cannot address your problem if I cannot understand how you are describing it.
>
>
> The gist of the code is:
> Calculate WhereClause as con('WHERE ',iFieldNamePrefix,'_SEQ=',$cinst.[iFieldNamePrefix]_SEQ)
>
> Do tStringFields.$prepareupdate(statement,otherThanStandardField,nam(iRowOld),"DATE_UPDATED`DATE_ENTERED`EDITNUM`CHANGED_BY_E_SEQ",tempPrefix)
>
> (the above is generic one liner code we wrote into TMOBjs (freely available to all omnis developers) to run though all variables in the $cinst of  table and decide which ones had changed.  Out of it comes something like
>
> Var1=[@var1],var10=[@var10]
>
> that SEEMS to be like your #S1 =  tsessobj.$updatenames([irowname])
>
> then we use $cinst.$serverTablesNames to get the name of the table
> Calculate statement as con('update ',$cinst.$servertablenames,' set ',statement,' ',WhereClause)
>
> then we do a postgres specific feautre to add returning variables
> Calculate statement as con(statement,' RETURNING ',ReturnCols)
>
> then we prepare the statement in a common method called $sendSQLDirect, which does two things.
>
> Do iStatementRef.$prepare(pStatement) Returns sqlOK
>
> Do iStatementRef.$execute() Returns sqlOK
>
>
>
> NOTE:  we use $prepare and then $execute INSTEAD of $execDriect.   it gives us more opportunity to check for errors at two step in the process.
>
>
> so to distill that even more and using your example variables, the gist of what we do on opur $update in the table class is very very generic and looks like this
>
> calculate whereclause as con(‘where ‘,indexfield,’=‘,value).  ## we just use the value instead of bind vars
> calculate statement as 'Var1=[@var1],var10=[@var10]’.    ## to update vars, we use the bind vars
> calculate statement as con(‘update ‘,ifileclass,’ set ‘,statement,’ ‘, whereclause).   ## note, there are no square brackets in this
>
> do tstaobj.$prepare(statement) returns #F
> do tstaobj.$execute()
>
> and if we have ‘returning’ (a postgres construct that is really useful), then we do a
>
> do tstaboj.$fetch()
>
>
>
>
> Summary of what I’m offering yo you to help:
> break your code apart so you can inspect it (like Dan confirmed)
> tell us what the exact contents of the SQL looks like before you send it
>
> then, as we see more, we can assist better.
>
> This is what we all want to do to see you succeed, but please never assume we are inside your code at all times following along.     There are two many ways to write things.   If I can’t see your results at each step clearly, it is harder to help
>
> terribly sorry about that.
>
> good luck
>
> .
>
>
>
> Our $update code in entirety:
>
> # Check the Data unless "pCheckDataYN" is kFalse.
> # Increment the "EditNum" column value by 1
> # Add the constraint "AND EditNum=pRowOLD.EditNum" to the update SQL to make sure we don't overwrite
> # an update done by another user. Then proceed with the $update Do default.
> # After the update, check the Status to make sure at the update was successful.
> # If the update fails, check the EditNum to see if another user updated the record ahead of us.
> # If that was the problem, analyze what has been changed. Compare the user's current row and old row
> # with the saved row in the data file. If the other user made changes, prompt the user with a list
> # showing what changed and ask them to decide what to do.
> #
> # ->pRowOLD ## Old Row, needed for matching the primary key. It is possible to send "current Row".
> # ->pDisableWhereNY ## Disable the "Where" clause in the default SQL statement. Default is kFalse.
> # ->pCheckDataYN ## If the calling method says kFalse, skip calling $CheckData
> # <-Return ## Flag
>
> If isclear($cinst.$getUniqueKey)
> # Trying to update a row with a Primary Key # of 0? That's not gonna work very well, may want to check your code
> Breakpoint
> Quit method kFalse
> End If
>
> Calculate pDisableWhereNY as kFalse ## Do NOT allow the programmer to disable the where clause. Too dangerous!
> Do $cinst.$setRecordChangedSettings ## Update the Standard Column values.
> Do $cinst.$preUpdateSettings ## Update the record with any final settings before saving. Will never fail. can refer to iRowOld to see old info
>
> # V81000 DM - Totally changed this to always use the unique key and optionally the editnum. Took out old unused functionality as well.
> If isclear(iFieldNamePrefix)
> Breakpoint
> Calculate WhereClause as $cinst.$wherenames
> Else
> Calculate WhereClause as con('WHERE ',iFieldNamePrefix,'_SEQ=',$cinst.[iFieldNamePrefix]_SEQ) ## s
> End If
> # add editnum if it is found in the column table (and we should use it -- rare programmer choice not to)
> If iSetEditNum
> If pUseEditNum
> Do tStringFields.$appendString(con(iFieldNamePrefix,'_EDITNUM=',$cinst.[iFieldNamePrefix]_EDITNUM),WhereClause,' and ') ## was pRowOld xx_editnum
> End If
> Else
> # V80000 DM - Patron Statistics and Daily Sales Summary don't have an edit num, so we don't need to watch for this. V90800 TicketTrove tables don't have them either
> If iFieldNamePrefix<>'CS'&iFieldNamePrefix<>'S'&iFieldNamePrefix<>'WP'&left(iFieldNamePrefix,3)<>'ITT'
> Breakpoint there is no edit num in the table. Might be wise to add it
> End If
> End If
>
> # pass field name prefix to let external know if its our table. Only fields with the prefix are included. If no prefix, then its daylite table and al fields are included
> # V1006112 workaround for bug in $prepareinsert where two tables start with similar prefixes for credit cards. Although, on update, the fCreditCardsEncrypted
> # columns will never have changed, so wouldn't be picked up. just look at CD_ for safety.
> Calculate tempPrefix as con(iFieldNamePrefix,pick(iFieldNamePrefix='CD','','_'))
> Do tStringFields.$prepareupdate(statement,otherThanStandardField,nam(iRowOld),"DATE_UPDATED`DATE_ENTERED`EDITNUM`CHANGED_BY_E_SEQ",tempPrefix)
>
> If len(statement)=0|otherThanStandardField=kFalse
> # V100737 nothing to update, so send trace log entry instead and it won't interrupt flow
> Send to trace log (Diagnostic message) tablebase: did not update [iTableDesc] seq #[$cinst.[iFieldNamePrefix]_SEQ], no updatable fields [statement]
> Quit method kTrue
> End If
>
> # build the statement using the servertable names
> If len(iFieldNamePrefix)
> # if TM table (vs else is for any system table or daylite table), then use the prefix to get servertable. allows updates based on queries
> # add in edit num if not there and we'll substitute with xx_EDITNUM=xx_EDITNUM+1 in $replaceUpdateDefaults
> Do $cinst.$addEDITNUMtoUpdate(statement)
> Calculate statement as con('update ',$ctask.tStringFields.$getFileNameFromField($cinst.$getUniqueKeyField),' set ',statement,' ',WhereClause)
> Else
> Calculate statement as con('update ',$cinst.$servertablenames,' set ',statement,' ',WhereClause)
> End If
>
> Do $cinst.$replaceUpdateDefaults(statement,ReturnCols)
> If len(ReturnCols)
> Calculate statement as con(statement,' RETURNING ',ReturnCols)
> End If
> #
> Do $cinst.$sendSQLDirect(statement) Returns UpdateOK
>
> # If the update was OK return true.
> If UpdateOK
> If iSetEditNum|len(ReturnCols)>0
> Do iStatementRef.$fetch(returnValuesRow,1)
>   
> If returnValuesRow.$linecount=0
> # should always be a return row ... even if empty .. so this shouldn't happen
> Calculate UpdateOK as kFalse
> Else If iSetEditNum&pUseEditNum ## V100300 do we ignore editnum
> # V100737 if no edit num in return row, then we didn't update anything and thats a problem
> If isclear(returnValuesRow.[iFieldNamePrefix]_EDITNUM)
> Breakpoint
> Calculate UpdateOK as kFalse
> End If
> End If
> # V81000 DM - Add condition to check when another user beat you to the punch at updating
> If not(UpdateOK)
> Calculate editNumCurrent as $cinst.[iFieldNamePrefix]_EDITNUM
> Calculate editNumExpected as $cinst.[iFieldNamePrefix]_EDITNUM+1
> If tStringFields.$isFullVersion
> Breakpoint Didn't update for some reason, verify edit numbers
> End If
>   
> Calculate employeeName as tEditCheck.$getEmployeeNameFromSeq(tDatabase.$getRecordField(con(iFieldNamePrefix,'_CHANGED_BY_E_SEQ'),con(iFieldNamePrefix,'_SEQ=',pRowOLD.[iFieldNamePrefix]_SEQ),0))
> # V82500 Use msgSqlError instead so that user can email stuff to us or copy to clipboard
> Calculate message as con('The ',iTableDesc,' record you were editing was changed by ',employeeName,' before you confirmed your changes.',kCr,kCr,'Try what you were doing again.',kCr,kCr,'This usually happens for one of 3 reasons:',kCr,kCr,I_BULLET_CHARACTER,' Another employee changed it while you were looking at it',kCr,I_BULLET_CHARACTER,' You were editing the same record in two separate windows',kCr,I_BULLET_CHARACTER,' Or is a slight change it may be a program issue.',kCr,kCr,'Please take a snaphot and send to ',gSupportEmail,' if ',employeeName,' was not editing the record.',kCr,kCr,kCr,kCr,'Current editnum: ',editNumCurrent,', expected editnum: ',editNumExpected,kCr,kCr,'Where: ',WhereClause,kCr,kCr,'Statement:',statement,kCr,kCr)
> # Do $cinst.$msgSqlError($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',message,'TitleText','Error: UPDATE did not occur','AllowQuit',kFalse,'Subject','TM Editnum')) ## $msgSqlError
> Open window instance wMsgOk/*/CEN ($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',message,'TitleText',con('UPDATE did not occur - ',iTableDesc),'OkText','Continue')) ## $msgSqlError
> Quit method kFalse
> End If
>   
> If returnValuesRow.$colcount>0 ## V100005 any returned values for any reason, stick into current row
> Do $cinst.$assignrowTM(returnValuesRow,kTrue) ## Merge data from the update row back into the list
> End If
> End If
>   
> Do $cinst.$setVariablesWhenRecordUpdated ## only called on update
> Do $cinst.$postUpdateSettings ## called on insert and update
> # V100677 set the cache after updating the record
> If iSetCacheAfterUpdate&not(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
> End If
> End If
> Quit method kTrue
> End If
>
> # Update was not OK?
> # Was the update caused because another user updated the record, changing the EditNum?
> # Find out by fetching the record and comparing it's EditNum to the pRowOLD.EditNum.
> Breakpoint if you get here... its cause update failed for some reason. Let doug Know you ran into this event
> # and then figure out why the old code read things into row, and does not put it back into $cinst.
> # Select and fetch the record which has the matching primary key.
> Do iRow.$getExactRecord(con(iFieldNamePrefix,'_SEQ'),iRowOld.[iFieldNamePrefix]_SEQ,kFetchAll,kFalse,0) Returns lFlag ## since getting existing record, dept of 0 is ok
> If not(lFlag)
> # Open window instance wMsgOk/*/CEN ($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',con('Error: It appears that another employee has deleted this record while you were editing it.',kCr,kCr,'Try locating the record to see if it still exists.')))
> Send to trace log (Diagnostic message) Did not update [iTableDesc] record #[$cinst.[iFieldNamePrefix]_SEQ] because another user edited it
> Quit method kFalse
> End If
>
> If tStringFields.$isFullVersion
> # Open window instance wMsgOk/*/CEN ($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',con('Developer Message:',kCr,kCr,'Update of record has failed. We need to complete a generic method for giving the user more information about why the update failed.')))
> Do $cinst.$msgOk($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',con('Developer Message:',kCr,kCr,'Update of record has failed. We need to complete a generic method for giving the user more information about why the update failed.')))
> Breakpoint
> End If
> Quit method kFalse
>
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
> w message -> mailto:omnisdev-en at lists.omnis-dev.com