VFP 9 bug referring to other tables while current work area is empty. 1

[Canonian]

Running VFP 9 SP 2 on Win 11 and I think I have discovered a bug.

When the current work area/local DBF is empty (no records) and you try to update data in another work area that has records the update does not happen.
Simple program example below, let's say we have two tables order_temp and orders, order_temp has no records (empty) and orders has 10 records and the current record is #1.

use orders
select 0
use order_temp
replace orders.order_date with date() <---- this will fail, no error, it executes the command however the order_date field in the orders table will be the old value and not update.
If however I do this

use orders
select 0
use order_temp
append blank
replace orders.order_date with date() <---- the command works perfectly. I'm not sure why this only happens when the current work area is empty.

Any ideas? Is there a patch for this?

 

[Steve Meyerson]

Try REPLACE Order_date WITH DATE() IN Orders

It shouldn't matter which area you're in at the time.

 

[Canonian]

Yes that works however why does the other fail? I've used alias.fieldname for ages and never had a problem?

 

[Chris Miller]

Well, if your current table is empty, you're both at BOF() and EOF() in it.
And it's well known and documented REPLACE does not work at EOF(). Also, if you REPLACE someothertable.field, even if someothertable is not at EOF(), that REPLACE will still only be done and work, if you're NOT at EOF() in the current table.

That's not a bug.

Chriss

 

[mJindrova]

A small example:
[pre]CLEAR

CREATE CURSOR xxone (xx000 I)
SELECT xxone
APPEND BLANK
REPLACE XX000 WITH -2
APPEND BLANK
REPLACE XX000 WITH -1
GO TOP


*******************************************


CREATE CURSOR xxtwo (xx000 I)

SELECT xxtwo
REPLACE xxone.XX000 WITH 1
?STRTRAN("select area %ALIAS% is empty", "%ALIAS%", ALIAS())
?"record affect", _TALLY
?"xxone.xx000", xxone.xx000
?"xxtwo.xx000", xxtwo.xx000

USE IN ("xxtwo")

*******************************************
CREATE CURSOR xxtwo (xx000 I)


SELECT xxtwo
APPEND BLANK
REPLACE xxone.XX000 WITH 1 ALL
?
?STRTRAN("select area %ALIAS% not is empty", "%ALIAS%", ALIAS())
?"record affect", _TALLY
?"xxone.xx000", xxone.xx000
?"xxtwo.xx000", xxtwo.xx000

USE IN ("xxtwo")


*******************************************
CREATE CURSOR xxtwo (xx000 I)

SELECT xxtwo
APPEND BLANK
APPEND BLANK

lixxoneS=RECNO('xxone')
lixxtwoS=RECNO('xxtwo')

REPLACE xxone.XX000 WITH 1 ALL
lixxoneE=RECNO('xxone')
lixxtwoE=RECNO('xxtwo')
?
?STRTRAN("select area %ALIAS% not is empty - more records", "%ALIAS%", ALIAS())
?"record affect", _TALLY
?"xxone.xx000", xxone.xx000, 'from record',lixxoneS, 'to record', lixxoneE
?"xxtwo.xx000", xxtwo.xx000, 'from record',lixxtwoS, 'to record', lixxtwoE
USE IN ("xxtwo")


**********************************************
CLOSE ALL

[/pre]

mJindrova

 

[Chris Miller]

I can do one more thing: I can setup this situation on Windows 10 instead of 11, since you're likely also assuming it's caussed by Win 11.

Close Data All
Cd GetEnv("TEMP")
Erase modifythis.*
Erase emptytable.*

Create Table modifythis.dbf Free (cData1 char(10))
Append Blank
REPLACE modifythis.cData1 with "Startval"

Select 0
Create Table emptytable.dbf Free (cData2 char(10))

Replace modifythis.cData1 with "Changed"
? modifythis.cData1

Replace cData1 with "Changed" In modifythis
? modifythis.cData1

So, when you do - as you say - use alias.fieldname as I do in the first REPLACE, that does not work, as the REPLACE still originates from the current workarea, that has the empty table, where you are at EOF().

In the alternate second REPLACE, I did not change current workarea, but I explicitly ask the REPLACE to be done IN the workarea named "modifythis", where the record pointer is on the one record of that table and NOT at EOF(), then it works.

What's true is that REPLACE can work on multiple workareas and specifying alias.fieldname is helpful for that, but you have to specifically ensure the main workarea that is either the workarea specified by the IN clause or, if not specified by IN is by default the current workarea are NOT at EOF(). To showcse that, let's even involve a third workarea, though nothing in it is changed:

Close Data All
Cd GetEnv("TEMP")
Erase modifythis.*
Erase emptytable.*
Erase noneoftable.*

Create Table modifythis.dbf Free (cData1 char(10))
Append Blank
REPLACE modifythis.cData1 with "Startval"

Select 0
Create Table emptytable.dbf Free (cData2 char(10))

Select 0
Create Table noneoftable.dbf Free (cData3 char(10))
Append blank

Select emptytable
Replace modifythis.cData1 with "Changed1"
? modifythis.cData1
Replace modifythis.cData1 with "Changed2" In noneoftable
? modifythis.cData1
Select noneoftable
Replace modifythis.cData1 with "Changed3"
? modifythis.cData1

If this outputs Startval, then Changed2 and Changed3 everythig is okay with your Foxpro.

Chriss

 

[Chris Miller]

Besides all that and Martina's example, there also is a sound reason about all this in conjunction with RELATIONS.

If you setup relations between tables and then do a REPLACE in all of them, you'll select the root table RELATIONS start to further tables (maybe even hierarchical over two or three levels) and then do REPLACES:

1. If you're at EOF in that root table, the replations should mean the record pointer is at EOF in all tables involved in relations. There is no sensible thing to do, but nothing
2. If you're not at EOF in the root current workarea but at EOF in some of the related tables, that just means they have no detail record related to the root data and therefore a REPLACE in them should do nothin (and also not error)

There are more details to this about SET SKIP and 1:n or 1:n:m etc relations and how REPLACE with NEXT n caluses then work, but all in all it's very natural to exclude workareas at EOF() from REPLACEMENTS, also depending on the root and/or current workare EOF state. It's a natural decision.

Chriss

 

[Chris Miller]

One more thing.

It's good you got more precsie about the situation.

When the current work area/local DBF is empty (no records)

You could also mean a workarea with nothing opened in it, a la the situation when you SELECT 0. EOF() then is .F., because there is no table where you're positioned anywhere at all.

You'd have another problem than EOF, in that case, though. Extend my last example with:

Set TablePrompt Off && optional, but not doing this you'll have to wait for a few seconds timeout
Select 0
Replace modifythis.cData1 with "Changed4"
? modifythis.cData1

This will result in the error "No table is open in the current workarea."

It wasn't your case, but for sake of completeness that case also won't work. REPLACE behavior really very much depends on the current workarea, even if it's not involved by all alias names in the REPLACE pointing to other workareas, REPLACE always at core replaces in the current workarea or the workarea specified by the IN clause, which does not change current workarea of the datasession but makes REPLACE consider it "as if". So that's also good to know, you can formulate your REPLACES with an IN clause to neither depend on the current workarea nor change the current workare and still have a foreseeable result.

Chriss

 

[Mike Lewis]

This is not a bug. It is a well-known situation and is well documented.

The point is that REPLACE is scoped to the current record, and the scoping applies to the current work area, which might not be the work area containing the table that is being updated.

In this case, you are updating Orders. But the current work area contains Order_Temp. If Order_Temp is empty or is at EOF, then there is no current record, so nothing is being updated.

The solution is always to use the IN clause with REPLACE. In this case, you would do [tt]REPLACE orders.order_date WITH date() [highlight #FCE94F]IN Orders[/highlight][/tt]. Alternatively, use a SQL UPDATE command, which works exactly as you would expect.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dobs_seemore]

Thanks for all the replies. Still seems logical that you could update other tables while the current area is at bof/eof using alias.fieldname or fieldname in alias. I'll have to start switching my code over to IN ALIAS.

thanks again

 

[Chris Miller]

...using alias.fieldname or fieldname in alias

Well, the second one is an option, the alias.fieldname without in clause implicitly means IN currentworkarea and then the EOF rule applies. It's a hard by design rule that you have to take into account.

Chriss

 

[Dobs_seemore]

I understand that now, I thought the alias.fieldname was telling it IN the other work area as I'm not referring to anything in the current one. Strange after all these years I run across this, always finding something new to me, old to others.

 

[Chris Miller]

I'll have to start switching my code over to IN ALIAS

While you're at it, now it's clear [tt]REPLACE alias.field WITH value[/tt] isn't precise and only works by chance of the current workarea not being at EOF.
[tt]REPLACE alias.field WITH value IN alias[/tt] will fix that, but the most concise formulation is [tt]REPLACE field WITH value IN alias[/tt].

And that pretty much compares to SQL-UPDATE, where writable fields are only in the main table sepcified by [tt]UDATE Tablename...[/tt].

With the REPLACE command you can update in multiple aliases and then you still only can specify one alias in the IN clause. It also only makes sense if you go to well define locations in the other aliases or let RELATIONS do this job. There's the reason to be able to specify alias.fieldname, it's not possible because it's sufficiently precise in itself. It's actually not.

Chriss

 

[Dobs_seemore]

REPLACE field WITH value IN alias. Another good things about this is the shortness of it as opposed to what I was doing replace alias.fieldname, alias.fieldname which makes for long replace statements.

 

[Chris Miller]

as opposed to what I was doing

Well, you can pat yourself on the shoulders as you did use one REPLACE for multiple fields. It's not uncommon to see many REPLACE statements for the same table in succession, which is worse programming style.

Chriss

 

[Mike Lewis]

Tamar describes the problem better than I can. This is from the Hacker's Guide:

The fields do not all have to be in the same table. However, there's a gotcha here. The Scope, FOR and WHILE clauses are applied to the controlling table (usually the current work area). If you reach EOF() in that table, no further replacements take place in any table. Despite much yelling by many Xbase programmers over the years, this really isn't a bug. It's supposed to work this way and it's as good a choice as any other behavior in this situation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mJindrova]

Hmm,

MAX, MIN, SUM, COUNT, AVG and long text in "one" step:

[pre]CREATE CURSOR xxresult (xxmin N(11) NULL, xxmax N(11) NULL, xxcnt N(11) NULL, xxavg n(10,3) NULL, xxsum N(11) NULL, xxmemo M)
INSERT INTO xxresult (xxmin, xxmax, xxavg, xxsum, xxmemo) VALUES (NULL, NULL, NULL, NULL, "")

CREATE CURSOR xxtwo (xx000 N(11), xx001 C(1))
=RAND(-1)
FOR m.lii=1 TO 100
INSERT INTO xxtwo (xx000) VALUES (RAND()*100)
NEXT

SELECT xxtwo
REPLACE xx001 WITH 'S', ;
xxresult.xxmin WITH MIN(xx000, NVL(xxresult.xxmin, xx000)),;
xxresult.xxmax WITH MAX(xx000, NVL(xxresult.xxmax, xx000)),;
xxresult.xxcnt WITH 1+NVL(xxresult.xxcnt, 0),;
xxresult.xxsum WITH xx000+NVL(xxresult.xxsum, 0),;
xxresult.xxavg WITH xxresult.xxsum/xxresult.xxcnt,;
xxresult.xxmemo WITH NVL(xxresult.xxmemo, '')+;
'Current value: '+LTRIM(STR(xx000, 11, 0))+;
'; Min value: '+LTRIM(STR(xxresult.xxmin, 11, 0))+;
'; Max value: '+LTRIM(STR(xxresult.xxmax, 11, 0))+;
'; Count: '+LTRIM(STR(xxresult.xxcnt, 11, 0))+;
'; Sum: '+LTRIM(STR(xxresult.xxsum, 11, 0))+;
'; Average: '+LTRIM(STR(xxresult.xxavg, 10, 3))+;
CHR(13);
ALL

GO TOP
BROWSE NORMAL nowait

SELECT xxresult
BROWSE NORMAL

CLOSE ALL
[/pre]

mJindrova

 

[TamarGranor]

Thanks, Mike. I was trying to decide whether to add anything to this thread, but since you already quoted me, I won't bother. (And FWIW, I think I wrote that bit. Can't always tell whether any given part is me or Ted, but I think that's mine.)

Tamar