MySQL procedure runs, but isn't created

[Andy-H]

I have checked all of the querys in this (replacing the variables for made-up values) and the querys run fine, however, when I run the sql, I get your query has been executed successfully, when I call the procedure, it says it doesn't exist, an if I remove the DROP PROCEDURE IF NOT EXIST, it allows me to run the query an infinite number of times, so the procedure is not even being created, anyone see an error or know a good way to debug this (preferably without downloading software)

 

 

Thanks.

 

 

DROP PROCEDURE IF EXISTS alerts;
DELIMITER $$
CREATE PROCEDURE alerts(
   IN lID    INT,
   IN lat    DOUBLE,
   IN lon    DOUBLE,
   IN date   DATE,
   IN time   TIME,
   IN device VARCHAR(13)
)
MODIFIES SQL DATA
rules: BEGIN
   DECLARE rID   INT;
   DECLARE rName VARCHAR(255);
   
   DECLARE rules_broken BOOLEAN;
   DECLARE no_more_rows BOOLEAN;


   DECLARE trig_cur CURSOR FOR
      SELECT r.ruleID, r.rule_name FROM 
      trackers t
      INNER JOIN ( rules r, rulesP rp  )                            ON    ( t.id = rp.trackerID AND rp.ruleID = r.ruleID )
      INNER JOIN ( rules_subrules rsub )                            ON    ( r.ruleID = rsub.ruleID )
      INNER JOIN ( rules_schedule rs   )                            ON    ( r.ruleID = rs.ruleID )
      LEFT JOIN  ( rules_locations rl, rules_locations_links rls ) ON    ( rls.locationID = rl.locationID AND r.ruleID = rls.ruleID )
         WHERE (POW(2, IF(DATE_FORMAT(STR_TO_DATE(date, '%d/%m/%Y'), '%w')=0,7,DATE_FORMAT(STR_TO_DATE(date, '%d/%m/%Y'), '%w'))-1) & rs.schedule_days)
         AND IF(rs.schedule_inorout, IF(time BETWEEN rs.schedule_start AND rs.schedule_end, 1, 0), IF(time NOT BETWEEN rs.schedule_start AND rs.schedule_end, 1, 0))
         AND IF(rl.location_lat IS NOT NULL, GEOFENCE(lat, lon, rl.location_lat, rl.location_lon, rl.location_radius), 1)
         AND t.unitid = device AND r.rule_enabled = 1;


   DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET no_more_rows = TRUE;


   OPEN trig_cur;
   rules_loop : LOOP
      FETCH  trig_cur
      INTO   rID,
             rName
      ;
      SET rules_broken = TRUE;
      
      IF no_more_rows THEN
         CLOSE trig_cur;
         LEAVE rules_loop;
      END IF;
      
      sub_rules: BEGIN
         DECLARE field              VARCHAR(255);
         DECLARE operator           VARCHAR(2);
         DECLARE value              VARCHAR(50);
         
         DECLARE no_more_rows_again BOOLEAN;
         
         DECLARE subrule_cur CURSOR FOR 
            SELECT rule_field, rule_operator, rule_value FROM rules_subrules WHERE ruleID = rID;
         DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET no_more_rows_again = TRUE;
         
         OPEN subrule_cur;
         subrules_loop : LOOP
            FETCH subrule_cur
            INTO  field, 
                  operator, 
                  value
            ;
            IF no_more_rows_again THEN
               CLOSE subrule_cur;
               LEAVE subrules_loop;
            END IF;
            
            SET @query = CONCAT('SELECT IF(', field, operator, value, ', 1, 0) INTO @broken FROM newlocations WHERE id = ', lID, ' LIMIT 1');
            PREPARE    stmt FROM @query;
            EXECURE    stmt;
            DEALLOCATE stmt;
            
            IF ( @broken = 0 ) THEN
               rules_broken = FALSE;
               CLOSE subrule_cur;
               LEAVE subrules_loop;
            END IF;
            
         END LOOP subrules_loop;
      END sub_rules;
      
      IF ( rules_broken ) THEN
         INSERT INTO alerts ( alertID, locationID, ruleID ) VALUES ( NULL, lID, rID );
      END IF;
      
   END LOOP rules_loop;
END rules;
DELIMITER ;

 

 

(GEOFENCE is a user-defined function)

[mikosiko]

your SP has syntax errors, hence is not been created... errors marked here in red

 

            SET @query = CONCAT('SELECT IF(', field, operator, value, ', 1, 0) INTO @broken FROM newlocations WHERE id = ', lID, ' LIMIT 1');

            PREPARE    stmt FROM @query;

            EXECURE    stmt;  // should be EXECUTE instead of EXECURE

            DEALLOCATE stmt;          // should be DEALLOCATE PREPARE stmt

           

            IF ( @broken = 0 ) THEN

              rules_broken = FALSE;    // missing SET

 

Note: I didn't review your code at all.. only syntax check

[Andy-H]

Thanks, I changed those and it still didn't work, I'm not at work any more so can't carry on with it until tomorrow

 

 

Thanks for the help