Tuesday, October 03, 2006

FoxPro's Sometimes-Mysterious Replace

"The English country gentleman galloping after a fox - The unspeakable in full pursuit of the uneatable."
-Oscar Wilde


The REPLACE command is a powerful command that allows developers to update fields in a table using the currently selected record (NEXT 1), a scope, or a set of records. It is also faster than SQL-UPDATE. The basic syntax is easy to understand, but its simplicity (REPLACE SomeFIeld with SomeValue) can get some developers into trouble – especially when no error or warning is raised.

Here are some examples:

1. REPLACE will not replace anything under the following conditions:

a. When the currently selected work area is at EOF:

**********************************************************
CREATE CURSOR myCursor (mydecimal N(8,2) )
APPEND BLANK IN myCursor
CREATE CURSOR myEOFCursor (foo c(1))
REPLACE myCursor.mydecimal WITH 111.00
*!* Nothing happens
**********************************************************


To fix code like this, use the "IN" clause of the REPLACE statement.

**********************************************************
REPLACE myCursor.mydecimal WITH 111.00 IN myCursor
*!* Works like a charm
**********************************************************


b. Likewise, the following code doesn't do a thing. Although expected, there is no warning or error that the REPLACE failed:

**********************************************************
CREATE CURSOR myCursor (mydecimal N(8,2) )
REPLACE myCursor.mydecimal WITH 123.00
**********************************************************


c. Consider this seemingly harmless code:

**********************************************************
CREATE CURSOR myCursor (some_int i , some_char c(3))
INDEX ON some_int TAG some_int
FOR x = 1 TO 100
APPEND BLANK
NEXT
LOCATE
SCAN
REPLACE some_int WITH RECNO() IN myCursor
REPLACE some_char WITH ALLT(STR(RECNO())) IN myCursor
ENDSCAN
**********************************************************


You would be surprised to see the results (only the first record is replaced). Now comment out the line "INDEX ON some_int TAG some_int" and rerun the code. The moral here has more to do with how FoxPro handles indexes than how it handles the REPLACE command. It is better to turn off indexes (or create them after a replace) when indexed field rely on the replace to populate it. Someday I may take the time to explain what FoxPro is doing behind the scenes.


2.) REPLACE will automatically round decimals that do not fit:

**********************************************************
CREATE CURSOR myCursor (mydecimal N(8,2))
APPEND BLANK IN myCursor
REPLACE mydecimal WITH 12.119 IN myCursor
*!* This stores the value 12.12 into the cursor
**********************************************************



3.) If the value doesn't fit at all, VFP will truncate the decimal!

**********************************************************
REPLACE mydecimal WITH 12345678.119 IN myCursor
*!* This stores 12345678 into the field,
*!* eliminating the decimal entirely
**********************************************************



Additional notes regarding the REPLACE command:

1.) If updating multiple fields, do not use this syntax:

**********************************************************
REPLACE firstname WITH "Tod" IN contact
REPLACE lastname WITH "McKenna" IN contact
**********************************************************


The above syntax forces foxpro to execute the command twice. Use this instead:

**********************************************************
REPLACE firstname WITH "Tod" ,;
lastname WITH "McKenna" IN contact
**********************************************************


2.) You may only include up to 128 statements using the above technique

...

To summarize, follow these rules when using REPLACE:


  1. Always use the "IN" clause!

  2. Examine decimal data and decide if boundary checks are necessary before a REPLACE.

  3. _TALLY will let you know if a REPLACE occurred. If you want to be absolutely sure that a REPLACE happened, you can try to ASSERT _TALLY>0 after your REPLACE command. This will alert you while testing your application that a REPLACE didn't occur.



Your questions and comments are welcome!

-Tod

No comments: