DataBase Choice

Jlouro

unread,

Feb 17, 2008, 12:06:08 PM2/17/08

to

What should be the best database to start a new project

Currently I use PARADOX with the BDE.

I need a change

I need a better and more evolver Database engine. But not the price and
Height of a SQLServer or a like

Also and what is better DevExpress or ADO.

The project is simple not so many records, speed is not critical and the
database model is not to complex and not so many tables

It would nice to have the power of a big database engine, but I do not need
or want the complexity of installing it on the client machines. And all must
fit into a single not so new machine.

So as you can see a normal scenario with a simple application.

Also the project is still and will be Win32.

Thanks

João Louro

Bill Todd [TeamB]

unread,

Feb 17, 2008, 12:44:34 PM2/17/08

to

If the app is single user and you want a database that has both SQL and
navigational (Paradox like) access look at Advantage Local Server. The
local server is free. The client/server engine is not free but is much
less expensive than SQL Server.

If you are willing to consider open source then look at PostgreSQL and
Firebird. You can use PostgreSQL with a ADO and the open source ODBC or
OLE DB driver. There are also 3rd party component sets available.
CodeGear does not provide a driver for Firebird but you can get a good
one from Upscene Productions at www.upscene.com.

If you want a commercial database look at Blackfish SQL or InterBase.
Blackfish SQL does require that .NET 2.0 be installed.

> Also and what is better DevExpress or ADO.

I assume you mean dbExpress (also called DBX) or ADO. The answer is
that it depends on what database you are using.

--
Bill Todd (TeamB)

Bob Swart

unread,

Feb 17, 2008, 1:50:15 PM2/17/08

to

Hi Jlouro,

> What should be the best database to start a new project
>
> Currently I use PARADOX with the BDE.

You can use all kinds of databases, as well as data access technologies.

> I need a better and more evolver Database engine. But not the price and
> Height of a SQLServer or a like

SQL Server 2000 MSDE or SQL Server 2005 Express is free, and so is
FireBird (and mySQL in some cases).

> Also and what is better DevExpress or ADO.

I think you mean *dbExpress* or ADO here.

It depends on your needs, and also depends on the database that you
decide to use in the end.

See my "Delphi for Win32 VCL Database Development" book published as PDF
or print-on-demand on Lulu.com at http://stores.lulu.com/DrBob42 for
some more information that can help you to make a decision (free preview
available with the TOC and first 6 pages)

In this paperback about Database Development with (Turbo) Delphi, I'll
cover the Borland Database Engine (BDE), dbGo for ADO, dbExpress and the
stand-alone TClientDataSet. I'm using the Paradox, MyBase and SQL Server
/ MSDE databases for the example application, which is built for each
data access technology, including examples of data conversion.
Finally, for Delphi 2007 developers, I've added a special introduction
section on DBX4 and the new features in this latest release of the
dbExpress data access framework.
The information in this paperback can be used with all versions of
Delphi, although ADO needs Delphi 5 or higher, dbExpress needs Delphi 6
or higher, and DBX4 is only part of Delphi 2007 (or higher).

> João Louro

Groetjes,
Bob Swart

--
Bob Swart Training & Consultancy (eBob42.com) Forever Loyal to Delphi
CodeGear Technology Partner -- CodeGear RAD Studio Reseller (BeNeLux)
Blog: http://www.drbob42.com/blog - RSS: http://eBob42.com/weblog.xml

Arthur Hoornweg

unread,

Feb 18, 2008, 8:59:37 AM2/18/08

to

Jlouro wrote:
> What should be the best database to start a new project
>
> Currently I use PARADOX with the BDE.

You didn't mention how many simultaneous users and what level of
user access security you want.

For a local database for a single (or few) user(s), the Microsoft
OleDB provider for Jet (=MS Access *.MDB file format) databases
is almost impossible to beat since it's free, it's already part of
Windows (so no installation hassle), it supports unicode and it
has a pretty decent SQL.

--
Arthur Hoornweg

(In order to reply per e-mail, please just remove the ".net"
from my e-mail address. Leave the rest of the address intact
including the "antispam" part. I had to take this measure to
counteract unsollicited mail.)

Zoren Lendry

unread,

Feb 18, 2008, 2:14:41 PM2/18/08

to

Arthur Hoornweg wrote:
> Jlouro wrote:
>> What should be the best database to start a new project
>>
>> Currently I use PARADOX with the BDE.
>
> You didn't mention how many simultaneous users and what level of
> user access security you want.
>
> For a local database for a single (or few) user(s), the Microsoft
> OleDB provider for Jet (=MS Access *.MDB file format) databases
> is almost impossible to beat since it's free, it's already part of
> Windows (so no installation hassle), it supports unicode and it
> has a pretty decent SQL.

You're killing me!!! Access is horrid. Stay away from it like the plague.

Unless, of course, you're building a system that competes with me. I
can't tell you how many access based systems we replace. Our systems are
built against IB and FB, and the complete lack of index corruptions is a
major plus.

Loren sZendre

MikeR

unread,

Feb 18, 2008, 2:33:54 PM2/18/08

to

Zoren Lendry wrote:
> You're killing me!!! Access is horrid. Stay away from it like the plague.

Huh? For a desktop app it's a great solution. Powerful and pretty much free. Proven
and stable. And I'm not a M$ fan. <g>
Mike

Zoren Lendry

unread,

Feb 18, 2008, 3:04:11 PM2/18/08

to

I've heard that a million times as well -- oh, we'll never need to use
it on more than one machine, or that design really isn't for more than
one X (whatever that might be). But then less than a few months later,
the requirement changes.

In the late 90's we adopted the decision to use a real db right up
front. It takes no extra work, and when you expand the app, NO changes
to the code or the db are necessary.

Like I said before, I hope all my competitors keep the attitude that
Access is great. I would amend part of your quote: proven unstable. In
my world -- one call to fix a corrupted index is not acceptable. But
hey, keeping using it!

Loren sZendre

MikeR

unread,

Feb 19, 2008, 7:23:27 AM2/19/08

to

Zoren Lendry wrote:
> Like I said before, I hope all my competitors keep the attitude that
> Access is great. I would amend part of your quote: proven unstable. In
> my world -- one call to fix a corrupted index is not acceptable. But
> hey, keeping using it!

Thanks, I will. I have *NEVER* had a corruption. I'm not saying it doesn't happen,
just that I've never encountered it. An internet application with hundreds of users,
and a db with millions of records, using classic ASP (ADO) pops up in my mind. The
tools for manipulating it are readily available, and powerful. The install is very
straightforward.
Is it the right db for every application? No. But don't be a data snob. <g>

Zoren Lendry

unread,

Feb 19, 2008, 11:27:58 AM2/19/08

to

MikeR wrote:
> Thanks, I will. I have *NEVER* had a corruption. I'm not saying it
> doesn't happen, just that I've never encountered it. An internet
> application with hundreds of users, and a db with millions of records,
> using classic ASP (ADO) pops up in my mind. The tools for manipulating
> it are readily available, and powerful. The install is very
> straightforward.
> Is it the right db for every application? No. But don't be a data snob. <g>

My feelings on the subject come from personal experience. Both from
using it, and replacing installations where frustration is rampant. To
me, it's just good advice. I'm glad it works for you.

Loren sZendre

Arthur Hoornweg

unread,

Feb 20, 2008, 6:49:30 AM2/20/08

to

Zoren Lendry wrote:

> My feelings on the subject come from personal experience. Both from
> using it, and replacing installations where frustration is rampant.

Hence my question, how many concurrent users etc.
For a single-user local database, MDB performs really well
IMHO.

I am not talking about full-fledged MS Access databases (with
embedded forms, business logic and user interfaces),
just about the MDB file format with tables, indexes and referential
integrity. And the word "installation" doesn't really apply;
One does not need Microsoft Access at all. The necessary
DLLs are supplied with Windows. This stuff works out of the box.

I personally think the choice of the connectivity layer is more
important than the choice of the DB server itself. If one uses
ADO as the connectivity layer, it's very easy to switch to any
other full-featured Sql server at a later stage should the need
arise. If one uses proprietary connectivity technology then one
becomes dependant on a single vendor.

In large corporations it's often the IT department that dictates what
SQL server is acceptable because they are the ones who maintain
it, manage access rights and take care of backup/restore.
Often they will use an existing server. One either complies with
that or one's out. If such companies are potential customers for
the product it makes only sense to use a connectivity technology
that's accepted and supported by every major database manufacturer.

Zoren Lendry

unread,

Feb 20, 2008, 1:04:56 PM2/20/08

to

Arthur Hoornweg wrote:
> Zoren Lendry wrote:
>
>> My feelings on the subject come from personal experience. Both from
>> using it, and replacing installations where frustration is rampant.
>
> Hence my question, how many concurrent users etc.
> For a single-user local database, MDB performs really well
> IMHO.

The bottom-line is to look at the architecture, and what it is meant
for. A file-server database (Paradox, Access) was originally designed to
work on a single machine. In spite of best efforts to make it work over
a network, you cannot ever fully overcome architecture.

So, if you need an app to run on a single machine, you can likely make a
file-server database work well. Or if you have a network, but have a
middle tier (or equivalent) make it look to the db as if all connections
are local, you can probably make that work too.

But for me, I find that starting out with a real database from the start
saves time. I've never had an app that started out as "just needing to
run on one machine" that didn't end up as a full-fledged multi-user,
multi-machine app.

Loren sZendre

Arthur Hoornweg

unread,

Feb 21, 2008, 11:22:35 AM2/21/08

to

Zoren Lendry wrote:

> The bottom-line is to look at the architecture, and what it is meant
> for. A file-server database (Paradox, Access) was originally designed to
> work on a single machine. In spite of best efforts to make it work over
> a network, you cannot ever fully overcome architecture.

Nobody disagrees with that. I prefer C/S any time.
But if one starts with a C/S database server (even if it is just a "light"
or embedded version) one might as well take one that uses standard
connectivity components. Just for keeping all future options open.

Zoren Lendry

unread,

Feb 21, 2008, 2:51:15 PM2/21/08

to

Arthur Hoornweg wrote:
> Zoren Lendry wrote:
>
>> The bottom-line is to look at the architecture, and what it is meant
>> for. A file-server database (Paradox, Access) was originally designed
>> to work on a single machine. In spite of best efforts to make it work
>> over a network, you cannot ever fully overcome architecture.
>
> Nobody disagrees with that. I prefer C/S any time.
> But if one starts with a C/S database server (even if it is just a "light"
> or embedded version) one might as well take one that uses standard
> connectivity components. Just for keeping all future options open.

I don't know what "standard connectivity components" means. When I
program for Firebird, I have a half a dozen options available. I choose
the one that is the best fit for the job. I have never used the ADO
connection components.

If I need future proofing, we accomplish that, for the most part, by
using Data Abstract, which allows us to have one middle tier that can
connect to any number of db backends without any change to our code.

Loren

Isaac

unread,

Feb 21, 2008, 7:45:16 PM2/21/08

to

I catch this thread by accident and it was the one I need now, I post a
thread about a system that will work in a factory for 24/7 and I am very
afraid for DB corruption or application hangup

the application will work in a network for 4 users only but speed is
critical demand, and from my bitter experience Paradox is hell no one
should use it ever

"Interbase" I like it so much but when power goes off sometimes the DB
get corrupted, a simple scan disk utility to the db partition solve the
problem but this is also not acceptable to be happened.

This leave me to the UGLY choice "access db" it did not get corrupted
ever from power loose but it is so stupid database with very poor SQL
features I dont like at all!!

I am really confused

Zoren Lendry

unread,

Feb 21, 2008, 8:27:31 PM2/21/08

to

Isaac wrote:
> the application will work in a network for 4 users only but speed is
> critical demand, and from my bitter experience Paradox is hell no one
> should use it ever
>
> "Interbase" I like it so much but when power goes off sometimes the DB
> get corrupted, a simple scan disk utility to the db partition solve the
> problem but this is also not acceptable to be happened.
>
> This leave me to the UGLY choice "access db" it did not get corrupted
> ever from power loose but it is so stupid database with very poor SQL
> features I dont like at all!!
>
> I am really confused

FWIW, I lump Paradox and Access in exactly the same category. I've had
similar luck with both.

As far as IB getting corrupted with the power being switched off -- if
you care about your data, you will invest in the proper UPS with
notification capability. The value of the data dictates how far you go
in that direction (how much money you spend). I think all the industrial
strength databases out there, when coupled with the proper hardware, can
provide the level of reliability that you need, including the free ones,
FB and PG.

Loren sZendre

Arthur Hoornweg

unread,

Feb 22, 2008, 2:44:37 AM2/22/08

to

Isaac wrote:

> "Interbase" I like it so much but when power goes off sometimes the DB
> get corrupted, a simple scan disk utility to the db partition solve the
> problem but this is also not acceptable to be happened.

Listen to Zoren's advice, invest the $100 or so in a UPS. Be sure to
connect its serial cable to the PC so Windows is notified that it has
to shutdown properly if the power goes away.

You said "scan disk utility", I hope you didn't mean Microsoft's
scandisk? I do hope you don't use the FAT32 file system on your
database partition? And do you use the "superserver" or "classic"
variety of Interbase?

Isaac

unread,

Feb 22, 2008, 7:29:38 AM2/22/08

to

Arthur Hoornweg wrote:

> You said "scan disk utility", I hope you didn't mean Microsoft's
> scandisk? I do hope you don't use the FAT32 file system on your
> database partition? And do you use the "superserver" or "classic"
> variety of Interbase?
>
>
Arthur Hoornweg wrote:
> You said "scan disk utility", I hope you didn't mean Microsoft's
> scandisk? I do hope you don't use the FAT32 file system on your
> database partition? And do you use the "superserver" or "classic"
> variety of Interbase?

- any scan disk indeed fix the corruption
- thanks you remind me to make sure the partition is NTFS
- do you mean superserver IB 7.5 ? If so then yes I use classic version
6.5 !!

Thanks

Isaac

unread,

Feb 22, 2008, 9:52:10 AM2/22/08

to

What also you r opinion about mers version ib_wi_os_tIB6_0_2_0.exe ???

Thanks

Wayne Niddery (TeamB)

unread,

Feb 22, 2008, 10:17:50 AM2/22/08

to

"Isaac" <as...@fsdf.com> wrote in message
news:47be...@newsgroups.borland.com...

>
> "Interbase" I like it so much but when power goes off sometimes the DB get
> corrupted, a simple scan disk utility to the db partition solve the
> problem but this is also not acceptable to be happened.

For InterBase, make sure you set the Forced Writes feature to true, this
insures changes to the database are written to disk immediately. With this
on, even pulling the plug should not result in a corrupted database unless
the timing is literally in the middle of a write.

In any case, you should be installing a UPS to protect the system.

--
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca)

Wayne Niddery (TeamB)

unread,

Feb 22, 2008, 10:27:35 AM2/22/08

to

"Isaac" <as...@fsdf.com> wrote in message

news:47bee177$1...@newsgroups.borland.com...

> What also you r opinion about mers version ib_wi_os_tIB6_0_2_0.exe ???

If you *must* use a free version of Interbase, then from mers.com most will
recommend using 6.0.1.6 rather than 6.0.2.0, the reason is the latter
version has a bug affecting the execution of triggers.

However I cannot emphasize too strongly that you should not bother with
these versions at all any longer. They are just too old and do not run well
on newer multi-core/multi-processor systems, nor with newer versions of
Windows. They will give you very poor performance as a result.

If this is a critical application, then the small cost of a new version of
InterBase and the few required concurrent licenses you seem to need for this
should not be an issue. If this cost really is an issue, then I would
recommend Firebird.

Wayne Niddery (TeamB)

unread,

Feb 22, 2008, 10:20:08 AM2/22/08

to

"Isaac" <as...@fsdf.com> wrote in message

news:47be...@newsgroups.borland.com...

> - do you mean superserver IB 7.5 ? If so then yes I use classic version
> 6.5 !!

If you are using "classic" version then, AFAIK< you must be using version 6,
not 6.5. There is no longer any "classic" architecture, only superserver.

See my other reply as well.

Bill Todd [TeamB]

unread,

Feb 22, 2008, 11:01:49 AM2/22/08

to

AFAIK there has not been a classic architecture version of IB since IB
5.x.

--
Bill Todd (TeamB)

Isaac

unread,

Feb 22, 2008, 11:43:25 AM2/22/08

to

Wayne Niddery (TeamB) wrote:
> "Isaac" <as...@fsdf.com> wrote in message
> news:47be...@newsgroups.borland.com...
>>
>> "Interbase" I like it so much but when power goes off sometimes the DB
>> get corrupted, a simple scan disk utility to the db partition solve
>> the problem but this is also not acceptable to be happened.
>
>
> For InterBase, make sure you set the Forced Writes feature to true, this
> insures changes to the database are written to disk immediately. With
> this on, even pulling the plug should not result in a corrupted database
> unless the timing is literally in the middle of a write.
>
> In any case, you should be installing a UPS to protect the system.
>

Thanks alot you ended my dizziness in this matter, I really appreciate
your pro answer !!!

Isaac

unread,

Feb 22, 2008, 12:11:08 PM2/22/08

to

Wayne Niddery (TeamB) wrote:
> "Isaac" <as...@fsdf.com> wrote in message
> news:47be...@newsgroups.borland.com...
>>
>> "Interbase" I like it so much but when power goes off sometimes the DB
>> get corrupted, a simple scan disk utility to the db partition solve
>> the problem but this is also not acceptable to be happened.
>
>
> For InterBase, make sure you set the Forced Writes feature to true, this
> insures changes to the database are written to disk immediately. With
> this on, even pulling the plug should not result in a corrupted database
> unless the timing is literally in the middle of a write.
>
> In any case, you should be installing a UPS to protect the system.
>

I opened the purchase page for Interbase I found many choices what
should I choose for 4 user database ???

Thanks

Wayne Niddery (TeamB)

unread,

Feb 22, 2008, 1:49:50 PM2/22/08

to

"Isaac" <as...@fsdf.com> wrote in message

news:47bf...@newsgroups.borland.com...

>
> I opened the purchase page for Interbase I found many choices what should
> I choose for 4 user database ???

You need:
- Interbase 2007 Server. For your needs it sound like you only need a single
CPU license, so pick the single user link ($200 U.S.). That gives you the
server license plus one user license.
- 3 Interbase 2007 Simultaneous User licenses ($150 U.S. each).

There are separate links for ESD (electronic download) and, further down,
traditional (wait for the CD delivery), price is the same.

Jlouro

unread,

Feb 26, 2008, 5:33:33 AM2/26/08

to

"Arthur Hoornweg" <antispam...@casema.nl.net> wrote in message
news:47b98f4a$3...@newsgroups.borland.com...

> Jlouro wrote:
>> What should be the best database to start a new project
>>
>> Currently I use PARADOX with the BDE.
>
> You didn't mention how many simultaneous users and what level of
> user access security you want.
>
> For a local database for a single (or few) user(s), the Microsoft
> OleDB provider for Jet (=MS Access *.MDB file format) databases
> is almost impossible to beat since it's free, it's already part of
> Windows (so no installation hassle), it supports unicode and it
> has a pretty decent SQL.
>

I has quite but I was reading ;)

I need C/S, and several users accessing that DB at the same time, how many I
don't know.

I agree with most that been said about Access, and it's not an option.

My options now are:

BlackFish

SQLite

FireBird

PostGres

myBase

Why blackfish, well I have watching some webminar about it and its
integration with DELPHI is a by bonus. It's not free but .

I also plan to be using ADO so that I can switch database from client to
client if needed, and to be able to build a system in what I can use no
matter what database.

But still I have to do some test with dbExpress, now DBx4 and ADO to make
the final choice.

Thanks all of you it's a been a very interesting post to read.

João Louro

Paul Scott

unread,

Feb 26, 2008, 6:25:21 AM2/26/08

to

On Tue, 26 Feb 2008 10:33:33 -0000, Jlouro <jlou...@clix.pt> wrote:

> I also plan to be using ADO so that I can switch database from client to
> client if needed,

In case anyone might read too much into that comment, it's worth repeating
that ADO does *not* give your application a database-agnostic front-end
which allows you to use the exact same SQL statement syntax on any
connected-via-ADO database

And, except for the most trivial of SQL statements, that also applies to
MS databases!

--
Paul Scott
Information Management Systems
Macclesfield, UK.

Zoren Lendry

unread,

Feb 26, 2008, 2:17:54 PM2/26/08

to

Jlouro wrote:
> My options now are:
>
> BlackFish

Never used it. Deployment licenses keep me away from it. But it has some
intriguing benefits when you read the feature matrix.

> SQLite

Missing some very critical standard SQL features.

> FireBird

Great connectivity options in Delphi. Great performance and stability
(and free). And the direction that Firebird is going bodes well for
long-term use. Dozens of functions that you used to have to use UDF's
for are now built-in.

> PostGres

Solid choice. Not so many connectivity options in Delphi (but hey, you
only need one, right!).

> myBase

Don't know enough to say anything.

Bottom line for me -- my own options come down to how rich the Stored
Procedure and Trigger language is, how well referential integrity is
implemented, existence of domains, use of sequences (generators) instead
of AutoIncrement fields is a huge advantage, live backup capabilities, etc.

Good luck to you,

Loren

Arthur Hoornweg

unread,

Feb 27, 2008, 5:33:17 AM2/27/08

to

Jlouro wrote:
> My options now are:
> BlackFish
> SQLite
> FireBird
> PostGres
> myBase

afaik SQLITE is a DLL-based beastie just like the Access/Jet database, so
similar disadvantages apply.

If you plan to use ADO, do take a closer look at MS Sql Server 2005
Express. It is free of cost and syntax-compatible to the larger versions.
It has no limitations other than the 1-CPU limit and 4 GB database size
(no speed limitation either). My experiences with this server are quite
positive.

Zoren Lendry

unread,

Feb 27, 2008, 1:17:13 PM2/27/08

to

Arthur Hoornweg wrote:
> If you plan to use ADO, do take a closer look at MS Sql Server 2005
> Express. It is free of cost and syntax-compatible to the larger versions.
> It has no limitations other than the 1-CPU limit and 4 GB database size
> (no speed limitation either). My experiences with this server are quite
> positive.

It is a good option for many people. I personally hate the way triggers
are implemented in SQL Server, and find its locking mechanisms to be a
bad design. Plus, as soon as you say the limitations won't get in your
way, you find yourself a year or two down the road paying license fees.

A lot of folks think MySQL is free, but it's not. If you want a free db
without a loss in power or features, then Firebird and PostreSQL seem to
be the best choices.

Loren sZendre

Arthur Hoornweg

unread,

Mar 4, 2008, 10:23:49 AM3/4/08

to

Zoren Lendry wrote:

> It is a good option for many people. I personally hate the way triggers
> are implemented in SQL Server, and find its locking mechanisms to be a
> bad design.

Could you please elaborate on the triggers? I get along with them
just fine. I had to get used a bit to handling the virtual "inserted"
and "deleted" tables but it isn't too bad.

I haven't run into any locking problems yet, either, but I religiously
use (short) transactions for everything including reading of data.

"read committed" is a good transaction isolation compromise IMHO.
A versioning/snapshot database may be better, of course, but I suppose
it could also become problematic if one has colleagues who are sloppy
with transactions...

Arthur Hoornweg

unread,

Mar 4, 2008, 10:26:52 AM3/4/08

to

Zoren Lendry wrote:

> Plus, as soon as you say the limitations won't get in your
> way, you find yourself a year or two down the road paying license fees.

This may be offset by the overwhelming amount of literature and free
support groups available. That certainly helped me...

Zoren Lendry

unread,

Mar 4, 2008, 12:48:23 PM3/4/08

to

Arthur Hoornweg wrote:
> Zoren Lendry wrote:
>
>> It is a good option for many people. I personally hate the way
>> triggers are implemented in SQL Server, and find its locking
>> mechanisms to be a bad design.
>
> Could you please elaborate on the triggers? I get along with them
> just fine. I had to get used a bit to handling the virtual "inserted"
> and "deleted" tables but it isn't too bad.

You've obviously been dealing with the limitations of SQL Server
triggers for a long time, judging from your statement "it isn't too
bad". And granted, all db's have limitations due to their original
design architectures and feature choices -- and a developer must learn
how to deal with them. But I find it absolutely ridiculous, that a
company the size of MS, knowing the SQL standard, does not provide a
simple "for each row" trigger like Oracle or InterBase. It does require
an undue burden on the developer to work around this fundamental design
mistake by using the inserted and deleted tables, resulting in code that
is much harder to debug and test. MS had 5 years before SQL Server 2005
was brought out, and they couldn't even correct the omission in that
time frame.

As far as locking goes, just read "Expert Oracle Database Architecture"
by Thomas Kyte. It's a complicated topic that could not be done
justice to by me. Kyte talks about how Oracle doesn't even have the
concept of locks as implemented in SQL Server, and the benefits it brings.

In fact, if you look at the Row Versioning engine in SQL Server, MS has
it turned off by default, with huge warnings against using it, for
performance reasons. If you can't do it correctly, why bother with the
feature?

Yes, a developer can get used to these limitations, and through years of
experience, can make it seem like those limitations do not exist. I,
however, having learned Oracle, InterBase and Firebird first, find SQL
Server's design flaws to be enough to make me stay away. If InterBase or
Firebird is not powerful enough, then I use Oracle.

Loren sZendre

unread,

Mar 5, 2008, 10:10:24 AM3/5/08

to

"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:47ce...@newsgroups.borland.com...

>
> You'd do it like every other workaround for weird legacy features in
> SQL Server: Make a global option somewhere to turn it on/off.

I definitely like the suggestion by Bill and Zoren better. Then you could
add new "per row" triggers to existing databases without conflict.

Craig Stuntz [TeamB]

unread,

Mar 5, 2008, 9:15:36 AM3/5/08

to

I was being sarcastic.

--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz

All the great TeamB service you've come to expect plus (New!)
Irish Tin Whistle tips: http://learningtowhistle.blogspot.com

Arthur Hoornweg

unread,

Mar 5, 2008, 11:04:51 AM3/5/08

to

Zoren Lendry wrote:

> You've obviously been dealing with the limitations of SQL Server
> triggers for a long time, judging from your statement "it isn't too
> bad".

Not really, I'm just eager to learn to avoid pitfalls. If you have any
horrific examples, please share them with me. I only started working
with MSSQL and Jet4/MDB using ADO some 5 years ago and had to
mainly learn by doing and by reading whatever was available. I
am by no means an expert. But I have grown to be fond of MSSQL
because it does quite well what I tell it to do.

> And granted, all db's have limitations due to their original
> design architectures and feature choices -- and a developer must learn
> how to deal with them. But I find it absolutely ridiculous, that a
> company the size of MS, knowing the SQL standard, does not provide a
> simple "for each row" trigger like Oracle or InterBase.

Since SQL is a language that handles sets, it never surprised
me that MS-SQL triggers would work with the same principle.
In fact I didn't even know that other databases do it differently
(I only work with MSSQL). I agree with you that they're hell to
debug and that a row-based thingy would make life much
simpler. But can't you just declare a cursor and iterate through
the set, if you want to manipulate them row-wise?

> It does require
> an undue burden on the developer to work around this fundamental design
> mistake by using the inserted and deleted tables, resulting in code that
> is much harder to debug and test. MS had 5 years before SQL Server 2005
> was brought out, and they couldn't even correct the omission in that
> time frame.

What about overhead? Is such a row-based trigger called once for
every row?

MSSQL only became our database of choice for the reason that all
of our customers (mainly oil companies) use it. That, and the availablility
of free versions (that we deploy in many field laboratories).
So far it hasn't let us down and performs very well for us. Never lost
a bit of data.

> As far as locking goes, just read "Expert Oracle Database Architecture"
> by Thomas Kyte. It's a complicated topic that could not be done justice
> to by me. Kyte talks about how Oracle doesn't even have the concept of
> locks as implemented in SQL Server, and the benefits it brings.
>
> In fact, if you look at the Row Versioning engine in SQL Server, MS has
> it turned off by default, with huge warnings against using it, for
> performance reasons. If you can't do it correctly, why bother with the
> feature?

Never tried it. In fact, I really need the "read committed" transaction
isolation
level. Our database is used for numerical data acquisition.
Multiple tasks store numerical data from multiple sources into the same
tables. Under these conditions, it is very well possible that multiple
tasks try to
update (different) columns in the same row simultaneously. And we expect
all these writes to succeed. I suppose this behaviour would be undesirable
in many other databases.

>
> Yes, a developer can get used to these limitations, and through years of
> experience, can make it seem like those limitations do not exist. I,
> however, having learned Oracle, InterBase and Firebird first, find SQL
> Server's design flaws to be enough to make me stay away. If InterBase or
> Firebird is not powerful enough, then I use Oracle.

Fair enough. I don't know what type of applications you write and for how
many concurrent users, so I really can't judge if MSSQL isn't up to the
job.

Bill Todd [TeamB]

unread,

Mar 5, 2008, 10:52:12 AM3/5/08

to

Arthur Hoornweg wrote:

> But can't you just declare a cursor and iterate through
> the set, if you want to manipulate them row-wise?

Sure you can and you write at least five times as many lines of code
for every trigger as you would in a database that supports per row
triggers. Even worse is the lack of before triggers. In SQL server you
must use instead of triggers which means you have to recode the
operation that fired the trigger. I learned per row triggers first and
I curse SQL Server every time I have to use it.

--
Bill Todd (TeamB)

Paul Scott

unread,

Mar 5, 2008, 1:49:52 PM3/5/08

to

On Wed, 05 Mar 2008 16:04:51 -0000, Arthur Hoornweg
<antispam...@casema.nl.net> wrote:

> Zoren Lendry wrote:
>
>> You've obviously been dealing with the limitations of SQL Server
>> triggers for a long time, judging from your statement "it isn't too
>> bad".
>
> Not really, I'm just eager to learn to avoid pitfalls. If you have any
> horrific examples, please share them with me. I only started working
> with MSSQL and Jet4/MDB using ADO some 5 years ago and had to
> mainly learn by doing and by reading whatever was available. I
> am by no means an expert. But I have grown to be fond of MSSQL
> because it does quite well what I tell it to do.

I'm in the same situation as Arthur (and for a similar time period). While
I would agree that MSSQL, Access and ADO do work quite well when you get
them running, to say that I was "fond" of them would be a gross
exaggeration - especially when I am trying to work out which particular
set of arcane incantations are needed to achieve decent performance!

But I have to say that despite our app inserting hundreds and thousands of
calculated performance figures at a time, the only "corrupt MDB" we have
ever suffered turned out to be a partially-copied MDB (because a network
disk was full) and I had wrongly assumed that the O/S would remove the
incomplete file.

Back to the subject, speaking personally (and exaggerating wildly for
effect), I consider most of these database-supplier-specific features such
as triggers and stored procedures to be "part of the problem", rather than
"part of the solution".

(And if you'd seen the 3,000+ line trigger in one of our customer's
(Oracle) databases, I think you might agree!)

Triggers, cascade deletes, etc might be fair enough in cases where you
really are /forced/ to let your users directly manipulate the data. But
seeing as this is a Delphi group, shouldn't all your user updates be done
through nice, safe, Delphi applications (or Delphi
wrappers/procedures/objects/DLLs/OPFs)?

After all there is very little that can be done in a trigger that can't be
done in (external) SQL. And although you might lose a little speed, the
more you keep your Business Rules in Delphi Code, the less time and effort
it will cost to switch to a different database supplier. Conversely, the
more you make use of the "features", the more you will be locked in to
that particular DB-supplier (and don't they know it!).

(Ok, I am exaggerating. If anything is "too slow", then by all means move
/that/ operation inside the database. But beware of "helpful" DBA's who
insist on putting /everything/ inside the DB - aka "making themselves
indispensable")

Zoren Lendry

unread,

Mar 5, 2008, 2:15:12 PM3/5/08

to

Bill, you just summed it up perfectly. All I can say to Arthur, is if
you get used to "real" triggers, you will hate SQL Server's trigger
architecture.

But I'm not saying SQL Server is inadequate. Many people have learned to
code around its design flaws, and do amazing things with it. But like
Bill, for me the trigger issue brings up the bile like few things in life.

Loren sZendre

Bill Todd [TeamB]

unread,

Mar 5, 2008, 1:30:27 PM3/5/08

to

Paul Scott wrote:

> After all there is very little that can be done in a trigger that
> can't be done in (external) SQL. And although you might lose a
> little speed, the more you keep your Business Rules in Delphi Code,
> the less time and effort it will cost to switch to a different
> database supplier. Conversely, the more you make use of the
> "features", the more you will be locked in to that particular
> DB-supplier (and don't they know it!).

Doing everything in Delphi code is ok if 1) you do not have to do any
batch record processing which will create a huge amount of network I/O
and 2) You have one and only one app that accesses the database. Trying
to keep the business rules in synch across half a doze apps hitting the
same database is almost impossible.

--
Bill Todd (TeamB)

Paul Scott

unread,

Mar 5, 2008, 3:00:57 PM3/5/08

to

On Wed, 05 Mar 2008 18:30:27 -0000, Bill Todd [TeamB] <n...@no.com> wrote:

> Doing everything in Delphi code is ok if 1) you do not have to do any
> batch record processing which will create a huge amount of network I/O
> and 2) You have one and only one app that accesses the database. Trying
> to keep the business rules in synch across half a doze apps hitting the
> same database is almost impossible.

Granted! (I did saying I was "exaggerating for effect")

... although in such cases it can be useful to confirm that all those
Business Rules are actually necessary (or are your trousers kept up by
belt, braces, piece-of-string AND hands-in-pockets triggers) - or if some
of those rules are a band-aid to cover up for an incorrect database design.

I recently hit a case where the initial design had created some
PrimaryKeys with too many columns - "two columns good, four columns (must
be) better!" - and triggers had been added to check and enforce uniqueness

Arthur Hoornweg

unread,

Mar 6, 2008, 2:45:36 AM3/6/08

to

Paul Scott wrote:

> Triggers, cascade deletes, etc might be fair enough in cases where you
> really are /forced/ to let your users directly manipulate the data. But
> seeing as this is a Delphi group, shouldn't all your user updates be
> done through nice, safe, Delphi applications (or Delphi
> wrappers/procedures/objects/DLLs/OPFs)?

Of course, but only if one single instance of a Delphi application would
be doing it. If multiple instances were connected to your DB you
would run into serious concurrency problems.
But if you would use a Delphi application as middleware for all your
clients it would work, yes, but it would have to be a serious piece
of software if you don't want it to be the bottleneck.

Middleware is very much in fashion nowadays, but IMHO it should
not go so far as to replace referential integrity (foreign key
relationships with constraints) on the server.

No piece of software is as directly attached to the data as the
database server itself, which caches most of the data in RAM,
so a "cascade delete" should be lightyears faster than a bunch
of "delete from..." statements that each involve a network roundtrip
and require intimate knowledge of foreign key relationships.
Having said this, with MSSQL2005 it should be possible to code
triggers etc in any .NET language rather than in SQL.

BTW, the thing I miss most of all on MSSQL is row-based permissions.

I have data tables that store data from numerous different projects
and it would be wonderful if I could elegantly limit a user's access
to rows instead of having to code around it with stored procedures
or updatable views that call UDF's. Especially since speed is an
issue.

Paul Scott

unread,

Mar 6, 2008, 2:00:21 PM3/6/08

to

On Thu, 06 Mar 2008 07:45:36 -0000, Arthur Hoornweg
<antispam...@casema.nl.net> wrote:

> Middleware is very much in fashion nowadays, but IMHO it should

> not go so far as to replace referential integrity...

I don't think I ever suggested anything /that/ extreme :)

Arthur Hoornweg

unread,

Mar 7, 2008, 4:51:27 AM3/7/08

to

Paul Scott wrote:

>> Middleware is very much in fashion nowadays, but IMHO it should
>> not go so far as to replace referential integrity...
>
> I don't think I ever suggested anything /that/ extreme :)

You did mention cascading deletes ...

Paul Scott

unread,

Mar 7, 2008, 9:59:40 AM3/7/08

to

On Fri, 07 Mar 2008 09:51:27 -0000, Arthur Hoornweg
<antispam...@casema.nl.net> wrote:

> Paul Scott wrote:
>
>>> Middleware is very much in fashion nowadays, but IMHO it should
>>> not go so far as to replace referential integrity...
>> I don't think I ever suggested anything /that/ extreme :)
>
> You did mention cascading deletes ...

And, boy!, do they cause me trouble when I try to collect all changes made
by a client to /his/ Access subset database and then apply them to our
central superset SQLserver...

Client: Original MDB didn't have row with PK=ABC and current MDB does
have one

Me: Add row with PK=ABC

SQLserver: Fine!

Client: Original MDB did have row with PK=XYZ but current MDB doesn't
have one

Me: Delete row with PX=XYZ

SQL/HAL: I'm sorry, Dave, I'm afraid I can't do that!

("Key not found" because the row was cascade deleted from a previous
update)

While I can (mostly) get round this by analysing the topography of the
ForeignKey relationships (and make sure I delete all "detail" rows before
removing any "master" rows) consequential modifications made by triggers
in the master SQLserver database are (almost) impossible to work-round

Paul Scott

unread,

Mar 7, 2008, 10:15:31 AM3/7/08

to

On Fri, 07 Mar 2008 14:59:40 -0000, Paul Scott
<paul....@inf-rem-ove-the-dashes-mansys.com> wrote:

Of course, "topography" should have read "topology"

(and the reference was to Kubrick's/Arthur C C's "2001")

unread,

Mar 18, 2008, 11:16:57 AM3/18/08

to

Paul Scott wrote:

> And I can't (locate in order to) delete a row which was cascade-deleted
> by the delete of a previous row or by a central database trigger. So the
> app has to analyse the database schema to determine the FK relationships
> and work out the best order to apply the updates.

I am using a MS Sql Server database at both ends, using Access only as the
transport format.
Because I update the "deletedobjects" table from inside MSSQL triggers,
it contains all deleted objects in the correct sequence (even when cascaded
deletes happen). So that avoids a tricky problem.

Also I recommend using GUIDS as primary key. This enables me to merge
an unlimited number of databases without collisions (the main database
contains 2 million records so far).

Alan

unread,

May 5, 2008, 10:18:43 AM5/5/08

to

On Mar 7, 10:59 pm, "Paul Scott" <paul.sc...@inf-rem-ove-the-dashes-

mansys.com> wrote:
> On Fri, 07 Mar 2008 09:51:27 -0000, Arthur Hoornweg
>

> <antispam.hoorn...@casema.nl.net> wrote:
> > Paul Scott wrote:
>
> >>> Middleware is very much in fashion nowadays, but IMHO it should
> >>> not go so far as to replace referential integrity...
> >> I don't think I ever suggested anything /that/ extreme :)
>
> > You did mention cascading deletes ...
>
> And, boy!, do they cause me trouble when I try to collect all changes made
> by a client to /his/ Access subset database and then apply them to our
> central superset SQLserver...
>

> Client: OriginalMDBdidn't have row with PK=ABC and currentMDBdoes

> have one
>
> Me: Add row with PK=ABC
>
> SQLserver: Fine!
>

> Client: OriginalMDBdid have row with PK=XYZ but currentMDBdoesn't

> have one
>
> Me: Delete row with PX=XYZ
>
> SQL/HAL: I'm sorry, Dave, I'm afraid I can't do that!
>
> ("Key not found" because the row was cascade deleted from a previous
> update)
>
> While I can (mostly) get round this by analysing the topography of the
> ForeignKey relationships (and make sure I delete all "detail" rows before
> removing any "master" rows) consequential modifications made by triggers
> in the master SQLserver database are (almost) impossible to work-round
>
> --
> Paul Scott
> Information Management Systems
> Macclesfield, UK.

Hi,

I have used MS Access for a long time and it is stable and works fine.
Besides, once unfortunaltely the database is corrupted, there is a
tool called Advanced Access Repair we can use. I once have used it to
repair many corrupt Access MDB files on my damaged disks successfully.
Its homepage is http://www.datanumen.com/aar/ So I think Access is
good choice.

Alan