I’m a hobbyist developing a Django Rest Framework app which communicates with a Mariadb database. I’ve written the following stored procedure to do some intermediate processing. Using Pycharm (pro) I can execute the procedure, verify it’s created in the database routines folder and call it from DRF. So far so good.

DELIMITER //
DROP PROCEDURE IF EXISTS FetchTariffTable;
CREATE PROCEDURE FetchTariffTable(p_cc_id INT, p_c_id INT, p_start_date VARCHAR(10), p_end_date VARCHAR(10))
BEGIN
    -- Declare variables
    DECLARE done INT DEFAULT 0;
    DECLARE id_contract_val INT;
    DECLARE cc_id_val INT;
    DECLARE tariff_sort_val VARCHAR(10);
    DECLARE amount_val DOUBLE;
    DECLARE cost_val DOUBLE;
    DECLARE frequency_val VARCHAR(10);
    DECLARE i_use_val INT;
    DECLARE ii_use_val INT;
    DECLARE gas_use_val DOUBLE;
    DECLARE start_date_val VARCHAR(10);
    DECLARE end_date_val VARCHAR(10);
    DECLARE days_in_range_val INT;
    DECLARE range_start_date_val VARCHAR(10);
    DECLARE range_end_date_val VARCHAR(10);

    -- Declare cursor
    DECLARE c1 CURSOR FOR

        SELECT cc_id, c_id, i_use, ii_use, gas_use, t_start_date, t_end_date, tariff_sort, amount, frequency,
        CASE
         WHEN frequency = 'YEAR' THEN
            ROUND(amount / 365 * DATEDIFF(t_end_date, t_start_date), 2)
         WHEN frequency = 'MONTH' THEN
            ROUND(((amount * 12) / 365) * DATEDIFF(t_end_date, t_start_date), 2)
         WHEN frequency = 'KWH' THEN
            ROUND(ii_use * amount, 2)
         WHEN frequency = 'M3' THEN
            ROUND(gas_use * amount, 2)
         WHEN frequency = 'DAY' THEN
            ROUND(DATEDIFF(t_end_date, t_start_date) * amount, 2)
        END as cost
    FROM
    (
        (
            SELECT
                   LAG(stand_i) OVER ( ORDER BY consumption_date DESC ) - stand_i as i_use,
                   LAG(stand_ii) OVER ( ORDER BY consumption_date DESC ) - stand_ii as ii_use,
                   ROUND(LAG(gas) OVER ( ORDER BY consumption_date DESC ) - gas, 2) as gas_use
            FROM
            (
                (SELECT Id, consumption_date, stand_i, stand_ii, gas
                    FROM reading_reading r1
                    WHERE DATE(consumption_date) = p_start_date
                    ORDER BY consumption_date LIMIT 1)
            UNION
            (
                SELECT Id, consumption_date, stand_i, stand_ii, gas
                    FROM reading_reading r2
                    WHERE DATE(consumption_date)  = p_end_date
                    ORDER BY consumption_date DESC LIMIT 1)
            ) as r1
            ORDER BY consumption_date LIMIT 1
        ) r2
        INNER JOIN
        (
            SELECT cc.id as cc_id, c.id as c_id,
                ct.start_date as t_start_date,
                ct.end_date   as t_end_date,
                ct.tariff_sort,
                ct.amount,
                ct.frequency
            FROM contract_contractcombination cc
                  JOIN energy_tracker.contract_contract c on cc.id = c.contract_combination_id
                  JOIN energy_tracker.contract_tariff ct on c.id = ct.contract_id
            WHERE (cc.id = p_cc_id AND p_c_id > 0 AND p_c_id = c.id)
                OR (cc.id = p_cc_id AND p_c_id = 0)
    )
    as tr
    ON cc_id = p_cc_id);

    DECLARE c2 CURSOR FOR SELECT tmp_contract_id, tmp_start_date, tmp_end_date, tmp_tariff_sort, tmp_amount,
                                 tmp_frequency, tmp_i_use_val, tmp_ii_use_val, tmp_gas_use_val
    FROM tmp_tariff_table;

    -- Declare handler for NOT FOUND condition
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Create temporary table
    -- DROP TABLE IF EXISTS tmp_tariff_table ;
    CREATE OR REPLACE TEMPORARY TABLE tmp_tariff_table  (
        tmp_contract_id INT,
        tmp_start_date VARCHAR(10),
        tmp_end_date VARCHAR(10),
        tmp_tariff_sort VARCHAR(10),
        tmp_amount DOUBLE,
        tmp_frequency VARCHAR(10),
        tmp_i_use_val INT,
        tmp_ii_use_val INT,
        tmp_gas_use_val DOUBLE
    ) ENGINE=MEMORY;

    -- Open the cursor
    OPEN c1;

    -- Start iterating through the result set
    read_loop: LOOP
        -- Fetch data into variables
        FETCH c1 INTO cc_id_val, id_contract_val, i_use_val, ii_use_val, gas_use_val, start_date_val, end_date_val, tariff_sort_val,
            amount_val, frequency_val, cost_val;

        -- Check for the end of the result set
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        INSERT INTO tmp_tariff_table (tmp_contract_id, tmp_start_date, tmp_end_date, tmp_tariff_sort,
                                      tmp_amount, tmp_frequency, tmp_i_use_val, tmp_ii_use_val, tmp_gas_use_val)
        VALUES (id_contract_val, start_date_val, end_date_val, tariff_sort_val,
                amount_val, frequency_val, i_use_val, ii_use_val, gas_use_val);

    END LOOP;

    -- Close the cursor super
    CLOSE c1;
    OPEN C2;
    -- Create temporary table
    -- DROP TABLE IF EXISTS tmp_processed_tariffs ;
    CREATE OR REPLACE TEMPORARY TABLE tmp_processed_tariffs (
        rec_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
        contract_id VARCHAR(10),
        start_date VARCHAR(10),
        end_date VARCHAR(10),
        tariff_sort VARCHAR(10),
        amount DOUBLE,
        frequency VARCHAR(10),
        days_in_range INT,
        gas_use DOUBLE,
        i_use INT,
        ii_use INT,
        tariff_cost DOUBLE
    ) ENGINE=MEMORY;

    SET done = 0;
    -- Start iterating through the result set
    read_loop: LOOP

        -- Fetch data into variables
        FETCH c2 INTO id_contract_val, start_date_val, end_date_val, tariff_sort_val, amount_val, frequency_val,
                    i_use_val, ii_use_val, gas_use_val;

        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        SET range_end_date_val = p_end_date;
        SET range_start_date_val = p_start_date;
        SET days_in_range_val = DATEDIFF(DATE(range_end_date_val), DATE(range_start_date_val));

        if days_in_range_val <= 0 THEN
            SET @errorMsg = CONCAT('Date range error ', p_start_date, ' column.');
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg;
        END IF;

        **IF DATE(start_date_val) < DATE(p_start_date) THEN
            SET range_start_date_val = p_start_date;
        ELSE
            SET range_start_date_val = start_date_val;
        END IF;
        IF DATE(end_date_val) <= DATE(p_end_date) THEN
            SET range_end_date_val = end_date_val;
        END IF;
        IF DATE(end_date_val) > CURRENT_DATE() THEN
            SET range_end_date_val = CURRENT_DATE;
        END IF;**
        INSERT INTO tmp_processed_tariffs
            (contract_id, start_date, end_date, tariff_sort, amount, frequency, days_in_range,
             tariff_cost, i_use, ii_use, gas_use)
        VALUES
            (id_contract_val, range_start_date_val, range_end_date_val, tariff_sort_val, amount_val,
             frequency_val, days_in_range_val, 0, i_use_val, ii_use_val, gas_use_val );
    END LOOP;
    CLOSE C2;
END //

DELIMITER ;

The problem begins when I try to put this into a django migration file using:

python manage.py makemigrations etracker --name Sp_migrations --empty

This is my 0002_Sp_migrations.py:

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('etracker', '0001_initial'),

    ]

    operations = [
        migrations.RunSQL(
            "DROP PROCEDURE IF EXISTS FetchTariffTable;" 
            "CREATE PROCEDURE FetchTariffTable(p_cc_id INT, p_c_id INT, p_start_date VARCHAR(10), p_end_date VARCHAR(10)) " +
            "BEGIN " +
            "DECLARE done INT DEFAULT 0; " +
            "DECLARE id_contract_val INT; " +
            "DECLARE cc_id_val INT; " +
            "DECLARE tariff_sort_val VARCHAR(10); " +
            "DECLARE amount_val DOUBLE; " +
            "DECLARE cost_val DOUBLE; " +
            "DECLARE frequency_val VARCHAR(10); " +
            "DECLARE i_use_val INT; " +
            "DECLARE ii_use_val INT; " +
            "DECLARE gas_use_val DOUBLE; " +
            "DECLARE start_date_val VARCHAR(10); " +
            "DECLARE end_date_val VARCHAR(10); " +
            "DECLARE days_in_range_val INT; " +
            "DECLARE range_start_date_val VARCHAR(10); " +
            "DECLARE range_end_date_val VARCHAR(10); " +
            "DECLARE c1 CURSOR FOR " +
            "SELECT cc_id, c_id, i_use, ii_use, gas_use, t_start_date, t_end_date, tariff_sort, amount, frequency, " +
            "CASE " +
            "WHEN frequency = 'YEAR' THEN " +
            "ROUND(amount / 365 * DATEDIFF(t_end_date, t_start_date), 2) " +
            "WHEN frequency = 'MONTH' THEN " +
            "ROUND(((amount * 12) / 365) * DATEDIFF(t_end_date, t_start_date), 2) " +
            "WHEN frequency = 'KWH' THEN " +
            "ROUND(ii_use * amount, 2) " +
            "WHEN frequency = 'M3' THEN " +
            "ROUND(gas_use * amount, 2) " +
            "WHEN frequency = 'DAY' THEN " +
            "ROUND(DATEDIFF(t_end_date, t_start_date) * amount, 2) " +
            "END as cost " +
            "FROM " +
            "( " +
            "( " +
            "SELECT " +
            "LAG(stand_i) OVER ( ORDER BY consumption_date DESC ) - stand_i as i_use, " +
            "LAG(stand_ii) OVER ( ORDER BY consumption_date DESC ) - stand_ii as ii_use, " +
            "ROUND(LAG(gas) OVER ( ORDER BY consumption_date DESC ) - gas, 2) as gas_use " +
            "FROM " +
            "( " +
            "(SELECT Id, consumption_date, stand_i, stand_ii, gas " +
            "FROM reading_reading r1 " +
            "WHERE DATE(consumption_date) = p_start_date " +
            "ORDER BY consumption_date LIMIT 1) " +
            "UNION " +
            "( " +
            "SELECT Id, consumption_date, stand_i, stand_ii, gas " +
            "FROM reading_reading r2 " +
            "WHERE DATE(consumption_date)  = p_end_date " +
            "ORDER BY consumption_date DESC LIMIT 1) " +
            ") as r1 " +
            "ORDER BY consumption_date LIMIT 1 " +
            ") r2 " +
            "INNER JOIN " +
            "( " +
            "SELECT cc.id as cc_id, c.id as c_id, " +
            "ct.start_date as t_start_date, " +
            "ct.end_date   as t_end_date, " +
            "ct.tariff_sort, " +
            "ct.amount, " +
            "ct.frequency " +
            "FROM etracker_contractcombination cc " +
            "JOIN etracker_contract c on cc.id = c.contract_combination_id " +
            "JOIN etracker_tariff ct on c.id = ct.contract_id " +
            "WHERE (cc.id = p_cc_id AND p_c_id > 0 AND p_c_id = c.id) " +
            "OR (cc.id = p_cc_id AND p_c_id = 0) " +
            ") " +
            "as tr " +
            "ON cc_id = p_cc_id);" +
            "DECLARE c2 CURSOR FOR SELECT tmp_contract_id, tmp_start_date, tmp_end_date, tmp_tariff_sort, tmp_amount, " +
            "tmp_frequency, tmp_i_use_val, tmp_ii_use_val, tmp_gas_use_val " +
            "FROM tmp_tariff_table;" +
            "DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;" +
            "CREATE OR REPLACE TEMPORARY TABLE tmp_tariff_table  ( " +
            "tmp_contract_id INT, " +
            "tmp_start_date VARCHAR(10), " +
            "tmp_end_date VARCHAR(10), " +
            "tmp_tariff_sort VARCHAR(10), " +
            "tmp_amount DOUBLE, " +
            "tmp_frequency VARCHAR(10), " +
            "tmp_i_use_val INT, " +
            "tmp_ii_use_val INT, " +
            "tmp_gas_use_val DOUBLE " +
            ") ENGINE=MEMORY;" +
            "OPEN c1;" +
            "read_loop: LOOP " +
            "FETCH c1 INTO cc_id_val, id_contract_val, i_use_val, ii_use_val, gas_use_val, start_date_val, end_date_val, tariff_sort_val, " +
            "amount_val, frequency_val, cost_val; " +
            "IF done = 1 THEN " +
            "LEAVE read_loop; " +
            "END IF; " +
            "INSERT INTO tmp_tariff_table (tmp_contract_id, tmp_start_date, tmp_end_date, tmp_tariff_sort, " +
            "tmp_amount, tmp_frequency, tmp_i_use_val, tmp_ii_use_val, tmp_gas_use_val) " +
            "VALUES (id_contract_val, start_date_val, end_date_val, tariff_sort_val, " +
            "amount_val, frequency_val, i_use_val, ii_use_val, gas_use_val);" +
            "END LOOP;" +
            "CLOSE c1;" +
            "OPEN C2;" +
            "CREATE OR REPLACE TEMPORARY TABLE tmp_processed_tariffs ( " +
            "rec_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, " +
            "contract_id VARCHAR(10), " +
            "start_date VARCHAR(10), " +
            "end_date VARCHAR(10), " +
            "tariff_sort VARCHAR(10), " +
            "amount DOUBLE, " +
            "frequency VARCHAR(10), " +
            "days_in_range INT, " +
            "gas_use DOUBLE, " +
            "i_use INT, " +
            "ii_use INT, " +
            "tariff_cost DOUBLE " +
            ") ENGINE=MEMORY;" +
            "SET done = 0;" +
            "read_loop: LOOP " +
            "FETCH c2 INTO id_contract_val, start_date_val, end_date_val, tariff_sort_val, amount_val, frequency_val, " +
            "i_use_val, ii_use_val, gas_use_val;" +
            "IF done = 1 THEN " +
            "LEAVE read_loop;" +
            "END IF; " +
            "SET range_end_date_val = p_end_date; " +
            "SET range_start_date_val = p_start_date; " +
            "SET days_in_range_val = DATEDIFF(DATE(range_end_date_val), DATE(range_start_date_val)); " +
            "if days_in_range_val <= 0 THEN " +
            "SET @errorMsg = CONCAT('Date range error ', p_start_date, ' column.'); " +
            "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg; " +
            "END IF;" +
            *"IF DATE(start_date_val) < DATE(p_start_date) THEN " +
            "SET range_start_date_val = p_start_date; " +
            "ELSE " +
            "SET range_start_date_val = start_date_val; " +
            "END IF; " +
            "IF DATE(end_date_val) <= DATE(p_end_date) THEN " +
            "SET range_end_date_val = end_date_val; " +
            "END IF; " +
            "IF DATE(end_date_val) > CURRENT_DATE() THEN " +
            "SET range_end_date_val = CURRENT_DATE;" +
            "END IF;" +*

            "INSERT INTO tmp_processed_tariffs " +
            "(contract_id, start_date, end_date, tariff_sort, amount, frequency, days_in_range, " +
            "tariff_cost, i_use, ii_use, gas_use) " +
            "VALUES " +
            "(id_contract_val, range_start_date_val, range_end_date_val, tariff_sort_val, amount_val, " +
            "frequency_val, days_in_range_val, 0, i_use_val, ii_use_val, gas_use_val ); " +
            "END LOOP;" +
            "CLOSE C2;" +
            "END"),
    ]

When I run migrate, the following error occurs:

“You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ” at line 1″)

It took a while to track down by commenting out each block and line and retrying the migration but I eventually determined that the error is with this block of code towards then end of the procedure. If I comment out the IFs and END IFs. I get Applying etracker.0002_Sp_migrations... OK

# "IF DATE(start_date_val) < DATE(p_start_date) THEN " +
"SET range_start_date_val = p_start_date; " +
# "ELSE " +
"SET range_start_date_val = start_date_val; " +
# "END IF; " +
# "IF DATE(end_date_val) <= DATE(p_end_date) THEN " +
"SET range_end_date_val = end_date_val; " +
# "END IF; " +
# "IF DATE(end_date_val) > CURRENT_DATE() THEN " +
"SET range_end_date_val = CURRENT_DATE;" +
# "END IF;" +

So my question is what’s wrong with the IFs? Can anyone spot it? Please note that previous IFs do not produce an error:

Both OK: 

"IF done = 1 THEN " +
"LEAVE read_loop; " +
"END IF; " +

"IF days_in_range_val <= 0 THEN " +
"SET @errorMsg = CONCAT('Date range error ', p_start_date, ' column.'); " +
"SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg; " +
"END IF;" +

I’d be grateful for any assistance; this is pretty much my last step and I just don’t think I’ll be able to re-write it using Django’s ORM statements.

What I tried? As described above, I individually commented out the strings in each line of the query to arrive at the offending block. I just cant see what’s wrong with the Ifs.