Why create database

[NasibKalsi]

Hi All:

I am not sure if it has benefits of using database instead of free tables. I see that it is good for documentation.
I could use any table of the database without opening the database. I am currently working on a small application and trying to start using database.

Any hints appreciated.

Regards


Nasib Kalsi

 

[Olaf Doschke]

It's really a question coming from the wrong century. You start using a feature, which has been introduced in what? VFP5? 3? I don't know, I always used it since I started in VFP6, and I was never unsatisfied with lowered performance or something like that. It's like saying you'd start using mobiles, instead of a landline phone, when everybody already is at smartphones.

It's true, that you can use a dbf of a dbc the way you always with free dbs, by specifying the full path, and the dbc will be opened automatically. That was surely implemented by Microsoft as an easy way for migratin to DBCs. Have you learned that just recently? I ask, because I mentioned it in some other thread.

What's even more comfortable is, you can OPEN DATABASE your.dbc and then don't have to care for paths of DBFs anymore, you just USE dbcname!tablename. You even can skip the dbcname, if it's the currently set database and use tablename, like with free DBFs, but you don't need to CD into the database directory. And alone is fine for me, because I like my default dir e the EXE dir, to easily access any subfolders with templaets, reports not included in the EXE and such stuff and access them with relatives paths from the EXE loacation.

Some swear it's faster to use free DBFs. But how much faster is it? How many tables do you plan to have in a database? Are you adding any temp table you generate (and if so, why not use cursors?). In a large database I have about 100 tables, Views ccan be in a seperate DBC, even local to the user, so your DBC file, which is a free table, is small, even for a large database. And so it's fast, if you trust free DBFs.

DBC advantages and features have been discussed many times, so I'll not begin to list them. Just the one, that may be most important: Transactions. You MAKETRANSACTABLE a free table, but thatwas introduced very late, when everybody using DBC already had that feature for several VFP versions. This is alone is worth any small percentage slowdown of dataaccess.

Bye, Olaf.

 

[Olaf Doschke]

To add:

I myself am neither a smartphone nor mobile user. So I am a very rare species of landline phone only users, but I don't have the need to have some computer with me on the go. It's enough to have a computer at the places I go, and I don't even need a laptop for that matter. As I am typically either at home or in the office or on vacation, where I can live without being reachable.

I had a mobile for a few years, but hadn't had much use for it.

I have a tablet now for a few weeks, 10" Nexus, but I mainly use it at home.

What I want to say with this: You're not alone with being outdated, and I don't want to poke fun at you. As far as I know, in 2008 Barbara Peisch also still swore about the performance of free tables only, as we almost had a quarrel about that in the 2008 MVP Summit, where I met her. But sorry, just take a look around at the features DBCs offer.

Bye, Olaf.

 

[Mike Lewis]

I wrote an article back in 2003 explaining the advantages and disadvantages of DBCs compared to free tables. See:

DBCs or free tables? The pros and cons

Since then, I have always used DBC-based tables in my apps, except in a few minor cases where free tables were necessary for some reason. Despite the few disadvantages, I can't seen any reason to do anything else.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

I agree to all you say, Mike.

With DBC-based tables, you have to go to a certain amount of trouble to ensure that the DBC stays in sync with the tables and indexes. Both the DBC and the DBF contain a record of the table's structure, and run-time errors will occur if these do not match. The situation is similar with index tags. This means that, whenever you make any structural change to any data files which have been deployed to a user's site, you have to be careful to deploy an updated copy of the database container files as well.

I also agree to that, but it get's simpler if you don't distribute updated dbf/fpt files, but distribute update scripts ALTER TABLE. What you write sounds like you need to take special care to update the DBC additional to DBFs. ALTER TABLE alters both the DBF and of course all meta data about the table in the DBC. The problem of out of sync DBC and DBF only arises, if you alter your DBFs on your development machine and distribute them, without also distributing the new DBC/DCT/DCX. But in a small way this also is nothing new to free table developers, because you know an ALTER TABLE alters DBF and often enough also FPT and you always need to distribute all files making up a table, not just the DBF file. You typically (re)distribute DBF,FPT and CDX, just because they belong together. Now it's just a few more files.

It's a route you often enough can't take anyway. If you alter table and add a field, you need to redo that alteration to a customer database in use, because you don't want your customer to loose his data. You might have gone the route to distribute an empty database and append users data. That's ok, because ALTER TABLE does the same, but you can save lots of time, if only a few tables are changed, you can leave many tables as is. Backups save you from harm an database update might do.

So also in the aspect of table updates, there is nothing new, really. You also would need to either ALTER free TABLES or distribute a new version of a DBF and append customer data.

And last not least there is the Stonefield Database Toolkit, where you can create meta data, deploy that and apply all needed changes. Also helpful is Tools/GenDBC, which analyses a DBC and creates a prg to create an empty DBC with the same structure.

Bye, Olaf.

 

[Mike Lewis]

... it get's simpler if you don't distribute updated dbf/fpt files, but distribute update scripts ALTER TABLE

I completeley agree, Olaf. Since I wrote the article, I have got into the habit of always using ALTER TABLE scripts, but I wasn't so well organised back then. If I ever update the article, I'll keep that point in mind.

I did use Stonefield on one project. The application's still in use, after about ten years. But although it's an impressive product, I found it a bit over-complex for most of my needs.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[NasibKalsi]

Thanks Olaf and Mike.

Olaf: you can only poke fun when it is poke(able). I have absolutely no problem with that. We are talking technical, nothing personal.

From your points, I remember testing dbc some years ago and had some problems using it. Due to time constraints, did not persue it.

I like the feature that, I have to just open the dbc and then can use any table without specifying the path. Some how it did not work for me. Now that I know it works fine, I will retest this. For temporary file, I always use them on the local specified temp directory.

Updates the structure: I have a table of all the updates and as I make changes I simply updated this table and send to the customer. Customer with a special button, run the script and goes through any new UID based records and start running script. I have update flag taht can be A:Add Field, R:Rename field, Delete a field, etc. and have 2 blob fields to run any extra script before and after the change. New changes added to the customer's table. This way, I do not need to keep track of which pc is updated or not.

I do not think there is any speed issue, I think it may cost me of maintaing extra two or so tables (dbc and some house keeping). It is going to be negligible for sure. May be 1980 or so could have shown some lag.

I will read Mikes's article to explore more.


Enjoy the Summer ...


Nasib

 

[NasibKalsi]

I just tested and found my error. I was using dot not exclamation sign.

Thank you.


Nasib

 

[Olaf Doschke]

>I was using dot not exclamation sign.

Understandable, that's how T-SQL does it and some more. That's of course something a forum posting could have clarified very fast.

Yu can also skip the database name. As long as you only use one DBC, you OPEN DATABASE yourdatabase.dbc, it's opened and the current DBC, like USE sometable opens a table and opens it in the current workarea.And then you can USE tablename, even if the tablename differs from the file name, this won't need a DBF extension anyway.

Just to demo, what few people do, as it only complicates things:

Local lcDBC, lcDBF, lcTable

Cd (GetEnv("TEMP"))
&&random names
lcDBC = Sys(2015)+".dbc"
lcDBF = Sys(2015)+".dbf"

Create Database (lcDBC)
Create Table (lcDBF) Name dbcTable (iID I, cData C(50))
Use
Close Database

Open Database (lcDBC) Shared
Use dbcTable
? lcDBF
? Alias()
? Dbf()
Close Tables All
Close Databases All

This demos, the DBC stores a table name differing from the file name, if you want. You don't need to make use of the name clause of CREATE TABLE. And using the visual table designer also helps discovering all dbc table features, so I suggest you use the designer, and not CREATE TABLE for the start.

It's totally okay and less confusing, if you don't specify a name inside the dbc, then still you won't need to specify the dbf extension, but you also don't need to do that for free tables, VFP always extends a file name without extension with DBF.

And while I am always tending towards verbose and precise code, not specifying the database name makes it easier to adopt code with another customized to a customer DBC name, for example.

Bye, Olaf.