[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-5227) Stored Procedure invocation gets 'top 2' embedded by SQLServerDialect

[Gail Badner (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5227?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Gail Badner resolved HHH-5227.
------------------------------

      Assignee: Gail Badner
    Resolution: Duplicate

> Stored Procedure invocation gets 'top 2' embedded by SQLServerDialect
> ---------------------------------------------------------------------
>
>                 Key: HHH-5227
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5227
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.3.1, 3.3.2
>         Environment: Hibernate 3.3.2/3.3.1 connection to a SQL Server database
>            Reporter: Eduard Martinescu
>            Assignee: Gail Badner
>
> I have an entity class, annotated with Named Native Queries that invoke stored procedures
> {code}
> @Entity
>   @NamedNativeQuery(name = "NetworkServiceElementAlarm_GetById",
>       resultClass=Alarm.class,
>       query = "{call Grock.dbo.NetworkServiceElementAlarm_GetById(:alarmId)}",
>       hints = {@QueryHint(name="org.hibernate.callable",value="true"),
>                @QueryHint(name="org.hibernate.readOnly",value="true")}),
> public class Alarm
>     implements java.io.Serializable
> {
> {code}
> I then later invoke that named query to retrieve a value from the database
> {code}
>       return (Alarm)
>       emGrock.createNamedQuery("NetworkServiceElementAlarm_GetById")
>              .setParameter("alarmId", alarmId)
>              .getSingleResult();
> {code}
> However, this query fails with an error returned from the JDBC driver layer, indicating "{" is invalid.  Upon further investigation, it turned out that the SQLServerDialect class is injecting "top 2" into the middle of the call, resulting in the following SQL {code} "{call Grock.dbotop 2.NetworkServiceElementAlarm_GetByID(?)}" {code}
> The problem is in the function 'getLimitString()' within the SQLServerDialect class.  It blindly assumes that the incoming SQL has either "select" or "select distinct", and inserts "top " + the current limit count into the SQL string.  
> I've worked around the problem temporarily by creating my own subclass of SQLServerDialect that overrides that method and looks for the presence of "{call", and if it finds it, returns the SQL string back unmodified (thereby not imposing a limit).  I'm sure there is probably something better that can be done....
> {code}
> public class SQLServerDialect
>     extends org.hibernate.dialect.SQLServerDialect
> {
>   @Override
>   public String getLimitString (String querySelect, int offset, int limit)
>   {
>     if (querySelect.indexOf("{call") >= 0)
>       return querySelect;
>     else
>       return super.getLimitString(querySelect, offset, limit);
>   }
> }
> {code}

-- 
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