Fog Creek Software
Discussion Board




Fox Pro

From following along in some excellent discussions, it seems that for database centric apps Access and FoxPro tend to be highly touted.  Could someone explain the basic architecture of FoxPro and when it might be a better solution than Access.

Thanks

Ryan
Wednesday, November 27, 2002

I'll confine it to Visual Foxpro.

VFP is a file based database, every single table is a separate set of files, up to three if memo or large text/binary fields are used.  The database structure, the relational integrity, triggers and field validation and so on are also contained in a table, a database container.

The DBC also contains Views (like stored queries) of tables.  VFP conceptualises database connections which means you can use VFP code to access any remote data and use exactly the same native database manipulation syntax on that source.  That also makes heterogeneous databases logically straightforward.

The language that VFP uses is an inheritor of xBase which is an algol like language in terms of control structures, scope and conditionals.  In other words the basic syntax is easy to pick up.

Because of the xBase inheritance it includes database manipulation syntax within the language.  This means you can do row or record oriented processing along with SQL processing giving the best of both worlds.

VFP is an OO language which is integrated around that core syntax.  Objects, properties and methods are usually encapsulated in libraries which are in fact database tables.  The language includes all of the usual inheritance and polymorphism you would expect. 

You can create novel objects that have more than one type of object by creating containers and filling that container with whatever objects make sense.  Although this is often used for visual objects, components of UI you can also create non-visual components in exactly the same way.

VFP can be used for a large range of applications, you can write fat clients to remote sources, departmental applications where the database is local and native and any level in between.  There are some extremely large VFP databases and applications in use.

Apart from using standard OLE/ActiveX components there are a large range of class hierarchies, VF components and tools available.  The hierarchy I generally use is Codebase, http://www.codebase.com

Access on the other hand is more like a productivity tool, useful for small databases.  It does have a language, VBA but its database specific syntax is very restricted.  In the past Access databases have had a bad reputation for corruption, especially in transactional situations.  This might have improved.

Robust applications that seem to use Access are, for the most part, using the Jet engine, the database engine underlying Access.  These applications tend to be written in VB and can use a vast array of third party components and tools.

In general I wouldn't compare Access the product with Visual Foxpro at all.  There are comparisons to be made between using the Jet engine and VFP.  On the whole I prefer VFP largely out of familiarity and use but also because I know there are no limits with VFP.

If I need to migrate a database to remote or back end server database products I know I can take virtually the same code and with planning exactly the same code, and run with the migrated database.

That is possible with VB but you have to write the majority of that encapsulation yourself or rely on ADO to provide the data.

I could go on, but I imagine people are yawning already...

Simon Lucy
Wednesday, November 27, 2002

I think you got it right Simon.

Alberto
Wednesday, November 27, 2002

Not about the yawning that is.

Alberto
Wednesday, November 27, 2002

Just on question : how do file-based DBMSs, ie. those without a server process acting as a front-end to the actual data, handle concurrent accesses?

I looked at light DBMS's like SQLite, Tsunami, etc., but they seem to depend on the underlying OS to handle this issue, and considering how poor 9x is, I'm reluctent to use them although they're greate for stand-alone applications.

Thx
Fred.

Frederic Faure
Wednesday, November 27, 2002

At the beginning of each table is a header which gives the structure of each record, as well as a back pointer to the containing database.

When a record is locked the region of the file is locked, otherwise access is open and can be read using the same underlying file system reads.  If there are multiple clients reading that file and that record then any bottleneck will be in the server raw read.  If you build it as a server and have a single access but provide the data as XML, strings of stuff or whatever then you may win on the roundabout of raw read but lose on the swings of apartment threading.  Though I believe VFP 8 might alleviate that.

Databases which maintain their own file systems tend to only lock regions of the file space and on some applications and types of database you can get superior performance on the same hardware using an xBase table as opposed to a Jet, SQL Server or Oracle table just because of that behaviour.

I'd still probably rate VFP as the fastest file database around and comparable with back end servers  for some kinds of applications.

As with everything you can tweak things like retry counts and timeouts for handling contention issues.

Simon Lucy
Wednesday, November 27, 2002

Thx Simon for the clarification. Bottom line if using a server-less, light DBMS : Stability is only as good as the underlying OS/filesystem. In other words, it wouldn't be a good idea to use those tools on a 9x host when multiple hosts are trying to read/write into the database.

Frederic Faure
Wednesday, November 27, 2002

Well I wouldn't use 95/98/Me for anything :-)

I'd use Win2K.

Simon Lucy
Wednesday, November 27, 2002

I do too... but there's a lot of 9x/ME/XP out there... Guess I'll go with (My|Postgre)SQL...

Frederic Faure
Wednesday, November 27, 2002

I know next to nothing about FoxPro, but I would like to correct a common misunderstanding about MS Access.

It's true that Access alone isn't the best choice for large scale database apps. 

But you can connect Access to any back end you want via ODBC. 

And beginning with Access 2000, Access includes the ability to build "Access Data Projects" (or "ADP's"), which are essentially front ends to MS SQL Server that retain almost all of the RAD aspects of Access with a heavy-duty database server.    It's true that VBA doesn't have the language specific database-handling commands that FoxPro does, but that's what ADO is for (and ADP's are based on ADO).

I think the combination of Access (using ADP's) and SQL Server is probably at least as well suited for large scale apps as FoxPro (and certainly better suited than FoxPro using only its file-based data system.)

Herbert Sitz
Wednesday, November 27, 2002

Which is why I talked about Jet, the database engine.

Simon Lucy
Wednesday, November 27, 2002

And at the risk of repeating myself, Visual Foxpro has always had the ability to front endback end data sources and use all of its native database commands.

That's about 9 years now.  Not that I'm really trying to willy wave about VFP compared to Access.

Simon Lucy
Wednesday, November 27, 2002

I should be point out that ms-access in fact is really two products now. It can be used in the old traditional way, as a file share system, or as a native OLEDB system with sql-server. (these are called ADP projects).

As for the question about locking, when used as a file-share system the management of locking is actually done by creating of locking files (ldb). In other words, a small file is created. This file is then used to control the locking mechanism.

As for the programming language used, it is straight VB. The only real difference here is the forms object model is different. Other then that, you will be hard pressed to find a difference between the VBA in ms-access, and straight VB.  They both actually share the same code base (DLL library).

Thus, the one real advantage of ms-access is that the time spent to learn the system means you also learn VB. On the other hand, VB is not hard, it is always the object model you are dealing with that is hard.

As mentioned, Ms-access for the last two versions has shipped with two database engines. (remember, ms-access is simply a front end to whatever database system you use).

The first engine you get is JET. This engine can be used to a file share, or it also can be used in a ODBC fashion to a variety of database systems such as Oracle etc. (In fact, ms-access is the most popular front end to Oracle databases next to their own tools!).

The other database engine you get is the MSDE. This is a 100% compatible sql-server based engine. It means that you can then write server side code (t-sql), and also use triggers etc in ms-access. (and you can manage sql-server from inside ms-access!!). This is classic Microsoft getting you hooked on sql-server!

To use the MSDE engine, you can use JET via ODBC, but the real news is as mentioned that you can make ADP projects in ms-access. This means NO JET, and NO local tables! If you create a ADP project in ms-access, then you are creating a native oleDB connection to sql-server. You generally want to create a adp project when your application is going to be used for sql-server, or you think that the number of users will grow. Doing so, allows you to create applications with great ease, but you are in fact creating a sql-server application. This means that the performance of a two tiered application in ms-access will now perform the same as any other offering from the Microsoft stable.

The free MSDE engine from Microsoft is a SQL server compatible database engine. When you use it, you are taking true client server here. The engine is throttled in an attempt to get you to upgrade to sql-server after 5, or 10 users.

Ms-access is a incredibility popular tool In fact, some articles
rate it 2nd in use to only VB as a development tool. There is still a very strong commitment from MS, and as a result the product is becoming a very good database development system.

The product is not what it was just a few versions ago. The addition of ADP projects now means that it is a scalable and robust  tool. It can now honestly be used in the corporate market. There are applications running with excess of 1000 users connected to the same database.

Take look at all the goodies you get with
the office developer version.

http://www.microsoft.com/office/developer/suite/fastfacts.asp



Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Wednesday, November 27, 2002

>>Apart from using standard OLE/ActiveX components there are a large range of class hierarchies, VF components and tools available.  The hierarchy I generally use is Codebase, http://www.codebase.com

A few things, I went to University with, and knew Ken Sawyer (the founder of codebase.com). I used to know him on a personal bases. I Just have not kept up with him as of late. His company is of course located in my City.

Now, back to my question:

www.codebase.com is simply a high performance xBase compatible library. Why in the world would you dump the use of the VPF engine, and use codebase?  (or are you suggesting the use of the codebase server?).

I most certainly recommend codebase for a C++ project, or even a VB project where you need a high performance data engine, or you want xBase compatible files. However, you are hinting that one should dump the VPF engine, and use codebase in it place?

Do you not loose features such as referential integrity when you do this? This does not make any sense here. Perhaps you might explain why one should dump the VBF engine, and use codebase?

By the way, I absolutely  recommend the product codebase, especially when writing a commercial program in C++, and you need to have an embedded engine. In this role..code base is a winner, but you are the first person I seen to suggest to use codebase WITH VPF.


Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Wednesday, November 27, 2002

Actually that's weird, since I don't use Codebase, but Codemine.

http://www.codemine.com

Nope, I have no explanation for that :-) 

Simon Lucy
Thursday, November 28, 2002

*  Recent Topics

*  Fog Creek Home