using stored procedure with input parameters

mcelwaa · **Joined:** Wed Aug 03, 2005 10:39 am **Posts:** 2

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0.1

Mapping documents:
1st Attempt

Code:

   <sql-query name="getSecurityInfo" callable="true">
      <return alias="aqlp" class="com.nationwide.ag.model.AQLP">
         <return-property name="objectId" column="id"/>
         <return-property name="returnValue" column="IsAPAdmin"/>
      </return>
       { ? = call upAPGetSecurityInfo() }
   </sql-query>

2nd Attempt

Code:

   <sql-query name="getSecurityInfo" callable="true">
      <return alias="aqlp" class="com.nationwide.ag.model.AQLP">
         <return-property name="objectId" column="id"/>
         <return-property name="returnValue" column="IsAPAdmin"/>
      </return>
       { ? = call upAPGetSecurityInfo(?) }
   </sql-query>

Code between sessionFactory.openSession() and session.close():
1st and 2nd Attempts - see stack traces below

Code:

results = getHibernateTemplate().findByNamedQuery(storedProcName, userId);

I have also tried the following code

Code:

results = getHibernateTemplate().findByNamedQueryAndNamedParam(storedProcName, "@NetworkId", userId);

but get the java.lang.IllegalArgumentException "Parameter @NetworkId does not exist as a named parameter in [<queryString>]".

The queryString I have attempted are:
- { ? = call upAPGetSecurityInfo() }
- { ? = call upAPGetSecurityInfo(?) }
- { ? = call upAPGetSecurityInfo(@NetworkId) }

Full stack trace of any exception that occurs:
1st Attempt
0 WARN [main] util.JDBCExceptionReporter - SQL Error: 201, SQLState: HY000
0 ERROR [main] util.JDBCExceptionReporter - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Procedure 'upAPGetSecurityInfo' expects parameter '@NetworkId', which was not supplied.
10 ERROR [main] hibernate.BaseHibernateDAO - Failure to retrieve by stored procedure getSecurityInfo because of the following database problem: (Hibernate operation): encountered SQLException [[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Procedure 'upAPGetSecurityInfo' expects parameter '@NetworkId', which was not supplied.]; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Procedure 'upAPGetSecurityInfo' expects parameter '@NetworkId', which was not supplied.
org.springframework.jdbc.UncategorizedSQLException: (Hibernate operation): encountered SQLException [[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Procedure 'upAPGetSecurityInfo' expects parameter '@NetworkId', which was not supplied.]; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Procedure 'upAPGetSecurityInfo' expects parameter '@NetworkId', which was not supplied.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Procedure 'upAPGetSecurityInfo' expects parameter '@NetworkId', which was not supplied.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.execute(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at org.hibernate.dialect.SybaseDialect.getResultSet(SybaseDialect.java:140)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:126)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1202)
at org.hibernate.loader.Loader.doQuery(Loader.java:368)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:208)
at org.hibernate.loader.Loader.doList(Loader.java:1522)
at org.hibernate.loader.Loader.list(Loader.java:1505)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1343)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at org.springframework.orm.hibernate3.HibernateTemplate$32.doInHibernate(HibernateTemplate.java:812)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:310)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:803)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:799)
at com.nationwide.ag.persistence.dao.hibernate.BaseHibernateDAO.retrieveByStoredProc(BaseHibernateDAO.java:104)
at com.nationwide.ag.persistence.dao.hibernate.DbaiDAOImpl.retrieveSecurityInfo(DbaiDAOImpl.java:43)
at com.nationwide.ag.persistence.dao.hibernate.DbaiDAOImplTest.testRetrieveSecurityInfo(DbaiDAOImplTest.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:85)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:58)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:60)
at java.lang.reflect.Method.invoke(Method.java:391)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:436)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:311)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

2nd Attempt
0 WARN [main] util.JDBCExceptionReporter - SQL Error: 0, SQLState: 07009
0 ERROR [main] util.JDBCExceptionReporter - [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
0 ERROR [main] hibernate.BaseHibernateDAO - Failure to retrieve by stored procedure getSecurityInfo because of the following database problem: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.validateParameters(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.validateParameters(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.preImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.execute(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at org.hibernate.dialect.SybaseDialect.getResultSet(SybaseDialect.java:140)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:126)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1202)
at org.hibernate.loader.Loader.doQuery(Loader.java:368)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:208)
at org.hibernate.loader.Loader.doList(Loader.java:1522)
at org.hibernate.loader.Loader.list(Loader.java:1505)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1343)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at org.springframework.orm.hibernate3.HibernateTemplate$32.doInHibernate(HibernateTemplate.java:812)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:310)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:803)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:799)
at com.nationwide.ag.persistence.dao.hibernate.BaseHibernateDAO.retrieveByStoredProc(BaseHibernateDAO.java:104)
at com.nationwide.ag.persistence.dao.hibernate.DbaiDAOImpl.retrieveSecurityInfo(DbaiDAOImpl.java:43)
at com.nationwide.ag.persistence.dao.hibernate.DbaiDAOImplTest.testRetrieveSecurityInfo(DbaiDAOImplTest.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:85)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:58)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:60)
at java.lang.reflect.Method.invoke(Method.java:391)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:436)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:311)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Name and version of the database you are using:
MS SQLServer 2000

The generated SQL (show_sql=true):
1st Attempt
Hibernate: { ? = call upAPGetSecurityInfo() }

2nd Attempt
Hibernate: { ? = call upAPGetSecurityInfo(?) }

Debug level Hibernate log excerpt:

max · **Posted:** Wed Aug 03, 2005 1:00 pm

what does @NetworkId mean ? Should it not be :NetworkId ?

in any case try to strip it down to just hibernate instead of adding yet another layer (via spring) while tracking down such issues.

Look at the unit test cases that uses stored procedures and see how it works.

mcelwaa · **Joined:** Wed Aug 03, 2005 10:39 am **Posts:** 2

Here is the stored procedure that I am trying to execute. @NetworkId is the parameter into the procedure. I am attempting to bind the variable userId to this parameter.

I have also attempted without Spring and got similar results. I will revert back to see if different stack traces or exceptions are thrown that might be more apparent to the problem.

I will also try to find unit test cases that used stored procedures.

thx.

Code:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[upAPGetSecurityInfo]
@NetworkId VARCHAR(10)

AS
SET NOCOUNT ON
DECLARE @IsAPAdmin VARCHAR(5)
SET @NetworkId = LOWER(@NetworkId)
IF EXISTS (   SELECT 
         *
      FROM
         tblApplicationAdmin
      WHERE
         LOWER(NetworkId) = @NetworkId AND
         ApplicationId = 14)
   SET @IsAPAdmin = 'true'
ELSE
   SET @IsAPAdmin = 'false'

Select @IsAPAdmin IsAPAdmin

SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

sarahl2 · **Joined:** Tue Jul 26, 2005 1:25 pm **Posts:** 14

In java i execute my stored procedures this way:

Code:

public String executeMyStoredProc(String[] params){   
Session session = HibernateOperator.getSession();   
Connection con = session.connection();
CallableStatement proc = null;
String msg = null;
try{
   String sql = "{ call p_my_stored_proc_name(?,?,?) }";
   proc = con.prepareCall(sql);

   proc.registerOutParameter(1, Types.VARCHAR); //varchar - @output_message 

  proc.setObject(2, params[0], Types.CHAR);   
  proc.setObject(3, params[1], Types.CHAR);
  proc.execute();
         
  msg = proc.getString(1);
      
}catch(SQLException se){
  LOG.error(se);
}finally{
  try{
  proc.close();
            //con.close();
            
         }catch(SQLException se){
            LOG.error(se);
         }
      }

sarahl2 · **Joined:** Tue Jul 26, 2005 1:25 pm **Posts:** 14

In java i execute my stored procedures this way:

Code:

public String executeMyStoredProc(String[] params){   
Session session = HibernateOperator.getSession();   
Connection con = session.connection();
CallableStatement proc = null;
String msg = null;
try{
   String sql = "{ call p_my_stored_proc_name(?,?,?) }";
   proc = con.prepareCall(sql);

   proc.registerOutParameter(1, Types.VARCHAR); //varchar - @output_message 

  proc.setObject(2, params[0], Types.CHAR);   
  proc.setObject(3, params[1], Types.CHAR);
  proc.execute();
         
  msg = proc.getString(1);
      
}catch(SQLException se){
  LOG.error(se);
}finally{
  try{
  proc.close();
  }catch(SQLException se){
     LOG.error(se);
  }
}

I've simplified this, so it may have errors

morgandev · **Joined:** Wed Jul 27, 2005 12:45 pm **Posts:** 1

When calling stored procs in Hibernate queries, '?'s are only need for input params.

eg

{ call upAPGetSecurityInfo(?) }

NOT

{ ? = call upAPGetSecurityInfo(?) }

max · **Posted:** Fri Aug 05, 2005 2:53 am

please read the docs about stored procedure before stating that ;)

? = xxx is needed since the procedure needs to *return* something (a resultset)

TonyT · **Posted:** Fri Aug 05, 2005 2:58 pm

max wrote:

? = xxx is needed since the procedure needs to *return* something (a resultset)

Is that in fact true? I'm no whiz at Hibernate (I just started using it two days ago), but it seems that with my Sybase stored procedures, and I presume MSSQL, too, the "? = xxx"
syntax is not used.

For instance, if I have the following SQL:

Code:

CREATE PROCEDURE check_credentials
@userid VARCHAR(10)

AS

SELECT isAPadmin = CASE
  WHEN EXISTS (
    SELECT  *
    FROM
    sometable
    WHERE
    LOWER(user_name) = LOWER(@userid))
    THEN  'true'
    ELSE 'false'
    END

Then, in my hbm file, shouldn't the following work?

Code:

  <sql-query name="getSecurityInfo" callable="true">
      <return alias="" class="foo.bar.blaz.Admin">
         <return-property name="credentialed" column="IsAPAdmin"/>
      </return>
       { call check_credentials(?) }
   </sql-query>

Tony

max · **Posted:** Sat Aug 06, 2005 3:00 am

please read the docs about how to use SP with hibernate

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

procedures don't return anything at the front.

they should have their out parameters whose values will be set.

so it makes sense to just use without ? =

and isn't that how it works anyway

max · **Posted:** Thu Sep 01, 2005 6:30 am

again - read the docs!

it explicitly states that we only support SP's that return a resultset - everything else should be done via a raw connection.

sk08 · **Joined:** Tue Aug 30, 2005 2:33 am **Posts:** 22

stored procedures can't have return statement. The only way to return something is via OUT

create or replace procedure A(x in number) return number is
begin
x := 1;
return x;
end;
/
This won't work so how can we put ? = for procedures

This is the only way procedures would return Resultset

create or replace procedure A(x OUT SYS_REFCURSOR) is
sr SYS_REFCURSOR;
begin
open sr for select id, name from person;
end;
/

max · **Posted:** Fri Sep 02, 2005 1:48 am

read the docs.....it works on oracle, mssql and db2. all can return a resultet - and yes on some it happens via the first output parameter, but in others it actually just happens as one would expect when you just return).

smingins · **Posted:** Wed Sep 14, 2005 12:44 am

mcelwaa wrote:

(DelegatingPreparedStatement.java:168)
at org.hibernate.dialect.SybaseDialect.getResultSet(SybaseDialect.java:140)
at org.hibernate.jdbc.AbstractBatcher.getResultSet

Hi ... was just reading this (as I am looking for Sybase Stored Procedure postings) and noticed in this stack trace the mention of a Sybase Dialect ... is that correct? What dialect are u using?

BTW ... to execute a stored procedure in Sybase looks like:

<sql-query name="getStdsByStdTypeCodeUsingStoredProcedure">
exec w_get_stds_by_type_code @std_type_code = :stdTypeCode
<return class="nz.govt.nzqa.eos.service.common.bo.Std"/>
</sql-query>

HTH
Shane

P.S. If anyone reading knows how to execute a sybase stored procedure that creates a temporary table (hash table) which the result set is returned from, could you let me know ... I get back a list with the correct size, but the objects in there are all null!

max · **Posted:** Wed Sep 14, 2005 3:05 am

all null ? is that when executing via jdbc or through a <sql-query> def in hibernate ?