Hi,
I wish to sort my records retrieved by a column field that is of data type nText.
i have a similar problem as this one "http://forum.hibernate.org/viewtopic.php?p=2386961" from trying to sort column fields of nText data type. But that post has no reply yet.
Checking the forums, i cast the nText to varchar(4000) with "cast(Cat as varchar(4000))". But this returned a syntax error..
Some other forums have suggested this: "cast(description, varchar(4000))"
But this returned a null exception...

This is the line that caused the exception: description is nText
getHibernateTemplate().find("from Cat order by cast(description as varchar(4000) ASC);

Any help would be appreciagted. Thanks.

Hibernate version:
version="1.0" encoding="UTF-8"
Hibernate/Hibernate Mapping DTD 3.0//
Mapping documents:
<class name="Cat" table="tbl_cat" lazy="true">
<id name="id" >
<generator class="native"/>
</id>

<property name="description"/>

<!-- Added a many-to-one for website -->
<many-to-one name="website" class="Website" column="websiteId" not-null="false" not-found="ignore"
unique="true" cascade="none" lazy="false" />
</class>
Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
nested exception is org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 81 [from Cat order by cast(description as varchar(4000)) ASC]

Name and version of the database you are using:
MS SQL Server Express 2005

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Cat.java:
public class Cat extends BaseObject implements Serializable {
. . .
private String description;
...
}

I don't know if its possible at all to use "cast" in the order by, but you could try the following: "from Cat c order by (cast(c.description as varchar(4000))) ASC"

Is cast supported in MSSQL? Isn't it "convert"?

Thanks for your reply.
I've tried the following from your response.
Hmm, i think i'm getting close.. My basic idea is really to cast any "nText/clob" to something sortable. But none of the syntax from the various forums seem to work... Or is there another approach

1) "from Cat c order by (cast(c.description as varchar(4000))) ASC"
output: returns the same exception as what i had earlier

2) 1) "from Cat c order by (convert(c.description, varchar(4000))) ASC"
output: SQL Error: 195, SQLState: S00010
'varchar' is not a recognized built-in function name.
could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

3) "from Cat c order by (cast(c.description, varchar(4000))) ASC"
4) "from Cat c order by (convert(c.description as varchar(4000))) ASC"
output: both returned a java.util.ArrayList that is null, which it shouldn't be null.

Thanks for your reply.
I've tried the following from your response.
Hmm, i think i'm getting close.. My basic idea is really to cast any "nText/clob" to something sortable. But none of the syntax from the various forums seem to work... Or is there another approach

1) "from Cat c order by (cast(c.description as varchar(4000))) ASC"
output: returns the same exception as what i had earlier

2) 1) "from Cat c order by (convert(c.description, varchar(4000))) ASC"
output: SQL Error: 195, SQLState: S00010
'varchar' is not a recognized built-in function name.
could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

3) "from Cat c order by (cast(c.description, varchar(4000))) ASC"
4) "from Cat c order by (convert(c.description as varchar(4000))) ASC"
output: both returned a java.util.ArrayList that is null, which it shouldn't be null.

after fiddling some time, this solved the problem...It was really just finding the right syntax...
convert( nvarchar(4000), description)
It seems hibernate uses convert while sql uses cast...
Thanks all.

Thank You Newbie.
I learnt two things here .
instead of CAST , need to use CONVERT
and then correct syntax of using this.

Thanks again.