Should us to STOP using DBF?

[George]

Dear forum

As we did with clipper, do you think that is time to stop using DBF database format and begin to use SQL Database format in our new applications?

What future does have DBF?

For us the DBF users without SQL knowledge, which is our best choice to use SQL?
A) Eagle
B) Ado
C) Mediator
D) FscODBC
E) TMySQL
F) xHarbour Enterprise

Regards



George

[Gale FORd]

Personally I see at least a couple of reasons for continuing with some dbf implementations.

1. Simple installs. Some programs do not need the extra setup involved for external data engines.

2. I cannot see all of my users adding client server.
It is so much cheeper and easier for small companies to maintain dbf file setup.

[George]

Gale

My first concern is about DATA INTEGRITY. We can get this using CLIENT/SERVER technology only, and in order to get CLIENT/SERVER we need to use SQL (Advantage DB is too expensive for small business to afford)

In the xHarbour forum I receive the following post from Przemyslaw, the programmer xHarbour guru.


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
On Thu, 1 Jun 2006 13:55:54 -0400,
George Abinader <georgeabinader@msn.com> wrote:
> Przemyslaw
> I like DBF. I am using it from clipper 87.
> My first concern is when I have to do a Browse in a 500,000 records file.
> I learned that SQL only return a few rows, but DBF return the 500,000
> RECORDS.

The result of SQL query can be few records but RDBMS have to collect
it first from 500'000. It can uses some optimizations technics to
avoid linear scanning of 500'000. The most common optimization
used by RDBMS is parsing the filter expression and for the subexpressions
which are indexed and using indexes to reduce size of the set with possible
valid records and then making linear checking only for the records
which still exists in record set. Exactly the same is done by MachSix,
ClipMore, xHarbour RMDBFCDX/RMDBFNTX (they support all MachSix m6_*()
and ClipMore cm*()/rl*() functions) and ADS when you are setting
filter to table or when you call explicitly call a function to create
such record set, f.e.: rlNewQuery( cFilter ). You do not need RDBMS
with SQL for it.

> Also we have the problem with possible index and file corruption
> because the DBF is working directly with the database instead of a copy like
> SQL.

Not SQL but some [R]DBMS which may use SQL or not - it doesn't matter.
Farther not all [R]DBMS have to do that. It depends on implemented
transaction model and transaction isolation level. Only few [R]DBMS
(usually using some additional OS/hardware support) can cleanly revert
transactions when the server crashes. Most of them can cleanly revert
only transactions not committed by clients f.e. due to client computer
crash.
You can reach the same functionality porting your xHarbour application
to Linux and running them remotely inside 'screen' like tool. Even if
your station crash you can connect to the server from other computer
and continue your job on the same instance of program you were working
before.
You do not not need [R]DBMS for that.
There is yet another reason to use transactions. Bugs in application
which which may interrupt execution during update so logical data
structures can not be updated correctly and probably I will think
about adding some transaction mechanism to native RDDs.

> I think that the advantage that SQL have over DBF is the CLIENT/SERVER
> technology.

Not SQL. SQL is a language to access data stored in database not
database format or database motor system.

> But the problem is that we don't have a Client/Server for DBF. I
> know about the ADVANTAGE DATABASE SERVER but it is too expensive for an
> apllicaction with only two or three computers in a local network
> What other options do we have?

Port it to Linux. Your whole application will run on the server side.

> Are there any Client/Server for DBF other than Advantage?

I do not know. Maybe in this year I'll finish NETRDD which should
have such functionality but it strictly depend on spare time I will
have. I cannot promise any terms or deadlines. It's even possible
that I'll never finish it.
But I created USRRDD which allows to create RDDs at .prg level.
I should commit it in few days. Maybe someone implement RDD with
remote data access using RPC or other network communication methods
which exists in xHarbour.

best regards,
Przemek

ps. I do not want to say that [R]DBMS are wrong or not worth to use
because it will not be true. Just simply people expects some
amazing functionality from [R]DBMS basing on some myths about
them which are very often far from true.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Regadrs


George

[ShumingWang]

DBF:
1. cdx/ntx often corrupted,dbf often damaged .
2.exe and dbf file place in same visialble drivers, dbf files can be easily deleted by mistake.
3. not direct support internet/ADSL
4. no forerign key , basic data setting may be modified by mistake.

Tmysql +mysql5.0 , direct TCP/IP/C++ API support

1. 3 years ,there is 0 damaged record.
2. no longer worry of cdx/ntx/dbf damaged .no longer need reindex !
3. support internet/ADSL as lan , without extra files nor settting.especially for remote use and maintian.
4. easy to install, backup/resotre data ,build in app exe
5. great reduce prg code programe under SQL(a large report may be replaced by a SQL ).also with xbase dbf use ,odb:qty:=10,odb:save(),odb:skip(1)
6.nerver worry of no index key create, set order to / where failure.
7.add index/ modify table column(width/type), backup without dbf must every user close.

sampes: http://www.xtech.com.cn/down/sampel1.prg
http://www.xtech.com.cn/down/sample2.prg
enhanced tmysql: http://www.xtech.com.cn/down/mysql.c
http://www.xtech.com.cn/down/mysql.ch
http://www.xtech.com.cn/down/tmysql.prg

Shuming Wang

[Rimantas]

DBF:
1. cdx/ntx often corrupted,dbf often damaged .
2.exe and dbf file place in same visialble drivers, dbf files can be easily deleted by mistake.
3. not direct support internet/ADSL
4. no forerign key , basic data setting may be modified by mistake.

Tmysql +mysql5.0 , direct TCP/IP/C++ API support

1. 3 years ,there is 0 damaged record.
2. no longer worry of cdx/ntx/dbf damaged .no longer need reindex !
3. support internet/ADSL as lan , without extra files nor settting.especially for remote use and maintian.
4. easy to install, backup/resotre data ,build in app exe
5. great reduce prg code programe under SQL(a large report may be replaced by a SQL ).also with xbase dbf use ,odb:qty:=10,odb:save(),odb:skip(1)
6.nerver worry of no index key create, set order to / where failure.
7.add index/ modify table column(width/type), backup without dbf must every user close.

Hello , )

Shuming I can't agree with you :

1. If application well writed , it doesn't exist any coruptions . My applications with DBF's alreday are running without any corruptions for some years .
2. I'm using subdirectories in which are stored DBF's . Also the paths are directed fully , without mapping - \\servername\shareddir\applic_dbfs\ for sample . And applications are working in all workday/worktime , so any deletion of file or subdirectory is denied , because if the files are opened any careless deletion will be unsucesfull . The deletion of SQL database can be done in the time as with DBF - then nothing is working with applicattion . So backup and for DBFs and for SQL dbs it's necessary .
3. With this option we can discuss too . It's alreday writed a good things such xBScript , ClipWEB ...

All other options are something missleading . The DBFs are very stables and very flexibles , not need complicated instalations of client/server configurations . I can it to say from mine experience . I can only to recognize that DBFs have one shortcomming - security . If something want to do secure system - then with DBFs it's more hard to do that . With SQL dbs it's more easy to create secure system . That's all in my opinion .

With best regards ! Rimantas

[ShumingWang]

Rimantas,
In multi users envirenment(include Novell,Windows,samba server) and open many dbf tables ,dbf tables opening/shareing by many users, if comeone power off his computer or windows hang, the cdx/ntx/dbf often damaged .
App harbour/xharbour+Tmysql , without any client/server setting just app files +libmysql.dll.
If for simple use ,package the mysql main files/mysql folder with app, after install, create a short cut run mysql datatbase server bat file contians "mysqld-nt.exe --standalone ",a bat file contians " mysqladmin -u root shutdown "
Shuming Wang

[Rimantas]

In multi users envirenment(include Novell,Windows,samba server) and open many dbf tables ,dbf tables opening/shareing by many users, if comeone power off his computer or windows hang, the cdx/ntx/dbf often damaged .
Shuming Wang

Shuming , all my applications are working in multiuser enviroment . I don't have standalone applications . And the database of DBFs is very stable . Believe me , they are working for some years alreday . The servers are with UPS , so power problem doesn't exist . I never detected that hang up or other problems of users PC can damage data in server side . Only some times when electricity fault , in places where doesn't existed UPS for networks controlers ( swiths , hubs ) , then dissapears some lines of data . But that doesn't damage database in server .
All applications are working with Win2K servers , in one place with Novell . Sorry , but I don't have experience with Linux .
What I noticed that the problem of damaging exist inside program . Well writen with well checked variables , without globals , in 99 % with locals vars the application will be very stable . I can only say many good words for Przemek - he did giant job , very good working CDX RDD .

All records for insertion and update use this sequence :
( cAlias )->( dbappend() ) or ( cAlias )->( rlock() )
... new record ... / or upate
( cAlias )->( dbcommit() )
( cAlias )->( dbunlock() )

Maybe you didn't used dbcommit ?

At this time I working with Navision Axapta and Oracle . Though about Oracle database exist legends , I can't to say that this DB worst so big moneys for that ... ))

With best regards !

[ShumingWang]

Rimantas,
Our old app by clip52e+comix+fw21+blinker, all with lock,commit() , in many cases, there are over 50 tables/alias opened at one time.Now only several customers not upgrade to Mysql.

Our new apps change into xharbour+fwh25+tmysql, over about 200 tables , over 40-50 users, one dtatabase with 4 years data together 300M-500M, updatet,select,report are as fast as beginning ,even in case server/workstation power failure/windows hange, mysql can auto recover (innodb table type) without any problem, never do reindex. reindex forget it .
Mysql another most important feature is foreign key .
There are basic info tables as customer,matrial items,currency,bom, and diarly tables like packing list,proform invoice,purchase order, the bill tables can't add an item id not exist in basic table, and basic table can't delete/update a item id if exist in bill tables , Mysql database done it auto, but dbf must self write codes in app.
Mysql has features triger,view,stored procedure, 5.1 version also with event, this great reduce program works.
I think , dbf is proper for much smaller use, SQL/MySQL is proper for common ,performance,reliable , secure, mission-critical ,heavy task use.
SQL is the triend, 5 years ago , most database software products in China market used dbf, now almost all are SQL.
Mysql is free.

Shuming Wang

[Rimantas]

Rimantas,
Our old app by clip52e+comix+fw21+blinker, all with lock,commit() , in many cases, there are over 50 tables/alias opened at one time.Now only several customers not upgrade to Mysql.

Our new apps change into xharbour+fwh25+tmysql, over about 200 tables , over 40-50 users, one dtatabase with 4 years data together 300M-500M, updatet,select,report are as fast as beginning ,even in case

Mysql has features triger,view,stored procedure, 5.1 version also with event, this great reduce program works.
I think , dbf is proper for much smaller use, SQL/MySQL is proper for common ,performance,reliable , secure, mission-critical ,heavy task use.

Shuming , I don't want to discuss how good is MySQL . I agree with you , that MySQL or other Cln/Srv DB it's a good thing and it's a better than DBFs . At this time I'm playing with FireBird . I like such things then isn't need to think what index to use , all work is done by server .
But I can't to agree that DBFs database isn't stable . It's stable . One application ( production ) is using about 80 dbfs + 75 cdxs . Work about 12-15 users at the same time . At this time is about 300 Mb data . Work without redindex 2 years . FWH 2.5 + xHarbour.org + CDX RDD . Also it's was working in earlier 2 years with FW 2.1 + Cliper 5.3b + Comix . With Clipper some times damage was . But that was Clipper , not xHarbour .
Also I can agree that DBFs database isn't good for a big companies . I think that this is a choice for a small enterprises or also it's very good for individuals projects .
All types of database will find own place and utilization . So a question that DBFs must be stopped , it's meaningless , I think .

With best regards !

[TimStone]

I have looked at MySQL, but I don't see why people say its cheap. When I looked at the pricing to distribute it with an application, it didn't work out to be lower than ADS.

I have people running 15 to 20 clients with ADS and they simply don't have problems with the DBF's.

I used DBF's because it was easy to retain the data.

Also, I can easily restore a single file that might be corrupted and not lose a lot of data. I maintain a slim use of drive space by using 151 data files. If someone corrupts one, then we can restore just that file and not lose all of the other data.

I can also repair DBF files. Are there utilities for fixing SQL files ? Programs I use that run with SQL servers are not necessarily well behaved.

Tim

[ShumingWang]

Mysql can be used free fee under GPL , or you can purchase .
Data damaged/corrupted /repair data ? use mysql , forget it .
Compare features of mysql and ADS, they not in a grade/level.

Shuming Wang

[George]

After two year I do the same questions:

1) As we did with clipper, do you think that is time to stop using DBF database format and begin to use SQL Database format in our new applications?

2) What future does have DBF?

3) Antonio can we have a forum for SQL questions?


Regards

George

[Silvio]

DBF files run from 1984 from Asthon Tate , then DBase I, II, III, IV, Clipper

There are 24 years of History...I not think that is time to stop using DBF database format

But If U want use another file SQL, MySql you can use it if the application requires

Sample you must use a big data file you can use mysql ...

I made many application with dbf and they don't have problems with the DBF's.

If you must create a small application I say you can use dbf

If you must make a big application U can use Mysql, mediator or ado

But the DBFs never 'll dead....

[jose_murugosa]

Some years ago I had to develop an application with clipper.

This aplication was for a post office, they process 3.000 letters a day, with 7 differents processes, all this data was stored in dbfs, one kits of dbfs for each year, that means 1.100.000 records with 22 fields each one in the principal database and hundred of thousands of records en many other databases and 5 terminals saving data at same time.

They had to reindex one or twice a year, I think that is not a great problem (users could do it from an option in tools menu)

5 years ago, they were imprest with a windows software that uses access databases and have beautiful look (by that moment I didn´t know fivewin).

They prove that software and two weeks later they call me to reinstall my soft, access databases crashes in 8 days of process, I think that dbfs are a good option if you want a cheap aplication, but of course client/server databases are better if is possible to pay for them and its hardware support.

Now they fuse with an other post and uses other software, but the point is that I believe that dbfs ar a robust database if you don´t use client/server option.

Now I´m working on my first application with pgsql y sqlrdd for an special client, that means a good server, raid, disk mirrored, energy backup, etc. with its cost, but for many other clients, dbfs works perfect.

(sorry for my bad english).

[Kleyber]

I think the same as Jose Murugosa. There is no reason to put a Client/Server structure in small clients who have only 2 or 3 computers in a LAN and I think DBF is very stable when the application is very well written. Logically we have to use SQL for those clients who must have a medium to large structure and can install a big server. Both of databases still have its space in programming universe.

Regards,