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:
Code:
@Entity
@NamedNativeQuery(name = "SampleNameQuery",query = "call spS_NamedQuery(?,?)",resultSetMapping="mapping",resultClass = NamedQuery.class)
@SqlResultSetMapping(name="mapping",columns=@ColumnResult(name="value"))
public class NamedQuery {
@Id
public String name;
@Column
public String value;
}
My mysql stored proc is:
Code:
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:
Code:
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 var_value = "";
query.setParameter(1,var_value);
List objList = query.list();
trx.commit();
} catch (Exception ex) {
trx.rollback();
throw ex;
} finally {
HibernateSessionFactory.closeSession();
}
}
My hibernate config file is as :
Code:
<?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">
<hibernate-configuration>
<session-factory>
<property name="connection.username">xxxxx</property>
<property name="connection.url">jdbc:mysql://localhost: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:
Code:
Sep 16, 2009 2:33:41 PM org.hibernate.tool.hbm2ddl.TableMetadata <init>
INFO: indexes: [primary]
Sep 16, 2009 2:33:41 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
INFO: schema update complete
Hibernate:
call spS_NamedQuery(?,?)
Sep 16, 2009 2:33:41 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1414, SQLState: 42000
Sep 16, 2009 2:33:41 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:47)
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.
My stack is:
Mysql Version 14.12 Distrib 5.0.67
Java
Hibernate 3
Thanks in advance
Ashish
Login
FAQ
Search