hi,

i am trying to execute a native sql thru Hibernate.

select max(CAST(substring(p1.fld1,9,25) as int)) as {p1.fld1}
from table1 {p1} where p1.fld1 like :cat_id

the cat id is : 'partcat%'

( from a string field , get a integer , then find its max, so i can create primary key. ie. emp-1, emp-2 etc.. )

if i removed the percentage the sql works ok, via Hibernate.

if i placed the percentage sign (important for me) , then Hibernate gives error saying :

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid column name: part_cat2_0_

Hibernate seems to think that there is an extra column ?

this error does not occur everytime, but once in 2 attempts at the sql.

the generated sql is :
select max(CAST(substring(p1.part_category_id,9,25) as int)) as part_category_id0_
from part_category_master p1 where p1.part_category_id like ?

any tips are welcome.

thanks in advance.

iyyappan

do you use setString to set the parameter value?

BTW, remember can only return hibernate mapped classes, not scalar values.

my java is :

List list = session.getNamedQuery("table1.test").setString("cat","emp%").list();

and the sql is :

<sql-query name="table.test">
<return alias="p1" class="abc.emp"/><![CDATA[
select max(CAST(substring(p1.emp,9,25) as int)) as {p1.emp_id}
from employees {p1} where p1.emp_id like :cat ]]>
</sql-query>