I'm trying to run a query that involves a custom variable over JDBC toward a MySQL 5.0 database (Hibernate 4 as the ORM):
SET @rownum := 0; SELECT rnum FROM (
SELECT (@rownum := @rownum + 1) AS rnum, col_name_a
FROM table_name
WHERE (col_name_b IS NULL OR col_name_b != 'USER' ) ORDER BY col_name_a) c
WHERE col_name_a = :aValue
The first SET @rownum := 0;
is needed to reset the variable, but the colon isn't required there (I've set the allowMutilQuery=true as suggested in this question).
The problem is with the following SELECT
, where I keep on getting:
org.hibernate.QueryException: Space is not allowed after parameter prefix ':'
Is there a way around this? Should I use other methods than
javax.persistence.Query q = EntityManager instance>.createNativequery(String)
and
q.setParameter(<param name>, <param value>)
?
Heredoc syntax is simply another way to delimit a string literal, it does not "escape" anything. In order to escape the string (to prevent SQL-injection) you need to either call mysqli_real_escape_string() , or (preferably) use parameterized queries.
Special characters such as commas and quotes must be "escaped," which means you place a backslash in front of the character to insert the character as a part of the MySQL string.
In hibernate the escape of the colon :
is done with a backslash:
SET @rownum \:= 0; SELECT rnum FROM ( ...
Or in java:
String sql = "SET @rownum \\:= 0; SELECT rnum FROM ( ...";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With