• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

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
0
vijji_lakshmi
Asked:
vijji_lakshmi
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now