Error 1295
Date: March 07, 2010 03:08PM
Hi there,
I'm using MySql 5.1 on Windows Vista x64 and in Sakila database I wrote
DELIMITER $$
USE `sakila`$$
DROP PROCEDURE IF EXISTS `usp_cursor_example`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_cursor_example`(
IN name_in VARCHAR(255)
)
READS SQL DATA
BEGIN
DECLARE name_val VARCHAR(255);
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE friends_cur CURSOR FOR
SELECT TABLE_NAME AS NAME FROM Information_Schema.TABLES
WHERE TABLE_SCHEMA='sakila' AND TABLE_TYPE='BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN friends_cur;
SELECT FOUND_ROWS() INTO num_rows;
the_loop: LOOP
FETCH friends_cur
INTO name_val;
IF no_more_rows THEN
CLOSE friends_cur;
LEAVE the_loop;
END IF;
/* This working fine
set @sql=CONCAT('SELECT * FROM ', name_val, ' INTO OUTFILE ''C:/Temp/', name_val, '.txt''');
*/
/* This has error: "This command is not supported in the prepared statement protocol yet" */
SET @SQL=CONCAT('Backup Table ', name_val, ' TO ''C:/Temp/''');
PREPARE myStmt FROM @SQL;
EXECUTE myStmt;
DEALLOCATE PREPARE myStmt;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
END$$
DELIMITER ;
As you can see the statement
set @sql=CONCAT('SELECT * FROM ', name_val, ' INTO OUTFILE ''C:/Temp/', name_val, '.txt''');
Is working just fine, but this one
SET @SQL=CONCAT('Backup Table ', name_val, ' TO ''C:/Temp/''');
PREPARE myStmt FROM @SQL;
Given me error #1295
How do I fix this error?
Thanks,
Oded Dror
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.