Solved

VFP beyond 2GB

Posted on 2013-07-01
9
1,302 Views
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.
/Krister
0
Comment
Question by:krhag28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 6

Expert Comment

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

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

Expert Comment

by:CaptainCyril
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
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
0
 
LVL 42

Expert Comment

by:pcelba
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:
http://foxpro.ntsl119.com/archives/00000406.html
http://fox.wikis.com/wc.dll?Wiki~ProblemswithLargeDBFs%5CFPTs
0
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 

Author Comment

by:krhag28
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.
0
 
LVL 42

Expert Comment

by:pcelba
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.
0
 
LVL 12

Expert Comment

by:jrbbldr
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
0
 
LVL 12

Expert Comment

by:jrbbldr
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
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 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.
0
 

Author Closing Comment

by:krhag28
ID: 39299105
Thanks, Olaf.
/Krister
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
With the rising number of cyber attacks in recent years, keeping your personal data safe has become more important than ever. The tips outlined in this article will help you keep your identitfy safe.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

622 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