Description:
When creating a cursor that runs the SHOW COLUMNS query inside a stored function the name of the table cannot be set with a variable.. so it seems.
Why am i trying to do this in a function? I'm trying to concat the names of the columns from a specific list of tables. However, since the DECLARE CURSOR sql statement is not updatable and must be declared at the beginning of the function block i need two functions to do this. It would work if only the SHOW COLUMNS statement would accept a variable for the table name inside the function.
If there is another way to obtain a list of columns in a table (without using the slow information schema) inside a function please describe how.
Thanks!
How to repeat:
DELIMITER $$
DROP FUNCTION IF EXISTS `bc_admin`.`GetCols` $$
CREATE FUNCTION `bc_admin`.`GetCols`(tab_name varchar(100)) RETURNS text CHARSET latin1
BEGIN
DECLARE ret text;
DECLARE done int;
DECLARE col_name,f2,f3,f4,f5,f6 varchar(200);
DECLARE cols CURSOR FOR SHOW COLUMNS IN tab_name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET done=0;
SET ret='';
OPEN cols;
col_data:LOOP
FETCH cols INTO col_name,f2,f3,f4,f5,f6;
IF (done=1) THEN LEAVE col_data; END IF;
SET ret = CONCAT(ret,'-',col_name);
END LOOP;
RETURN ret;
END $$
DELIMITER ;
Now run:
SELECT GetCols('mytable');
Which returns:
Table 'bc_admin.tab_name' doesn't exist
It should return:
Table 'bc_admin.mytable' doesn't exist
OR the concat list of column names if the table does exist
Suggested fix:
Other sql statements replace local variable names with their proper value, so why not the tab_name variable in the SHOW COLUMNS query?
If the query "SHOW COLUMNS IN tab_name" was changed to "SELECT tab_name" then the output shows that the variable value is used, which is what i would expect.
Description: When creating a cursor that runs the SHOW COLUMNS query inside a stored function the name of the table cannot be set with a variable.. so it seems. Why am i trying to do this in a function? I'm trying to concat the names of the columns from a specific list of tables. However, since the DECLARE CURSOR sql statement is not updatable and must be declared at the beginning of the function block i need two functions to do this. It would work if only the SHOW COLUMNS statement would accept a variable for the table name inside the function. If there is another way to obtain a list of columns in a table (without using the slow information schema) inside a function please describe how. Thanks! How to repeat: DELIMITER $$ DROP FUNCTION IF EXISTS `bc_admin`.`GetCols` $$ CREATE FUNCTION `bc_admin`.`GetCols`(tab_name varchar(100)) RETURNS text CHARSET latin1 BEGIN DECLARE ret text; DECLARE done int; DECLARE col_name,f2,f3,f4,f5,f6 varchar(200); DECLARE cols CURSOR FOR SHOW COLUMNS IN tab_name; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; SET done=0; SET ret=''; OPEN cols; col_data:LOOP FETCH cols INTO col_name,f2,f3,f4,f5,f6; IF (done=1) THEN LEAVE col_data; END IF; SET ret = CONCAT(ret,'-',col_name); END LOOP; RETURN ret; END $$ DELIMITER ; Now run: SELECT GetCols('mytable'); Which returns: Table 'bc_admin.tab_name' doesn't exist It should return: Table 'bc_admin.mytable' doesn't exist OR the concat list of column names if the table does exist Suggested fix: Other sql statements replace local variable names with their proper value, so why not the tab_name variable in the SHOW COLUMNS query? If the query "SHOW COLUMNS IN tab_name" was changed to "SELECT tab_name" then the output shows that the variable value is used, which is what i would expect.