Space is not allowed after the ':' parameter prefix

Question

My problem is that I am trying to insert text containing char: in my request

I tried setting a double backslash // to char: but still doesn't work.

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

Here, Hibernate parses an insert containing a strictly encoded value that has a colon in it. If you rewrite an insert to use parameters, then Hibernate will not see the value as part of the statement.

Answer 2

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

 eg: where user_id = :userId 

You will not get any problems here if you specify a parameter with the same name as "userId";
2) you attribute the value

 eg: select count(id) :: integer 

when you do this, you need to use an escape character, otherwise hibernate will assume that it is a parameter. and this will lead to the error "All parameters are not set", you can overcome this by writing code with the escape character

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

So this will solve your problem. And if you mistakenly use a slash instead of a backslash, you will get the error message “after the prefix”

 Worng: select count(id)//://: integer right:select count(id)\\:\\: integer 

But I strongly recommended using the CAST function instead of using the "::" this operator ie select CAST(count(id) as integer) This is the best way to cast types, and this will lead to minimal errors, and think that this is a parameter. and this will lead to the error "All parameters are not set", you can overcome this by writing code with the escape character

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

So this will solve your problem. And if you mistakenly use a slash instead of a backslash, you will get the error message “after the prefix”

 Worng: select count(id)//://: integer right:select count(id)\\:\\: integer 

But I strongly recommended using the CAST function instead of using the "::" this operator ie select CAST(count(id) as integer) This is the best way to type cast, resulting in minimal errors

Answer 3

The problem is that the RECORD column contains ":", so Hibernate is waiting for a parameter after it. I had the same problem with you.