0

I'm trying to make a prepared query but MySQL refuses to accept it when I use placeholders:

This query works

WITH x AS (SELECT 'root' as user_id)
SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id = user.user)
WHERE user.user = (select user_id FROM x);

This query also works

PREPARE test_query FROM "WITH x AS (SELECT 'root' as user_id)
SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id = user.user)
WHERE user.user = (select user_id FROM x);";

Adding the ? placeholder fails

PREPARE test_query FROM "WITH x AS (SELECT ? as user_id)
SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id = user.user)
WHERE user.user = (select user_id FROM x);";

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? as user_id' at line 1

MySQL Information: mysql Ver 15.1 Distrib 10.2.6-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

The queries were tested on the mysql database.

  • What about PREPARE test_query FROM "SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id = user.user) WHERE user.user = ?;"; The docs say you can use a ? in place of a data value. It may not consider a select list item to be a data value. – RDFozz Jan 9 '18 at 15:40
  • Is that really your query? Can't it be done in a much simpler way?? – Rick James Jan 10 '18 at 1:41
  • @RickJames this is the tiny version of the real query. I used this answer as a guide to solve the problem and it works but, fails if I try to make a prepared statement with same query with couple placeholders.stackoverflow.com/questions/7745609/… – riteshshrv Jan 10 '18 at 4:25
  • @RDFozz Using real column name in select list and ? in place of a data value also fails. – riteshshrv Jan 10 '18 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.