Adding arguments to a stored procedure in InterBase

2 comments. Current rating: (1 votes). Leave comments and/ or 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:

2008-12-02, 04:30:44

chAlx from Russian Federation

I had same experience and found same solution. But there was some way to restore partially-compiled procedure stack without using backup (smth like commenting all dependent procedures and recompile them all). But how to recompile dependency tree only? IBExpert is not so clever yet..

NEW: Optional: Register   Login 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, or post under a registered account.
	Show city and country Show country only Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:
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. Add a picture: