This command is not supported in the prepared statement protocol yet

Hi i am writing a procure to drop triggers as below.
CREATE PROCEDURE droptriggers7()

BEGIN

declare viewname varchar(80);

declare NO_MORE_ROWS BOOLEAN DEFAULT FALSE;

 

 

  DECLARE cursor1 cursor  FOR

SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS T where trigger_schema=(select database());
 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_ROWS=true;

 

                        OPEN cursor1;

                        cursor_loop:LOOP

                            FETCH cursor1 INTO viewname;

                            IF NO_MORE_ROWS THEN

                                     LEAVE cursor_loop;

                            END IF;

 

                          SET @tmp_sql= CONCAT("DROP TRIGGER ",viewname);

                          PREPARE s1 FROM @tmp_sql;

                          EXECUTE s1;

                          DEALLOCATE PREPARE s1;



                        END LOOP cursor_loop;

                        CLOSE cursor1;

 

        SET NO_MORE_ROWS=false;

END

when iam calling this procedure iam getting
"This command is not supported in the prepared statement protocol yet" error.
please help how to reslove this problem.
Regds,
vijji
vijji_lakshmiAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
as the error indicates, you cannot DROP TRIGGER in the dynamic sql, and there is AFAIK no way around (from inside mysql)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.