I am trying to map a legacy database schema. In one table there is a primary key of type numeric and another table that references that primary key as a varchar. ( I can not change the schema)

I want to use hibernate annotations to automatically perform the joining of those two tables.
I was able to use @SQLInsert to write to the database correctly but hibernate throws an error when it tries to load.



Here is the SQL that hibernate generated (I have removed unnessessary columns and changed the names of the tables/columns to protect the guilty)

Hibernate:
select
this_id as id1_24_2_,
this_.value as value24_2_,
...
otherTable_.id as Other1_5_0_,
...
from
reference this_
left outer join
otherTable
on this_.value=otherTable_.id
...
where
...

Mapping documents:

@Entity
@Table(name="reference")
@SQLInsert(sql ="insert into reference (value, <other fields>) "+
"values " +
"(CONVERT('VARCHAR',?) , ?, ?)")
public class Reference {

@ManyToOne
@JoinColumn(name="value")
public OtherObject getOtherObject() {
return other;
}

...
}


@Entity
@Table(name = "otherTable"
public class OtherObject {
@Id
@GeneratedValue
@Column(name = "id")
public long getId(){
...
}
}


Full stack trace of any exception that occurs:


15:39:45,707 WARN [JDBCExceptionReporter] SQL Error: 257, SQLState: 42000
15:39:45,707 ERROR [JDBCExceptionReporter] Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed. Use the CONVERT function to run this query.

Name and version of the database you are using:

Sybase 10


Thanks in advance,
dkatzel