MariaDB extract a column for a variable of Type ROW dynamically

Question

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS t_agents;
    SET @SQL := CONCAT('CREATE TEMPORARY TABLE t_agents AS SELECT * FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    BEGIN
        DECLARE c_data CURSOR FOR SELECT * FROM t_agents;
        BEGIN
            DECLARE rec ROW TYPE OF c_data;
            DECLARE done INT DEFAULT FALSE;
            DECLARE col CHAR(40);
            DECLARE val TEXT(10000);

            DECLARE c_columns CURSOR FOR 
                SELECT column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = in_db_name
                    AND table_name = in_tbl_name
                ORDER BY ordinal_position;

            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

            OPEN c_columns;
            OPEN c_data;

            FETCH c_data INTO rec;          

            SET @create_list := "";

            read_loop: LOOP
                FETCH c_columns INTO col;

                IF done THEN
                    LEAVE read_loop;
                END IF;

                -- SELECT rec.col; -- <<====

                SET @create_list := CONCAT(@create_list, "'", col, "', '", rec.name, "', ");

            END LOOP read_loop;

            SET @SQL := CONCAT ("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", TRIM(TRAILING ", " FROM @create_list), ")");
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            CLOSE c_columns;
            CLOSE c_data;
        END;
    END;
 END$$

In the above procedure, i am looping through all the columns in a table and i am planning to build a query to populate a table with dynamic column.

Is there any way to substitute the value of col in the line highlighted in above procedure?

Eg. the value stored in col is 'name'. So the output required is SELECT rec.name

Update

Somehow i managed to write a dirty solution without the use of ROW TYPE and with single cursor. It works so, so far i did not find any problem. Pls guide me if there is any better way.

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper_new3`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
proc: BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE col CHAR(40);
        DECLARE val TEXT(10000);
        DECLARE q TEXT(100000);     

        DECLARE c_columns CURSOR FOR 
            SELECT column_name
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = in_db_name
                AND table_name = in_tbl_name
            ORDER BY ordinal_position;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN c_columns;

        SET q = "";
        SET @val = "";

        read_loop: LOOP
            FETCH c_columns INTO col;

            IF done THEN
                LEAVE read_loop;
            END IF;
            SET q = CONCAT(q, "'", col, "','|', IFNULL(QUOTE(", col, "), ''),'$'");

        END LOOP read_loop;

        SET q = TRIM(TRAILING "," FROM q);

        SET q = CONCAT("CONCAT(", q, ") INTO @val");

        EXECUTE IMMEDIATE CONCAT("SELECT ", q , " FROM ", in_tbl_name, " WHERE row_id = ", in_row_id);

        IF @val = '' THEN
            LEAVE proc;
        END IF;

        SELECT CONCAT("'", REPLACE(@val, "|", "',"), "'") INTO @val;

        SELECT REPLACE(@val, "$", ",") INTO @val;

        SELECT TRIM(TRAILING ",'" FROM @val) INTO @val;

        EXECUTE IMMEDIATE CONCAT("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", @val, ")" );

        CLOSE c_columns;
 END$$

I don't understand, what you are doing in the read loop You get the columname and the what? you can do an **stmt** like at the start. — nbk, Dec 09 '19 at 22:36
rec is a variable of type ROW so it has multiple columns. So i want to fetch each column one by one in each iteration of the loop and the name of the column is again a variable 'col' which changes for each iteration but rec doesn't. I tried prepared statement it doesn't work — Vinod Nadar, Dec 09 '19 at 22:48
you rroblem is that rec is of the type cursor not a table. What you must do is make first run trough all columns and then trough all rows. That males much more sense, but i still dion't get what you try to achieve. I never seen a cursor with SELECt * . You should explain hwat you try to achive with concrete data examples. — nbk, Dec 09 '19 at 23:24
I removed the mysql tag, because MySQL doesn't have a `ROW` data type, or the `TYPE OF` syntax. The procedure will not work on MySQL. — Bill Karwin, Dec 10 '19 at 00:06
@nbk I have updated the final query which i am planning to build. The only issue here is, it inserts the value of rec.name in each iteration. Instead i want the value of rec.col where col is a variable. One of the value of col is name. — Vinod Nadar, Dec 10 '19 at 04:19

Alexey · Answer 1 · 2019-12-10T09:45:20.897

1

As far as I've seen in mariadb documentation, you can't do it directly. There's no loop support for the ROW TYPE also. However what I would suggest to do is to leave only INFORMATION_SCHEMA.COLUMNS cursor and generate dynamic selects like



 read_loop: LOOP
    FETCH c_columns INTO col;
    IF done THEN
       LEAVE read_loop;
    END IF;
    SET @SQL := CONCAT('SELECT ', col, ' into @colval FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
    PREPARE stmt FROM @SQL;
    execute stmt using col;
    DEALLOCATE PREPARE stmt;
    SET @create_list := CONCAT(@create_list, "'", col, "', '", @colval, "', ");

END LOOP read_loop;

As you're splitting only one table row it will be fine enough.

UPDATE: with additional info it can be done like that. After rethinking it:


 set @SQL := 'SELECT CONCAT(';
 read_loop: LOOP
    FETCH c_columns INTO col;
    IF done THEN
       LEAVE read_loop;
    END IF;
    SET @SQL := CONCAT(@SQL, "'\\'", col, "'\\', \\''", col, "'\\', '");

END LOOP read_loop;
set @SQL := substring(@SQL, 1, CHAR_LENGTH(@SQL) - 3);
set @SQL := concat(@SQL, ') into @create_list FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
PREPARE stmt FROM @SQL;
execute stmt using col;
DEALLOCATE PREPARE stmt;

edited Dec 10 '19 at 09:45

answered Dec 09 '19 at 23:33

Alexey

2,388
1
16
32

2

You can't use a `?` placeholder for a column name. All identifiers (tables, columns, etc.) must be fixed at the time you PREPARE the statement. – Bill Karwin Dec 09 '19 at 23:59
However, you _should_ parameterize `in_row_id` instead of concatenating it into the SQL string. But you aren't. – Bill Karwin Dec 09 '19 at 23:59
Nope, I shouldn't. It's parameter of stored procedure, so keeping it static is much better idea. Thanks for the note about column name however – Alexey Dec 10 '19 at 00:00
This would run a query for every column, i didn't wanted to do so as i am planning to call this procedure via trigger on every update on all tables – Vinod Nadar Dec 10 '19 at 02:57
well that's what you were originally doing in your question – Alexey Dec 10 '19 at 09:09
Thanks alot for your support. This approach is much cleaner. – Vinod Nadar Dec 10 '19 at 14:50
After i all these efforts, i got stuck with below error when i call this procedure from a trigger Dynamic SQL is not allowed in stored function or trigger – Vinod Nadar Dec 10 '19 at 15:29
well, you could use event_scheduler approach. create event that's periodically checks some table where you add ids and table name and calls the stored procedure and in trigger just fill this table with table name and new id. Doesn't seem very elegant, but you can do whatever there at least – Alexey Dec 10 '19 at 17:21

score 0 · Answer 2 · answered Dec 10 '19 at 14:49

@alexey Thanks for your support. I tweaked your answer a bit to add support for null and escape character. Below is the final procedure

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper_new5`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
proc: BEGIN

    DECLARE done INT DEFAULT FALSE;
    DECLARE create_list TEXT(10000);
    DECLARE col TEXT(10000);

    DECLARE c_columns CURSOR FOR 
        SELECT column_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = in_db_name
            AND table_name = in_tbl_name
        ORDER BY ordinal_position;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN c_columns;

    set @SQL := 'SELECT CONCAT(\'';

    read_loop: LOOP
        FETCH c_columns INTO col;
        IF done THEN
           LEAVE read_loop;
        END IF;
        SET @SQL := CONCAT(@SQL, "\\'", col, "\\'\', \', \', IFNULL(QUOTE(", col, "), \'\'), '\, ");
    END LOOP read_loop;

    set @SQL := substring(@SQL, 1, CHAR_LENGTH(@SQL) - 5);

    set @SQL := concat(@SQL, ') into @create_list FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);

    PREPARE stmt FROM @SQL;
    execute stmt;
    DEALLOCATE PREPARE stmt;

    EXECUTE IMMEDIATE CONCAT("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", @create_list, ")" );

    CLOSE c_columns;

END$$

MariaDB extract a column for a variable of Type ROW dynamically

2 Answers2