6

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>)

?

Community
  • 1
  • 1
watery
  • 5,026
  • 9
  • 52
  • 92

1 Answers1

8

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 ( ...";
Joop Eggen
  • 107,315
  • 7
  • 83
  • 138
  • Great! Do you know if that will be ORM-independent? – watery Jan 21 '14 at 18:37
  • I do not know of any standard in JPA query language, jpql. The definition of parameters, colon+identifier, would not need escaping the colon. But know, I have never encountered this problem. – Joop Eggen Jan 21 '14 at 20:36
  • @Joop Eggen will it also work in case of a stored procedure, `lp_dyn:LOOP` I have this in a dynamic stored procedure I have to write, but it gives me an error when I escape it with '//' – rd22 Aug 18 '15 at 09:11
  • @endlessProgrammer `"....lp_dyn:\\:LOOP..."` isn't it (backslash, twice when inside quoted string? I do not know. – Joop Eggen Aug 18 '15 at 09:46
  • @Joop Eggen Yeah! My bad, I meant `\\\`. but this doesn't work inside of a stored proc. – rd22 Aug 18 '15 at 09:49
  • @Joop Eggen did you just use double colon ?? was that intentional ? – rd22 Aug 18 '15 at 12:00
  • side Note: Escape of backslash(as in file path) is also done with another backslash. – kestrel Oct 22 '20 at 12:40