CODEWITHSUNDEEP
javaoracle-databasehibernate
ggDeGreat
ggDeGreat

Reputation: 1196

Space is not allowed after parameter prefix ':'

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

Upvotes: 18

Views: 29801

Answers (4)

menoktaokan
menoktaokan

Reputation: 506

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.

Upvotes: 0

Arjun Nagathankandy
Arjun Nagathankandy

Reputation: 725

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

Upvotes: 18

Mohamed K.
Mohamed K.

Reputation: 9

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

Upvotes: -1

Nathan Hughes
Nathan Hughes

Reputation: 96394

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.

Upvotes: 4

Related Questions