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

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 = {(a)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}