In trying to find the "demons" in this legacy VFP app (shared/used by both desktop and internet/website users), I find there's a lot of APPEND BLANKS everywhere followed by an immediate REPLACE (and sometimes more than 1). Here's a sample of my findings:
SELECT notes APPEND BLANK REPLACE notes.id WITH order.invoice REPLACE notes.dtg WITH DATETIME() LOCAL lnnotes_id lnnotes_id = notes.notes_id = TABLEUPDATE(.T.) GOTO BOTTOM = SEEK(lnnotes_id, 'notes', 'notes_id') SET ORDER TO ID thisform.pgf1.page5.grdnotes.coldetail.setfocus()
Now, 1st, I'd say "switch this over to explicit INSERT INTO MyTable (ID, Dtg) VALUES (order.invoice,datetime())" Also, not sure why they're go to the bottom and then reposition the record pointer immediately afterwards. That's wasted moves/cycles, imo, although perhaps minuscule.
If the user did NOT have table buffering in place, I can see a bigger knock against this approach because with every update, the table's CDX indexes would all have to be updated (if those fields were involved). The backend table's CDX file isn't updated on changes to the buffered cursor.
Question: if you update fields that are indexed, I know the index file has to update to reflect those changes. If you update fields that are NOT IN ANY INDEXES, there's no update needed for the CDX then...right?
Any other tips?
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
Hi Mike, I have seen this coding style sometimes. Yes, you can change the APPEND BLANK / REPLACE combos with an INSERT INTO without any negative siedeeffects.
The part with that GO BOTTOM / SEEK() was normally done for repositioning other tables, which are linked with a SET RELATION. You could substitute four lines (together with that "lnNotes" stuff) with a simple GO RECNO(), which does a reposition to the current recno together with a buffer refresh.
Interestingly in the SEEK, they use a TAGname "notes_id", but later on do a SET ORDER TO "id" , not "notes_id". Was this a failure on your "copy code" job, or do they really have two tags?
For your third question: Index files are only updated, if indexed fields (or expressions) are touched (means: they get updated in any way). Writing to non-indexed fields does not trigger index files updates.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von MB Software Solutions, LLC Gesendet: Mittwoch, 2. Oktober 2019 19:42 An: profox@leafe.com Betreff: APPEND BLANK with immediate field values set right after
In trying to find the "demons" in this legacy VFP app (shared/used by both desktop and internet/website users), I find there's a lot of APPEND BLANKS everywhere followed by an immediate REPLACE (and sometimes more than 1). Here's a sample of my findings:
SELECT notes APPEND BLANK REPLACE notes.id WITH order.invoice REPLACE notes.dtg WITH DATETIME() LOCAL lnnotes_id lnnotes_id = notes.notes_id = TABLEUPDATE(.T.) GOTO BOTTOM = SEEK(lnnotes_id, 'notes', 'notes_id') SET ORDER TO ID thisform.pgf1.page5.grdnotes.coldetail.setfocus()
Now, 1st, I'd say "switch this over to explicit INSERT INTO MyTable (ID, Dtg) VALUES (order.invoice,datetime())" Also, not sure why they're go to the bottom and then reposition the record pointer immediately afterwards. That's wasted moves/cycles, imo, although perhaps minuscule.
If the user did NOT have table buffering in place, I can see a bigger knock against this approach because with every update, the table's CDX indexes would all have to be updated (if those fields were involved). The backend table's CDX file isn't updated on changes to the buffered cursor.
Question: if you update fields that are indexed, I know the index file has to update to reflect those changes. If you update fields that are NOT IN ANY INDEXES, there's no update needed for the CDX then...right?
Any other tips?
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
On Thu, Oct 3, 2019 at 4:51 AM juergen@wondzinski.de wrote:
Hi Mike, I have seen this coding style sometimes. Yes, you can change the APPEND BLANK / REPLACE combos with an INSERT INTO without any negative siedeeffects.
Well a minor difference, I think: APPEND BLANK / REPLACE changes the RECNO() to the record being added to, where INSERT INTO may not. If other code tries a REPLACE without locating the proper record, you could end up changing the wrong one following an INSERT INTO. A good reason to be cautious when mixing XBase with SQL is the first thinks there is a "current record" where SQL does not.
On 03/10/2019 14:15, Ted Roche wrote:
Well a minor difference, I think: APPEND BLANK / REPLACE changes the RECNO() to the record being added to, where INSERT INTO may not. If other code tries a REPLACE without locating the proper record, you could end up changing the wrong one following an INSERT INTO. A good reason to be cautious when mixing XBase with SQL is the first thinks there is a "current record" where SQL does not.
Just looked at the help file and it says "After executing the *INSERT* command, Visual FoxPro positions the record pointer on the new record."
I'm sure I have some code that may rely on this somewhere. Just wondering why you say INSERT INTO "may not"?
Peter
This communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960
On Thu, Oct 3, 2019 at 9:50 AM Peter Cushing pcushing@whisperingsmith.com wrote:
Just looked at the help file and it says "After executing the *INSERT* command, Visual FoxPro positions the record pointer on the new record."
I did say "I think" as my Fox days are pretty much behind me.
I'm sure I have some code that may rely on this somewhere. Just wondering why you say INSERT INTO "may not"?
Don't confuse the INSERT [BLANK] command ( https://www.tedroche.com/hackfox7/s4g058.html) with the INSERT - SQL command (https://www.tedroche.com/hackfox7/s4g080.html) which are two different things.
When you execute one of the SQL commands, you are telling Fox what you want it to do, but not how to do it. The SQL interpreter may open the target file(s) in new work areas, with temporary aliases and perform the INSERT-SQL, UPDATE-SQL or DELETE-SQL without affecting the current work area or record pointers.
See a bigger discussion in "SQL - The Original" at https://www.tedroche.com/hackfox7/s1c4.html or Tamar Granor's excellent book, Taming Visual FoxPro's SQL, http://hentzenwerke.com/catalog/tamingvfpsql.htm
Sorry but SQL INSERT always does position on the new record. This is documented even in the helpfile:
---------------------------------------------------------------------------- - If the table you specify is open, SQL INSERT appends the new record to the table. If the table is open in a work area other than the current work area, it is not selected after the record is appended; the current work area remains selected.
If the table you specify is not open, Visual FoxPro opens it in a new work area, and the new record is appended to the table. The new work area is not selected; the current work area remains selected.
While the SQL INSERT command is executing, the current work area becomes the area into which the new record is inserted. In other words, when the SQL INSERT command is executed, it is in the context of the table being inserted into regardless of the current work area before the command was issued.
After executing the INSERT command, Visual FoxPro positions the
record pointer on the new record. <<<<<
Visual FoxPro updates the _TALLY system variable with the number of rows inserted if you include a SQL SELECT statement. For more information, see _TALLY System Variable. ---------------------------------------------------------------------------- -
wOOdy
[excessive quoting removed by server]
On Thu, Oct 3, 2019 at 10:45 AM juergen@wondzinski.de wrote:
Sorry but SQL INSERT always does position on the new record. This is documented even in the helpfile:
OK, w00dy, thanks for the correction. My VFP knowledge is legacy these days ¯_(ツ)_/¯.
Ask me about MariaDB 10 or PHP 7 on Linux :)
I think the key point is that if you "ASSuME" the record pointer in a table is in the correct place, you can get burnt. I've seen grids/browse refresh change the current record, or a UDF fired off a timer, or even a menu option (I'm thinking a WHEN?) that wiggles pointers, or multiple forms/application instances interfere. I'm a big fan of the "belt and suspenders" method of updating tables, so
REPLACE ThatTable.ThatColumn WITH "ThisValue" FOR ThatTable.PrimaryKey = TheRIghtValue AND ThatTable.ThatColumn = "OldValue"
or:
UPDATE ThatTable SET ThatColumn="ThisValue" WHERE ThatTable.PrimaryKey = TheRightValue AND ThatTable.ThatColum = "OldValue"
-
juergen＠wondzinski.de -
MB Software Solutions, LLC -
Peter Cushing -
Ted Roche