LIMIT in FoxPro

Question

I am attempting to pull ALOT of data from a fox pro database, work with it and insert it into a mysql db. It is too much to do all at once so want to do it in batches of say 10 000 records. What is the equivalent to LIMIT 5, 10 in Fox Pro SQL, would like a select statement like

select name, address from people limit 5, 10;

ie only get 10 results back, starting at the 5th. Have looked around online and they only make mention of top which is obviously not of much use.

score 5 · Answer 1 · answered Mar 23 '09 at 12:23

5

Take a look at the RecNo() function.

answered Mar 23 '09 at 12:23

Eyvind

5,221
5
40
59

I've done this a couple of times. Add a new number column, then do a replace all with RECNO(). It is the same as an identity column. I would then follow a scenario like the one described by Mark Robinson. – Clinemi Mar 24 '09 at 15:32
1

Yeah, but you wouldn't need a new column when using recno(), you could just issue a command like SELECT * FROM people where recno() > 10 and recno() < 50 – Eyvind Mar 24 '09 at 20:51
2

Also, recno() only respects the physical order of records in the table - if you sort the records you will not get the results you want with recno() – Brian Vander Plaats Mar 26 '09 at 22:01

score 2 · Answer 2 · answered Jul 21 '15 at 17:28

2

Visual FoxPro does not support LIMIT directly. I used the following query to get over the limitation: SELECT TOP 100 * from PEOPLE WHERE RECNO() > 1000 ORDER BY ID; where 100 is the limit and 1000 is the offset.

answered Jul 21 '15 at 17:28

Nacharya

229
2
8

score 2 · Accepted Answer · answered Apr 06 '09 at 23:20

FoxPro does not have direct support for a LIMIT clause. It does have "TOP nn" but that only provides the "top-most records" within a given percentage, and even that has a limitation of 32k records returned (maximum).

You might be better off dumping the data as a CSV, or if that isn't practical (due to size issues), writing a small FoxPro script that auto-generates a series of BEGIN-INSERT(x10000)-COMMIT statements that dump to a series of text files. Of course, you would need a FoxPro development environment for this, so this may not apply to your situation...

score 1 · Answer 4 · answered Feb 10 '16 at 08:49

It is very easy to get around LIMIT clause using TOP clause ; if you want to extract from record _start to record _finish from a file named _test, you can do :

[VFP]

** assuming _start <= _finish, if not you get a top clause error

*

_finish = MIN(RECCOUNT('_test'),_finish)

*

SELECT * FROM (SELECT TOP (_finish - _start + 1) * FROM (SELECT TOP _finish *, RECNO() AS _tempo FROM _test ORDER BY _tempo) xx ORDER BY _tempo DESC) yy ORDER BY _tempo

**

[/VFP]

score 0 · Answer 5 · answered Jul 03 '13 at 23:30

Here, adapt this to your tables. Took me like 2 mins, i do this waaaay too often.

N1 - group by whatever, and make sure you got a max(id), you can use recno() to make one, sorted correctly

N2 - Joins N1 where the ID = Max Id of N1, display the field you want from N2

Then if you want to join to other tables, put that all in brackets and give it an alias and include it in a join.

Select N1.reference, N1.OrderNoteCount, N2.notes_desc LastNote
FROM
(select reference, count(reference) OrderNoteCount, Max(notes_key) MaxNoteId
from custnote 
where  reference != '' 
Group by reference
) N1
JOIN 
(
select reference, count(reference) OrderNoteCount, notes_key, notes_desc
from custnote 
where  reference != '' 
Group by reference, notes_key, notes_desc
) N2 ON N1.MaxNoteId = N2.notes_key

score 0 · Answer 6 · answered Apr 05 '16 at 12:07

Depending on the number of the returned rows and if you are using .NET Framework you can offset/limit the gotten DataTable on the following way:

dataTable = dataTable.AsEnumerable().Skip(offset).Take(limit).CopyToDataTable();

Remember to add the Assembly System.Data.DataSetExtensions.

score 0 · Answer 7 · answered Mar 23 '09 at 12:08

I had to convert a Foxpro database to Mysql a few years ago. What I did to solve this was add an auto-incrementing id column to the Foxpro table and use that as the row reference.

So then you could do something like.

select name, address from people where id >= 5 and id <= 10;

The Foxpro sql documentation does not show anything similar to limit.

score 0 · Answer 8 · edited Jan 25 '14 at 21:41

To expand on Eyvind's answer I would create a program to uses the RecNo() function to pull records within a given range, say 10,000 records.

You could then programmatically cycle through the large table in chucks of 10,000 records at a time and preform your data load into you MySQL database.

By using the RecNO() function you can be certain not to insert rows more than once, and be able to restart at a know point in the data load process. That by it's self can be very handy in the event you need to stop and restart the load process.

LIMIT in FoxPro

8 Answers8