[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-7207) SQLServer2005Dialect.insertRowNumberFunction() is broken for field names containing "from"

[Holger Pretzsch (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-7207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46115#comment-46115 ] 

Holger Pretzsch commented on HHH-7207:
--------------------------------------

Workaround: Implement your own dialect and override this method so that it matches only the "from" keyword. Code:

{code}
import org.hibernate.dialect.SQLServer2005Dialect;

public class FixedSqlServer2005Dialect extends SQLServer2005Dialect {
    @Override
    protected void insertRowNumberFunction(StringBuilder sql, CharSequence orderby) {
        // Find the end of the select statement
        int selectEndIndex = sql.indexOf(" from ");

        // Insert after the select statement the row_number() function:
        sql.insert(selectEndIndex, ", ROW_NUMBER() OVER (" + orderby + ") as __hibernate_row_nr__");
    }
}
{code}

> SQLServer2005Dialect.insertRowNumberFunction() is broken for field names containing "from"
> ------------------------------------------------------------------------------------------
>
>                 Key: HHH-7207
>                 URL: https://hibernate.onjira.com/browse/HHH-7207
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 4.1.1
>         Environment: Hibernate 4.1.1, Microsoft SQL
>            Reporter: Holger Pretzsch
>
> The Microsoft SQL 2005 and 2008 dialects, which are both based on SQLServer2005Dialect, create broken queries when the entities involved have a mapped column with a name containing "from". This is evidently happens because of the implementation of the dialect's insertRowNumberFunction() method
> {code}
> 	protected void insertRowNumberFunction(StringBuilder sql, CharSequence orderby) {
> 		// Find the end of the select statement
> 		int selectEndIndex = sql.indexOf( FROM );
> 		// Insert after the select statement the row_number() function:
> 		sql.insert( selectEndIndex - 1, ", ROW_NUMBER() OVER (" + orderby + ") as __hibernate_row_nr__" );
> 	}
> {code}
> which searches the entire query string for the string "from" and inserts the given string at the first occurance. This will break queries if "from" appears as a column name.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira