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

[John Roberts (JIRA)]

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