VFP9 with CDX concerns ?

[FoxProMatt]

Robert - "At shutdown" could be too late to mimic current VFP behavior.

Presently in VFP, as soon as you issue REPLACE command, the data is written to file right then (unless Table Buffering is used). To delay that write-to-disk operation at all would not be in line with how VFP works now, and it could have effects on VFP apps, as you never know what kind of other code people might execute following a REPLACE command that expects to find the data already written to disk.

[lumberjack]

Hi Dex,

Hello Johan,
[ "If you FieldPut("ColName", ColValue) you should be fine. And just add a COMMIT afterwards." ]

I don't think FieldPut has a "field name" overloads? it only accept "field index"

Yes you are correct! FieldPut("ColName", <ColValue>) is part of the DbServer class.

[robert]

Matt,

Robert - "At shutdown" could be too late to mimic current VFP behavior.

Presently in VFP, as soon as you issue REPLACE command, the data is written to file right then (unless Table Buffering is used). To delay that write-to-disk operation at all would not be in line with how VFP works now, and it could have effects on VFP apps, as you never know what kind of other code people might execute following a REPLACE command that expects to find the data already written to disk.

So you say that if I write:

Code: Select all

REPLACE FirstName WITH "Matt"
REPLACE LastName WITH "Slay"

that FoxPro will issue 2 writes to the DBF ? I find that hard to believe.

I suspect that FoxPro will update a buffer in memory and when it detects that you are ready then it will update the DBF and its indexes.

If It would not do that and if there is an index on LastName+FirstName then these 2 write operations would involve 4 writes:
1) Update FirstName in the DBF
2) Update the index with the new Firstname and old LastName
3) Update LastName in the DBF
4) Update the index again with the new FirstName and new LastName

Even when you ignore the performance fit this would take, it also does not make much sense to write a partial transaction to disk (e.g. only an updated FirstName).

Most implementations of file sharing that I have seen do it differently:

1) Write the fields to a local buffer and mark the buffer "Hot"
2) When the record pointer is moved or the file is "committed" then a "GoCold" operation is performed. In this operation:
a) the buffer is written to disk
b) the "before" and "after" results of each index expression are calculated. When these are different then the old key is deleted from the index and the new key is inserted.

The only thing that happens immediately is an Append:
1) The header of the DBF is locked
2) A blank record is added to the end of the DBF this record is locked
3) The record count in the header of the DBF is updated
4) The header is unlocked
In the "GoCold" operation after an append the system "knows" that it does not have to delete old index keys and only insert the new index keys.

But maybe you are right. I will use a tool such as ProcMon from Sysinternals to look and see what VFP is doing.

Robert

[Karl-Heinz]

The problem only arises when I used a DBF file that has a CDX file, REPLACE command is not suffice. and I need add to COMMIT and CLOSE it just to reflect the changes T_T

Hi Dex,

the values are also written if the record pointer is moved after the REPLACE, e.g. doing a GO TOP or a SKIP 0.

Code: Select all

FUNCTION Start() AS VOID STRICT
    
    SELECT 0 
    USE "C:TEMPTESTDB.DBF" SHARED
    
    APPEND BLANK
    *   
    Replace DOC_TYPE with "RR"  ,;
            DOC_NO   with "12345"   

    SKIP 0 // <-------------

   
    WAIT "Press a key to exit ^_^"

    
RETURN

regards
Karl-Heinz

[DexterZ]

"Presently in VFP, as soon as you issue REPLACE command, the data is written to file right then (unless Table Buffering is used). To delay that write-to-disk operation at all would not be in line with how VFP works now,
and it could have effects on VFP apps, as you never know what kind of other code people might execute following a REPLACE command that expects to find the data already written to disk. " -- Matt


Exactly Matt , I believe you are correct.


" So you say that if I write: "

Code: Select all

REPLACE FirstName WITH "Matt"
REPLACE LastName WITH "Slay"

that FoxPro will issue 2 writes to the DBF ? I find that hard to believe. " -- Robert


@Robert I believe this is also correct in VFP, because that's already 2 commands, that's why I normally wrote in this following manner a single command replacing multiple fields

Code: Select all

REPLACE FirstName WITH "Matt"  ,;
        LastName  WITH "Slay"

Dex ^_^y

[DexterZ]

The problem only arises when I used a DBF file that has a CDX file, REPLACE command is not suffice. and I need add to COMMIT and CLOSE it just to reflect the changes T_T

Hi Dex,

the values are also written if the record pointer is moved after the REPLACE, e.g. doing a GO TOP or a SKIP 0.

Code: Select all

FUNCTION Start() AS VOID STRICT
    
    SELECT 0 
    USE "C:TEMPTESTDB.DBF" SHARED
    
    APPEND BLANK
    *   
    Replace DOC_TYPE with "RR"  ,;
            DOC_NO   with "12345"   

    SKIP 0 // <-------------

   
    WAIT "Press a key to exit ^_^"

    
RETURN

I tried it and it works karl, but that's odd : ) but it works, ^_^y

Many thanks,
Dex

[FoxProMatt]

So you say that if I write:

Code: Select all

REPLACE FirstName WITH "Matt"
REPLACE LastName WITH "Slay"

that FoxPro will issue 2 writes to the DBF ? I find that hard to believe.

Robert - here is one test I just performed:

1. In one instance of running VFP IDE, I call the USE command on a DBF with SHARED, then I started a second instance ot VFP IDE app on my machine and USE the same DBF with SHARED.

2. In session 1 I issue one REPLACE command on one field. I do not move the pointer and I do not make any other command or function calls.

3. I switch over to the other running instance of VFP IDE on my machine and I call the BROWSE command, and I see the changes from the REPLACE command issued in session.

From this it seems clear that VFP updated the DBF immediately.

Granted, this is behavior in the IDE, and not running VFP apps, but I do not have time to test in running VFP EXE apps at this moment.

Oh, one more point... This DBF does have a CDX, but the field I wrote to was not one of the fields referenced in any of the indexes.

[kevclark64]

On a bit of tangent ... we've found that when using a server the timing of the writes also depends on the caching settings of the server. At SWFox in 2018 Christoph Wollenhaupt suggested turning FileInfoCacheLifetime, FileNotFoundCacheLifetime, and DirectoryCacheLifetime off, which we have now done.

Disabling these three caching items has stopped a pretty bad problem we were having where we would:

1) Insert a new record into a table that has an autoinc id field
2) read the new autoinc id value with GetAutoIncValue
3) select the new record from the table where id=AutoIncValue

In such a case we could do the insert and we could get the AutoIncValue but the select statement returned no records. (Of course, if you stepped through the code it worked perfectly due to the slower timing.) Someone mentioned using FLUSH but that didn't help either.

[robert]

"
" So you say that if I write: "

Code: Select all

REPLACE FirstName WITH "Matt"
REPLACE LastName WITH "Slay"

that FoxPro will issue 2 writes to the DBF ? I find that hard to believe. " -- Robert


@Robert I believe this is also correct in VFP, because that's already 2 commands, that's why I normally wrote in this following manner a single command replacing multiple fields

Code: Select all

REPLACE FirstName WITH "Matt"  ,;
        LastName  WITH "Slay"

Apparently VFP adds an implicit "commit" for each REPLACE statement. VO and other XBase dialects do not do that. But this should be fairly easy to add to the REPLACE UDC. Something like this:

Code: Select all

#command REPLACE <f1> WITH <v1> [, <fN> WITH <vN> ]                     ;
      => _FIELD-><f1> := <v1> [; _FIELD-><fN> := <vN>] ; DbCommit()

Robert

[FoxProMatt]

Robert - What about the Table Buffering mechanism that is very commonly used in VFP apps that access DBFs?

You got something up your sleeve for that?