Hi Guys,

I am new to this form and I am sorry if this question has been asked somewhere else in this forum.

Ok, I am trying to convert existing hibernate code to call stored function, which was working for mysql, over to sql server. I am getting the following exception trace;

Exception:
----------

2010-11-02 15:35:45,444 [geoPatternQueueJmsContainer-1] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 07000
2010-11-02 15:35:45,445 [geoPatternQueueJmsContainer-1] ERROR org.hibernate.util.JDBCExceptionReporter - Parameter #3 has not been set.



Hibernate Mapping (version 3.3) MySQL version (working)
-------------------------------------------------------
<sql-query name="getDistanceInMilesFNC" callable="true">
<return-scalar column="distance" type="double"/>
select distance_in_miles(?,?) as distance
</sql-query>


Hibernate Mapping (hibernate 3.3) MS SQL SERVER version (not working)
---------------------------------------------------------------------
<sql-query name="getDistanceInMilesFNC" callable="true">
<return-scalar column="distance" type="double"/>
{ ? = call distance_in_miles(?,?) }
</sql-query>




Java Code (same code used for MySQL & SQL Server call)
-------------------------------------------------------
try {
Query query = sess.getNamedQuery("getDistanceInMilesFNC")
.setParameter(0, ipAddress).setParameter(1, postCode);
Object obj = query.uniqueResult(); //query.list() causes the same exception
} catch (Exception e) {....}


Any help would be greatly appreciated. I have already wasted 1.5 days on this problem.

Many thanks

-Fahd

We're using Oracle db and it makes no difference, IMO.

Here it goes for functions and pkg call that returns a cursor.

For FUNCTION Call that returns one value only:
------------------------------------
<sql-query name="FN_GET_NBR" callable="false">
<query-param name="param" type="java.lang.String"/>
<query-param name="param" type="java.lang.String"/>
<query-param name="param" type="java.lang.Integer"/>
<![CDATA[ select FN_GET_NBR(?,?,?) from dual ]]>
</sql-query>

Code:
----------
Query namedQry = session.getNamedQuery("FN_GET_NBR");
namedQry.setString(0, strOne);
namedQry.setString(1, strTwo);
namedQry.setInteger(2, num);
List searchResults = namedQry.list();
BigDecimal result = (BigDecimal) searchResults.get(0);
// if not null then show the value
System.out.println(result.intValue());


For Package Call that returns a cursor (multiple rows):
-------------------------------------------------------
<sql-query name="PKG.RESULTS" callable="true">
<query-param name="param" type="java.lang.Long"/> <!-- Value 1 -->
<query-param name="param" type="java.lang.Long"/> <!-- Value 2 -->
<query-param name="param" type="java.lang.Integer"/> <!-- Value 3 -->
{?= call PKG.RESULTS(?,?,?) }
</sql-query>

Code:
----------
Query namedQuery = session.getNamedQuery("PKG.RESULTS");
namedQuery.setLong(0, arg1);
namedQuery.setLong(1, arg2);
namedQuery.setInteger(2, arg3);
resultsList = namedQuery.list();

Iterator itemIter = resultsList.iterator();
List dtoList = new ArrayList();
yourClassDTO d = null;

Object[] result = null;
while (iterator.hasNext()) {
result = (Object[]) itemIter.next();
Long itemId = (result[0] != null) ? new Long(result[0].toString()) : null;
Integer typeId = (result[1] != null) ? new Integer(result[1].toString()) : null;
d = yourClassDTO();
d.setItemId(itemId);
d.setTypId(typeId);
dtoList.add(d);
}

return dtoList;

You have to iterate through the resultsList and parse each row to get the values. For this, you need to know the number of columns and their type in rows returned to parse it correctly. You can instantiate your class [yourClassDTO], populate with the values in the object array above, build a list with your dto class and return it. That's pretty much it.

Hope this helps to get to the next step :)

Thanks see_one.

You are a life saver. It works like a charm...

Regards.