Adding arguments to a stored procedure in InterBase

This article has not been rated yet. After reading, feel free to leave comments and rate it.

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:

1. identify the procedure where you need to change the arguments. Here: XXX
   
2. identify which other procedures (triggers, views..) depend on this one. Here AAA and BBB.
   
3. 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.
   
4. 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).
   
5. Otherwise now make your code change in XXX
   
6. go to each of the commented out stored procedures (AAA, BBB), remove the comment and adjust the calling code
   
7. 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.

http://www.ibexpert.com/

Comments:

2007-10-24, 01:57:51

anonymous from Mauritius

Good! Keywords:

Email address (not necessary): Rate as Off-topic Very Helpful Helpful Ok article Not useful Very bad	Hide my email when showing my comment. Please notify me once a day about new comments on this topic. Please provide a valid email address if you select this option.
It seems that you are from Washington, US . Info/ Feedback on this	Show city and country Show country only Hide my location
Leave your comment here:
Please type in the code:
Add a picture: Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity. The owner of this web site reserves the right to delete such material.