Hello Everyone,

I am trying to call stored procedure using Hibenate and JPA Entity Manager. I am utilizing annotations and the whole application is using Struts+Spring+ Hibernate combination. The database is Informix11.5. In the class mentioned below I created a NamedNativeQuery with query name="packageList". The stored procedure I am trying to call is "get_QQPackages(?,:STATE) RETURNING int, char(200)"
If I call the same procedure in informix I get the result set. But when I deploy the war file with the code mentioned below in my app-server(jboss-5.1) during the deployment time itself I am getting the "org.hibernate.cfg.NotYetImplementedException". The stored procedure is supposed to returns me the list with int and corresponding String values.

Here is the code:

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.NamedNativeQuery;
import javax.persistence.QueryHint;
import javax.persistence.Table;

/**
* The persistent class for the emp database table.
*
*/
@Entity
@Table(name = "emp")
@NamedNativeQuery(name = "packageList", query = "{call get_QQPackages(?,:STATE) RETURNING int, char(200)}",
hints = {@QueryHint(name = "org.hibernate.callable",
value = "true"),
@QueryHint(name = "org.hibernate.readOnly",
value = "true")
} )
public class EMP{
private static final long serialVersionUID = 1L;
private Date classlimit;
private BigDecimal classcollrate;
private BigDecimal classotcrate;
private String clerkname;
private Timestamp clerkdate;
private int classdedamt;

@EmbeddedId
protected EmpPK pk;

public EMP(){

}

@Column(nullable = false)
public Date getClasslimit() {
return this.classlimit;
}

public void setClasslimit(Date classlimit) {
this.classlimit = classlimit;
}

@Column(nullable = false, precision = 7, scale = 3)
public BigDecimal getClasscollrate() {
return this.classcollrate;
}

public void setClasscollrate(BigDecimal classcollrate) {
this.classcollrate = classcollrate;
}

@Column(nullable = false, precision = 7, scale = 3)
public BigDecimal getClassotcrate() {
return this.classotcrate;
}

public void setClassotcrate(BigDecimal classotcrate) {
this.classotcrate = classotcrate;
}

@Column(length = 8)
public String getClerkname() {
return this.clerkname;
}

public void setClerkname(String clerkname) {
this.clerkname = clerkname;
}

public Timestamp getClerkdate() {
return this.clerkdate;
}

public void setClerkdate(Timestamp clerkdate) {
this.clerkdate = clerkdate;
}

public int getClassdedamt() {
return classdedamt;
}

public void setClassdedamt(int classdedamt) {
this.classdedamt = classdedamt;
}

public EmpPK getPk() {
return pk;
}

public void setPk(EmpPK pk) {
this.pk = pk;
}

}


Here is the code which I am using to call that namednativequery:
try {
Query query = getEntityManager().createNamedQuery("packageList");
query.setParameter("STATE",state);
logger.info("got the package list");
return query.getResultList();
// return result;
} catch (RuntimeException re) {
logger.error("getting the package list failed", re);
throw re;
}

Here is the snap shot of the exceptions:

13:44:00,690 ERROR [ContextLoader] Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Invocation of init method failed; nested exception is org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1338)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
at java.security.AccessController.doPrivileged(Native Method)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:423)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3910)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4393)
at org.jboss.web.tomcat.service.deployers.TomcatDeployment.performDeployInternal(TomcatDeployment.java:367)
at org.jboss.web.tomcat.service.deployers.TomcatDeployment.performDeploy(TomcatDeployment.java:146)
at org.jboss.web.deployers.AbstractWarDeployment.start(AbstractWarDeployment.java:460)
at org.jboss.web.deployers.WebModule.startModule(WebModule.java:118)
at org.jboss.web.deployers.WebModule.start(WebModule.java:96)
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.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:157)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:96)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:668)
at org.jboss.system.microcontainer.ServiceProxy.invoke(ServiceProxy.java:206)
at $Proxy36.start(Unknown Source)
at org.jboss.system.microcontainer.StartStopLifecycleAction.installAction(StartStopLifecycleAction.java:42)
at org.jboss.system.microcontainer.StartStopLifecycleAction.installAction(StartStopLifecycleAction.java:37)
at org.jboss.dependency.plugins.action.SimpleControllerContextAction.simpleInstallAction(SimpleControllerContextAction.java:62)
at org.jboss.dependency.plugins.action.AccessControllerContextAction.install(AccessControllerContextAction.java:71)
at org.jboss.dependency.plugins.AbstractControllerContextActions.install(AbstractControllerContextActions.java:51)
at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
at org.jboss.system.microcontainer.ServiceControllerContext.install(ServiceControllerContext.java:286)
at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1598)
at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1062)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:822)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:553)
at org.jboss.system.ServiceController.doChange(ServiceController.java:688)
at org.jboss.system.ServiceController.start(ServiceController.java:460)
at org.jboss.system.deployers.ServiceDeployer.start(ServiceDeployer.java:146)
at org.jboss.system.deployers.ServiceDeployer.deploy(ServiceDeployer.java:104)
at org.jboss.system.deployers.ServiceDeployer.deploy(ServiceDeployer.java:45)
at org.jboss.deployers.spi.deployer.helpers.AbstractSimpleRealDeployer.internalDeploy(AbstractSimpleRealDeployer.java:62)
at org.jboss.deployers.spi.deployer.helpers.AbstractRealDeployer.deploy(AbstractRealDeployer.java:50)
at org.jboss.deployers.plugins.deployers.DeployerWrapper.deploy(DeployerWrapper.java:171)
at org.jboss.deployers.plugins.deployers.DeployersImpl.doDeploy(DeployersImpl.java:1439)
at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1157)
at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1178)
at org.jboss.deployers.plugins.deployers.DeployersImpl.install(DeployersImpl.java:1098)
at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1598)
at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1062)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:822)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:553)
at org.jboss.deployers.plugins.deployers.DeployersImpl.process(DeployersImpl.java:781)
at org.jboss.deployers.plugins.main.MainDeployerImpl.process(MainDeployerImpl.java:545)
at org.jboss.system.server.profileservice.ProfileServiceBootstrap.loadProfile(ProfileServiceBootstrap.java:304)
at org.jboss.system.server.profileservice.ProfileServiceBootstrap.start(ProfileServiceBootstrap.java:205)
at org.jboss.bootstrap.AbstractServerImpl.start(AbstractServerImpl.java:405)
at org.jboss.Main.boot(Main.java:209)
at org.jboss.Main$1.run(Main.java:547)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported
at org.hibernate.cfg.annotations.QueryBinder.bindNativeQuery(QueryBinder.java:118)
at org.hibernate.cfg.AnnotationBinder.bindQueries(AnnotationBinder.java:277)
at org.hibernate.cfg.AnnotationBinder.bindClass(AnnotationBinder.java:417)
at org.hibernate.cfg.AnnotationConfiguration.processArtifactsOfType(AnnotationConfiguration.java:498)
at org.hibernate.cfg.AnnotationConfiguration.secondPassCompile(AnnotationConfiguration.java:277)
at org.hibernate.cfg.Configuration.buildMappings(Configuration.java:1115)
at org.hibernate.ejb.Ejb3Configuration.buildMappings(Ejb3Configuration.java:1269)
at org.hibernate.ejb.EventListenerConfigurator.configure(EventListenerConfigurator.java:150)
at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:888)
at org.hibernate.ejb.Ejb3Configuration.configure(Ejb3Configuration.java:416)
at org.hibernate.ejb.HibernatePersistence.createContainerEntityManagerFactory(HibernatePersistence.java:126)


I am stuck and I dont know what to do. Please help me what should I do. Please do let me know if this is the right approach to call the storedprocedure or not?

I was able to fix the previous issue by using this setting:

@Table(name = "emp")
@SqlResultSetMapping(name = "packages", columns = {
@ColumnResult(name = "pkgcode"), @ColumnResult(name = "pkgdesc") })
@NamedNativeQuery(name = "packageList", query = "{call get_QQPackages(?)}", resultSetMapping = "packages", hints = {
@QueryHint(name = "org.hibernate.callable", value = "true"),
@QueryHint(name = "org.hibernate.readOnly", value = "true") })

where pkgcode and pkgdesc are the columns names which I will get as results from the storedprocedure, pkgcode is int and pkgdesc is String.
Here is the exception I am getting:

17:43:31,330 INFO [STDOUT] Hibernate: {call get_QQPackages(?)}
17:43:31,348 ERROR [M106cclassDAO] getting the package list failed
java.lang.UnsupportedOperationException: org.hibernate.dialect.InformixDialect does not support resultsets via stored procedures
at org.hibernate.dialect.Dialect.registerResultSetOutParameter(Dialect.java:1090)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1560)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
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 org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)
at com.emp.service.MempcclassDAO.getPackageList(MempcclassDAO.java:109)
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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy137.getPackageList(Unknown Source)
at com.emp.action.EmpAction.getEmpList(EmpAction.java:75)
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 com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:148)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:93)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:128)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:104)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:148)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:128)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:468)
at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
at org.apache.struts2.dispatcher.ng.filter.StrutsExecuteFilter.doFilter(StrutsExecuteFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareFilter.doFilter(StrutsPrepareFilter.java:65)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
17:43:31,357 WARN [JDBCExceptionReporter] SQL Warning: 0, SQLState: 01I01
17:43:31,357 WARN [JDBCExceptionReporter] Database has transactions

I'm having this same issue. How did you solve?

I saw a similar posting on coderanch/javaranch:

http://www.coderanch.com/t/461849/Object-Relational-Mapping/java/Named-SQLQuery-ResultTransformer-not-working

Please try it delete "@QueryHint(name = "org.hibernate.callable", value = "true")," line.

I know this is an ancient post, but before posting my own identical question, I wanted to check if this had ever been successfully resolved. Thanks!