How to debug stored procedures?
Submitted by: AdministratorFirebird 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 does not support input parameters, so you need to convert all of those to local variables (with DECLARE VARIABLE)
Submitted by: Administrator
* 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 does not support input parameters, so you need to convert all of those to local variables (with DECLARE VARIABLE)
Submitted by: Administrator
Read Online Firebird Job Interview Questions And Answers
Top Firebird Questions
☺ | How to detect the server version? |
☺ | How to write UDF s in Delphi? |
☺ | How do convert or display the date or time as string? |
☺ | How to drop all foreign keys in database? |
☺ | Is there some bulk load or other way to import a lot of data fast? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |