SQLServer 2008 Stored Procedure: Query did not return any result.
Hi guys,
Once more time, I came here to ask your help.
The problem isn't actually to connect or even select something from my database,
because it's working fine, it's about calling stored procedures. My procedure is
something like the code below and also works fine if I call it from the
SQLServer 2008 Management Studio with the query:
Description: Called query insine microsoft management studio exec PRC_JBOSS_TAREFA '04/04/2013 00:00:00', '05/04/2013 23:59:59', 1, 3, '1648, 3, 5, 12'
Description: Procedure to call
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[PRC_JBOSS_TAREFA]
@DATAINICIO varchar(50),
@DATAFINAL varchar(50),
@TIPODATA int,
@DESTINO numeric(19,0),
@FONTES varchar(1000)
as
begin
declare @CODIGO int
create table #FONTE(CODIGO int)
if LEN(@FONTES) > 0
begin
set @FONTES = @FONTES + ','
end
while LEN(@FONTES) > 0
begin
set @CODIGO = CONVERT(int, LTRIM(SUBSTRING(@FONTES, 1, CHARINDEX(',', @FONTES) - 1)))
insert into #FONTE (CODIGO) values (@CODIGO)
set @FONTES = SUBSTRING(@FONTES, CHARINDEX(',', @FONTES) + 1, LEN(@FONTES))
end
if @TIPODATA = 1
begin
SELECT DISTINCT
XXX.STR_XXX AS XXXXXX,
dbo.FNC_CONCATENA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK, @DESTINO) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX
FROM XXXXX XXX(NOLOCK)
INNER JOIN XXXXXXX XXXXX (NOLOCK)
ON XXX.LON_CODIGO_XXXXX_FK = XXXXX.LON_CODIGO_PK
WHERE XXXXX.LON_CODIGO_XXXXXXX_FK in (select CODIGO from #FONTE)
AND XXX.STA_EXCLUIDO = 0
AND XXX.DTA_XXXXXXXX BETWEEN @DATAINICIO AND @DATAFINAL
AND NOT XXX.LON_CODIGO_XXXXXXX_FK IN (4,8)
end else
if @TIPODATA = 2
begin
SELECT DISTINCT
XXX.STR_XXX AS XXXXXX,
dbo.FNC_CONCATENA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK, @DESTINO) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX
FROM XXXXXXXXXXX XXX (NOLOCK)
INNER JOIN XXXXXXXXXX XXXX (NOLOCK)
ON XXX.LON_CODIGO_XXXX_FK = XXXX.LON_CODIGO_PK
INNER JOIN YYYYYYYYYYYYYY (NOLOCK)
ON YYY.LON_CODIGO_XXX_FK = XXX.LON_CODIGO_PK
AND YYY.LON_CODIGO_ZZZ_FK = 1
WHERE XXXX.LON_CODIGO_FON_FK in (select CODIGO from #FONTE)
AND YYY.DTA_XXXXXXXX BETWEEN @DATAINICIO AND @DATAFINAL
AND XXX.STA_EXCLUIDO = 0
AND NOT XXX.LON_CODIGO_XXXXXXXXXXX_FK IN (4,8)
end else
if @TIPODATA = 3
begin
SELECT DISTINCT
XXX.STR_XXX AS XXXXXX,
dbo.FNC_CONCATENA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK, @DESTINO) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXX,
dbo.FNC_DTA_XXXXX(XXX.LON_CODIGO_PK) AS XXXXXX
FROM XXXXXXXXXXXX XXX (NOLOCK)
INNER JOIN XXXXXXXXXXXXXXX XXXX (NOLOCK)
ON XXX.LON_CODIGO_TOC_FK = XXXX.LON_CODIGO_PK
WHERE XXXX.LON_CODIGO_XXXXX_FK in (select CODIGO from #FONTE)
AND XXXX.DTA_XXXXXXXXXX BETWEEN @DATAINICIO AND @DATAFINAL
AND XXX.STA_EXCLUIDO = 0
AND NOT XXX.LON_CODIGO_TPP_FK IN (4,8)
end
drop table #FONTE
return
end
In my JPA project I've been trying to call this procedure as follow and the lowDateTime works properly:
Description: Java code to call this procedure.
final Query query = this.entityManager.createNativeQuery("{call PRC_JBOSS_TAREFA (?, ?, ?, ?, ?)}");
query.setParameter(1, format.format(this.lowDateTime(dataInicio)) + " 00:00:00");
query.setParameter(2, format.format(this.lowDateTime(dataFinal)) + " 23:59:59");
query.setParameter(3, tipoData);
query.setParameter(4, destino.getCodigo());
query.setParameter(5, fonte.toString());
return query.getResultList();
Curiously yesterday this was working fine, withou a problem. The only thing I did in the mean time was
to shutdown my PC and start it today.
Below is the stacktrace log generated by JBoss:
Subtitle: A instrução não retornou um conjunto de resultados. -> Query did not return any result.
Caused by: javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: A instrução não retornou um conjunto de resultados.
at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:166) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:230) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:304) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:190) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ejb3.remote.EJBRemoteTransactionPropagatingInterceptor.processInvocation(EJBRemoteTransactionPropagatingInterceptor.java:80) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:59) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:32) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ee.component.TCCLInterceptor.processInvocation(TCCLInterceptor.java:45) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:165) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.as.ejb3.remote.LocalEjbReceiver.processInvocation(LocalEjbReceiver.java:179) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:179) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at org.jboss.ejb.client.TransactionInterceptor.handleInvocation(TransactionInterceptor.java:43) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:181) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at org.jboss.ejb.client.ReceiverInterceptor.handleInvocation(ReceiverInterceptor.java:128) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at org.jboss.ejb.client.EJBClientInvocationContext.sendRequest(EJBClientInvocationContext.java:181) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at org.jboss.ejb.client.EJBInvocationHandler.doInvoke(EJBInvocationHandler.java:136) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at org.jboss.ejb.client.EJBInvocationHandler.doInvoke(EJBInvocationHandler.java:121) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at org.jboss.ejb.client.EJBInvocationHandler.invoke(EJBInvocationHandler.java:104) [jboss-ejb-client-1.0.5.Final.jar:1.0.5.Final]
at com.sun.proxy.$Proxy67.getRelatorioQueryTarefasNorma(Unknown Source)
at br.com.XXX.XXXXX.controle.TaskController.getTarefas(TaskController.java:56) [classes:]
at br.com.XXX.XXXXX.controle.TaskController.initialize(TaskController.java:47) [classes:]
at br.com.XXX.XXXXX.controle.TaskController.atualizar(TaskController.java:40) [classes:]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_17]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_17]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_17]
at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_17]
at org.apache.el.parser.AstValue.invoke(AstValue.java:262) [jbossweb-7.0.13.Final.jar:]
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278) [jbossweb-7.0.13.Final.jar:]
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105) [jsf-impl-2.1.7-jbossorg-2.jar:]
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
... 22 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: A instrução não retornou um conjunto de resultados.
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:261) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
at br.com.XXX.api.persistencia.RelatoriosDao.getTarefasNorma(RelatoriosDao.java:96) [API_XXX_JBOSS_PERSISTENCIA.jar:]
at br.com.XXX.api.bean.DaoFactoryBean.getRelatorioQueryTarefasNorma(DaoFactoryBean.java:59) [API_XXX_JBOSS_PERSISTENCIA.jar:]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_17]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_17]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_17]
at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_17]
at org.jboss.as.ee.component.ManagedReferenceMethodInterceptorFactory$ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptorFactory.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:36) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:228) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
... 60 more
Caused by: org.hibernate.exception.GenericJDBCException: A instrução não retornou um conjunto de resultados.
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:52) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at com.sun.proxy.$Proxy69.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1978) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:829) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2463) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2449) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.loader.Loader.list(Loader.java:2274) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:331) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1585) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:224) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:156) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:252) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
... 81 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: A instrução não retornou um conjunto de resultados.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:408)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(XXXuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source) [:1.7.0_17]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_17]
at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_17]
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
... 95 more
Thank you again for your help!