LIMIT in FoxPro

Question

I am trying to extract ALOT from fox pro database, work with it and paste it into mysql db. There is too much to do all at once, so want to do it in batches of, say, 10,000 records. Which is equivalent to LIMIT 5, 10 in Fox Pro SQL, I would like to select an instruction like

select a name, address from the limit of people 5, 10;

those. get only 10 results starting from the 5th. Take a look around the Internet and they only mention the top, which is clearly not very useful.

Answer 1

FoxPro does not have direct support for the LIMIT clause. It has "TOP nn", but it provides only the "highest records" in a given percentage and even has a limit on 32k records returned (maximum).

You might be better off dumping the data as a CSV, or if this is impractical (due to size problems) by writing a small FoxPro script that automatically generates a series of BEGIN-INSERT (x10000) - COMMITs that upload a series of text files. Of course, for this you will need the FoxPro development environment, so this may not apply to your situation ...

Answer 2

Take a look at the RecNo () function.

Answer 3

It is very easy to bypass the LIMIT clause with the TOP clause; if you want to extract from the _start entry for the _finish entry from a file named _test, you can do:

[VFP]

** Taking _start <= _finish if you do not get a top sentence 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]

Answer 4

I had to convert the Foxpro base to Mysql a few years ago. What I did to solve this problem was to add the auto-incrementing index column to the Foxpro table and use this as a link to the row.

So you can do something like.

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

Foxpro sql documentation does n't show anything like a constraint.

Answer 5

To extend the answer to Eyvind, I would create a program to use the RecNo () function to pull records in a given range, for example, 10,000 records.

Then you could programmatically cycle through a large table in chucks of 10,000 records at a time and transform the loading of data into your MySQL database.

Using the RecNO () function, you can be sure that you do not insert lines more than once, and you can restart at the knowledge point during the data loading process. That in itself can be very convenient in case you need to stop and restart the boot process.

Answer 6

Here, adapt this to your tables. Took me like 2 minutes, I do it waaaay too often.

N1 is a group no matter and make sure you have max (id), you can use recno () to create correctly sorted

N2 - joins N1, where ID = Max Id from N1, displays the field you want from N2

Then, if you want to join other tables, put it all in brackets and give it an alias and include it in the 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 

Answer 7

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

Answer 8

Depending on the number of rows returned and if you use the .NET Framework, you can compensate / limit the resulting DataTable as follows:

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

Remember to add Assembly System.Data.DataSetExtensions.