Solved

VFP beyond 2GB

Posted on 2013-07-01
9
1,191 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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 6

Expert Comment

by:Vijay Pratap Singh
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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
 

Author Comment

by:krhag28
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, Olaf.
/Krister
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Veeam Backup & Replication has added a new integration – Veeam Backup for Microsoft Office 365.  In this blog, we will discuss how you can benefit from Office 365 email backup with the Veeam’s new product and try to shed some light on the needs and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now