[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3811) Bug: No ability to escape SQL keywords in HQL queries.

[John Roberts (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3811?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32623#action_32623 ] 

John Roberts commented on HHH-3811:
-----------------------------------

Bah, finally figured it out. Use *backticks* in the mapping document, and the HQL will generate the proper escape sequences.

There is still no way to have a field named an HQL keyword, e.g. " WHERE where='here' " is not a possible query, but at least the set of illegal field names is small and well-known with that one.

The bug here is this is completely missing from the HQL documentation. It's actually in section 5.4 with the mapping documentation. So if you approach the documentation from the HQL side, you'll never find it.

> Bug: No ability to escape SQL keywords in HQL queries.
> ------------------------------------------------------
>
>                 Key: HHH-3811
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3811
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.3.0.CR1
>         Environment: 3.3.0.CR1, MS SQL Server 2005 Express
>            Reporter: John Roberts
>            Priority: Critical
>
> There appears to be no way to escape property names that are SQL keywords in HQL queries. For example, if you have a property named "key", which is a reasonable name for a property, and you are constructing an HQL query, e.g.:
>   session.createQuery("select from Entities where key=?");
> That will fail on MS SQL Server, because "key" is a reserved T-SQL keyword. However, there is no way to make it succeed, as either an absent-minded developer appears to have forgotten to add the ability to escape keywords to HQL (oops), or it's the secret of the millennium and was left out of the documentation on purpose (it has certainly boggled the minds of the folks on FreeNode #java, #hibernate, and the hibernate forum). Note that none of the following will succeed (this is what I have tried so far):
>   session.createQuery("select from Entities where key=?");
>   session.createQuery("select from Entities where [key]=?"); // <-- mssql style still fails to be parsed
>   session.createQuery("select from Entities where `key`=?");
>   session.createQuery("select from Entities where 'key'=?");
>   session.createQuery("select from Entities where \"key\"=?");
>   session.createQuery("select from Entities where ''key''=?");
>   session.createQuery("select from Entities where {key}=?");
>   session.createQuery("select from Entities where <key>=?");
>   session.createQuery("select from Entities where (key)=?");
>   session.createQuery("select from Entities where $key$=?");
>   session.createQuery("select from Entities where $key=?");
> Note that using the underlying DBMS's escape method *does* work for column names in the entity mappings, but does not work in actual HQL.
> Since most DBMS's have their own dialects of SQL, the inability to escape keywords in HQL means that it is generally impossible to guarantee that any given HQL query will work unless you specifically design your application around the underlying DBMS (which is precisely one of the things you were hoping to avoid with Hibernate -- although on the other hand you're already tied to the DBMS with escaped keywords in the entity mapping anyways).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira