Reputation: 11
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