I am trying to execute a stored procedure and am getting the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 3.
This is with SQL Server 2000 SP4 and Hibernate 3.2.5.
Here is my mapping file:
------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class dynamic-insert="false" dynamic-update="false" mutable="true"
name="com.ohfs.arc.value.EmployerValue" optimistic-lock="version"
polymorphism="implicit" select-before-update="false" table="Client">
<id column="ClientID" name="clientId">
<generator class="native"/>
</id>
<property column="ERTaxID" name="employerId"/>
</class>
<sql-query name="GetEmployersSP" callable="true">
<return alias="cm" class="com.ohfs.arc.value.EmployerValue">
<return-property name="clientId" column="ClientID"/>
<return-property name="employerId" column="EmployerIdentifier"/>
</return>
{ ? = call usp_WSGetEmployers(:taxId, :divFlag) }
</sql-query>
</hibernate-mapping>
----------------------------------
And my DAO:
----------------------------------
public class EmployerHibernateDAO {
public List<EmployerValue> getList(String taxId) {
Session s = HibernateUtil.getSessionFactory().openSession();
Query q = s.getNamedQuery("GetEmployersSP")
.setString("taxId", taxId)
.setString("divFlag", "N");
List<EmployerValue> evs = q.list();
return evs;
}
}
----------------------------------
When I call this code, i.e.:
EmployerHibernateDAO dao = new EmployerHibernateDAO();
List<EmployerValue> employers = dao.getList("411437794");
I get the following error:
2009-04-03 09:04:50,886 DEBUG jdbc.AbstractBatcher.logOpenPreparedStatement - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2009-04-03 09:04:50,886 DEBUG jdbc.ConnectionManager.openConnection - opening JDBC connection
2009-04-03 09:04:50,886 DEBUG connection.DriverManagerConnectionProvider.getConnection - total checked-out connections: 0
2009-04-03 09:04:50,886 DEBUG connection.DriverManagerConnectionProvider.getConnection - using pooled JDBC connection, pool size: 0
2009-04-03 09:04:50,886 DEBUG hibernate.SQL.log -
/* named native SQL query GetEmployersSP */ { ? = call usp_WSGetEmployers(?, ?) }
Hibernate:
/* named native SQL query GetEmployersSP */ { ? = call usp_WSGetEmployers(?, ?) }
2009-04-03 09:04:50,886 DEBUG jdbc.AbstractBatcher.getPreparedStatement - preparing statement
2009-04-03 09:04:50,933 DEBUG loader.Loader.bindNamedParameters - bindNamedParameters() N -> divFlag [2]
2009-04-03 09:04:50,933 DEBUG type.StringType.nullSafeSet - binding 'N' to parameter: 2
2009-04-03 09:04:50,949 DEBUG loader.Loader.bindNamedParameters - bindNamedParameters() 411437794 -> taxId [1]
2009-04-03 09:04:50,949 DEBUG type.StringType.nullSafeSet - binding '411437794' to parameter: 1
2009-04-03 09:04:50,980 DEBUG jdbc.AbstractBatcher.logClosePreparedStatement - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2009-04-03 09:04:50,980 DEBUG jdbc.AbstractBatcher.closePreparedStatement - closing statement
2009-04-03 09:04:50,980 DEBUG util.JDBCExceptionReporter.logExceptions - could not execute query [{ ? = call usp_WSGetEmployers(?, ?) }]
com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 3.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildParamTypeDefinitions(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doPrepExec(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown Source)
at org.hibernate.dialect.SybaseDialect.getResultSet(SybaseDialect.java:186)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:193)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1784)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at com.ohfs.arc.dao.EmployerHibernateDAO.getList(EmployerHibernateDAO.java:15)
at com.ohfs.arc.dao.EmployerHibernateDAOTest.testGetEmployersNamedQuery(EmployerHibernateDAOTest.java:57)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
at junit.framework.JUnit4TestAdapter.run(JUnit4TestAdapter.java:32)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:421)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:912)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:766)
2009-04-03 09:04:50,980 WARN util.JDBCExceptionReporter.logExceptions - SQL Error: 0, SQLState: null
2009-04-03 09:04:50,980 ERROR util.JDBCExceptionReporter.logExceptions - The value is not set for the parameter number 3.
------------------------------------------
To me, it seems the error is saying that nothing is mapped to the return parameter in the call, which doesn't make sense. Has anyone been able to get Hibernate to work with SQL Server stored procs? I've seen some similar forum posts regarding SQL Server and stored procs, but no answers; maybe this is a general problem with SQL Server?
I'd appreciate any help or ideas on things to try. thanks!
jeff
Login
FAQ
Search