Subject Re: [firebird-support] how to debug PSQL and print log
Author Thomas Steinmaurer
> set term ^ ;
> create procedure INS_INST_YMD(INST varchar(32), YMD date, TB integer, MB integer, DB integer)
> as
> declare variable ID integer;
> declare variable ID2 integer;
> begin
> ID = 0;
> select ID from I where INST = :INST into :ID;
> if (ID> 0) then
> begin
> ID2 = 0;
> select ID from HD where ID = :ID and YMD = :YMD into :ID2;
> if (ID2 = 0) then
> begin
> insert into HD(ID, YMD, TB, MB, DB) values(:ID, :YMD, :TB, :MB, :DB);
> end
> end
> end
> ^
> set term ; ^
> For the above stored procedure, I execute like
> execute procedure INS_INST_YMD 's0001', '2011-12-21',11,12,13;
> I would like to trace the internal execution of INS_INST_YMD for debugging.
> How do I print logging information to a file?

You can't out-of-the box. You have the following options:

- Insert trace data into an external table, which by design works out of
transaction control, so you even get trace stuff inserted when the
(outer) transaction is rolled back

- Use an AUTONOMOUS TRANSACTION block which surrounds inserting trace
data into a regular table. This way, you even get trace data into the
trace table when the (outer) transaction is rolled back. A regular table
is also easier to manage when it comes to adding indexes for faster
search in trace data etc ...

- Use a tool which lets you debug stored procedures. While there is no
debug API/hook for client application, e.g. Database Workbench is pretty
good simulating debugging stored procedures. Look here:

With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist