Avatar of fmoore0001
fmoore0001
 asked on

Table / Cursor Needs Tableupdate

I have a form that last some 200 tables opened that we buffer and require a TABLEUPDATE() for record additions and edits.   We have a routine called UpdateAll() that make an array of all table object  then runs through a check to TABLEUPDATE() all the tables:

For i = 1 TO LEN(atu[i, 1])
     m.lSuccess = TABLEUPDATE(.T.,.F., atu(i, 1))
ENDFOR

However, with some 200 tables some saves are quite lengthy and clients are complaining.  The fact is that while any of the 200 tables may be changed and require an update, usually it is only a couple of tables needing an update, but I have a feeling TABLEUPDATE is doing an entire scan of a table.

It stuck me if Foxpro has a routine to check IF a table/cursor has been changed (buffered?) THEN my TABLEUPDATE() routine could kick in, otherwise ignore that TABLEUPDATE.  VFP has routines for individual fields fields being changed, but is there a method to check if the table itself requires TABLEUPDATE?

Any ideas on that?

Frank
FoxPro

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
Pavel Celba

GetFldState(-1) returns the update status of the whole table BUT I don't think your design is correct.  To allow 200 tables modifications in one form does not result in a stable application. Long processing times can just increase the possibility of data corruption during some unexpected situation like a power failure or OS/app crash.
fmoore0001

ASKER
Based on Pcelba's suggestions, this is what I setup to use instead, replacing the full TABLEUPDATES:

IF atu(i, 1) <> "FPWERROR"
      IF "2" $ GETFLDSTATE(-1, atu(i, 1))
            m.lSuccess = TABLEUPDATE(.T.,.F., atu(i, 1))
            IF NOT m.lSuccess
                  m.lSuccess = TABLEUPDATE(.T.,.T., atu(i, 1))
            ENDIF
      ELSE
            m.lSuccess = .T.
      ENDIF
ENDIF      

Seems to work much faster.  Opinions?

Frank
ASKER CERTIFIED SOLUTION
Pavel Celba

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Olaf Doschke

First of all, this is also my experience: Tableupdate() time grows not only linear to the number of changed records, but also with the overall reccount. So, first rule of thumb: Don't load all data. And I second Pavel, 200 tables is really a sign of a bad design, no user acts on 200 tables, no form should ever maintain that much tables. 200 tables is even a large number of tables for a whole database.

Pavel, I don't know how you interpret the sentence of the help "You can specify –1 for nFieldNumber to return a character string consisting of deletion and modification status values for all fields in the table or cursor." That supports your solution, but it means the current record only.

I just made sure, and indeed GetFldState(-1) returns no "2" inside it's return value, if you're not on a changed record of a table. So that is not a good detection mechanism.

Look into your favorite framework and you'll see it will rather check for buffered changes by locating the first changed record via GetNextModified(0,cAlias). If that returns 0 no buffered changes exist. Another thing you could do is requery(), because that errors, if there a pending buffered changes. But GetNextModified(0) is fast and easy.

Frank, your issue has to be solved on another level than here. You need to rethink your design and how you load what amount of data, it seems to me you not only load all or almost all tables of your database, just to be sure you can edit anything, but you also seem to load all data of each table. You do neither, you create forms that maintain a table or a hierarchy of tables, 3-4 could be counted as normal. And you may load further 3-10 tables just for read access, but not 200. Are you creating a table per date or per customer or such things?

Bye, Olaf.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Pavel Celba

You are absolutely right Olaf.
I am using buffering so rarely that it was necessary to study VFP help to provide some answer... and GetFldState is more on top than GetNextModified... and I did not test my solution as obviously...
Olaf Doschke

Pavel,

you just were a victim of the misleading help description. Nobody always tests all his answers thoroughly. Some tips come from the back of our minds and are easily corrected by halfways knoledgable developers anyway. It's often an idea that counts and not the concrete code.

The valuable advice is neither GetNextModified nor GetFldState. You find the other, once you read about the one. The essential thing we agree is, that 200 tables is just too much.

Bye, Olaf.
fmoore0001

ASKER
Pavel,

Thank your final suggest was the key.  The SAVEs sail now.  Good fix!

Olaf,

That was rather a blanket statement on the number of files needed to be opened.  I have over 383 tables in this database, 200 in the Borrower screen, with about 1/3 actively reading/writing at any given time.   This is a very complex Mortgage office system and I have not one more file open than necessary any any given time, per my clients needs.  The buffering is necessary- someimes you have to stop and start again.  It is easily the most complex program I have ever written in 30 years in Xbase, let alone VFP.  it is called i-Programs (www.goldenomega.net).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pavel Celba

Points should have been assigned to Olaf as well. He fixed my bad assumptions about GetFldState and introduced the correct GetNextModified solution.
Olaf Doschke

Hi Frank,

it wasn't only my concern. But you're right.  It's not my business, I take it for granted. But your statement also is rather blank. "It's complex" can be used as a justification for anything. I take it for granted and not bother any more about this.

Bye, Olaf.
fmoore0001

ASKER
Pavel,  You are right.  Olaf, sorry I missed you.

On the point assignment I do normally assign points to all helpers, and you are right I should have included Olaf for the additional info he supplied.   I jumped on your code answer, being in a hurry.  I guess there is no way to reassign points once done :(

Any ideas?  Or just owe Olaf the next time you guys jointly give me an answer :)

Frank
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Pavel Celba

OK, I'll remember I have some Olaf's points. :-)