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

[Eduard Martinescu (JIRA)]

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.2, 3.3.1
         Environment: Hibernate 3.3.2/3.3.1 connection to a SQL Server database
            Reporter: Eduard Martinescu


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