convert Firebird stored procedure to MySQL

Question

I have trouble converting a stored procedure from Firebird to MySQL.

Here is my existing code from Firebird:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE "ActPerHour" (
    days integer)
returns (
    "DateHour" varchar(32),
    "ActCount" integer)
as
declare variable "hours" integer;
declare variable "momentum" timestamp;
BEGIN
    select cast('NOW' as timestamp) from rdb$database into :"momentum";
    IF (:days > 31) THEN
    EXCEPTION "max_a_month";
    "hours" = :days*24;
    WHILE (:"hours" >= 0) DO
    BEGIN
        select substring(addhour (:"momentum", -:"hours") from 1 for 13),
               CASE
                   WHEN count(*) is NULL THEN 0
                   ELSE count(*)
                END
        from "Activity"
        where substring(timestamp '01/01/1970' + "started" / 86400.00000 from 1 for 13) = substring(addhour (:"momentum", -:"hours") from 1 for 13)
        and "started" > cast((:"momentum"-:days - timestamp '1970-01-01') * 86400 as integer)
        into :"DateHour", :"ActCount";
        "hours" = "hours" - 1;
        SUSPEND;
    END
END^

SET TERM ; ^

GRANT SELECT ON "Activity" TO PROCEDURE "ActPerHour";

GRANT EXECUTE ON PROCEDURE "ActPerHour" TO USER;

I tried the following in MySQL:

DELIMITER $$
CREATE PROCEDURE ActPerHour (
    IN days INT,
    OUT DateHour VARCHAR(32),
    OUT ActCount INT)
BEGIN
    DECLARE hours INT;
    DECLARE momentum DATETIME;
SELECT NOW() INTO momentum;
    IF (days > 31) THEN
        BEGIN
            SELECT 'Maximum een maand' AS message;
        END;
    END IF;
    set hours = days * 24;
    WHILE (hours >= 0) DO
    BEGIN
        select (momentum - hours),
               CASE
                   WHEN count(*) is NULL THEN 0
                   ELSE count(*)
               END
        from result
        where started = (momentum - hours)
        and started > momentum - days
        into DateHour, ActCount;
        set hours = hours - 1;
    END;
END$$
DELIMITER ;

But I always end up with a 1064 error.

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 28

Where do I go wrong?

convert Firebird stored procedure to MySQL

0 Answers0