Table / Cursor Needs Tableupdate

fmoore0001
fmoore0001 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Author

Commented:
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
It is probably not necessary to call the TABLEUPDATE twice. Same effect has just the second call. Also you should test GetFldState result for 3 and 4 digit existence, so:
IF atu(m.i, 1) <> "FPWERROR"
  IF EMPTY(CHRTRAN(GETFLDSTATE(-1, atu(m.i, 1)), "1", ""))
    llSuccess = .T.
  ELSE
    llSuccess = TABLEUPDATE(.T.,.T., atu(m.i, 1))
ENDIF

Open in new window


Additional enhancement would be to test EOF because GetFldState returns NULL when the record pointer is at EOF.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Olaf DoschkeSoftware Developer

Commented:
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.
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 DoschkeSoftware Developer

Commented:
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.

Author

Commented:
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).
Points should have been assigned to Olaf as well. He fixed my bad assumptions about GetFldState and introduced the correct GetNextModified solution.
Olaf DoschkeSoftware Developer

Commented:
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.

Author

Commented:
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
OK, I'll remember I have some Olaf's points. :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial