How to debug stored procedures?


Firebird still doesn't offer hooks for stored procedure debugging yet. Here are some common workarounds:

* You can log values of your variables and trace the execution via external tables. External tables are not a subject of transaction control, so the trace won't be lost if transaction is rolled back.

* You can turn your non-selectable stored procedure into selectable and run it with 'SELECT * FROM' instead of 'EXECUTE PROCEDURE' in order to trace the execution. Just make sure you fill in the variables and call SUSPEND often. It's a common practice to replace regular variables with output columns of the same name - so that less code needs to be changed.

* Some commercial tools like IBExpert or Database Workbench parse the stored procedure body and execute statements one by one giving you the emulation of stored procedure run. While it does work properly most of the time, please note that the behaviour you might see in those tools might not be exactly the same as one seen with actual Firebird stored procedure - especially if you have uninitialized variables or other events where behavior is undefined. Make sure you file the bug reports to tool makers and not to Firebird development team if you run such 'stored procedure debuggers'.

* Since Firebird 2.0 you can also use EXECUTE BLOCK to simulate stored procedures. EXECUTE BLOCK in Firebird 2.0 does not support input parameters (there is support in 2.1 and above), so you need to convert all of those to local variables (with DECLARE VARIABLE). For debugging, try changing the procedure until you find the problematic part. You can also add a bunch of suspends to see what's happening inside.


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.



All contents are copyright © 2007-2024 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous