VFP beyond 2GB

Hi Take help from following Microsoft article regarding this

http://msdn.microsoft.com/en-us/library/ms175890%28v=sql.90%29.aspx

FoxPro cannot handle a table more than 2GB. The database can be over 2GB. So to be able to read data more than 2GB, you need to have them in a separate table.

I am not sure that SQL Querying these tables into one in memory would help either. The limitation is that FoxPro is 32-bit. If it was 64-bit it would have handled it. However, if your query gets some records and not all, then it's possible.

Let's say you have identical tables.
SELECT table1.field1, table1.field2 FROM table1 WHERE condition1
UNION ALL
SELECT table2.field1, table2.field2 FROM table2 WHERE condition2

Here are some examples:
http://en.allexperts.com/q/FoxPro-1817/combining-dbf-1-foxpro.htm
http://www.componentace.com/sql/select-union.htm
http://stackoverflow.com/questions/4432763/append-contents-of-muliple-tables-visual-foxpro

Captain answered your question. I would add some notes...

It is always better to work with smaller data files in FoxPro. The data access is much faster then.

2 GB is theoretical maximum in FoxPro which is better to keep untouched in many cases... We have a few 2 gigs files and the querying is rather slow even when we have 256 GB memory and these tables are read from the cache obviously...

I would recommend to split such a large tables into several parts. You should keep one core table containing small fields necessary for data filtering and querying (i.e. used in FOR and WHERE clauses) and the rest can be in separate tables and retrieved when needed. The "large data" tables are connected to the core table by some integer ID column (indexed, of course).

OTOH if you really need tables over 2 GB limit then you may use another data engine, e.g. Lianja (http://www.lianja.com/) or Sybase advantage database server (http://www.sybase.com/products/databasemanagement/advantagedatabaseserver) which both can read DBF data over 2 GB. CodeBase library also supports DBF files over 2 gigs (mentioned here: http://computer-programming-forum.com/2-vfp/e25f379ce0af18bd.htm).

If you don't need DBF data format then look for any SQL engine like MySQL, MS SQL Server etc.

Useful links:
http://foxpro.ntsl119.com/archives/00000406.html
http://fox.wikis.com/wc.dll?Wiki~ProblemswithLargeDBFs%5CFPTs

Thanks for relevant comments. However, it does not bring me forwards. I saw some years ago a case study of a big project, something related to a construction project for the english channel. Foxpro was used and the 2 GB limit was overcome using UNION. I'm interested to see this, or some other solution of the same type. I'm aware of alternative options, but that is not a solution at present.

Could you please clarify if you still mean one single DBF file having the size > 2 GB?

I don't know the case study mentioned but I could say I know FoxPro and I have to say to manage one single DBF file having size over 2 GB is not possible in FoxPro. And it does not matter if UNION or anything else is used... The internal file pointer has 4 bytes in FoxPro which means 2 GB addressing (one bit is used for the unnecessary sign).

Of course, if we use exact wording "Visual FoxPro database" means many DBF files (tables) each of them up to 2 GB... So the Visual FoxPro database can contain hundreds gigabytes of data independently on UNIONs used or not.

UNION is just one element of SQL-SELECT command (a part of DML - data manipulation language). You can use UNIONs to put data from several tables each one up to 2 GB together. So if all these tables are in one database you could say "SQL UNION can manage databases beyond 2 GB".

BUT (again) UNION (SQL-SELECT) creates either a DBF table or temporary table (cursor) which cannot exceed 2 GB in size.

Do you really think such case study can be useful somehow? I would guess it is a dead end for your development... Isn't it better to use recommendations from above answers?

BTW, we have 47 GB of data managed by FoxPro but no one file exceeded 2 GB. Large memo files are self-manageable. If the size exceeds certain level a new file is created automatically.

Just to add to Pavel's excellent explanation....

If your VFP application were using a non-'native' data backend such as M$ SQL Server, your Data Tables could indeed be quite a bit larger than might be limited in a VFP Data table.

Then you could have your VFP application make a SQL Server Query into that database and retrieve a sub-set of records from the desired tables such that the 2GB limit was not exceeded.

That does not require the use of UNION, but it could be utilized.

For example the FTC Do Not Call list (currently a 2.4GB TXT document) would exceed the 2GB limitation if it were to be put into a single VFP data table and therefore not be usable.  
But the same data in a single M$ SQL Server table is readily usable via SQL Queries (returning record sub-sets) through ODBC from a VFP application.

Good Luck

to manage databases beyond 2 GB.

Also keep in mind that "Databases" is not the same as "Data Tables"

Yes, in the past the use of the word got mixed up, but a "Database" is a container (sometimes "Intelligent", sometimes not) for one or more Data Tables.

As has been said rather clearly above -- In the VFP World (and M$ Access as well) each individual Data Table FILE SIZE (the DBF, CDX, IDX, FPT files) may not exceed 2GB.

Good Luck

Thanks, Olaf.
/Krister