Mysql – Creating a table from variable names

MySQLstored-procedures

Is it possible to create a table from a variable name? For example I have the following stored procedure that I'm trying to write but it will not compile past with the create statement.

CREATE DEFINER = 'sabretooth'@'%' PROCEDURE `sp_archive_sabretooth`(
    IN vhost INTEGER(11),
    IN record_age DATETIME,
    IN delete_records BOOLEAN
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i_exist BOOLEAN DEFAULT FALSE;
    DECLARE table_to_create, backup_table_name, database_name VARCHAR(255);
    DECLARE backup_tables_cursor CURSOR FOR 
            SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE 'vhost%' 
                             OR
                             INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid') 
                         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'sabretooth%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    

    INSERT INTO sabretooth.debug (message) VALUES (CONCAT('Initializing sp_archive_sabretooth; vhost = ', vhost, '; record age = ', record_age, '; delete_records = ', delete_records)); 

    SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
      INTO database_name
      FROM INFORMATION_SCHEMA.SCHEMATA
     WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'sabretooth%'
       AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'sabretooth_archive';

      OPEN backup_tables_cursor;

    create_tables_loop: LOOP
        FETCH backup_tables_cursor INTO table_to_create;
        IF done THEN
            LEAVE create_tables_loop;
        END IF;
        CREATE DATABASE IF NOT EXISTS sabretooth_archive;

        SELECT CONCAT(database_name, '.', table_to_create,'_archive') INTO backup_table_name;

        INSERT INTO sabretooth.debug (message) VALUES (CONCAT('I am here 38 backup_table_name = ',backup_table_name));

        CREATE TABLE CONCAT('sabretooth_archive.',table_to_create) LIKE CONCAT(database_name,'.',table_to_create);
    END LOOP;
END

Best Answer

What you are looking for is a concept called dynamic sql. To do this, you are going to have to use prepared statements. Lucky for you, MySQL permits prepared statements in stored procedures, but not stored functions or triggers.

Also, lucky for you, MySQL permits prepared statements on CREATE TABLE commands.

So you are going to have to prepare the CREATE TABLE something like this:

SET @SQL = CONCAT('CREATE TABLE ',CONCAT('sabretooth_archive.',table_to_create),' LIKE ',CONCAT(database_name,'.',table_to_create));

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Related Solutions

MySQL: Loop over cursor results ends ahead of schedule

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 
    until done
end repeat; 

close my_cursor; 

end

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat; 

close my_cursor; 

end

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

Best Answer

Related Solutions

MySQL: Loop over cursor results ends ahead of schedule

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

Related Question