JPQL String Function

[Amandeep Singh]

I am unable to find the right usage for LOCATE string function.


i use like this-

-----------
select LOCATE(steering, horn, 1) from Car
--------------

here steering and horn are the column's for Car table.

if i modify the query like this and run in Apache Derby Client, it give's me error. I haven't yet tried thru EJB3 JPA.

---------------
select LOCATE(steering, hor, 1) from Car
------------------

error
-------------------
Error: Column 'HOR' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'HOR' is not a column in the target table.
SQLState: 42X04
ErrorCode: -1
-----------------------

i want to first run it cleanly here before i do run thru JPA.

[Christophe Verré]

What is that column called "hor" in your SQL ? And also be careful, not all databases support the third argument for LOCATE.

[Amandeep Singh]

hor is not a column. It is a searchable string.


LOCATE(searchString, stringToBeSearched), so why should i expect i'st and 2nd parameters as arguments.

[Christophe Verré]

Don't you have to put strings into quotations ? If you don't, the container will think it's an entity field.

[Amandeep Singh]

Great!

it works like this (mine silling mistake)

select locate("steering", "hor", 1) from Car

there is no syntax specified under EJB 3 in Action for its usage.