See >> below. Will take a while to get my arms around the rest, after try
Paul's suggestion, and look at the link. Thanks for thorough reply.

tks,

jwc

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Friday, June 20, 2003 9:01 PM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] SQL Error Trapping

At 04:02 PM 20/06/2003 -0400, you wrote:

>Would appreciate a pointer to some sample code showing how to trap SQL
>errors. Looked in the samples but my search mask is too broad (except or
>try).

Have you read the TI sheet on exception handling? If not, you'll find it
at http://www.ibobjects.com/TechInfo.html

>Am trying to trap errors in the ExecSQL method of tIboSql.

Do you mean TIB_DSQL?
Assuming yes..

>> It is tIboQuery (sorry; not tIboSql), which traces into the folllowing:

procedure TIBODataset.ExecSQL;
begin
CheckInactive;
FIncludeOutputParams := true;
try
{$IFDEF IBO_VCL40_OR_GREATER}
if not Prepared then
Prepare
else
SysUpdateParams;
if not InternalDataset.SQLIsValid then
{$ENDIF}
Prepare;
if IsSelectSQL then
Open
else
InternalDataset.ExecSQL;
except
FIncludeOutputParams := false;
raise;
end;
end;

As Paul pointed out, you are possibly not getting to your exception
handling code because you are resetting the debugger each time it breaks on
exception.

Looking at your code (and assuming it's a TIB_DSQL we are looking at):

// SqlError := '';

with TheQuery do begin
// close; This isn't a dataset, so there's nothing to close.
sql.Clear; // This causes the statement to Unprepare
sql.Add( SQLCommand ); // currently unprepared
try
Prepare; // can't execute statement that's not prepared
// and IBO will create an EIB_ISCError if Prepare fails
// With an ad hoc query like this, Prepare could fail
any time
Execute; // native method, ExecSQL is for compatibility,
// so save a bit of execution code
{ Success, so call the next thing you want to do}
except
on e:exception do
begin
if e is EIB_ISCError then
HandleAPIException (TheQuery, EIB_ISCError(e))
else
HandleDelphiException (e);
// this could be simply to call Raise
end;
end;
end;

procedure MyDatamodule.HandleAPIException (Statement: TIB_Statement,

Exception:
TIB_ISCError);
var SqlError: string;
begin
{ Now access all the properties of the Exception argument - refer to help
for TIB_ISCError - and build up your string for logging the error,
then e.g. }
// assuming we now have something in SqlError
if SqlError <> '' then
begin
if pWebApp <> nil then
SendErrorToWebServer (SqlError) // your error log mechanism
else
ShowMessage( sqlError ); // only use if you want to stop the show
end
else {something is wrong with your logic if it failed to put something
in the string };
{ now go ahead and handle the exception, e.g. }
with Statement.IB_Transaction do
if InTransaction then
RollBack;
GoBackToSquareOne;
end;

(*
ExecSQL fails with an error message; the exception is not handled below. I
receive a debugger exception notification stating that my project raised
exception class EIBO_ISCError with 'ISC ERROR CODE:nnn, and an ISC ERROR
MESSAGE
*)

Now, with IBO, handling API exceptions yourself like this is reinventing
the wheel. TIB_Statement has an OnError event which is designed to trap
API exceptions, and it has a heap of arguments that have the properties of
the EIB_ISCError already massaged into shape, ready for you to pass them on
to your own handler procedure(s). You should always move the actually
handling code out of the event to one or more other methods - basically
just test the OnError arguments for the type of exceptions you're planning
to handle and pass on the ones you want to use through to the handler that
deals with that particular exception.

There are lots of good reasons for doing it this way. From an
architectural point of view, you want to handle the same kind of exception
the same way for all statements - you don't want 100 statements, each with
its own idiomatic OnError handler that goes over the same ground for every
possible exception.

But it's also risky to try and resolve the exception inside the OnError
event. It could only work if the handler code itself doesn't cause more
exceptions. The likelihood that it will is high, because you can't tell at
which phase of which method the exception occurred. If you have a handler
with the potential to go into a spiral of exceptions that may or may not be
related to the one you first trapped, it's unlikely that the original
exception will get resolved at all.

IOW, never touch any of the data access objects (statement, transaction,
connection, etc.) inside the OnError event handler. At most, set some flag
to signal some condition, that an external method can read to work out
what's going on.

Keep your "tree" of handler methods as flat as possible. Not only does it
make debugging easier, but it keeps things flexible so that you can modify
or add handler code as new problems appear, without breaking anything else.

As for sample code, I don't think we have any in the demos. It's a project
that's been on my To-Do list for a long time, because we really need
it. Norman and I went part-way there with the (rather general) examples in
the TI sheet, but it's a work-in-progress.

It could be worth your while to visit the DbErrors demo in your Delphi
installation tree. For Delphi 5 on my system, it's in C:\Program
Files\Borland\Delphi5\Demos\Db\Dberrors. The VCL db error handling is very
cut-down compared to IBO's, but it might give you some ideas.

hth
Helen



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]