At 09:36 AM 17-09-02 +0300, you wrote:

>Hi Jason.
>
> I'm writing a finance application and it's multiuser. I'm trying to
> get meaningful err messages
>from the running SP's ( I don't use Triggers much I like controllig the
>events). IB's event option
>is not suitable for me becouse you can only get the messages when the
>transaction committed, this is
>too late for me.

No, this is not correct.

In your SP or trigger you catch an exception using an EXCEPTION statement
(using a custom exception which you have previously declared in your
metadata with a message string of up to 78 bytes).

Then you use ON EXCEPTION GDSCODE (for a specific database exception) or ON
EXCEPTION MyException to handle it. When an exception occurs, control
always passed into this block and you can do whatever you need, including
fire an event if you want.

In your handler you can either "silence" the exception (do something about
the bad data) and then, at the end of the block, control will pass back to
the statement following the one where the exception occurred; or you can
make the procedure terminate. If the exception which you caught was a
custom exception, the exception message which you defined returns to the
client in the Error Status Vector. IBO provides access to everything in
this array in several ways, including the OnError events of all TIB_ and
TIBO components.

You also have the option of firing a database event upon catching an exception.

There is a TI sheet on the TechInfo page about exception handling.

>When a user get's in a module a transaction starts separetaly and when the
>job is
>done the transaction commits. There are many SP's running in a
>transaction. I have to get the
>messages when the exception occures. I can't use exceptions also becouse
>it crashes the SP
>execution

What do you mean by "crashes the SP execution"?

>( And I can't handle the exception from client side, If there is a chance
>to do it please
>inform me).

Oh yes, oh yes. If you haven't explored IBO's capabilities for supporting
and expanding on the server side exception-handling features then you have
missed something wonderful. :-))

>Most of the time I call one SP at a time and that SP call's many sp's
>internally. One of
>The most important reaseon why I need this messages, if an exception
>occurs I have to roll back the
>SP's for data integritiy.

That is EXACTLY why PSQL has exception-handling and why IBO takes so much
care to surface it for you.

>Now waht will I do with database handle??
>
> I get the transaction handle when the transaction starts. I'm trying
> to get the dbhandle when my
>main connection connects. Wit these I'll get a two field unique (I
>couldn't test unique becouse I
>can't get dbhandle yet) index. I'll send these two field to my all SP's
>and when a exception occurs.
>An another SP will write the required information to a table with this
>informaiton. After execution
>of a SP I'll read the table, if there are records about these two field,
>I'll decide what to do
>depending to the exception states.
>
>This is why I need dbhandle.

No, you don't need the dbhandle, nor the transaction handle. Just use what
is already well encapsulated for you in that marvellous triangle: server,
client and IBO.

Helen