REPLACE command

[millsrs]

Greetings All!

I am working on an old FoxPro 2.0 for DOS system. Things usually work well but recently I bumped into the following situation:

1) From within FoxPro via the Command Window, a new table was created normally.

2) However, a Date field, a Numeric field and 1 of 4 Character fields behaved surprisingly. (Size is not the issue.)

3) At the Command Window putting data of each type into a properly Declared and Initialized variable and then using the REPLACE command to store the corresponding variables into the proper fields in the new table there was NO problem.

4)When doing the exact same populating process using the REPLACE command within a program the value from the correct variable would NOT be stored in the table's corresponding field. (IT WOULD, however, store values put into it from another table's field of correct data type.)

5) The debugger was used to make sure that the above was truly happening and the debugger clearly showed the table fields NOT getting populated with the REPLACE command.

Any ideas or suggestions? All responses are greatly appreciated.

 

[JRB-Bldr]

First: each type into a properly Declared
You do not have to Declare Foxpro variables.

They can be initialized as one type and, while I highly DO NOT recommend it, they can be populated with a different variable type later.

Next...
Within Foxpro you can have a variable with the same name as a table field.
Foxpro will, by default, give preference to the table field of the same name.
* I am guessing that this is what is happening for you.

You should differentiate these 2 types with a 'Prefix'.
Typically that 'Prefix' is the Alias of the table for referencing the table field
And the 'Prefix' would be the letter 'm' for a memory variable.

Example:
Table: Customer with field "Name" typical reference: Customer.Name
Memory variable: "Name" typical reference: m.Name

That way Foxpro does not have to choose which one is which.

Alternatively (one of many equally valid alternatives) memory variables are referenced with one or more characters as a "Prefix' indicating to the developer (as a reminder) what the data type might be.

Example:
Memory variable (character): name that variable "cName"
Memory variable (date): name that variable "dEntered"

This would also differentiate for Foxpro between Table Field names and Memory Variable names

Hopefully this might help you out.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

It would help, if you post code.

Bye, Olaf.

 

[millsrs]

To Jrbbldr and Olaf,

Thank you for your submissions. First, I want to apologize for taking so long to respond to your comments.

jrbbldr, your response was helpful and helped some and taught me a thing or two. Olaf, your comment as to showing some code was right-on. Here are two sample code lines and their success status:

REPLACE emailtbl->amount WITH ih_net_amt (ih_net_amt is a numeric field in a second dbf table.) THIS LINE WORKS!!

REPLACE emailtbl->amount WITH nTotNetAmt (a numeric variable initialized to zero at program's start and then added to as needed.) This line DOES NOT WORK.

This is the root of my question. I look forward to your responses. Your time IS appreciated.

MillsRS

 

[Dan Freeman]

REPLACE emailtbl->amount WITH ih_net_amt (ih_net_amt is a numeric field in a second dbf table.)

So you are replacing into a workarea that is not the currently selected workarea? From your description, the workarea holding ih_net_amt is the currently selected workarea. It would error otherwise.

This has all the earmarks of the classic "replace while at end of file" issue. (Some call it a bug, but it isn't.)

If you do this:

Go Bottom
Skip
Replace (Field(1)) With "Foo"

Nothing will happen because you're on the "ghost record" but there also won't be any error.

If a workarea that isn't the currently selected workarea is the target of a replace and the target workarea is on the ghost record, no replace will happen and there will be no error. It's been that way since 1981.

I haven't experienced this problem in years because modern coding techniques all but obliterated it, but it can still be achieved! Even in the old days, *safe* coding practices avoided it most of the time.

We probably need more code/information than just two replace statements.

 

[Olaf Doschke]

As you want to put in a total: Are you by chance at the end of the emailtbl? At EOF() REPLACE has nothing to replace in, you have to be positioned on a record.

If you eg want to put a total into a report, then do so in a group or report footer and not in the table. You may use report variables to create a sum.

Bye, Olaf.

 

[DSummZZZ]

On the other hand, since the first replace works the table(s) may not be at EOF(). This raises the question of is nTotNetAm a value in the currently selected table? If so, REPLACE emailtbl->amount WITH nTotNetAm will replace amount with the table value. You may need to change the code to:
REPLACE emailtbl->amount WITH m.nTotNetAm.



-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[dbMark]

Those suggestions should work but I have another thought. I don't have the specs for FP that far back, but I do know that dBase for DOS as recent as version 5 (1995) forced you to be in the table's work area that you were updating. I use VFP 9.0 and as the others have indicated you don't need to be in a table's work area to REPLACE field data. Does FP's REPLACE command allow the "IN [workarea|alias]" parameter? If so, then try using that too to see if it makes a difference.

 

[Dan Freeman]

You've always been able to replace in a workarea other than the one currently selected, going back at least to dBase III.

It was suggested you avoid it because of the end-of-file issue, but syntactically all you need is the alias appended to the field. (In other words, the field needs to be properly scoped.)

 

[millsrs]

To all:

Many thanks for your input! The EOF scenario isn't the issue since other fields in the emailtbl table are populating just fine. (I wish that was it so I could use the suggestion.) Dan Freeman's comment about properly scoping the field is a method I used in the program. Based on all the great suggestions and the situation the way it is, I'm going to try to create a temporary table to put the numeric value into and then try to populate emailtbl's field from that one and see what happens.

Again, to all, thank you!

MillsRS

 

[JRB-Bldr]

I'd recommend that you put some Debug Breakpoints into the code (they will require you to have your TRACE/DEBUG window open to work) or, even better, add a few SET STEP ON commands at key locations within your code.

Doing off-line testing is good to get a general understanding, but for specific debugging it may not recreate the entire 'live' environment.
Working within your 'live' code itself would most likely prove more effective and 'enlightening'

If these Debug commands are in the code just prior to your REPLACE command you can then utilize the Command Window to examine the Table and/or Record situation in detail.

From that you should (hopefully) be able to fully understand what is going on.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

I would still not put away EOF, as you replace in a scan loop in all records and talk of a total you'd put last, in worst case when you're at EOF. It's no wonder all other replaces work in that case, but not the last one.

Bye, Olaf.

 

[Dan Freeman]

I agree with Olaf, and also with the suggestion to use the debugger to single-step through the process.

To have your own code stop, waive a red flag, and say 'HERE IT IS!' add EOF("emailtbl") to the debug window and step through your code. Also add your memory variables so you can see what's going on in them. It's possible the replace is actually working but the variables don't contain what you think they do.

The debugger will tell you what's actually going on, disabusing you of what you think is going on.

 

[DSummZZZ]

One more thing to note (pardon me if I somehow missed a relation earlier in the thread) is that if there is a relation set into a table and it is at EOF(), the REPLACE won't happen.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Olaf Doschke]

We talked about EOF, but it's a good hint, the EOF could be due to a non match in a relation, which puts the table at the end of the relation at EOF, just like a LOCATE with no found results puts the record pointer at EOF there.

Bye, Olaf.

 

[millsrs]

To All,

Thanks for your insights and time! Before posting this thread I used the debugger to follow the REPLACE processes comparing the related variable and it's field in the table. When the line containing the REPLACE command was stepped through the Debug window, with an entry for both the variable and the related field, didn't show the table field having the value from the variable AFTER the REPLACE command was executed.

MillsRS

 

[Dan Freeman]

That means the table is at EOF(), as explained above.

It's the only time REPLACE will "fail" (it's actually behaving as designed) without throwing an error.

 

[Olaf Doschke]

The thing to check when you are at the REPLACE line in the debugger, before you execute the line is EOF(). If it's .T. you are after the last record, there is nothing to store into at that place. REPLACE doesn't error but also doesn't do something.

Notice the EOF and BOF is talking about the FILE, not about records. EOF is after the last record, not at the last record, and even BOF does not mean you are at a record, an empty table has no record, but still is a file, in that case you are both at BOF and EOF at the same time, because the EOF mark is right after the dbf header. In a normal table with records BOF is at RECNO() 1, but EOF always is at the place after the last record, where a new record would be appended/inserted, but REPLACE can only REPLACE in an existing record, it doesn't add records.

Bye, Olaf.

 

[DSummZZZ]

Another simple way to tell besides testing for EOF() is by using RECNO("emailtbl") and RECCOUNT("emailtbl") in the watch list while you're debugging. If RECNO() is one larger than RECCOUNT(), you are at EOF().
If you actually set focus to the emailtbl using the mouse or whatever, the record pointer will no longer be at EOF().


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Dan Freeman]

I don't think Fox 2.x actually *had* a watch window as we know it today. (VFP didn't either until VFP5.)

It's the debug window (which actually behaves pretty much like today's watch window).