flexible search query returns java.sql.SQLException: Invalid value for getLong()

[Former Member]

Hello all ,

I have made a many - many relation between two tables , first table is Showrooms the other one is Plants and the Joining table is ShowroomPlantRelation , my aim is to get list of plants by a showroom code ,, i have wrote this flexible search query

SELECT {p:plantCode} FROM { Plants as p JOIN ShowroomPlantRelation as rel ON {p:PK} = {rel:target} JOIN Showrooms AS S ON {rel:source} = {S:PK} } WHERE {S.showRoomCode}=0000

and it has worked in HAC flexible search and give me the correct results 'p1' , but when i embed it into the code it gives me this exception java.sql.SQLException: Invalid value for getLong() - 'p1'

my java code is :-

 **public List<PlantsModel> findPlantsByShowRoomCode(final String showRoomCode)
 {
     final String queryString = "SELECT {p:plantCode} FROM {Plants as p JOIN ShowroomPlantRelation as rel ON {p:PK} = {rel:target}JOIN Showrooms AS S ON {rel:source} = {S:PK}}WHERE {S.showRoomCode}= " + "?showRoomCode ";
     final FlexibleSearchQuery query = new FlexibleSearchQuery(queryString);
     query.addQueryParameter("showRoomCode", showRoomCode);
     return flexibleSearchService.<PlantsModel> search(query).getResult();
 }**

what i am missing here that makes my flexible search query doesn't work in my code ?

[Former Member]

Can you please try to load model using pK "eg : SELECT {p: Plantsmodel.PK}" in your query.

[arvind-kumar_avinash]

Please try to form the queryString as given in findStadiumsByCode at https://wiki.hybris.com/pages/viewpage.action?pageId=294094164#Trail~TestingtheDAO_-WritetheDAOimple...

[Former Member]

Thank you , I have solved the issue by changing the flexible search query into :-

 **final String queryString = //
             "SELECT {p:" + PlantsModel.PK + "}" + "FROM {" + PlantsModel._TYPECODE
                     + " AS p JOIN ShowroomPlantRelation as rel ON {p:PK} = {rel:target}JOIN Showrooms AS S ON {rel:source} = {S:PK}}"//
                     + "WHERE " + "{S.showRoomCode}="
             + "?showRoomCode ";**

and it returns the values correctly.

[Former Member · Accepted solution]

You should return PKs in your search:

 `SELECT {p:PK} FROM { Plants as p JOIN ShowroomPlantRelation as rel ON {p:PK} = {rel:target} JOIN Showrooms AS S ON {rel:source} = {S:PK} } WHERE {S.showRoomCode}=0000`

[Former Member · Accepted solution]

Thank you , I have solved the issue by changing the flexible search query into :-

final String queryString = // "SELECT {p:" + PlantsModel.PK + "}" + "FROM {" + PlantsModel._TYPECODE + " AS p JOIN ShowroomPlantRelation as rel ON {p:PK} = {rel:target}JOIN Showrooms AS S ON {rel:source} = {S:PK}}"// + "WHERE " + "{S.showRoomCode}=" + "?showRoomCode ";

[Former Member]

Hi Prihan,

In the flexible search you select codes (String) but you put as result class PlantsModel.
you must specify String as result class then return the result.

 query.setResultClassList(Arrays.asList(String.class));
 final SearchResult<String> result = getFlexibleSearchService().search(query);
 return result.getResult();

Best regards,
Anouar