In mysql dynamic stored proc, is @var required to build a prepared statement?
I have a similar stored proc (but longer). It is called from PHP (GET
request on Apache)
delimiter //
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
SET @full_statement = CONCAT('SELECT ',col,' FROM ',tbl );
PREPARE stmt FROM @full_statement;
EXECUTE stmt;
END
//
delimiter ;
From what I read, @s is a mysql session variable living as long as my
session is alive. The @s presence annoys me since I fear that 2 concurrent
request on that stored proc might play with this "global variable". So I
tried to remove the '@' like this
delimiter //
CREATE PROCEDURE dynamic(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
DECLARE full_statement VARCHAR(300);
SET full_statement = CONCAT('SELECT ',col,' FROM ',tbl );
PREPARE stmt FROM full_statement;
EXECUTE stmt;
END
//
delimiter ;
to build the prepared statement without success.
I seem to have constantly
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'full_statement; EXECUTE stmt;
Does my fear of 2 calls within my php session is really a problem ? (If
not, what about having 200 stored procedures using that same global
variable) ?
Can I really achieve my goal and remove the '@' and let the prepared
statement being handle by a simple stored proc variable or is it a
constraint of prepared statement ?
Regards.
No comments:
Post a Comment