Mysql – prepare stmt is not working

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

• Dec 23, 2011 : check constraint does not work?
• Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END// 

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;  

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

You shouldn't be seeing a perceptible performance difference from preparing a statement compared to having one hard-coded into your stored procedure. You also should see no difference at all from the fact that the procedures are stored in a different database from your data.

From the start, though you're presenting us with two different unknowns -- different schema and dynamic SQL. The best way to troubleshoot this is to sectionalize the problem down to the individual variables... by putting a procedure with static (not dynamic) in a different schema from the data (shouldn't matter) or by putting a procedure with a dynamic query in the same schema as the data (also shouldn't matter).

But I suspect the problem is related to neither factor. I think it's the dynamic queries you're building. You may actually be getting wrong results, in addition to your queries taking longer to run.

You appear to have startdate and enddate concatenated right into the text of your dynamic sql. That cannot possibly be valid, since prepared statements do not share procedure variable scope with stored procedures, and concatenation like this puts the names of the variables, not their contents, in to the string... in fact, that dynamic sql should throw an error when you run it -- however, there's an insidious exception, here: it would appear to work if the view you're querying also happens to have columns called startdate and enddate... but the query isn't giving you the correct results, because it thinks you're referring to columns in the queried table in your WHERE clause -- not the procedure variables, making your where clause essentially not eliminate any rows based on those dates.

You could replace startdate with @startdate and enddate with @enddate in the body of your prepared statement, and then, before the actual PREPARE, assign the values of those session ("@") variables to be the same as the procedure variables:

SET @startdate = startdate;  
SET @enddate = enddate;

Note that this doesn't put the contents of the variables into your query, either -- concatenating the contents of variables from outside your procedure into dynamic sql almost always is a bad (insecure) idea -- but this doesn't do that. It puts the variable names into the statement, which is safe.

I think this is the solution to your problem, if the question accurately represents the dynamic sql you're building.

However, I have a further recommendation, in the interest of security and good practice. I mentioned never concatenating external variables into dynamic sql... but you can easily sanitize the variable db_name, making it safe to concatenate in.

Replace this:

FROM ',
db_name, '.view 
WHERE 

with this:

FROM `',
(SELECT schema_name FROM information_schema.schemata 
  WHERE catalog_name = 'def' 
    AND schema_name = db_name), '`.view 
WHERE 

This scalar subquery will return only two possible things: it will always return either the same value passed in as db_name if that database really does exist; otherwise it will return NULL. This will make the generated SQL throw an error if an invalid database name is provided, rather than potentially executing arbitary sql code slipped into your dynamic query string via the db_name variable and executed with the permissions of the procedure definer.