About hibernate NamedNativeQuery

Question

Hello,

I am a newbie in hibernate, I am using @javax.persistence.NamedNativeQuery to resolve my stored proc calls from hibernate to mysql but i am getting errors.

Please help:

My persistent class is:

@Entity    
@javax.persistence.NamedNativeQuery(name = "SampleNameQuery", query = "call spS_NamedQuery(?,?)", resultClass = NamedQuery.class)    
public class NamedQuery {

 @Id
 public String name;

 @Column
 public String value;
}

My mysql stored proc is:

DELIMITER $$

DROP PROCEDURE IF EXISTS `cpgDB`.`spS_NamedQuery`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `spS_NamedQuery`(IN name VARCHAR(255),OUT var_value VARCHAR(255))
BEGIN

SET var_value = (SELECT value FROM NamedQuery WHERE NamedQuery.name = name);
END$$

DELIMITER ;

The main method that is calling this code is as:

public static void main(String[] args) throws Exception {
  Transaction trx = null;
  Session session = HibernateSessionFactory.getSession();
  try {
   trx = session.beginTransaction();

   org.hibernate.Query query = session.getNamedQuery("SampleNameQuery");
   query.setParameter(0,"fsdfsdf");       
   String value = "";
   query.setParameter(1,value);       
   List objList = query.list();    
   trx.commit();
  } catch (Exception ex) {
   trx.rollback();
   throw ex;
  } finally {
   HibernateSessionFactory.closeSession();
  }    
 }

My hibernate config file is as :

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"&gt;

<hibernate-configuration>
 <session-factory>
  <property name="connection.username">xxxx</property>
  <property name="connection.url">jdbc:mysql://127.0.0.1:3306/cpgDB</property>
  <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="myeclipse.connection.profile">MySQL</property>
  <property name="connection.password">xxxxx</property>
  <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hbm2ddl.auto">update</property>
  <property name="show_sql">true</property>
  <property name="format_sql">true</property>  
  <mapping class="Demo.NamedQuery"/>
 </session-factory>
</hibernate-configuration>

On code execution i am getting following error/exception:

Sep 15, 2009 8:54:16 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: OUT or INOUT argument 2 for routine cpgDB.spS_NamedQuery is not a variable or NEW pseudo-variable in BEFORE trigger
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 at org.hibernate.loader.Loader.doList(Loader.java:2214)
 at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
 at org.hibernate.loader.Loader.list(Loader.java:2090)
 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 Demo.TestDrive.main(TestDrive.java:44)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: OUT or INOUT argument 2 for routine cpgDB.spS_NamedQuery is not a variable or NEW pseudo-variable in BEFORE trigger
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:930)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2864)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1567)
 at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1154)
 at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:679)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1256)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
 at org.hibernate.loader.Loader.doQuery(Loader.java:662)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
 at org.hibernate.loader.Loader.doList(Loader.java:2211)
 ... 7 more

Please help what is going wrong and help me get it corrected. Also refer me to suitable links where i can learn more about this technique.

Thanks in advance

Ashish

Answer 1

I was unable to find answer to this question, the solution i implemented then was to not use the 'out' field in my stored procedure and return the result of sql query via select command only.

Possibly Hibernate has not fully implemented this feature of returning 'Out' parameters.