Tutorial :Is it possible to execute a string in MySQL?



Question:

I have to convert a MSSQL stored proc that passes a varchar that is a query:

INSERT INTO Results    EXEC (@Expresion);  

This isn't working. I'm pretty sure that EXEC and EXECUTE aren't MySQL commands, but CALL doesn't work either.

Does anyone know if it's even possible to have something like JavaScript's eval function for MySQL?


Solution:1

EXECUTE is a valid command in MySQL. MySQL reference manual


Solution:2

I think you're looking for something like this:

SET @queryString = (  SELECT CONCAT('INSERT INTO user_group (`group_id`,`user_id`) VALUES ', www.vals) as res FROM (      SELECT GROUP_CONCAT(qwe.asd SEPARATOR ',') as vals FROM (              SELECT CONCAT('(59,', user_id, ')') as asd FROM access WHERE residency = 9       ) as qwe   ) as www  );    PREPARE stmt FROM @queryString;  EXECUTE stmt;  DEALLOCATE PREPARE stmt;   SET @asd = NULL;  


Solution:3

This is the SQL equivalent of eval(my_string);:

@Expression = 'SELECT "Hello, World!";';  PREPARE myquery FROM @Expression;  EXECUTE myquery;  

Basically I combined the existing answers, neither tells you how to do eval exactly.


If you want to add parameters, you can use this:

@username = "test";  @password = "asdf";  @Expression = 'SELECT id FROM Users WHERE name = ? AND pass = ?;'  PREPARE myquery FROM @Expression;  EXECUTE myquery USING @username, @password;  

And to answer the original question exactly:

@Expression = 'SELECT "Hello, World!";'  PREPARE myquery FROM @Expression;  INSERT INTO Results    EXECUTE myquery;  

Note that the PREPARE ... FROM statement wants a session variable (prefixed with @). If you try to pass a normal variable, it will throw its hands up in the air and it just won't care.


Solution:4

The EXECUTE MySQL command can only be used for one prepared statement.

If case you want to execute multiple queries from the string, consider saving them into file and source it, e.g.

SET @query = 'SELECT 1; SELECT 2; SELECT 3;';  SELECT @query INTO OUTFILE '/tmp/temp.sql';  SOURCE /tmp/temp.sql;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »