-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Positional parameter inside string literal with HQL
PostPosted: Thu Jan 18, 2007 2:54 pm 
Newbie

Joined: Thu Jan 18, 2007 2:39 pm
Posts: 1
Hibernate version: 3.2


Code:

Code:
getHibernateTemplate().find(         
    "SELECT user FROM com.affno.ricall.user.User AS user join fetch user.userRoles as userRoles join fetch user.userAccess as userAccess join fetch user.company as company WHERE  (user.firstName LIKE '%' + ? + '%' OR user.lastName LIKE '%' + ? + '%') AND user.emailAddress LIKE '%' + ? + '%' AND company.companyName LIKE '%' + ? + '%' AND user.registrationApproved\=1",
    new Object[] { name, name, email , company });


running against Microsoft SQL Server this runs fine but against MySQL it gives the error:

Code:
bad SQL grammar [select user0_.userID as userID0_, userrole2_.userRoleID as userRoleID1_, useraccess4_.userAccessID as userAcce1_2_, company5_.companyID as companyID3_, user0_.rootPublicFolder as rootPubl2_13_0_, user0_.password as password13_0_, user0_.firstName as firstName13_0_, user0_.lastName as lastName13_0_, user0_.emailAddress as emailAdd6_13_0_, user0_.telephone as telephone13_0_, user0_.country as country13_0_, user0_.folder as folder13_0_, user0_.signature as signature13_0_, user0_.registrationApproved as registr11_13_0_, user0_.company as company13_0_, user0_.postCode as postCode13_0_, user0_.notes as notes13_0_, user0_.companyName as company15_13_0_, user0_.termsAndConditions as termsAn16_13_0_, user0_.companyAddress1 as company17_13_0_, user0_.companyAddress2 as company18_13_0_, user0_.companyAddress3 as company19_13_0_, user0_.positionValue as positio20_13_0_, user0_.sectorValue as sectorV21_13_0_, user0_.notReceiveEmails as notRece22_13_0_, user0_.directTelephone as directT23_13_0_, user0_.fax as fax13_0_, user0_.mobile as mobile13_0_, user0_.skype as skype13_0_, user0_.emailFormat as emailFo27_13_0_, user0_.trackDownloadRights as trackDo28_13_0_, user0_.flatRateCommission as flatRat29_13_0_, user0_.copyWriteOwner as copyWri30_13_0_, user0_.brokerID as brokerID13_0_, user0_.created_time as created32_13_0_, user0_.last_update as last33_13_0_, user0_.createdBy as createdBy13_0_, user0_.modifiedBy as modifiedBy13_0_, user0_.status as status13_0_, userrole2_.userRoleName as userRole2_17_1_, userrole2_.userRoleDesc as userRole3_17_1_, userrole2_.created_time as created4_17_1_, userrole2_.last_update as last5_17_1_, userrole2_.createdBy as createdBy17_1_, userrole2_.modifiedBy as modifiedBy17_1_, userrole2_.status as status17_1_, userroles1_.USER_ID as USER1_0__, userroles1_.USER_ROLE_ID as USER2_0__, useraccess4_.userAccessName as userAcce2_16_2_, useraccess4_.created_time as created3_16_2_, useraccess4_.last_update as last4_16_2_, useraccess4_.createdBy as createdBy16_2_, useraccess4_.modifiedBy as modifiedBy16_2_, useraccess4_.status as status16_2_, useraccess3_.USER_ID as USER1_1__, useraccess3_.USER_ACCESS_ID as USER2_1__, company5_.companyName as companyN2_20_3_, company5_.postCode as postCode20_3_, company5_.companyAddress1 as companyA4_20_3_, company5_.companyAddress2 as companyA5_20_3_, company5_.companyAddress3 as companyA6_20_3_, company5_.country as country20_3_, company5_.website as website20_3_, company5_.sectorValue as sectorVa9_20_3_, company5_.created_time as created10_20_3_, company5_.last_update as last11_20_3_, company5_.createdBy as createdBy20_3_, company5_.modifiedBy as modifiedBy20_3_, company5_.status as status20_3_ from T_USER user0_ inner join T_USER_USER_ROLES userroles1_ on user0_.userID=userroles1_.USER_ID inner join T_USER_ROLE userrole2_ on userroles1_.USER_ROLE_ID=userrole2_.userRoleID inner join T_USER_USER_ACCESS useraccess3_ on user0_.userID=useraccess3_.USER_ID inner join T_USER_ACCESS useraccess4_ on useraccess3_.USER_ACCESS_ID=useraccess4_.userAccessID inner join T_COMPANY company5_ on user0_.company=company5_.companyID where (user0_.firstName like '%'+?+'%' or user0_.lastName like '%'+?+'%') and (user0_.emailAddress like '%'+?+'%') and (company5_.companyName like '%'+?+'%') and user0_.registrationApproved=1]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+'a'+'%' or user0_.lastName like '%'+'a'+'%') and (user0_.emailAddress like '%'+' at line 1

Have tried numerous variations on the HQL without success.

How can I use the positional parameter inside the string literal please?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.