AW: Trying to go generic on update

Sun Aug 9 11:07:05 EDT 2020

Hi Das,

Do $cinst.$sessionobject.$assign(tvSessObjRef) Returns #F
Do $cinst.$update($cinst) Returns #F

This really ought to work. We use an older version of Studio, and sometimes the bind variables no longer work, but this happens not very often, so I guess this will not be the issue you are having.

If you have a compound index, set all the fields that make up your index as primary in the schema, then Omnis will use all the primary=kTrue fields for the WHERE clause of a $update and $delete.

Regards

Rudolf

-----Ursprüngliche Nachricht-----
Von: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] Im Auftrag von Das Goravani
Gesendet: Samstag, 8. August 2020 20:58
An: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
Betreff: Re: Trying to go generic on update

Rudolf,

Thank you for the post but that is incredibly basic.. I guess it’s good to check basics.. but I’m begging to be understood as someone who gets it.. I think I am doing all the code correctly.. and fully.. As far as I know I have everything in place for it to be working correctly.. I definitely have these basics you just covered handled.  I have tables which dont have a primary key, that is true, they have an indexed field, I am about to remake my database, and at that time I am going to insert primary keys for the tables that dont have them now.. so I’m going to handle that problem.. but that is not why I am not able to insert and update

For I have plenty of tables WITH primary keys and they don’t insert or update either.. then they do, and the others do, when I spell out everything for it

When I use variables in place of the spelled out table and row etc it won’t work.. it should.. I’m using syntax that works for other people and I GET IT HOW to do indirection with square brackets.. I get how Omnis works.. been doing it for 34 years.. 

My code works for all data gets.. I am reading data just fine.. all respects.. I WAS inserting but now it’s become intermittent.. update has always been a problem

I know how old_row works and I know what fields are in it for what reason.. how it’s used.. 

In fact the update command $update returns flag true for me.. as if it worked.. but upon inspection no data was changed on disk

But if I spell it out then it works

So it would seem my old_row is not correct.. what it is exactly is the state of the data the last time I did a select and fetch.. right then I set old row.. with the contents of the row when it is first read in from disk.. so that old_row will equal the state of the data on disk.. but I know that the key field is the primary key.. but I have those and they won’t work either.. I test for presence of the values and they are there.. in the row.. and in my case in the CRB as well.. but I stick with notation and the row in this case, around my SQL dealings.. 

Here’s my code in order of how it happens for an insert for example. They are not in fact together, there are other lines inbetween where variables are assigned their values, 

Do tvO.$updatefile(‘fcCh’,’ADD’). This is the call in code to begin an insertion
Do [lrowname].$definefromsqlclass($tables.[ltablename])
Do $cinst.$sessionobject.$assign(tvSessObjRef) Returns #F
Do $cinst.$insert() Returns #F

Those are the lines that matter, that deal with something concrete that affects Omnis and the task at hand, these are the key statements

Its not much different for Update 

Do $cinst.$update(old_row)

By the way I use all rows.. I really dont have a call for lists except I will probably use a smart list to speed up a loop of insertions I have when a new chart (main record) is added to my program. Other than that idea, no I dont have lists of data being fetched much.. I do some build list equivalents for when I’m fetching lookup lists.. but I dont do inserts and updates with lists only with rows.. all the code I’m posting relating to this subject is rows

Right now I’m dealing with “What makes a session Object Reference work or not work, as I’ve implemented one, and it’s partially working.. some SQL works, some doesn’t, it was more stable when I was using my session object and statement object directly.. now I’m logging on, with one session object, then making the object ref to the dam, then logging on with it, so I have two sessions now, and I’m using the 2nd to instantiate lists (as you can see the code above it is an object ref that I’m using to bless the row)

Back to my problem.. when I use the session object, not a reference, fetching data is really stable.. inserting is intermittent, update is not working unless I spell out in an execdirect.. using variables doesn’t work. That summary is the key to attacking my problem.. the fact that I can UPDATE data when I spell it out tells us the database works.. it’s in the automation of the $update command that something goes wrong.. but I am doing things it’s way.. I have tables with primary keys in place that won’t update.. 

From all things said about what you have to do to make things work I’ve done all I’ve heard so far.. am still stuck

There is something happening that hasn’t been hit on yet

> On Aug 8, 2020, at 11:11 AM, Rudolf Bargholz <rudolf at bargholz.ch> wrote:
> 
> Hi Das,
> 
> Your code will need to be
> 
> Row.$update(OldRow) 
> 
> The OldRow will have the column defined as primary=kTrue, which will then be used for your WHERE clause. Both rows need to be defined from an sqlclass.
> 
> Regards
> 
> Rudolf Bargholz
> 
> -----Ursprüngliche Nachricht-----
> Von: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag von Das Goravani
> Gesendet: Freitag, 7. August 2020 21:10
> An: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Betreff: Re: Trying to go generic on update
> 
> In hopes of finding out why I cannot UPDATE no matter what I try, I am here posting my code for UPDATE.. there’s nothing to it really.. update is after all one line of code
> 
> This is my update code from my master table class (after the call to it from an object class). I have followed advice and I use Table Classes for all my schemas or tables. 
> 
> I can do inserts with $insert, deletes with $delete, and I wish I could do updates with $update, but it will not work for me so far.
> 
> Here’s my code, there’s nothing to it that would swing this one way or the other.. I dont get what’s going wrong.
> 
> 
> This is my method that I call to INSERT or UPDATE any data.. it is generic, it is in an object
> 
> It’s simply a place to define the list and then I fork for INSERT and UPDATE to my table class, the lrowname is my row’s name and that invokes it’s table class which invokes the master table class and the method is there
> 
> Calculate lrowname as con('tv',pfilename,'row')
> Calculate ltablename as con('t',mid(pfilename,2,30))
> Do [lrowname].$definefromsqlclass($tables.[ltablename])
> 
> That last line invokes the construct of my Table Super Class which runs the following single line of code
> 
> Do $cinst.$sessionobject.$assign(tsessobj) Returns #F
> 
> Now I am WORRIED that the fact that I use a task variable my original session object.. worried that I’m using it straight always, over and over, and not a reference.. many have told me to use a reference.. I thought “This works, why bother”.. but maybe this “doesn’t work”.  I’m asking. Is it OK to use your main session object to instantiate rows one after the other.. do you have to use a reference to gain stability? Or to make edit work?  I’m going to try it out.
> 
> Now to the rest of our method in the object.. simply forks two ways for insert and update to methods in my master table class
> 
> If pmode='ADD'
> Do [lrowname].$insertrow(pfilename)
> Else
> Do [lrowname].$updaterow(pfilename)
> End If
> 
> The name of the file in question is passed to this method as the only parameter. 
> 
> Calculate lrowname as con('tv',pFileClass,'row')
> Calculate loldrowname as con('tv',pFileClass,'old')
> Do method $packrowfromcrb (pFileClass)
> 
> # this next section simply pulls our primary key from a list I have created
> Do tvUpdateIndexFields.$search(1=2,1,0,1,1)
> Calculate tvfilename as ''
> Do tvUpdateIndexFields.$search(pFileClass=tvUpdateIndexFields.tvfilename,1,0,1,1) Returns #F
> Calculate lSearchSuccess as #F
> If lSearchSuccess
> Do tvUpdateIndexFields.$first(1,0)
> Do tvUpdateIndexFields.$loadcols(tvfilename,tvrsnfieldname)
> Calculate lindexfield as tvrsnfieldname
> End If
> 
> #this section represents 5 tables I have made “Spelled Out” execdirect update statements and they WORK
> #the primary key is include in the update names and it works.. so exclude from update is not the thing stopping my $updates
> #so these 5 following work, but they are not generic, they are spelled out, BUT THEY WORK
> If pFileClass='fcRepSets'
> Do tstaobj.$execdirect(con('UPDATE fcRepSets ',tsessobj.$updatenames(tvfcRepSetsrow),' WHERE repsetrsn = @[tvfcRepSetsrow.repsetrsn]')) Returns #F
> Else If pFileClass='fcCentral'
> Do tstaobj.$execdirect(con('UPDATE fcCentral ',tsessobj.$updatenames($ctask.tvfcCentralrow),' WHERE cons = 1')) Returns #F
> Else If pFileClass='fcWindStyles'
> Do tstaobj.$execdirect(con('UPDATE fcWindStyles ',tsessobj.$updatenames(tvfcWindStylesrow),' WHERE prsavedviewname = @[tvfcWindStylesrow.prsavedviewname]')) Returns #F
> Else If pFileClass='fcCh'
> Do tstaobj.$execdirect(con('UPDATE fcCh ',tsessobj.$updatenames(tvfcChrow),' WHERE nrsn = @[tvfcChrow.nrsn]')) Returns #F
> Else If pFileClass='fcEvents'
> Do tstaobj.$execdirect(con('UPDATE fcEvents ',tsessobj.$updatenames(tvfcEventsrow),' WHERE evrsn = @[tvfcEventsrow.evrsn]')) Returns #F
> Else
> 
> #all other tables come here, and I try this first simple line which I wish would work, but I get flag false, no update
> 
> Do $cinst.$update(loldrowname) Returns #F
> 
> #I then tried execdirect and it’s way, here I pack variables that get used in it
> Calculate iFileClass as pFileClass
> Calculate irowname as lrowname
> Calculate iindexfield as lindexfield
> Calculate ivalue as [iindexfield]
> Calculate iupdatenames as tsessobj.$updatenames([irowname])
> 
> #then I try it so many ways, none work, this is one example
> 
> Do tstaobj.$execdirect(con('UPDATE ',iFileClass,' ',iupdatenames,' WHERE ',iindexfield,' = [ivalue]')) Returns #F
> 
> #and this is another example that’s partially typed out and still doesn’t work
> 
> Do tstaobj.$execdirect(con('UPDATE fcNums ',tsessobj.$updatenames([irowname]),' WHERE well = @[ivalue]')) Returns #F
> 
> #I then tried Sta: and that method and it didn’t work either
> 
> Begin statement
> Sta:UPDATE [iFileClass] [iupdatenames]
> Sta:WHERE [iindexfield] = [ivalue]
> End statement
> Get statement lsqlscript
> Do tstaobj.$execdirect() Returns #F
> 
> End If
> 
> #if successful and we reach here I update old_row so it’s ready for the next time around
> 
> Calculate [loldrowname] as $cinst
> Quit method #F
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 

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