Description:
Hi All,
Actually don't know that is it a error or bug but whenever i'm passing any query in i_QueryString in parameter inside the following procedure i'm getting the error Not lock tables
plz lemme know the solution !!
these procedure calls one more procedure and that is Get_Table_Name which generates the table name and gives the out parameter as table name..
and the outer procedure takes some in parameters(as any select * from tablename query) through which it creates table with the generated table name...
Regards
Rahulc Chaudahri
How to repeat:
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS Generate_Table$$
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE PROCEDURE Generate_Table
-> (
-> IN i_QueryString LONGTEXT,
-> IN i_MaxNoOfTrans INTEGER,
-> OUT o_TableName VARCHAR(30),
-> OUT o_AtualNoOfRecords INTEGER,
-> OUT o_OutputStatus INTEGER,
-> OUT o_OutputMessage VARCHAR(100)
-> )
-> BEGIN
-> DECLARE v_TableName VARCHAR(30);
-> DECLARE v_MaxnoRecords INTEGER;
-> DECLARE v_TabQuery LONGTEXT;
-> DECLARE v_ErrorCode INTEGER DEFAULT 0;
-> BEGIN
-> SET v_ErrorCode = 1;
-> SET v_TableName = GET_TABLE_NAME();
-> SET v_TabQuery = concat('CREATE TABLE ',v_TableName,' AS ', i_QueryString);
-> SET @v_String = v_TabQuery;
->
-> IF i_MaxNoOfTrans IS NOT NULL THEN
-> SET @v_String = concat(v_TabQuery,' LIMIT ', i_MaxNoOfTrans + 1);
-> END IF ;
->
->
-> SET v_MaxnoRecords = concat('SELECT COUNT(*) INTO ', v_MaxnoRecords, ' FROM ', v_TableName);
->
-> SET o_AtualNoOfRecords = v_MaxnoRecords ;
-> SET o_TableName = v_TableName;
-> SET o_OutputStatus = 0;
-> SET o_OutputMessage = 'Table Created Successfully';
->
-> prepare stmt FROM @v_String;
-> execute stmt;
-> deallocate prepare stmt;
-> END;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> DELIMITER ;
mysql> call Generate_Table('select * from event',2,@a,@w,@e,@r);
ERROR 1100 (HY000): Table 'event' was not locked with LOCK TABLES
mysql>
+----------------------------------------------+
| Get_Table_Name Procedure |
+----------------------------------------------+
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `snox`.`Gen_Table_Name`$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE `snox`.`Gen_Table_Name`
-> (
-> OUT o_TableName VARCHAR(30)
-> )
-> BEGIN
-> DECLARE v_Tbl_Name VARCHAR(30);
-> DECLARE v_TName VARCHAR(30);
-> DECLARE v_TINT INTEGER;
-> DECLARE v_TNint INTEGER;
-> DECLARE V_No_Data INTEGER DEFAULT 1;
-> DECLARE No_Data_Found CONDITION FOR 1329;
-> DECLARE CONTINUE HANDLER FOR No_Data_Found SET v_No_Data = -1;
-> BEGIN
-> SELECT Tbl_Name INTO v_Tbl_Name FROM stblname
-> WHERE Tbl_Id IN (SELECT MAX(Tbl_Id) FROM Stblname);
-> IF V_NO_DATA = -1 THEN
-> SET V_No_Data = 1;
-> SET v_TINT = 123456789;
-> ELSE
-> SET v_TINT = SUBSTR(v_Tbl_Name,8,30);
-> END IF;
-> SET v_TNint = v_TINT + 1;
-> SET v_TName = Concat(Upper('SN_TEMP'),v_TNint);
-> INSERT INTO stblname(Tbl_Name)
-> VALUES(Upper(v_TName));
-> SET o_TableName = v_TName;
-> END;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
Description: Hi All, Actually don't know that is it a error or bug but whenever i'm passing any query in i_QueryString in parameter inside the following procedure i'm getting the error Not lock tables plz lemme know the solution !! these procedure calls one more procedure and that is Get_Table_Name which generates the table name and gives the out parameter as table name.. and the outer procedure takes some in parameters(as any select * from tablename query) through which it creates table with the generated table name... Regards Rahulc Chaudahri How to repeat: mysql> DELIMITER $$ mysql> mysql> DROP PROCEDURE IF EXISTS Generate_Table$$ Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE PROCEDURE Generate_Table -> ( -> IN i_QueryString LONGTEXT, -> IN i_MaxNoOfTrans INTEGER, -> OUT o_TableName VARCHAR(30), -> OUT o_AtualNoOfRecords INTEGER, -> OUT o_OutputStatus INTEGER, -> OUT o_OutputMessage VARCHAR(100) -> ) -> BEGIN -> DECLARE v_TableName VARCHAR(30); -> DECLARE v_MaxnoRecords INTEGER; -> DECLARE v_TabQuery LONGTEXT; -> DECLARE v_ErrorCode INTEGER DEFAULT 0; -> BEGIN -> SET v_ErrorCode = 1; -> SET v_TableName = GET_TABLE_NAME(); -> SET v_TabQuery = concat('CREATE TABLE ',v_TableName,' AS ', i_QueryString); -> SET @v_String = v_TabQuery; -> -> IF i_MaxNoOfTrans IS NOT NULL THEN -> SET @v_String = concat(v_TabQuery,' LIMIT ', i_MaxNoOfTrans + 1); -> END IF ; -> -> -> SET v_MaxnoRecords = concat('SELECT COUNT(*) INTO ', v_MaxnoRecords, ' FROM ', v_TableName); -> -> SET o_AtualNoOfRecords = v_MaxnoRecords ; -> SET o_TableName = v_TableName; -> SET o_OutputStatus = 0; -> SET o_OutputMessage = 'Table Created Successfully'; -> -> prepare stmt FROM @v_String; -> execute stmt; -> deallocate prepare stmt; -> END; -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> DELIMITER ; mysql> call Generate_Table('select * from event',2,@a,@w,@e,@r); ERROR 1100 (HY000): Table 'event' was not locked with LOCK TABLES mysql> +----------------------------------------------+ | Get_Table_Name Procedure | +----------------------------------------------+ mysql> DELIMITER $$ mysql> mysql> DROP PROCEDURE IF EXISTS `snox`.`Gen_Table_Name`$$ Query OK, 0 rows affected (0.00 sec) mysql> CREATE PROCEDURE `snox`.`Gen_Table_Name` -> ( -> OUT o_TableName VARCHAR(30) -> ) -> BEGIN -> DECLARE v_Tbl_Name VARCHAR(30); -> DECLARE v_TName VARCHAR(30); -> DECLARE v_TINT INTEGER; -> DECLARE v_TNint INTEGER; -> DECLARE V_No_Data INTEGER DEFAULT 1; -> DECLARE No_Data_Found CONDITION FOR 1329; -> DECLARE CONTINUE HANDLER FOR No_Data_Found SET v_No_Data = -1; -> BEGIN -> SELECT Tbl_Name INTO v_Tbl_Name FROM stblname -> WHERE Tbl_Id IN (SELECT MAX(Tbl_Id) FROM Stblname); -> IF V_NO_DATA = -1 THEN -> SET V_No_Data = 1; -> SET v_TINT = 123456789; -> ELSE -> SET v_TINT = SUBSTR(v_Tbl_Name,8,30); -> END IF; -> SET v_TNint = v_TINT + 1; -> SET v_TName = Concat(Upper('SN_TEMP'),v_TNint); -> INSERT INTO stblname(Tbl_Name) -> VALUES(Upper(v_TName)); -> SET o_TableName = v_TName; -> END; -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELIMITER ;