Subject Re: Maintainance of stored procedures
Author Adam
Hi Stefan,

I wouldn't worry too much about the off the cuff comments, some of us
don't have the luxury of dealing with a system well designed from the
outset and do need to change parameters etc. Forgetting the idealistic
you should never need to in a well designed system, your (or whoever
started this thread's) gripe is this.

Firebird won't let you change parameters etc of a stored procedure if
it is being used by another database object, such as a view / stored
procedure or trigger.

Like it or not (I don't) there is a very good reason for this. If it
did let you change it, then the dependancies would stop working
properly, and it may even prevent a restore.

So, IMO the first priority of Firebird is to prevent us from as others
have said, sliting our throat. If me running a particular DDL will
stuff the database dependancies up, then Firebird must either fix what
it stuffs up, give me an oportunity to fix what it stuffs up, or not
let me do it at all.

By the way, the slit throat comment should not have been directed at
the developer making the changes, but rather it is would be an
unintentional consequence of these DDL succeeding.

Now ideally, Firebird should detect all the dependancies that will
fall over since this change, and if you do not fix them before you
commit the ddl, it should raise an exception or something and roll
back all the ddl changes you just made.

I don't however imagine that is a quick thing to do, so the least it
should do is to prevent the ddl in the first place.

Actually, maintenance of stored procedures (even nested ones) is
relatively simple (but cumbersome), you simply adjust all the
dependancies to not refer to it (comment out the line of code, or the
whole thing), make the change, then re-instate the old dependancies.
It may be cumbersome, but at least you don't have cascading problems
if you blank out the SP rather than drop it completely. Try doing it
with a view, and you have yourself a real gotcha (no alter view syntax) :(

As others have mentioned (and you may not be aware), there are tools
that can automate this script somewhat. Because of the availability of
such tools, I imagine the enhancement will not be a high priority.