Mega Code Archive

Adding arguments to a stored procedure in interbase

Question: I need to add an argument to a stored procedure XXX. I did the code change, compiled the stored procedure and at first it seems to work, but now I get these error message like 'invalid request BLR at offset 12345'. I noticed that I have 2 other stored procedures AAA and BBB which call XXX and when I tried to update them, I couldn't compile them anymore. Worse, I cannot undo the change in XXX. What went wrong? Answer: Changing stored procedures within a network of dependencies can be tricky. If you use a good tool like IBExpert then you are lucky. IBExpert will warn you before you change the list of arguments or the returned data type. It will actually present a list of all procedures that use your procedure. The way to handle this problem is: identify the procedure where you need to change the arguments. Here: XXX identify which other procedures (triggers, views..) depend on this one. Here AAA and BBB. go to each of these (AAA, BBB) and comment out the reference to XXX. If necessary, comment out the complete procedure code body. Good tools like IBExpert will have a shortcut for this. go back and get a list of all procedures (triggers, views) depending on XXX. This list should now be empty. If not, go back to the previous step (except in a case of recursive stored procedures; I've never seen such a thing though). Otherwise now make your code change in XXX go to each of the commented out stored procedures (AAA, BBB), remove the comment and adjust the calling code Finally: always a good idea to 'Recompile All'. Again, a good tool like IBExpert does this with one mouse click for you. Good luck and hopefully you had a backup before you ran in that 'BLR offset' error.