All 10 messages in topic - view as tree
From: Bhru - view profile
Date: Mon, Dec 4 2006 10:56 pm
Email: "Bhru"
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.
Reply » Rate this post: Text for clearing space
From: Claus Samuelsen - view profile
Date: Tues, Dec 5 2006 11:08 am
Email: Claus Samuelsen
- Hide quoted text -
- Show quoted text -
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').
Reply » Rate this post: Text for clearing space
From: b301 - view profile
Date: Tues, Dec 5 2006 7:05 pm
Email: "b301"
Thanks for reply.
How do i get 'exec' bladelet? We have Informix IDS 9.4 TC3. What things
are needed to install?
- Hide quoted text -
- Show quoted text -
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').
Reply » Rate this post: Text for clearing space
From: Claus Samuelsen - view profile
Date: Tues, Dec 5 2006 8:42 pm
Email: Claus Samuelsen
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.
Reply » Rate this post: Text for clearing space
From: Art S. Kagel - view profile
Date: Wed, Dec 6 2006 1:02 am
Email: "Art S. Kagel"
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:
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
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
Reply » Rate this post: Text for clearing space
From: SaltTan - view profile
Date: Wed, Dec 6 2006 9:43 am
Email: "SaltTan"
It works for us IDS 9.4 TC7
"""Tambi Dude wrote:
"""
- Hide quoted text -
- Show quoted text -
> But this appears to be a Windows IDS (from the version).
> AFAIK, exec data blade doesn't run on Windows.
> "Art S. Kagel"
> news:4575FA6B.6030702@bloomberg.net...
> > 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:
> >
> > 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
> > 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
Reply » Rate this post: Text for clearing space
From: Rich or Kristín - view profile
Date: Wed, Dec 6 2006 12:19 pm
Email: "Rich or Kristín"
Tambi Dude wrote:
> But this appears to be a Windows IDS (from the version).
> AFAIK, exec data blade doesn't run on Windows.
Yes, it does.
Reply » Rate this post: Text for clearing space
From: Mike Aubury - view profile
Date: Wed, Dec 6 2006 1:31 pm
Email: Mike Aubury
Wouldn't the simplest way be something like :
drop procedure drop_proc;
create procedure drop_proc(inproc char(200))
SYSTEM "/usr/local/bin/drop_proc.sh thisdbname " || inproc;
end procedure;
Then just write a shell script to do the drop, something like
(called /usr/local/bin/drop_proc.sh in the SP above) :
#!/bin/sh
echo "drop procedure $2" | dbaccess "$1" - > /dev/null 2> /dev/null
exit 0
On Monday 04 December 2006 20:56, Bhru wrote:
- Hide quoted text -
- Show quoted text -
> 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.
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
--
Mike Aubury
Reply » Rate this post: Text for clearing space
From: b301 - view profile
Date: Fri, Dec 8 2006 6:18 pm
Email: "b301"
Thanks, I will join IIUG.
- Hide quoted text -
- Show quoted text -
Art S. Kagel wrote:
> 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:
>
> 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
> 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
Reply » Rate this post:
End of messages
No comments:
Post a Comment