Hibernate version: 3.0

Mapping documents:
<sql-query name="selectSummaryStatus" callable="true">
<return-scalar column="status" type="string" />
(select dbo.SUMMARYSTATUS(?) as status)
</sql-query>

Full stack trace of any exception that occurs:
org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1565)
at org.hibernate.loader.Loader.list(Loader.java:1545)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1406)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at org.springframework.orm.hibernate3.HibernateTemplate$32.doInHibernate(HibernateTemplate.java:906)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:362)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:897)
at com.sterling.sif.DAO.SummaryStatusDAOImpl.getOrderNumberfromStsHrXml(SummaryStatusDAOImpl.java:31)
at com.sterling.sif.DAO.SummaryStatusmain.main(SummaryStatusmain.java:36)
Caused by: java.sql.SQLException: Invalid JDBC call escape at line position 6.
at net.sourceforge.jtds.jdbc.SQLParser.copyProcName(SQLParser.java:367)
at net.sourceforge.jtds.jdbc.SQLParser.callEscape(SQLParser.java:482)
at net.sourceforge.jtds.jdbc.SQLParser.escape(SQLParser.java:925)
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1063)
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.<init>(JtdsPreparedStatement.java:104)
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.<init>(JtdsCallableStatement.java:66)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareCall(ConnectionJDBC2.java:2155)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareCall(ConnectionJDBC2.java:2141)
at org.apache.commons.dbcp.DelegatingConnection.prepareCall(DelegatingConnection.java:212)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareCall(PoolingDataSource.java:268)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:380)
at org.hibernate.jdbc.AbstractBatcher.prepareCallableQueryStatement(AbstractBatcher.java:96)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1125)
at org.hibernate.loader.Loader.doQuery(Loader.java:373)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
at org.hibernate.loader.Loader.doList(Loader.java:1562)

Name and version of the database you are using: MSSQL Server 2000

I am trying to invoke a user defined function externalized in the hbm file as mentioned above. Following code snippet is how this function is invoked from the DAO code.

Object args = new Object();
args = orderid; // orderid is a Long passed as an argument
log.debug("OrderId:"+orderid);
list=(List) getHibernateTemplate().findByNamedQuery"selectSummaryStatus",args);

Is there anything wrong with way the function is defined in the hbm file? Or is there anything wrong with the way it is being invoked from the DAO?
Is the way in which it is defined in the hbm file depend on the underlying driver being used as the aforementioned code snippet and definition of the user defined function works well with JSQL and not with the JTDS?

In Hibernate 3.0, the main effect of callable="true" was to require an unused return parameter. So remove the callable="true" (which isn't needed).

If you really have written the code that you posted, you might also get someone to proof read it. I don't know what the second parameter of your "findByNamedQuery" method is, but it's almost certainly not a single Long. Even if it was, why did you allocate a new Object then immediately discard it?

Thanks, I shall remove the callable="true".

The second parameter is the orderId that is taken by the user defined function.

Apart the code that has the error of creating a new instance of the object and discarding it, is there anything wrong with the way the function is defined in the hbm file.

I don't think so. What I'm most worried about is that you should probably be passing a list of parameters into that helper/template method: you'll get strange class cast exceptions and other badness if you pass a parameter in directly.