Solved

ADO vs other Databases?

Posted on 2002-03-20
29
363 Views
Last Modified: 2013-11-23
I am the programming manager of a small all-around computer store and we have been using MS Access for a couple of years for quick and easy (and cheap) applications.  We have recently moved to Delphi and are wondering what is the best Database provider to use.  I have already written some ADO stuff to connect to an Access database, but I'd like to get completely away from Access if possible.  I'd love to use Interbase but it's too expensive for our clients.  I'd love to use the BDE but I'd like to have a much smaller footprint.

I'll go ahead and list some of my wants and you can correct me if it's not possible.
-  Something a little heavier than Access with an actual Client/Server model so that heavier tasks can be done on the server.
-  Royalty free distribution (most of our clients are small businesses that are stretching their budgets just to write a custom program, let alone pay for a SQL server)
-  Relational
-  Must be good in a multiuser environment (unlike Access)
-  Built in security is a plus

I'm sorry if this is a topic that is discussed over and over, but I couldn't find much in the search so if you know of another topic that covers this same topic please let me know.
0
Comment
Question by:header
  • 10
  • 8
  • 7
  • +2
29 Comments
 
LVL 3

Expert Comment

by:SteveWaite
ID: 6884505
interested :-)
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6884648
Hi header,

There is a completely free version of Interbase which is the Open Source version (freely downloadable from the Borland Community website - community.borland.com).  We have been using this for quite a while and it's fast, robust and very easy to configure.

The beauty of using Interbase is you can use IBX components which allow you to access the Interbase database directly (effectively making the BDE obsolete).

Interbase (Open Source) meets all you requirements.  To download a copy, you need to become a Borland Community Member (which is free - you just need a working email address).  Once you've singed up, have a look in the Interbase section and you'll find the links to download it.

The only other alternative would be MySQL.  MySQL is great and it's free, although it's a little tricker to setup.  I'm of the understanding that Delphi 6 has native support for MySQL as well (although I will stand corrected on this).

Over all, you can't go wrong with Interbase.

Cheers!

Stu.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6884654
Hi again.  Just to save you digging, here is a direct link to download Interbase 6.01.  Just choose your platform, click the link and you're set.

If you've never used Interbase before through Delphi, I would probably advise using the BDE first and then move over to IBX.  It helps with the learning curve :)

Cheers,

Stu.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6884655
Damn!  Forgot to paste in the link:

http://www.borland.com/devsupport/interbase/opensource/

Cheers

Stu.
0
 
LVL 2

Author Comment

by:header
ID: 6885636
Stuart, this sounds too good to be true.  You mean that Interbase can be developed and distrubuted for free?  No liscenses, no distribution fees?

When I read about Interbase the prices were $200 per server and $100-$150 per client.  (and that was not including the distribution rights)
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6887008
Yep.  That's correct.  But only the Open Source version (which I've provided the link for).  It's royalty free (and we confirmed that with Borland Australia only three weeks ago when they were doing a presentation for us).  We have a number of sites using IB O/S - some of them have upto 50 concurrent users.

Read the licence if you're not 100% sure of anything before making a final decision, but my understanding is it's completely free of charge and royalties.

Stu.

PS.  You can download the source code for it as well if you want to know how it ticks :)
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6887014
This is from the license:

The Initial Developer hereby grants You a world-wide, royalty-free, non-exclusive license, subject to third party intellectual property claims:
0
 
LVL 2

Author Comment

by:header
ID: 6888726
By the way, just for refrence, mysql.com has some excellent Performance tests and a crash-me test.

I'm not sure what to make of it, but both Access and Interbase had a crash-me result of (and I'll Quote):

    "Anyone with normal access to the database server can
     take it down, possibly forever!".

Just glancing down the test results, it would appear that Access actually did better than Interbase.  With the Exception that Interbase can accept 100 simultaneous connections and Access can accept 64.  Add that to the fact that Interbase has Delphi controls specifically made for connections to it (rather than general ADO) and it has an actual server/client model.

Do these few improvements make up for the poor test results?  I'm asking your honest opinion.  What has your experience with Interbase been like?  Have you had many problems with corruption or multi-user hangups?  If you feel comfortable with it, so do I.

(I probably should have mentioned that my question is based upon a commercial situation and MySQL is not free for commercial use.  It costs upwards of $200 per user.)
0
 

Expert Comment

by:ARW
ID: 6889425
We have built an app on Interbase, the open source free version and have been less than happy with its performance and stability.  We are currently thinking about using the MSDE version of SQL Server which is relatively free of licensing constraints, but is not true client/server.  Also, a few years ago I did some work with FlashFiler in Delphi 4, which did not go well.  We eventually went to the Advantage Database Server, which has some expensive licensing.  However, in retrospect Delphi 4 was not a particularly stable development environment I have been told by several developers.  SO perhaps, FlashFiler from TurboPower which costs a couple hundred dollars, but that is it, could be worth a look.

Thanks
ARW
0
 
LVL 2

Author Comment

by:header
ID: 6889581
I took a brief look at FlashFiler and according to the blank and white it looks like something worth looking into.  I will try to get ahold of them for a demo.

Not to make you relive a nightmare, but could you go into more detail on your Interbase experiences?  Is there some difference between the bought version and the open source version?
0
 

Expert Comment

by:ARW
ID: 6889604
I was not the main developer on that project, but my recollection is that the upgrade process was not that smooth as we went through different versions of the upgrades necessary to fix bugs and apply patches, etc.  Also, the IBX components would not work correctly after an upgrade sometimes or would be out of phase and need an upgrade of their own.  Also, there was one incident where a client's data got eaten.  We ran GFIX several different times and tried other ways to rebuild the database after it crashed and were never able to recover parts of the database.  Eventually, we just had to go to an old backup and recreate the data.  Not a pleasant experience, which I think finally drove us to consider an alternative DB.

ARW
0
 
LVL 2

Author Comment

by:header
ID: 6889631
Do you mean upgrades to the Database that was being developed or to Interbase itself?

If you mean Interbase, why was it in need of upgrades all the time?
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6892641
ARW/Header,

I'll agree with ARW about the IBX components - we have one app which uses IBX and it is a pain when we try to do any new development on it.  We've steered clear of IBX now and just use BDE.

I've never had any problems with Interbase at all.  We have it installed in a number of sites ranging from 2 - 50 simultaneous users.  We've never had any corruption at all, and the only time we've had any problems accessing the data is when the server was unavailable.

I must also stress that there are a lot of very happy IB users who are using the open source version.  The failures of one individual/business is not to be considered a benchmark for the entire programming community.  I'd recommend having a search around and seeing how many people are happy/disappointed by it.

And, if I may add, one other thing that Interbase has going for it is the fact it is cross platform.  And that can make a big difference these days!

Stu
0
 

Expert Comment

by:ARW
ID: 6892694
The upgrades were to the database engine itself at least once and to the IBX components themselves to fix bugs in them.

Don't get me wrong I'd like to see Interbase succeed, but it is also hard to recommend it strongly, just due to some of the things that happened to me while working with it.  Plus it does not seem to benchmark very well in all the tests I have seen.  MySQL seems impressive there.    

Anyway, "header"  as a decision-maker I think you should look at Interbase, but look closely, because if you have to use the BDE you'll have a large footprint.  Take a look at FlashFiler too, though.
0
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.

 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6892769
>Don't get me wrong I'd like to see Interbase succeed,
>but it is also hard to recommend it strongly,  just due
>to some of the things that happened to me while working
>with it.  Plus it does not seem to benchmark very well
>in all the tests I have seen.  MySQL seems impressive
>there.    

That's not a really fair comparison though.  MS SQL Server is one of the fastested on the market.  We've tested Interbase against MS SQL, DB2 and Oracle (8.1.6e).  I'd say Interbase, when put under the same load as the other DB's, would compare favorably to our installation of Oracle (which is running on the same server).  And, I might also add, we tend to have a lot more problems with Oracle than we do with Interbase (but that maybe lack of knowledge as none of us are really Oracle experts).

My prefered DB is MS-SQL, but it's too expensive to deploy in an inexpensive application.

Who makes Flash Filer?  I've never heard of it before, but I'm keen to try something new out :)  

Cheers,

Stu
0
 
LVL 2

Author Comment

by:header
ID: 6894040
FlashFiler can be found at www.turbopower.com and I just heard back from them, there is a trial version at www.turbopower.com/trialrun.  I will be trying it as soon as I find time.
0
 

Expert Comment

by:ARW
ID: 6894117
The upgrades were to the database engine itself at least once and to the IBX components themselves to fix bugs in them.

Don't get me wrong I'd like to see Interbase succeed, but it is also hard to recommend it strongly, just due to some of the things that happened to me while working with it.  Plus it does not seem to benchmark very well in all the tests I have seen.  MySQL seems impressive there.    

Anyway, "header"  as a decision-maker I think you should look at Interbase, but look closely, because if you have to use the BDE you'll have a large footprint.  Take a look at FlashFiler too, though.
0
 
LVL 2

Author Comment

by:header
ID: 6902864
I have downloaded and installed the Flash Filer demo.  It's early, but so far I'm impressed.

Here's a summary of the pricing:
For $500 dollars you get Flash Filer and it's royalty free.  There is also an ODBC driver for $250.  (If you buy the package I think you save about $100)  The ODBC gives you 500 licenses to distribute, then you have to purchase another 500.

Anyone else tried it yet?
0
 

Expert Comment

by:ARW
ID: 6903280
Wow!  They have almost tripled their price, but given the current state of the database engine market, it is probably a price the market can bear...perhaps happily.  They were working on SQL compliance when I stopped using them in early 1999.  Is FlashFiler SQL-92 compliant now?


Thanks
AW
0
 
LVL 2

Author Comment

by:header
ID: 6903324
This is from their web-site:
"For easy data retrieval, FlashFiler 2 provides a TffQuery component that replaces the VCL's TQuery component. With TffQuery you can send industry-standard SQL-92 queries to the server for high-speed processing."

That however is the only place that SQL-92 is mentioned (that I have found anyway).

Unless I have blatently missed it, there is no support for relations either.  I'm sure I can live with out it, but it will be missed.

What's your opinion, is a Relational DB that much different from one that isn't relational?  (I mean is it something that can't be lived without)
0
 

Expert Comment

by:ARW
ID: 6903378
Yes, I think they were working on TffQuery when I stopped using them.  I think it was pretty important for them to get that working to be competitive.  I am not sure I can speak that intelligently on the relational vs. non-relational databases debate, but this URL provides a pretty nice encapsulation of the debate and comparison.  Though it is specific to a mainframe database called IMAGE, it still makes some fundamental explanations on the issues related to the comparison.

http://www.adager.com/VeSoft/RelationalAndIMAGE.html


Thanks
AW
0
 
LVL 2

Author Comment

by:header
ID: 6945063
I have been so far very impressed with Flash Filer.  The main disadvantage is that they don't support any SQL but select statements.  I'm told that they will have support for Insert, Delete, & Update statements very shortly (within weeks).

My initial speed tests against access were very good.  A mass transactional insert of 10,000 moderately sized records took 30 seconds on Flash Filer and 2 minutes in Access.  (This was on a single machine, over the network I'm sure the gap would open)

Anyone else try it?
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6946540
header,

Have you tried this same test against Interbase?

If Flash Filer is missing so much in the way of SQL, surely this would be a worry as far as being seriously considered for an application backend?  What I mean is, if you've only currently got a select statement, but an insert, delete and update is going to be added shortly, what other shortcomings has it got?  I'd give this serious consideration before using it as it may cause you a lot of grief in the future.

Stu
0
 
LVL 2

Author Comment

by:header
ID: 6947749
Stu, your undying loyalty to Interbase has been noted.  No I have not tested against Interbase.  I can't really explain it, but I have a very bad connotation of Interbase.  It happend way before this question was asked and ARW revealed his bad experiences.  There is also the fact that the BDE is the best way to connect to Interbase.  Borland is no longer developing the BDE, it is what it is and will never be any more.  For this reason, as well as the large footprint it requires, I decided long ago never to use the BDE.

Interbase may be the perfect solution for some people, but I don't think it's the right solution for me.

As far as Flash Filer goes, I will try it on a small project and see how it goes.  We have a program currently installed in a manufacturing plant that has never worked correctly since day one.  (Mostly the fault of the programmer who wrote it, but Access has some of the blame as well)  They are more than willing to try something new and I will probably be rewriting that application shortly.  I hope to use Flash Filer as the back-end.  This will be a perfect situation to give Flash Filer a test run.
0
 

Accepted Solution

by:
ARW earned 50 total points
ID: 6947992
I think Stu makes a reasonable point that the lack of full SQL support is certainly a valid concern.  However, one needs to weigh against the requirements of the app.  The best two advantages of SQL, I think are:  

1.  Large database updates can be pushed off to the server.  
2. an application that does most or all of its I/O in SQL can be updated and expanded without necessarily having to recompile the EXE.  

SQL is great, but I can imagine many apps that do not require it and when FlashFiler is evaluated against the background of alternatives, especially when it comes to licensing and how much it will cost to ramp it out to your market...well maybe FlashFiler is the best choice.

ARW
0
 
LVL 2

Author Comment

by:header
ID: 6948040
Well put.  I totally agree.
0
 
LVL 1

Expert Comment

by:AAMFK
ID: 7260244
so what got you so scared of Access, anyway?

is there some story we're missing?

0
 
LVL 1

Expert Comment

by:AAMFK
ID: 7260249
and what is interbase anyway?

is it still around??
0
 
LVL 1

Expert Comment

by:AAMFK
ID: 7260257
just for the record-

Access fits all of these requirements:
-  Something a little heavier than Access with an actual Client/Server model so that heavier tasks can be done on the server.
-  Royalty free distribution (most of our clients are small businesses that are stretching their budgets just to write a custom program, let alone pay for a SQL server)
-  Relational
-  Must be good in a multiuser environment (unlike Access)
-  Built in security is a plus

you just gotta know how to do it. ive been doing it ft for years-- and i dont think that Access is inherently a bad platform.  i think that there are bad access designs.

but access is the best platform out there for your needs. it is free.

if you insist that the db engine itself is too slow-- then i reccomend something like MSDE (a freeware version of SQL Server)

if you still have Access apps that need performance help-- please drop me a line!!

aaron_kempf@hotmail.com
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

13 Experts available now in Live!

Get 1:1 Help Now