[nhibernate-issues] [JIRA] Commented: (NH-837) Error using Limits with DB2400Dialect

[NHibernate J. <mik...@us...>]

     [ http://jira.nhibernate.org/browse/NH-837?page=comments#action_14625 ]
     
Christophe Vigouroux commented on NH-837:
-----------------------------------------

I've just looked to the Hibernate 3.2.1 version of DB2400Dialect and it is implemented like this :

public String getLimitString(String sql, int offset, int limit) {
		return new StringBuffer(sql.length() + 40)
			.append(sql)
			.append(" fetch first ")
			.append(limit)
			.append(" rows only ")
			.toString();
	}

The limit is applied but not the offset. The only way to have something like Row_Number as in DB2 UDB is to create a custom function (UDF) which could have the same behavior, but it should not be used in any public version of NHibernate because it would ask users to install this UDF on their database system.

> Error using Limits with DB2400Dialect
> -------------------------------------
>
>          Key: NH-837
>          URL: http://jira.nhibernate.org/browse/NH-837
>      Project: NHibernate
>         Type: Bug
>   Components: Data Providers
>     Versions: 1.2.0.Beta2
>     Reporter: Christophe Vigouroux

>
> Using with DB2400Dialect :
> query.SetFirstResult(startRow);
> query.SetMaxResults(maxRows);
> IList<Core.Imprimante> retour = query.List<Core.Imprimante>();
> results in the generation of the following SQL statement :
> select * from (select rownumber() over() as rownum, this_.CDIMPLOG as CDIMPLOG0_0_, this_.CDSOC as CDSOC0_0_, this_.LBIMP as LBIMP0_0_ FROM TIMPRIM this_) as tempresult where rownum <= ?
> This statement is for use with DB2 UDB, but not compatible with DB2 for iSeries.
> The statement should use RRN(table) instead of rownumber() and use "fetch first n rows" instead of over().
> DB2400Dialect should override GetLimitString of DB2Dialect to provide with functionality.
> Thanks for your support !

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