Hello All,

I am gettiing a "Implicit conversion from datatype 'text' to 'varchar' is not allowed. Use the CONVERT function to run this query." error when trying to query on a field that is a "text" in a sql server 6.5 database. Does anyone know how to solve this problem?? Is anything wrong with my xdoclet tags in the object or with the hibernate mapping file property?

My objects are generated using middlegen. Here is the get and set methods for the field that is generating the error

/**
* @hibernate.property
* column="TM_Description"
* length="2147483647"
*
*/
public String getTmDescription() {
return this.tmDescription;
}

public void setTmDescription(String tmDescription) {
this.tmDescription = tmDescription;
}

I generate the hibernate mapping file using java2hbm ant task and this how it is in the mappin g file for the above field:

<property
name="tmDescription"
type="java.lang.String"
update="true"
insert="true"
column="TM_Description"
length="2147483647"
/>

I appreciate any inputs or help that you can provide. Thanks.

What's the jdbc driver you use ? Don't use microsoft own driver but prefer the jtds one
What it the column type generated in the database ? Do you know why the length is 2147483647 ?

Thank you for responding.

I am using the com.inet.Tds.Driver not the microsoft's driver.

Middlegen automatically sets the length to "2147483647" in the object. The corresponding field in the sql server 6.5 database is a 'text' with a length of 10.

FYI, I am not generating the database shema using hibernate. I have an existing sql server 6.5 database. I just generated the objects from middlegen and then used java2hbm to generate the hibernate mappings for the objects using the xdoclet tags that are included in the objects by middlegen.

I have also tried this in my object:
/*
*@hibernate property
*@hibernate column name="TM_Description" sql-type="text"
*
*/
public String getTmDescription() {

}

it generates the corresponding <property> in the mapping file but it still does not work.

My sql query is using the above "text" field in the WHERE clause and thats when it generates a convsersion error.

I solved the problem!

My hibernate query was using WHERE lower(description) LIKE ? (it was using lower because I set the ignoreCase() on the net.sf.hibernate.expression.Example that I was using to do my query).

Apparently, SQL Server 6.5 doesn't like it when you try to convert a 'text' field to a lower case.

It works just fine without the lower in the WHERE (I had to remove the ignoreCase() from the Example usage).

Thanks for all that tried to help!