Newbie - How do I read one record at a time from a results table?

[smsmail]

Please help me!!!!!

I want to be able to read one record at a time from a result table (i.e resulting from a SELECT statement).

With DB2, you can create a CURSOR, so as to be able to read one record and a time using the FETCH command.

How is this accomplished with FOXPRO. I have read some about FOXPRO cursors, but I am not sure if it is exactly what I am looking for.

PLEASE HELP ME!!!!

 

[Mike Lewis]

Welcome to the forum and to Visual FoxPro.

To get the results of your SELECT into a cursor, just add INTO CURSOR XXX (where xxx is the name you wish to give the cursor) to the SELECT.

To retrieve one record at a time from the cursor, use a SCAN / ENDSCAN construct. For example, the following code will display the ID field from each record in the cursor, one per line:

SELECT ID, Name, Address FROM Customers ;
  WHERE City = "Paris" ;
  INTO CURSOR Parisians

SELECT Parisians

SCAN 
  ? ID
ENDSCAN

The SCAN takes you to the top of the cursor. The ENDSCAN goes to the next record, then loops back to the SCAN. This is repeated until you reach the end.

The second SELECT statement in my example is not a SQL SELECT. It is a FoxPro command that makes a particular cursor or table the current one.

Hope this helps. Come back if you need to ask anything else.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

In fact a foxpro cursor is a table, just a temporary table stored in the TEMP folder, if stored to disc at all.

You can also USE a table (open it) and then SCAN through it.

Even without a scan you can GOTO Recno, SEEK, LOCATE a record or SKIP +/-N records and then access that record, foxpro is very much record oriented. If you have open a table in a datasession in a workarea you ca see that using the datasession window. use the command SET from the command window to open that datasession window.

the current table, cursor, view or in general alias will be marked here or you can click on some alias name to make it current.

Besides browse to see the whole set of records you can access the current record with aliasname.fieldname, quite like an object property.

Each alias has a record pointer you can influence by the commands given and surely some more I forgot to mention, you're always at one record when working with foxpro data, even while you browse a table you are at one record marked in the browse window.

You can work set oriented with SQL or record oriented with scuh commands as APPEND (BLANK) IN alias, DELETE (NEXT 1) IN alias, REPLACE field WITH value IN alias. where the alias clause is optional and defaults to the current ALIAS().

Bye, Olaf.