--- In [email protected], Thomas Steinmaurer wrote: > > > --- In [email protected], "camalot_todd" wrote: > >> > >> > >> > >> --- In [email protected], "Leyne, Sean" wrote: > >>> > >>> > >>>>> I would like to add a script that does a "DROP EXTERNAL FUNCTION SQRT". > >>>>> > >>>>> The problem I have is that some of the databases that will run this > >>>>> script > >>>> already have the UDF dropped and so the script errors. > >>>>> > >>>>> Is there a way to run statements like DROP INDEX, DROP EXTERNAL > >>>> FUNCTION, etc. in a script without generating an error of the object > >>>> isn't > >>>> found? > >>>> > >>>> In a pure SQL script, I wouldn't know. > >>> > >>> This should do: > >>> > >>> EXECUTE BLOCK > >>> AS > >>> BEGIN > >>> IF (EXISTS (SELECT 1 FROM RDB$Functions WHERE RDB$FunctionName = > >>> 'SQRT') ) THEN > >>> EXECUTE STATEMENT 'DROP EXTERNAL FUNCTION SQRT'; > >>> > >>> END > >>> > >>> > >>> > >>> Sean > >>> > >> > >> Thanks Sean Worked great! Just had to surround the statements with SET > >> TERM and fix the column name (RDB$FUNCTION_NAME). > >> > >> This will allow us to clean up a bunch of things in the database. > >> > >> Todd > >> > > > > We no longer have a need for UDF's because the functions we used are now > > part of Firebird (we have clients using 2.1 and 2.5). So it is pretty cool > > that I can drop all UDF's with the code below. > > > > SET TERM ^ ; > > > > EXECUTE BLOCK > > > > AS > > declare variable RDB$FUNCTION_NAME CHAR(31); > > BEGIN > > FOR SELECT RDB$FUNCTION_NAME FROM RDB$Functions WHERE RDB$FUNCTION_NAME > > <> 'LOWER' INTO :RDB$Function_Name > > DO > > EXECUTE STATEMENT 'DROP EXTERNAL FUNCTION '||:RDB$Function_Name; > > > > END > > ^ > > > > SET TERM ; ^ > > > > > > Didn't think it would work to concatenate the partial statement with a > > variable name to EXECUTE the drop. > > > > Anyhow I found I can't drop the UDF called LOWER. Gives me an error (Token > > Unknown) I believe because LOWER is now a function in Firebird. > > > > Anyone know how to drop the UDF called LOWER? > > In a dialect 3 database, try with enclosing double quotes: > > "LOWER" > > > -- > With regards, > Thomas Steinmaurer > http://www.upscene.com/ >
We are Dialect 1 .... something else we need to look at doing ... convert to Dialect 3
