Creating generic routine to drop procedure
Hi I am trying to create a generic drop procedure routine.
Any help would be appreciated.
Here is the code.
-- This is test procedure
CREATE DBA PROCEDURE btest()
RETURNING CHAR(1); return 'Y';
END PROCEDURE;
GRANT execute on btest to public;
-- This procedure would delete the procedure name passed in param
CREATE DBA PROCEDURE test_gen_drop_pro (inpProcName varchar(200))
IF EXISTS (select procname from sysprocedures where procname =
inpProcName) THEN
drop procedure inpProcName; END IF;
END PROCEDURE;
EXECUTE PROCEDURE test_gen_drop_pro('btest');
DROP PROCEDURE test_gen_drop_pro;
EXECUTE PROCEDURE btest();
It still keeps btest procedure in database.
Bhru wrote:
> Hi I am trying to create a generic drop procedure routine.
> Any help would be appreciated.
>
> Here is the code.
> -- This is test procedure
> CREATE DBA PROCEDURE btest()
> RETURNING CHAR(1);> return 'Y';
> END PROCEDURE;
> GRANT execute on btest to public;>
> -- This procedure would delete the procedure name passed in param
> CREATE DBA PROCEDURE test_gen_drop_pro (inpProcName varchar(200))>
> IF EXISTS (select procname from sysprocedures where procname =
> inpProcName) THEN
> drop procedure inpProcName;> END IF;
> END PROCEDURE;
> EXECUTE PROCEDURE test_gen_drop_pro('btest');
> DROP PROCEDURE test_gen_drop_pro;
> EXECUTE PROCEDURE btest();>
> It still keeps btest procedure in database.
>
You cannot use dynamic sql in spl. Your code will try to drop a procedure called 'inpprocname'.
To get dynamic sql in spl install the 'exec' bladelet.
When dropping udrs you should use parameters, fx drop procedure btest(int) or drop procedure btest(char(4)) since you
can have several udrs with the same name. This means that your drop-procedure should take multible parameters, fx
dropproc('btest','int').
Thanks for reply.
How do i get 'exec' bladelet? We have Informix IDS 9.4 TC3. What things
are needed to install?
Claus Samuelsen wrote:
> Bhru wrote:
> > Hi I am trying to create a generic drop procedure routine.
> > Any help would be appreciated.
> >
> > Here is the code.
> > -- This is test procedure
> > CREATE DBA PROCEDURE btest()
> > RETURNING CHAR(1);> > return 'Y';
> > END PROCEDURE;
> > GRANT execute on btest to public;> >
> > -- This procedure would delete the procedure name passed in param
> > CREATE DBA PROCEDURE test_gen_drop_pro (inpProcName varchar(200))> >
> > IF EXISTS (select procname from sysprocedures where procname =
> > inpProcName) THEN
> > drop procedure inpProcName;> > END IF;
> > END PROCEDURE;
> > EXECUTE PROCEDURE test_gen_drop_pro('btest');
> > DROP PROCEDURE test_gen_drop_pro;
> > EXECUTE PROCEDURE btest();> >
> > It still keeps btest procedure in database.
> >
>
> You cannot use dynamic sql in spl. Your code will try to drop a procedure called 'inpprocname'.
> To get dynamic sql in spl install the 'exec' bladelet.
> When dropping udrs you should use parameters, fx drop procedure btest(int) or drop procedure btest(char(4)) since you
> can have several udrs with the same name. This means that your drop-procedure should take multible parameters, fx
> dropproc('btest','int').
b301 wrote: > Thanks for reply. > How do i get 'exec' bladelet? We have Informix IDS 9.4 TC3. What things > are needed to install? > Try iiug.org look under software or use google, it's not that difficult.
b301 wrote: > Thanks for reply. > How do i get 'exec' bladelet? We have Informix IDS 9.4 TC3. What things > are needed to install? > > Claus Samuelsen wrote: > >>Bhru wrote: <SNIP> The 'Exec' datablade is available for download from the IIUG Software Repository. Go to www.iiug.org (please join if you are not a member. Membership is free and includes access to the IIUG Forums in addition to other benefits) and select Software then Repository Index from the top menus then select <Object-Relational Database Extensibility, Including Datablades> on the Software Repository Index page itself. First item on the list under Individual Files is the exec_sql_udr Bladelet. Just click Info to see what you're getting and Download and install (see that BladeManager manual for details). You'll need a C compiler and linker to build the datablade's shared library, that's all. Art S. Kagel