0

I came up with the great idea that to begin the normalization of characters and collate to utf8, I found the sentences that would help me to carry out this activity, but I have to go table by table. Then I thought about not doing a stored procedure to help me with this.

Everything was perfect, I'm even setting the name of the database and I automatically look for the tables to work, but when making an ALTER TABLE I'm generating the following error.

Error Code: 1146. Table 'ARMADILLO_BASE.tableNameFull' does not exist

Below I share my storage procedure.

CREATE DEFINER=`root`@`%` PROCEDURE `setCollate`(
IN dbName VARCHAR(255),
OUT foundRows INTEGER
)
BEGIN
    -- ###############################################################
    -- GLOBAL VARS
    -- ###############################################################
    DECLARE tableName, tableNameFull VARCHAR(255) DEFAULT FALSE;
    DECLARE noMoreRowsOne BOOLEAN DEFAULT FALSE;
    DECLARE querydbName CURSOR FOR
        SELECT 
            TABLE_NAME
        FROM
            INFORMATION_SCHEMA.TABLES
        WHERE
            TABLE_SCHEMA = dbName
                AND TABLE_NAME NOT IN (SELECT 
                    TABLE_NAME
                FROM
                    INFORMATION_SCHEMA.VIEWS
                WHERE
                    TABLE_SCHEMA = dbName);
    -- set noMoreRowsOne
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRowsOne = TRUE;                        
    -- ###############################################################
    -- PROCESS CURSOR
    -- ###############################################################  
    -- open cursor
    OPEN querydbName;
        -- set @foundRows
        SELECT FOUND_ROWS() INTO foundRows;
        -- begin LOOP
        querydbName_LOOP: LOOP
             -- fetch tableName
            FETCH querydbName INTO tableName;   
            -- SET tableNameFull
            SET tableNameFull = CONCAT('`',dbName,'.',tableName,'`');
            -- SELECT tableNameFull;
            -- validate noMoreRowsOne
            IF noMoreRowsOne THEN
                CLOSE querydbName;
                LEAVE querydbName_LOOP;
            END IF;
            -- SET utf8 and utf8_general_ci
            SET foreign_key_checks = 0; 
            ALTER TABLE tableNameFull CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 
            SET foreign_key_checks = 1;            
        END LOOP; 
END

They help me? What am I failing? ... Or is this not possible?

1

Only values, not table names nor column names, can be filled in the easy way.

Instead, construct the SQL statement with CONCAT, then PREPARE and EXECUTE the statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.