Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
What's the difference between free tables and database tables? 2
- Thread starter Mandy_crw
- Start date
One of the main differences, if I remember correctly, is that you can have longer field names in a table under a database.
But, there are also things like stored procedures and thus triggers (code that is executed when you do things to a record
or table, such as adding a record).
The database, for all it's technical differences, is also a very 'comfortable' arrangement - giving a parental feel.
Having tables in a database makes some operations, such as iterating through all the tables to look for specific fields
very simple - although you can do that by looking for .dbf files!
Regards
Griff
Keep [Smile]ing
But, there are also things like stored procedures and thus triggers (code that is executed when you do things to a record
or table, such as adding a record).
The database, for all it's technical differences, is also a very 'comfortable' arrangement - giving a parental feel.
Having tables in a database makes some operations, such as iterating through all the tables to look for specific fields
very simple - although you can do that by looking for .dbf files!
Regards
Griff
Keep [Smile]ing
There are 10 kinds of people in the world, those who understand binary and those who don't.
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
Mike Lewis
Programmer
Mandy,
This article might help:
DBCs or free tables? The pros and cons
In summary:
- Database tables can have long field names. For me, this is an overriding benefit.
- Database tables allow you to centralise your validation rules, field captions and other properties.
- Free (non-database) tables are inherently simpler, and provide backward compatibility.
But there are several other factors as well, as the above article will explain.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
This article might help:
DBCs or free tables? The pros and cons
In summary:
- Database tables can have long field names. For me, this is an overriding benefit.
- Database tables allow you to centralise your validation rules, field captions and other properties.
- Free (non-database) tables are inherently simpler, and provide backward compatibility.
But there are several other factors as well, as the above article will explain.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
-
2
- #4
Chris Miller
Programmer
First, for future forum searchers, could you edit the thread title to "What's the difference between free tables and database tables?"
The TLDR; answer is the database node lists DBC files and their tables (DBF files) are a hierarchy level below. While the node of free tables in the project manager lists all DBFs that don't belong to a DBC. If you want a fuller picture, read on.
It all has to do with how FoxPro evolved. Let's begin with some terminology.
At first there only were free tables in FoxPro, they were called databases, actually the file extension DBF means DataBase File. They actually are just tables, not databases, as a database in the overarching computer science and information technology terminology is the hierarchy level for a set of multiple tables and views, stored procedures and more. So there can be a root of confusion no matter if you start learning in VFP9 and don't have a background knowledge in old FoxPro or FoxBase or even dBase versions, or if you actually do know the term from having knowledge about the older versions. The DBF extension actually came from dBase.
The essence of that is that database is a term with two meanings in FoxPro. Today it's used as the project manager uses it. As you are referring to the project manager treeview of Data, we can now connect that with a look at a project you also have in the Samples folder of Foxpro, the Solution project (solution.pjx):
Screenshot 1 - The Project manager Data tab of the Solution Project
You see the solution project has three databases dvds, newid, and testdata. They are not DBFs, databases in the sense of the old FoxPro versions, they are database containers, file extension DBC. I don't remember which version introduced DBCs, but I guess it was the first Windows version that Microsoft developed after acquiring FoxPro from Fox Software.
Still, Foxpro developer jargon also changed the term for DBFs from database to table and thus a database container actually better would be described as a table container, as that's what a database is, in short. But then the normal term for a table container is database and thus we just usually drop the "container" detail and call a DBC a database, just like the project manager does. And when you look into the folders of the solution project you'll see that the tables of say the testdata database are DBF files, just like the free tables are. They just belong to a DBC:
Screenshot 2 - database tables on the file level
Free tables in the Solution project point to this folder (don't be confused tables are in a File system folder called Graphics):
Screenshot 3 - free tables on the file level
The way other database systems (RDBMS) technically store tables in databases is having a database file, which includes tables. Even just talking of Microsoft products that's not only true for database servers, but also for another file based database of Microsoft: Access. And what's the difference anyway, if a "real" database server stores its databases and tables of the databases in files, anyway. What's making them a server is that they are a service to which you connect to and use the service, not directly the tables. But that's going off topic.
So to get back to the core of your question, that should now be clear. I already pointed out in above screenshots 2 and 3 how free tables and database tables are both DBFs in the file system. Differences are in the files, but that's the least important to know.
In anticipation of upcoming questions I could already point out this and other technical differences. I would like to refer to the VFP help, but I don't find a single help topic that covers all the differences. The differences of free and database tables are scattered within the help in single aspects, for example the topic "How to: Name Fields" says in its second sentence:
That's not the only difference and also not the most important advantage of database tables, as field names are not limiting how you can caption grid headers or labels in forms. Other details to mention are default values are only possible for fields in database tables, not in free tables. And within the default values, you can use FoxPro functions but more to the point also stored procedures which is code stored in the DBC. And on top of that operations on database tables, both xBASE command APPEND, REPLACE, DELETE and SQL-Statements Insert, Update, Delete and even manually using Browse Window and CTRL+Y or editing fields or using the deletion mark of the browse window, cause triggers that also are usually stored in stored procs and can do things like complex rule checks for referential integrity of data in relation to other tables. Checking whether a foreign key value you set to a foreign key field of a table actually does find a record in the referenced table with that foreign key as its primary key. But triggers are also free to use for more aspects. An insert trigger of an order could also add records for shipping history and other things automatically, for example, like having a start record of "order placed" and that in turn could trigger (technically or just in terms of the workflow) users of an order system to start packaging and processing the order. Limits are only your imagination of what to do with the call of a trigger.
Before others tell it, the most given reason against DBCs is that free table access is faster and I doubt that it's a very heavy difference, because a DBC in itself is just a table, a free table. So USE tablename instead of USE freetablename.dbf differs in that the first needs DBC access, too, for file location, long field names, etc. So, of course, there is an overhead in using database tables, it just becomes neglectable at least when you have the DBC local. But caution, that introduces difficulties with upgrading DBCs, location of DBC and DBFs, so only start using this idea when you know what you'll have to deal with in the software lifecycle of upgrades, configuration of network shares and more. With a separate DBC and local DBC for each user (which usually only is recommended for a DBC of views - for the same reasons but with less side effects), concurrent access to that file is not needed and that's just one building block of dealing with the performance problem you may or may not even encounter using DBCs.
So to make it clear: I recommend using databases (DBC) to have a fuller set of features that matches up with what database servers offer. And you don't need to worry about the caution warning above, if the DBC is just in the share with its DBFs. In a well configured network you also will not have much difficulties about the shared access to the DBC along with DBF file access.
One further conclusion: If you inherit code that uses free tables, it's not hard to change to a DBC, as you're not forced to open a table of a DBC with it's database long table name or the syntax including the exclamation mark (USE database!tablename), you can still USE filename.dbf, when a free DBF is made to a database table by the ADD TABLE command. You can still have the same USE and SQL-Selects pointing to the DBF files. Every DBF of a DBC knows its DBC and the first DBF of a DBC you open directly will open its DBC, too, to know its own long field names and default values etc. Further usage of other database tables from the same DBC also still with a direct USE filename.dbf will find the DBC is already open and cause no more overhead about that. Just as long as there is also old EXEs and DLLs that work on the free tables with older runtimes they force to use, they will lose the ability to access the modified DBFs, as their header changes with an added backlink to the DBC file they belong to. (There's a hint of a problem you may get with DBC tables and a DBC that's local with DBFs being on a central share).
Sorry for the long post, I could have get it done with just pointing out it's the difference of DBFs belonging to a DBC and standalone DBFs, which I added as TLDR; at the start. I made quite some excursions and pointed out details you don't need to know right away. If you are using free tables up until now, you may like some of the advantages, like default values that could produce a primary key other than an autoinc integer. Triggers that ay cause automatic cascades of operations in further tables, etc. On the other side, if you don't have the time for the learning curve about actually making use of all the added features, you could stay with all free tables and be backed by many other experts still preferring them over database tables.
Chriss
The TLDR; answer is the database node lists DBC files and their tables (DBF files) are a hierarchy level below. While the node of free tables in the project manager lists all DBFs that don't belong to a DBC. If you want a fuller picture, read on.
It all has to do with how FoxPro evolved. Let's begin with some terminology.
At first there only were free tables in FoxPro, they were called databases, actually the file extension DBF means DataBase File. They actually are just tables, not databases, as a database in the overarching computer science and information technology terminology is the hierarchy level for a set of multiple tables and views, stored procedures and more. So there can be a root of confusion no matter if you start learning in VFP9 and don't have a background knowledge in old FoxPro or FoxBase or even dBase versions, or if you actually do know the term from having knowledge about the older versions. The DBF extension actually came from dBase.
The essence of that is that database is a term with two meanings in FoxPro. Today it's used as the project manager uses it. As you are referring to the project manager treeview of Data, we can now connect that with a look at a project you also have in the Samples folder of Foxpro, the Solution project (solution.pjx):
Screenshot 1 - The Project manager Data tab of the Solution Project
You see the solution project has three databases dvds, newid, and testdata. They are not DBFs, databases in the sense of the old FoxPro versions, they are database containers, file extension DBC. I don't remember which version introduced DBCs, but I guess it was the first Windows version that Microsoft developed after acquiring FoxPro from Fox Software.
Still, Foxpro developer jargon also changed the term for DBFs from database to table and thus a database container actually better would be described as a table container, as that's what a database is, in short. But then the normal term for a table container is database and thus we just usually drop the "container" detail and call a DBC a database, just like the project manager does. And when you look into the folders of the solution project you'll see that the tables of say the testdata database are DBF files, just like the free tables are. They just belong to a DBC:
Screenshot 2 - database tables on the file level
Free tables in the Solution project point to this folder (don't be confused tables are in a File system folder called Graphics):
Screenshot 3 - free tables on the file level
The way other database systems (RDBMS) technically store tables in databases is having a database file, which includes tables. Even just talking of Microsoft products that's not only true for database servers, but also for another file based database of Microsoft: Access. And what's the difference anyway, if a "real" database server stores its databases and tables of the databases in files, anyway. What's making them a server is that they are a service to which you connect to and use the service, not directly the tables. But that's going off topic.
So to get back to the core of your question, that should now be clear. I already pointed out in above screenshots 2 and 3 how free tables and database tables are both DBFs in the file system. Differences are in the files, but that's the least important to know.
In anticipation of upcoming questions I could already point out this and other technical differences. I would like to refer to the VFP help, but I don't find a single help topic that covers all the differences. The differences of free and database tables are scattered within the help in single aspects, for example the topic "How to: Name Fields" says in its second sentence:
VFP help said:
That's not the only difference and also not the most important advantage of database tables, as field names are not limiting how you can caption grid headers or labels in forms. Other details to mention are default values are only possible for fields in database tables, not in free tables. And within the default values, you can use FoxPro functions but more to the point also stored procedures which is code stored in the DBC. And on top of that operations on database tables, both xBASE command APPEND, REPLACE, DELETE and SQL-Statements Insert, Update, Delete and even manually using Browse Window and CTRL+Y or editing fields or using the deletion mark of the browse window, cause triggers that also are usually stored in stored procs and can do things like complex rule checks for referential integrity of data in relation to other tables. Checking whether a foreign key value you set to a foreign key field of a table actually does find a record in the referenced table with that foreign key as its primary key. But triggers are also free to use for more aspects. An insert trigger of an order could also add records for shipping history and other things automatically, for example, like having a start record of "order placed" and that in turn could trigger (technically or just in terms of the workflow) users of an order system to start packaging and processing the order. Limits are only your imagination of what to do with the call of a trigger.
Before others tell it, the most given reason against DBCs is that free table access is faster and I doubt that it's a very heavy difference, because a DBC in itself is just a table, a free table. So USE tablename instead of USE freetablename.dbf differs in that the first needs DBC access, too, for file location, long field names, etc. So, of course, there is an overhead in using database tables, it just becomes neglectable at least when you have the DBC local. But caution, that introduces difficulties with upgrading DBCs, location of DBC and DBFs, so only start using this idea when you know what you'll have to deal with in the software lifecycle of upgrades, configuration of network shares and more. With a separate DBC and local DBC for each user (which usually only is recommended for a DBC of views - for the same reasons but with less side effects), concurrent access to that file is not needed and that's just one building block of dealing with the performance problem you may or may not even encounter using DBCs.
So to make it clear: I recommend using databases (DBC) to have a fuller set of features that matches up with what database servers offer. And you don't need to worry about the caution warning above, if the DBC is just in the share with its DBFs. In a well configured network you also will not have much difficulties about the shared access to the DBC along with DBF file access.
One further conclusion: If you inherit code that uses free tables, it's not hard to change to a DBC, as you're not forced to open a table of a DBC with it's database long table name or the syntax including the exclamation mark (USE database!tablename), you can still USE filename.dbf, when a free DBF is made to a database table by the ADD TABLE command. You can still have the same USE and SQL-Selects pointing to the DBF files. Every DBF of a DBC knows its DBC and the first DBF of a DBC you open directly will open its DBC, too, to know its own long field names and default values etc. Further usage of other database tables from the same DBC also still with a direct USE filename.dbf will find the DBC is already open and cause no more overhead about that. Just as long as there is also old EXEs and DLLs that work on the free tables with older runtimes they force to use, they will lose the ability to access the modified DBFs, as their header changes with an added backlink to the DBC file they belong to. (There's a hint of a problem you may get with DBC tables and a DBC that's local with DBFs being on a central share).
Sorry for the long post, I could have get it done with just pointing out it's the difference of DBFs belonging to a DBC and standalone DBFs, which I added as TLDR; at the start. I made quite some excursions and pointed out details you don't need to know right away. If you are using free tables up until now, you may like some of the advantages, like default values that could produce a primary key other than an autoinc integer. Triggers that ay cause automatic cascades of operations in further tables, etc. On the other side, if you don't have the time for the learning curve about actually making use of all the added features, you could stay with all free tables and be backed by many other experts still preferring them over database tables.
Chriss
Mike Lewis
Programmer
By the way, you mention "free tables found in the project manager". Whether the table appears in the project manager has nothing to do with whether it is a free table or not. You can place an entire database in the project manager, in which case you can then drill down to see the tables (and views, etc). Or you can work perfectly happily with no tables at all showing in the project manager.
One other point: As well as tables, a database can store views (local and remote) and connections. That might be relevant if you are working with a back-end database. It can also store persistent relationships, although I personally don't find that very useful.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
One other point: As well as tables, a database can store views (local and remote) and connections. That might be relevant if you are working with a back-end database. It can also store persistent relationships, although I personally don't find that very useful.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
Chris Miller
Programmer
Mike Lewis said:
I find that a bit misleading. I don't think you mean the aspect that not every free table in the directory of the project is listed in the project manager. The project manager always only shows what's been added to the project, of course.
But you make me wonder, if I drag a DBF into the project manager window, that is a DBF of a DBC, and I drag it explicitly into the node "Free tables", does the project manager automatically sort it in as database table including the DBC node with the database file name or will it be listed under free tables even though it isn't.
And in short this leads to a Project Error message:
A bit cut off, but I think it's clear: You cannot betray the project manager. At least not this way.
So you can be quite sure what the project manager lists under free tables are actually free tables.
There's another thing Mike and I both didn't mentioned yet, but if you think of it it's very clever and also obvious: Many things in VFP like reports, forms, class libraries, and also the database container itself, are information about a collection of similar items, a report has a group of report controls, a database is a list of tables, mainly, a form is a group of controls, a class library - perhaps the simplest to see - is a list of classes. and what would you do to store such information? A table, of course, that's what VFP is specialized on. And that's what VFP does, too. an SCX, a DBC, an FRX, also a PJX project, all are free tables, just with different file extension than DBF. And they are just treated specifically by their file extension.
You could get behind this secret, as the help tells it, indirectly. When you search for ".dbf" in the help, you most likely find the help topic "File Extensions and File Types" which gives a great overview of which file extension means what. And the secret, that very many of these file types are tables is revealed in the "See also" reference section in the link "Table Structures of Table Files (.dbc, .frx, .lbx, .mnx, .pjx, .scx, .vcx)". If you know (or learn from that help topic) what all those file extensions are, you hereby learn, that all of these files are indeed tables - free tables.
That's the basis of several tools about changing things without using the usual VFP IDE designers. I think the first time I encountered this was as starting a form caused an error saying something about record N of the scx being broken. If you realize this means an SCX has records and thus is a table and you USE some.scx you can see the data stored about the SCX and may be able to actually fix the error by editing the record given by that error message.
It's very useful to know, but also not recommended to delve into without further knowledge. Some of the data stored within fields of the table are a binary format not documented and so fiddling with anything in it might make things unusable by the designers of the VFP IDE or even break things completely. Even just changing some readable portion in a memo might need to also fix a binary part before the readable text to reflect the changed length.
Chriss
Mike Lewis
Programmer
Chris said:
That's not what I'm seeing, Chris. If I drag a DBC-bound DBF into the project manager, and drop it under Free Tables - then the entire DBC is added to the Database node.
Conversely, if I drop a free table in the Database node - or even in a database within that node - it is correctly placed in the Free Tables node.
In fact, going further, it appears that you can drop any supported file type (forms, reports, PRGs) anywhere in the project manager, and VFP will place it in the right place. This also allows you to drop multiple files of mixed types in the project manager. In fact, isn't that a fairly common way of populating a project from an existing folder full of files?
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
Another feature of using databases instead of free tables is the ability to use BEGIN TRANSACTION and END TRANSACTION. I use these in all my table updates. This allows me to recover gracefully if an error occurs during one of the table updates by doing a ROLLBACK. The ROLLBACK will restore all the tables before the update began.
Example:
The second insert command will cause a failure due to the undeclared variable value (will be .F.) and will be caught in the CATCH. The CATCH will issue a ROLLBACK which removes the first inserted row.
Example:
Code:
TRY
[indent]BEGIN TRANSACTION[/indent]
[indent][indent]INSERT INTO mytable (id, field1, field2) VALUES (1, "value 1", "value 2")[/indent][/indent]
[indent][indent]INSERT INTO mytable (id, field1, field2) VALUES (2, "value 3", lcUndeclared)[/indent][/indent]
[indent]END TRANSACTION[/indent]
CATCH TO loException
[indent]ROLLBACK[/indent]
ENDTRYThe second insert command will cause a failure due to the undeclared variable value (will be .F.) and will be caught in the CATCH. The CATCH will issue a ROLLBACK which removes the first inserted row.
Mike Lewis
Programmer
Ggreen,
You are right of course about the usefullness of transactions. But did you know that VfP 9.0 supports transactions for free tables, thanks to the MAKETRANSACTABLE() function? There is also a ISTRANSACTABLE() function, which does what its name suggests.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
You are right of course about the usefullness of transactions. But did you know that VfP 9.0 supports transactions for free tables, thanks to the MAKETRANSACTABLE() function? There is also a ISTRANSACTABLE() function, which does what its name suggests.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
Chris Miller
Programmer
Mike,
I wasn't adding a DBC, but a DBF of a DBC (The orders.dbf of the testdata.dbc, see the path in the error message screenshot) to the Free Tables node of the project manager.
In fact I planned drag&drop, but it didn't work at all (the crossed circle icon indicated forbidding drop into the project manager window) so I picked Free Tables, then clicked Add and then chose a dbf of a dbc.
Chriss
I wasn't adding a DBC, but a DBF of a DBC (The orders.dbf of the testdata.dbc, see the path in the error message screenshot) to the Free Tables node of the project manager.
In fact I planned drag&drop, but it didn't work at all (the crossed circle icon indicated forbidding drop into the project manager window) so I picked Free Tables, then clicked Add and then chose a dbf of a dbc.
Chriss
Mike Lewis
Programmer
Chris, I just rpeated my tests from yesterday - just to double-check I did them right - and I am still seeing the same thing: that you can drag any table (free or DBC-bound) into either the Database or Free Table node, and VFP correctly puts it in the right place.
However, if I try to add a DBC-bound table to Free Tables by using the Add button, I see the error message that you mentioned.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
However, if I try to add a DBC-bound table to Free Tables by using the Add button, I see the error message that you mentioned.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
Chris Miller
Programmer
Mike,
thanks for that. I think something's wrong with my installation as Drag&Drop of files into the project manager is forbidden. I think the drop target is the project manager treeview frame anyway, it doesn't matter where exactly you drop, does it?
I remember you could easily recreate a broken PJX by simple dragging all project files into a new empty pjx, but something breaks it. aybe because I used bereznikers registry keys to not start a new VFP IDE session when double clicking a DBF, but I'm not at all sure the drag&drop behavior is controlled by registry entries, it should simply work.
Chriss
thanks for that. I think something's wrong with my installation as Drag&Drop of files into the project manager is forbidden. I think the drop target is the project manager treeview frame anyway, it doesn't matter where exactly you drop, does it?
I remember you could easily recreate a broken PJX by simple dragging all project files into a new empty pjx, but something breaks it. aybe because I used bereznikers registry keys to not start a new VFP IDE session when double clicking a DBF, but I'm not at all sure the drag&drop behavior is controlled by registry entries, it should simply work.
Chriss
- Status
