DelphiFAQ Home Search:

Adding arguments to a stored procedure in InterBase

 

comments4 comments. Current rating: 5 stars (2 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.


Note:
IBExpert is developed at HK-Software, Germany and you may download a time limited trial at http://www.ibexpert.com/
Content-type: text/html

Comments:

2007-10-24, 01:57:51
anonymous from Mauritius  
Good!


Keywords:
2008-12-02, 04:30:44
chAlx from Russian Federation  
rating
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..
2010-06-02, 23:45:19
WWW.NOKILIVE.COM/MR(DAHIR-DUALE-ABDI@DD.CA)+(44)07865023279 from United Kingdom  
rating
WWW.NOKILIVE.COM/MR(DAHIR-DUALE-ABDI@DD.CA)+(44)07865023279
2012-11-13, 00:14:56
anonymous from China  
http://www.texansfansgo.com Texans Nike Elite Jersey
http://www.texansfa..son-jersey Andre Johnson Jersey
http://www.texansfa..ter-jersey Arian Foster Jersey
http://www.texansfa..lus-jersey Whitney Mercilu Jersey
http://www.texansfa..aub-jersey Matt Schaub Jersey
http://www.texansfa..all-jersey Alan Ball Jersey
http://www.texansfa..ith-jersey Antonio Smith Jersey
http://www.texansfa..ate-jersey Ben Tate Jersey
http://www.texansfa..mes-jersey Bradie James Jersey
http://www.texansfa..ann-jersey Brett Hartmann Jersey
http://www.texansfa..ing-jersey Brian Cushing Jersey
http://www.texansfa..ain-jersey Brice McCain Jersey
http://www.texansfa..eed-jersey Brooks Reed Jersey
http://www.texansfa..ers-jersey Chris Myers Jersey
http://www.texansfa..win-jersey Connor Barwin Jersey
http://www.texansfa..ing-jersey Danieal Manning Jersey
http://www.texansfa..sey-jersey Devier Posey Jersey
http://www.texansfa..nes-jersey Donnie Jones Jersey
http://www.texansfa..own-jersey Duane Brown Jersey
http://www.texansfa..ham-jersey Garrett Graham Jersey
http://www.texansfa..uin-jersey Glover Quin Jersey
http://www.texansfa..att-jersey J.J. Watt Jersey
http://www.texansfa..sey-jersey James Casey Jersey
http://www.texansfa..eph-jersey Johnathan Joseph Jersey
http://www.texansfa..son-jersey Kareem Jackson Jersey
http://www.texansfa..ter-jersey Kevin Walter Jersey
http://www.texansfa..ean-jersey Lestar Jean Jersey
http://www.texansfa..els-jersey Owen Daniels Jersey
http://www.texansfa..mps-jersey Quintin Demps Jersey
http://www.texansfa..ham-jersey Shayne Graham Jersey
http://www.texansfa..tes-jersey T.J. Yates Jersey
http://www.texansfa..lan-jersey Troy Nolan Jersey
http://www.texansfa..ith-jersey Wade Smith Jersey

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
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.

photo Add a picture: