🚀 go-pugleaf

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)

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

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.)

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

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

MikeR wrote:
> 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>

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

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>

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

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.




-- 
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.)

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

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.




-- 
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.)

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

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

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

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?


-- 
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.)

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

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

Thanks

"Isaac" <asda@fsdf.com> wrote in message 
news:47be1af8@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) 

"Isaac" <asda@fsdf.com> wrote in message 
news:47bec00f@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.

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

"Isaac" <asda@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 (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

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

-- 
Bill Todd (TeamB)

Wayne Niddery (TeamB) wrote:
> "Isaac" <asda@fsdf.com> wrote in message 
> news:47be1af8@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 !!!

Wayne Niddery (TeamB) wrote:
> "Isaac" <asda@fsdf.com> wrote in message 
> news:47be1af8@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

"Isaac" <asda@fsdf.com> wrote in message 
news:47bf0209@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.

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

"Arthur Hoornweg" <antispam.hoornweg@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

On Tue, 26 Feb 2008 10:33:33 -0000, Jlouro <jlourodev@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.

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

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.


-- 
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.)

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

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

(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 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...



-- 
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.)

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

Wayne Niddery (TeamB) wrote:

> How do you make it so a trigger can be called for each record without
> forcing massive changes for all existing code that attempts to
> iterate affected records in one call?

Add a new keyword to the trigger declaration syntax to indicate that
the trigger should fire per row. I agree that the need for backward
compatibility is absolute.

-- 
Bill Todd (TeamB)

"Zoren Lendry" <zorenlendry@yahoo.com> wrote in message 
news:47cd897f$1@newsgroups.borland.com...
> 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.


To be fair, MS SQL server already had a large installed base before this 
time and backward compatibilty would be rather crucial (not that MS is 
particularly good at that generally, but they would no doubt take it more 
seriously with something like SQL Server). How do you make it so a trigger 
can be called for each record without forcing massive changes for all 
existing code that attempts to iterate affected records in one call?

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

Wayne Niddery (TeamB) wrote:
> To be fair, MS SQL server already had a large installed base before this 
> time and backward compatibilty would be rather crucial (not that MS is 
> particularly good at that generally, but they would no doubt take it 
> more seriously with something like SQL Server). How do you make it so a 
> trigger can be called for each record without forcing massive changes 
> for all existing code that attempts to iterate affected records in one 
> call?

Were it up to me :), I would simply allow the old triggers to keep on 
working as before. But I would introduce a new syntax (I would probably 
copy Oracle's syntax), where if you declare the trigger as "for each 
row", then you get the new behavior. The old way could be supported in 
perpetuity, without requiring any rewrites.

It really is a shame. SQL Server has some great features; just think of 
all the cool things you can do with T-SQL. It's too bad dealing with 
triggers is such a pain. Developers like A. Hoornweg take it in stride, 
and write complicated triggers in their sleep. But many developers hit a 
brick wall when it comes to SQL Server triggers.

Loren sZendre

Wayne Niddery (TeamB) wrote:

> How do you make it so a trigger can be called for each record without
> forcing massive changes for all existing code that attempts to
> iterate affected records in one call?

	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.

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Useful articles about InterBase development:
  http://blogs.teamb.com/craigstuntz/category/21.aspx

	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

"Zoren Lendry" <zorenlendry@yahoo.com> wrote in message 
news:47cdcfe2$1@newsgroups.borland.com...
>
> Were it up to me :), I would simply allow the old triggers to keep on 
> working as before. But I would introduce a new syntax (I would probably 
> copy Oracle's syntax), where if you declare the trigger as "for each row", 
> then you get the new behavior. The old way could be supported in 
> perpetuity, without requiring any rewrites.

Yes, you and Bill both came up with that - declaring it in the trigger 
header would certainly be a way to do it. Now you just need to persuade MS! 
<g>

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

"Craig Stuntz [TeamB]" <craig_stuntz@nospam.please [a.k.a. acm.org]> wrote 
in message news:47cea19a@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.

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

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)

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.






-- 
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.)

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)

On Wed, 05 Mar 2008 16:04:51 -0000, Arthur Hoornweg  
<antispam.hoornweg@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")

-- 
Paul Scott
Information Management Systems
Macclesfield, UK.

Bill Todd [TeamB] wrote:
> 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, 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

On Wed, 05 Mar 2008 18:30:27 -0000, Bill Todd [TeamB] <no@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


-- 
Paul Scott
Information Management Systems
Macclesfield, UK.

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.



-- 
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.)

On Thu, 06 Mar 2008 07:45:36 -0000, Arthur Hoornweg  
<antispam.hoornweg@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 :)

-- 
Paul Scott
Information Management Systems
Macclesfield, UK.

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 ...




-- 
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.)

On Fri, 07 Mar 2008 09:51:27 -0000, Arthur Hoornweg  
<antispam.hoornweg@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
Information Management Systems
Macclesfield, UK.

On Fri, 07 Mar 2008 14:59:40 -0000, Paul Scott  
<paul.scott@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")


-- 
Paul Scott
Information Management Systems
Macclesfield, UK.