Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Overloaded Stored Procedures

    Posted 09/02/20 01:17 PM

    Does anyone here have a handy-dandy script that will drop all versions of an overloaded stored procedure?

    e.g.,

    DROP PROCEDURE myproc(CHAR); DROP PROCEDURE myproc(INT); DROP PROCEDURE myproc(CHAR,INT);

    ------------------------------
    TOM GIRSCH
    ------------------------------

    #Informix


  • 2.  RE: Overloaded Stored Procedures

    Posted 09/02/20 01:22 PM

    This has been  a RFE for years, at least 15 now

     






  • 3.  RE: Overloaded Stored Procedures

    Posted 09/02/20 01:49 PM
    Hi Tom

    We have a SPL solution for that. If you can use SPL and you have the exec datablade here is our solution.

    Create dba procedure drop_routine(_procname like sysprocedures.procname)

    Define sqlstmt lvarchar(2048);
    Define result varchar(255);

    Foreach 'drop '|| decode (isproc, 'f', 'function ', 'procedure ') || ' ' || procname || '( '|| paramtypes::lvarchar || '); '
    Into sqlstmt
    From sysprocedures
    Where procname = _procname

    Let result =exec(sqlstmt);

    End foreach;
    End procedure;

    ------------------------------
    Gábor Fekete
    Software Engineer
    Capital Systems
    Budapest
    ------------------------------

    Original Message


  • 4.  RE: Overloaded Stored Procedures

    Posted 09/02/20 01:51 PM
    Tom:

    myschema -d somedb -f procname --drop-proc |fgrep DROP >drop.procs

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 5.  RE: Overloaded Stored Procedures

    Posted 09/02/20 02:21 PM

    Here's what I came up with:

    SELECT procname, isproc, paramtypes::LVARCHAR AS paramtypes FROM sysprocedures WHERE [whatever conditions] INTO TEMP proctemp WITH NO LOG; UNLOAD TO "dropspatialproc.sql" DELIMITER ";"u SELECT '{ ' || TRIM(procname) || '-' || (1000 + LENGTH(paramtypes))::LVARCHAR || ' } ' || 'DROP ' || CASE WHEN isproc = 'f' THEN 'FUNCTION' ELSE 'PROCEDURE' END || ' IF EXISTS ' || TRIM(procname) || CASE WHEN (LENGTH(paramtypes) > 0) THEN '(' || TRIM(paramtypes) || ')' ELSE '' END FROM proctemp ORDER BY 1 DESC;

    ------------------------------
    TOM GIRSCH
    ------------------------------



Related Content