Space is not allowed after parameter prefix ':'

Question

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

Answer 1

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

Answer 2

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.