Why is my mySQL cursor not populating the full variable list?

Viewed 31

SQL Server guy here but I'm trying to write a mySQL stored procedure which takes a temp table (from a sql dump) and merges it into an existing database.

The issue I'm having is that whilst the cursor does populate the first variable col_name it does not populate the other two - data_type and is_nullable.

I've separated out the core cursor part of the procedure to illustrate the issue.

According to the documentation and other posts I've found online, this should work.

Any thoughts from mySQL experts on what might be happening here?

DELIMITER $$;
CREATE PROCEDURE sp_Test()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE col_name VARCHAR(64) DEFAULT "";
    DECLARE data_type VARCHAR(64) DEFAULT "";
    DECLARE is_nullable VARCHAR(3) DEFAULT "";
       
    DECLARE column_cursor CURSOR FOR 
        SELECT `COLUMN_NAME`, `DATA_TYPE`, `IS_NULLABLE` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='country';
        
     DECLARE CONTINUE HANDLER 
          FOR NOT FOUND SET finished = 1;
    
    OPEN column_cursor;
    
    build_query: LOOP
       FETCH column_cursor INTO col_name, data_type, is_nullable;
    
        IF finished = 1 THEN
            LEAVE build_query;
        END IF;
    
        SELECT col_name, data_type, is_nullable;    
    END LOOP build_query;
    CLOSE column_cursor;
END$$;
DELIMITER;
1 Answers

Ok, figured it out. It's because the variable names are the same as the column names. When I changed the variable names to dta_type and is_null it works.

Related