Satyam Kumar
Satyam Kumar

Reputation: 11

I am trying to drop procedure after 30 seconds but I got this error?

module.exports.mainStoredProcedure = async(allQuerys,message) => {
    const mainCallForSP = `
    create procedure SP_${nanoId}()
    begin
       DECLARE errno varchar(300);
       DECLARE text varchar(300);
       DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
       BEGIN
        GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO,text = MESSAGE_TEXT;
        SELECT text,errno;
        ROLLBACK;
        END;
       START TRANSACTION;
           ${allQuerys}
       COMMIT WORK;
    end;
    call SP_${nanoId}();
    call checkRules("${message.jobId}","${message.jobType}");

    CREATE EVENT DropSPEvent_${nanoId}
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND
    DO
      BEGIN
        DROP PROCEDURE IF EXISTS SP_${nanoId};
        DROP EVENT IF EXISTS DropSPEvent_${nanoId};
      END;
    `

    let sqlResponse;
    sqlResponse = await sqlConnection(mainCallForSP);
   
    if(sqlResponse.code === "ER_SP_ALREADY_EXISTS")
    {
        await sleep(3000)
        sql`your text`Response =  sqlConnection(mainCallForSP)
    };   

    const result = JSON.parse(JSON.stringify(sqlResponse));
    return result;
}

Please help me to resolve this issue. I want to drop the stored procedure after 30 seconds of call that procedure.

I tried to use Event but I got this error Error Code: 1357. Can't drop or alter a PROCEDURE from within another stored routine

CREATE EVENT DropSPEvent_${nanoId}
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND
    DO
      BEGIN
        DROP PROCEDURE IF EXISTS SP_${nanoId};
        DROP EVENT IF EXISTS DropSPEvent_${nanoId};
      END;

Upvotes: 1

Views: 43

Answers (0)

Related Questions