16

My problem is i try to insert text that contain char : in my query

I have tried to put double backslash // before char : but still not working.

ABNORMALLY.java.lang.IllegalArgumentException: 
org.hibernate.QueryException: Space is not allowed after parameter prefix ':' 

INSERT INTO TABLE_A  (A_ID, TYPE_ID, F_ID, REFNO, RECORD) VALUES 
( A_ID_SEQ.nextval, 4 , 9 , 'NY167', q'[LA2010167|SNIP' N CLIP|LMG|1.Unit no\\: 1046, 1 st Floor, Limbang Plaza, 98700 Limbang|2010-12-10||]')
ggDeGreat
  • 1,098
  • 1
  • 17
  • 33
  • enclosing the query in single-quotes seems wrong. what context is this appearing in? – Nathan Hughes Feb 10 '15 at 03:50
  • Similar question was answered here http://stackoverflow.com/a/9461939/4454454 – MaxZoom Feb 10 '15 at 03:51
  • @NathanHughes i just update the info. there is no single quote for enclosing the query. this is when i put the query in log.info() – ggDeGreat Feb 10 '15 at 04:00
  • @MaxZoom using interceptor in one way, but i really dont know how to code it. i prefer other faster way, which something like put additional double backslash to make it worked. – ggDeGreat Feb 10 '15 at 04:05
  • 4
    wouldn't it be easier to add the values as parameters instead of hard-coding them? that way they would be out of the way at the time that hibernate parses this looking for named parameters. – Nathan Hughes Feb 10 '15 at 04:08

4 Answers4

17

From my experience I will tell you. There are two scenarios
1) You want to specify a parameter in the query whose value set dynamically.

eg: where user_id = :userId

Here you wont get any problem if you are setting parameter with same name as "userId";
2) You are typecasting the value

eg: select count(id) :: integer

when you are doing this you have to use escape character otherwise hibernate will think that it is a parameter. And it will give an error "All parameters are not set " you can overcome this with writing code using escape character 

eg:select count(id) \\:\\: integer

So this will solve your problem. And if you are wrongly used forward slash instead of backward slash you will get the error "space is not allowed after prefix"

Wrong: select count(id)//://: integer
Right: select count(id)\\:\\: integer

But I highly recommended you to use the CAST function instead of using "::" this operator ie select CAST(count(id) as integer) It is the better way of type casting and it will lead to minimal errors

davidml
  • 153
  • 5
  • 20
Arjun Nagathankandy
  • 705
  • 8
  • 15
  • 1
    i had the problem with assignment in mysql: @current_client_user_id := client_user_id – tibi Apr 11 '19 at 11:57
4

Here Hibernate is parsing an insert that contains a hard-coded value that has a colon in it. If you rewrite the insert to use parameters then Hibernate won't see the value as part of the statement.

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
0

I didn't solve this problem via Spring Boot and I have created a function (actually a procedure) and used it as a query and the problem has been solved.

menoktaokan
  • 346
  • 3
  • 13
-1

The problem is that your RECORD column contains ":", so, Hibernate waits for a parameter after it. I hade the same problem of you

Mohamed K.
  • 9
  • 1