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.

Jeremie

Answer 1

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...

Loritalorn

Answer 2

Take a look at the RecNo() function.

Septavalent

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. – Statuary

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 – Septavalent

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() – Daemon

Answer 3

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.

Finegan

Answer 4

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]

Propertied

Answer 5

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.

Turbine

Answer 6

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.

Fleshly

Answer 7

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

Epithalamium

Answer 8

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.

Jacintha