Delphi handling of SQL server generated errors

As I understand it, Delphi should raise an Exception whenever a client
app that is connected to an SQL Server runs an SQL statment that
fails. Anotherwords, whenever SQL Server rasies an error using the
"raiserror" statement in SQL Delphi should raise an exception in the
client application. However I noticed some unexpected behavior when
running some SQL scripts that caused errors, and have reduced the
situation to the following simple case:

Assume I've created a table called foo with a column called bar in a
database of my choice as shown:

create table foo (
bar varchar(100)
)

When running the following script in query analyzer, I get a red error
message as I would expect:

insert into foo (bar) values ('Test of delphi ADO error handling')
raiserror( 'Test Error Message', 16, 10 )

However, when I run this exact same script in a TADOCommand (using the
CommandText property and Execute method ) or a TQuery (using SQL
property and ExecSQL method), no error is shown.

If I comment out the first line of the script, the error is shown as
expected. If I change the first linet to a select statement, the error
is once again hidden.

So my impression is that a delphi component will hide any errors
raised by an SQL batch run against SQL Server, assuming that the batch
has had at least one successful insert or select statement previously
in the batch. Has anyone else noticed anything along these lines?

This example is obviously trivial, but this is actually a big problem
because my apps frequently run a series of long SQL batches that might
each contain nested stored procedures, cursors, etc. Each batch is
written so that if it encounters an error, it will use GOTO to jump to
the end of that batch and raise an error with raiserror('msg'. 16.
10). If one of these batches fails, I would like for the app to notice
and stop executing subsequent batches, and show an error message. As
it is however, the app has no idea something has gone wrong if the
raiserror occurs after a successfuly inser, so it will simply continue
running the rest of the batches until either one of them fails in a
more Delphi friendly way, or worse, all are completed and the user has
no idea something went wrong.

Any suggestions?
.

Relevant Pages

  • Re: TADODataset VERY Slow With MS SQL
    ... We've built applications in Delphi ... > application to Delphi 7 with MS SQL using TADODataset, ... > file and 2 details files containing payment status) as separate record sets ...
    (borland.public.delphi.database.ado)
  • Re: Codegear sold
    ... It dicovered and properly recognized MS SQL and IBM DB servers at my ... It is not a delphi app. ... Empty app takes about 50Mb. ... It asociates sql and eqb extensions. ...
    (borland.public.delphi.non-technical)
  • New project coming up...stay with Python, or go with a dot net language??? Your thoughts please!
    ... Did alot of work in Delphi, ... Began using MS SQL server as database. ... enthralled with Python, and using it, with wxWindows to write the interface, ...
    (comp.lang.python)
  • Re: how do you specify a port # when setting up a TadoConnection?
    ... I also created an ODBC datasource and used it to access my SQL tables from ... But if I use that same ODBC ... Interestingly I can do it from outside of Delphi but not in Delphi. ...
    (borland.public.delphi.database.ado)
  • Re: Idea of SQL integration
    ... to be able to transport its definition back and forth between Sql ... Query Analyzer window to a Delphi string constant declaration (I wrote ...
    (borland.public.delphi.non-technical)