Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


VFP beyond 2GB

Posted on 2013-07-01
Medium Priority
Last Modified: 2013-07-03
I saw a case study some years ago about using SQL UNION to manage databases beyond 2 GB. I need to investigate how to use UNION for this purpose, so can somebody point me in the right direction for samples, utilities or case studies.
Question by:krhag28
  • 2
  • 2
  • 2
  • +3

Expert Comment

by:Vijay Pratap Singh
ID: 39292067
Hi Take help from following Microsoft article regarding this

LVL 27

Expert Comment

ID: 39292139
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
SELECT table2.field1, table2.field2 FROM table2 WHERE condition2

Here are some examples:
LVL 43

Expert Comment

ID: 39292591
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:
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39292740
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.
LVL 43

Expert Comment

ID: 39293800
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.
LVL 12

Expert Comment

ID: 39294051
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
LVL 12

Expert Comment

ID: 39294079
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
LVL 30

Accepted Solution

Olaf Doschke earned 2000 total points
ID: 39295720
You're talking about the euro tunnel project, Split of data into 2gb or 1gb chunks was used.

This project is not open source, I can't tell you if the queried against UNION of the smaller tables, but what I can tell for sure is, the result of a UNION is put into one result cursor and that is still limited to 2GB.

Bye, Olaf.

Author Closing Comment

ID: 39299105
Thanks, Olaf.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
In my humble opinion (IMHO), TouchDown from Symantec is the best in class for this type of application, but Symantec has end-of-lifed it and although one can keep using it, it will no longer be supported or upgraded.  Time to look for alternatives t…
Screencast - Getting to Know the Pipeline
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question