Procedure Delete Script !

Hi all, Your this student is again back with a question. Thanks to all the people who have replied to my previous question BTW, I shud say that you people are marvellous and sometimes i am amazed by the knowledge u posses and it also incite me to work hard to come somewhere near to ur level. Also, With many people working with me it still becomes difficult to get answers to some question haunting and hence i have to come to u for help. ##---- My Question ------## I have a .sql file where i have written delete statements like this ##------Cut Here-------## DROP PROCEDURE proc1; DROP PROCEDURE proc2; DROP PROCEDURE proc3;. . . . . . DROP PROCEDURE proc4;##------Cut Here-------## Now what happens is that if there is any procedure missing the dbaccess stops at that procedure drop statement. Then i have to comment that procedure drop statement and rerun the .sql But now the earlier procedures which were dropped cannt be dropped so...they also have to be commented. )-; Is there any way that i have a script which will have all the procedure names (with drop stmts) and then delete whichever are there and skip whichever are not there.. Let em tell u that this will be a common script that will be running at many servers. Currently i go to that server, find how many procs are there, prepare drop statments, execute .sql file. seems to be boring and tiresome when u have many servers. Sorry for the long mail...)-; TIA, Nayan Jain !

HI Buddy, I believe that if you run dbaccess dbname sqlscriptname from the command line then it will do what you want to do. Because when run from command line dbaccess ignores any errors encountered and just goes over to the next one. But when run in interactive mode it will stop at the Ist error and you will have the issue that you are talking about. BTW are you having these issues while working on some project or you are just learning Informix and thinking about how Informix behaves in these situations. I am intrested to know about you. Please send me email at kchande@yahoo.com telling me what you are doing there in Tata Infotec. Thanks, Khem Chander. Nayan Jain wrote: > Hi all, > > Your this student is again back with a question. > Thanks to all the people who have replied to my previous question > > BTW, I shud say that you people are marvellous and sometimes i am amazed > by the knowledge u posses and it also incite me to work hard to come > somewhere near to ur level. > > Also, With many people working with me it still becomes difficult to get > answers to some question haunting and hence i have to come to u for help. > > ##---- My Question ------## > > I have a .sql file where i have written delete statements like this > > ##------Cut Here-------## > DROP PROCEDURE proc1; > DROP PROCEDURE proc2; > DROP PROCEDURE proc3;> . > . > . > . > . > . > DROP PROCEDURE proc4;> ##------Cut Here-------## > > Now what happens is that if there is any procedure missing the dbaccess > stops at that procedure drop statement. Then i have to comment that > procedure drop statement and rerun the .sql > But now the earlier procedures which were dropped cannt be dropped > so...they also have to be commented. )-; > > Is there any way that i have a script which will have all the procedure > names (with drop stmts) and then delete whichever are there and skip > whichever are not there.. > > Let em tell u that this will be a common script that will be running at > many servers. Currently i go to that server, find how many procs are > there, prepare drop statments, execute .sql file. seems to be boring and > tiresome when u have many servers. > > Sorry for the long mail...)-; > > TIA, > Nayan Jain !

Nayan Jain wrote: > [...flattery for netizens of c.d.i omitted...] > > ##---- My Question ------## > > I have a .sql file where i have written delete statements like this > > ##------Cut Here-------## > DROP PROCEDURE proc1; > DROP PROCEDURE proc2; > DROP PROCEDURE proc3;> . > . > . > . > . > . > DROP PROCEDURE proc4;> ##------Cut Here-------## > > Now what happens is that if there is any procedure missing the > dbaccess stops at that procedure drop statement. Then i have to > comment that procedure drop statement and rerun the .sql > But now the earlier procedures which were dropped cannt be dropped > so...they also have to be commented. )-; > > Is there any way that i have a script which will have all the > procedure names (with drop stmts) and then delete whichever are > there and skip whichever are not there.. > > Let em tell u that this will be a common script that will be > running at many servers. Currently i go to that server, find how > many procs are there, prepare drop statments, execute .sql file. > seems to be boring and tiresome when u have many servers. You have (at least) two choices. 1. Do a SELECT which lists the procedures in the database and use this to build a series of drop procedure statements: OUTPUT TO "dropproc.sql" SELECT 'DROP PROCEDURE ' || procname || ';' FROM "informix".SysProcedures WHERE procname IN ('proc1', 'proc2', ...); Then run the output file through DB-Access. 2. Get an SQL command processor that allows you to control what it does on an error. Eg, SQLCMD from the IIUG archives: continue push; continue on; drop procedure proc1; ... drop procedure procN; continue pop; Using the push/pop operations means that you preserve the prior 'continue' status -- it was probably stop, but you don't want to damage it unnecessarily. You might also want to ask yourself "Why are the procedures sometimes present and sometimes absent from the database?" The answer might be enlightening. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>