SCAN loops : Continuing the discussion 7

[Mike Lewis]

There was a thread here last week about the behaviour of SCAN loops - in particular, certain traps that developers might fall into. The entire thread was removed by Tek Tips management because of offensive language by the OP.

Despite that, the thread did contain some useful points, and I thought it would be worth continuing the discussion. In particular:

1. It is rarely necessary to test for EOF() within a SCAN loop. In other words, code like this is rarely necessary:

SCAN
  IF EOF()
    EXIT
  ENDIF
ENDSCAN

By definition, the the SCAN loop will terminate at the end of file.

2. A SCAN loop on an empty table does nothing. It will simply go straight from the SCAN to the ENDSCAN.

3. The behaviour of a SCAN loop on a table that only contains deleted records will depend on the setting of DELETED. With DELETED ON, it is the same as if the table was empty. With DELETED OFF, the records are processed as usual.

4. Point 1 does not necessarily apply if the loop includes one or more SKIPs. If the SKIPs take you beyond EOF, there is no error generated.

5. The SCAN loop always starts at the top of the table. There is never any need to do this:

GO TOP
SCAN
  ...
ENDSCAN

6. I don't think this was mentioned in the thread, but it's worth remembering that SKIP loops will operate on the correct alias, even if you change the work area within the loop. In other words, in code like this:

SELECT Table1
SCAN

   && do something
   
   SELECT Table2
   && do something with another table

  SELECT Table1
ENDSCAN

the final SELECT is unnecessary. The SCAN will automatically apply the correct alias when looping back to the start.


I hope this is of interest. Feel free to add your comments and suggestions (but without the insults and personal attacks, of course).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

One interesting point, for me at least, if there is no need/advantage in doing a seek to position a starting record (in the default case)
because SCAN always starts at the first in-scope record in the currently selected order - unless the REST or NEXT x clause is used.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Dan Olsson]

And of course there is the somewhat underestimated:

SCAN FOR <condition>
DO something
ENDSCAN

Which is superfast if <condition> matches an index

 

[vernpace]

Mike,

Looks like you covered it. I would like to add one thing:

Long processes can be executed in a SCAN loop. For example, we have a project that backs up 10 SQL Server FILESTREAM databases using TSQL and SPT. Each database has many GBs of FS data, so the entire backup process can take a long time:

LOCAL llSuccess. llError
SELECT cur_dbbackup

SCAN FOR cur_dbbackup.ilsn > 1
     llSuccess = BackupDatabase(dbName)

     IF llSuccess
        llSuccess = BackupLog(dbName)
     ENDID

     IF NOT llSuccess
        llError = .T.
        EXIT
     ENDIF
ENDSCAN


*!*    Quality check
FUNCTION BackupDatabase(tcDatabase AS String, tcDataPath AS String) AS Logical
   LOCAL lcCmdUpd, lcDataPath, lcAlias, liHandle, llReturn
   STORE "" TO lcCmdUpd, lcDataPath

   IF NOT EMPTY(tcDataPath) AND VARTYPE(tcDataPath) = "C"
      lcDataPath = ADDBS(tcDataPath) + tcDatabase + ".bak"
   ELSE
      IF NOT GetDBBackUpPath(tcDatabase, @lcDataPath, .F.)
         RETURN .F.
      ENDIF
   ENDIF

   lcAlias = ALIAS()
   liHandle = GetLBHandleMaster()

   IF liHandle <= 0
      RETURN .F.
   ENDIF

TEXT TO lcCmdUpd TEXTMERGE NOSHOW
USE master
BACKUP DATABASE <<tcDatabase>> TO DISK = '<<lcDataPath>>' WITH INIT, FORMAT, CHECKSUM
SELECT @@ERROR
ENDTEXT

   IF SQLEXEC(liHandle, lcCmdUpd, "sqlErrors") = 1 AND RECCOUNT("sqlErrors") = 1
      llReturn = (sqlErrors.exp = 0)
   ENDIF

   SQLDISCONNECT(liHandle)

   IF USED("sqlErrors")
      USE IN sqlErrors
   ENDIF

   IF USED(lcAlias)
      SELECT (lcAlias)
   ENDIF

   RETURN llReturn

ENDFUNC

While this process runs in an out-of-process COM server, the important point is that SCAN can stay on a record for a long time (running a process) before moving to the next record. It is worth noting that long processes within a SCAN loop should be encapsulated such that the SCAN is not disturbed by various IO events - if not using a COM EXE, the WINDOWs ShowCursor and BlockInput APIs can acheive the desired results.

 

[Mike Lewis]

One of the other questions raided in the earlier thread (by Griff, if I remember right) was what would happen with something like this:

SCAN FOR MyFunc()
  * do stuff
ENDSCAN

where MyFunc() does something to move the record pointer. Might be worth experimenting?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thanks for your original note, Mike(Lewis). Glad this is being tidied, although I suspect that there may have been some history and perhaps other persons may have helped the process along (as well as the op).

Out of interest, who are the 'Tek Tips management'. I am grateful for thie Tek-Tips service, but how did you learn of this decision - or did you just deduce the reason by observing that the thread had been removed and guessing the probable reason.

Thanks again. Andrew

 

[Chris Miller]

I think it was different, but anyway, the format was "guess the outcome" and in that way check your understanding of loops.

I remember Rick showed how a SCAN loop can be compromised as Griffs suggested by his observation SCAN reselects the same Workarea at each loop iteration. And Rick showed you can easily replace the data in that workarea during the loop. The same is true though, when your exit condition is EOF('alias') and you replace what's used with an alias during the loop.

You can always fool loops intentionally, if you do quirky things. I talked about programming without side effects as one principle to keep in mind.

And I remember I asked how something like this, with a skip in the loop body, behaves:

CREATE CURSOR OddOrEven (data c(40))
APPEND BLANK
SCAN
   ? RECNO(), EOF()
   SKIP 1
   ? RECNO(), EOF()
ENDSCAN

APPEND BLANK
SCAN
   ? RECNO(), EOF()
   SKIP 1
   ? RECNO(), EOF()
ENDSCAN

Chriss

 

[Mike Lewis]

Glad this is being tidied, although I suspect that there may have been some history and perhaps other persons may have helped the process along (as well as the op).

That's right, Andrew. I was just one of several people who contributed to the original thread.

Out of interest, who are the 'Tek Tips management'. I am grateful for thie Tek-Tips service, but how did you learn of this decision

There is in fact a real person at Tek Tips who actually manages the forum, although I don't think he actually appears here in person. He will usually delete posts that contain abusive language or personal attacks on forum members. However, he (or she?) doesn't actually monitor the posts. Instead, he relies on forum members to report any inappropriate posts.

If you come across a post that contains personal insults or other inappropriate language, or looks like spam or a scam, you should click the "Report" icon in the bottom right corner of the post. Then enter a brief description of what you think is wrong with the post. Usually, that will result in the post being deleted.

In the case of the thread under discussion (the one on the behaviour of SCAN loops on which the present thread is based), I know that several forum members reported the offending post. I suspect that the entire thread was deleted because it was the OP who was reported, and the post in question was the first one in the thread. When the management delete a post, all subsequent posts in the thread are probably removed as well, which I assume is what happened in this case.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[tomk3]

Hello,

thanks for all the info.

What does scan/endscan if there is an index but the value representing the index is changed in the loop ?
It seems to skip records

SELECT 0
CREATE CURSOR c_dummy (pk I, lname c(20), done c(1))
INSERT INTO c_dummy (pk,lname) VALUES (1,"LA1")
INSERT INTO c_dummy (pk,lname) VALUES (1,"LA2")
INSERT INTO c_dummy (pk,lname) VALUES (1,"LB1")
INSERT INTO c_dummy (pk,lname) VALUES (1,"LA4")
INSERT INTO c_dummy (pk,lname) VALUES (1,"LA3")
INSERT INTO c_dummy (pk,lname) VALUES (1,"LB2")
INDEX on lname TAG lname
SCAN 
 REPLACE done WITH "X"
 IF lname ="LA4"
  REPLACE lname WITH "ZZ"
 ENDIF  
ENDSCAN 
BROWSE

(ok, I will never do it, just for interest)

What if done in scan for ?
What happens if index tag is changed during scan (ok, there is indexseek , but someone may use set order to / seek)
recommendations ? documentation on that ?

regards
tom

 

[Mike Lewis]

Nice one,Tom. What you are saying is that if the table has a controlling index, and if the value of the index field (or, rather, expression) changes during the Scan, the Scan will follow the new index order.

In your example, you are changing an index value to one later in the index sequence, so the Scan is jumping forward and skipping records. Conversely, if you changed it to a value earlier in the index sequence, the Scan would presumably jump backwards and possibly process some records more than once.

As you say, this is not something you would ever do. But it's good to be aware of these behaviours.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Oh I once made that mistake and thankfully found out by cases that exited too early rather than cases that caused an infinite loop.

One good protection against such effects, if you really need to update in the data you scan with an effect on the order is first copy the essential fields into a cursor that's just used for scanning without modifying it. Sync to the record in the original workarea (for example by a relation or by SCAN FOR SEEK(..). The advantage of the loop cursor being active vanishes, which besides other things is a reason to use SQL-Update for such things and not need to worry about how the iteration itself is done by the sql engine.

Chriss

 

[Rick C. Hodgin]

The compromised SCAN loop:

CLEAR
CREATE CURSOR c_test (cField c(3))
FOR lnI = 1 TO 5
    INSERT INTO c_test VALUES (TRANSFORM(lnI))
NEXT

SELECT * ;
    FROM c_test ;
    WHERE RECNO() <= 3 ;
    INTO CURSOR c_test2 READWRITE

SELECT c_test
lnWorkArea = SELECT()
SCAN
    IF RECNO() = 2
        lnRecno = RECNO()
        USE
        USE (DBF("c_test2")) AGAIN ALIAS c_test3
        GOTO lnRecno
    ENDIF
    ? ALIAS(), RECNO(), RECCOUNT()
ENDSCAN

Have been busy these last days.

--
Rick C. Hodgin

 

[Rick C. Hodgin]

SCAN FOR <condition>
    DO something
ENDSCAN

Reminded me of a movie line from way back in the day:

SCAN FOR pick_up_a_broom
    DO something
ENDSCAN

--
Rick C. Hodgin

 

[Mike Lewis]

Reminded me of a movie line from way back in the day:

SCAN FOR pick_up_a_broom
    DO something
ENDSCAN

Sorry, Rick. I don't get it. Please explain.

(I don't suppose it has anything to do with curling?)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Clever Mike.

Could be a Harry Potter reference?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Rick C. Hodgin]

It's a quote from the movie Friday. Smokey was complaining to his mother that she doesn't do anything. He asked her to "Pick up a broom! Do something!" The "do something" part made me cue on it.

I remember all these movie quotes from back in the day having grown up a person of my culture. Haven't seen the movies since the 90s or early 00s though. I used to be able to quote Top Gun from beginning to end with emphasis, tone, everything. Such useless trivia / knowledge up there in my brain.

--
Rick C. Hodgin

 

[Mike Lewis]

Rick, I'm confessing my ignorance here. I'd never heard of that movie. I've heard of Friday the 13th, Friday Night Lights, The Long Good Friday, and best of all His Girl Friday. But not just plain Friday. Ah, well. Never mind.

But I did learn something new as a result of this exchange: that in a Tek Tips post, you can nest

 tags within [quote] tags (as per my previous post).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

[url=http://www.ml-consult.co.uk/foxstuff.htm]Visual FoxPro articles, tips and downloads[/url]

 

[Rick C. Hodgin]

It's not a good movie from a family friendly point of view, but it had a huge cult following. It's where the term "Bye, Felicia" comes from.

--
Rick C. Hodgin

 

[Chris Miller]

Oh, I think I saw a list of (movie) quotes that sound like FoxPro. Starting with USE theforce, for example. And in that context the anti jedi command FLUSH FORCE.

But I don't remember other things and I don't find that list with a simple google search.



Chriss

 

[Rick C. Hodgin]

There's a lot of things from my youth I wish I had to do over again.

But learning FoxBASE+ isn't one of them. Learning x86 assembly isn't another. And spending the time I did reading through the Intel IA-32 Architecture manuals (back then called 80486, Pentium, and Pentium Pro manuals) also isn't another.

I do wish I could've completed Visual FreePro. One of my notable regrets / sadnesses.

--
Rick C. Hodgin