Quantcast

Possible bug when returning a result set from a named StoredProcedureQuery?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Possible bug when returning a result set from a named StoredProcedureQuery?

fast-reflexes
Hi!

I am using EclipseLink 2.6.2 together with a MySQL 5.5.24 and I have run into a problem that I find very peculiar...

I have this stored procedure defined in the database:

CREATE PROCEDURE sp.getStates() BEGIN SELECT * FROM states; END;

Calling it in the database gets me the proper result with 22 entries. In JPA I have defined this named stored procedure in XML:

<named-stored-procedure-query name="sp.p_getStates" procedure-name="sp.getStates" result-class="bean.State" returns-result-set="true" />

Now, when using this from within a servlet, I am successful when doing either of these two things:

(EntityManager em, fully working in all other contexts)

TypedQuery<State> sq1 = em.createNamedQuery("sp.p_getStates", State.class);
List<State> sl1 = sq1.getResultSet();

StoredProcedureQuery sq2 = em.createStoredProcedureQuery("sp.getStates", State.class);
List<State> sl2 = sq2.getResultSet();


However, when executing the following:

StoredProcedureQuery sq3 = em.createNamedStoredProcedureQuery("sp.p_getStates");
List<State> sl3 = sq3.getResultSet();


... I get the following error and stack trace:

feb 01, 2016 2:11:50 EM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [EditProfileController] in context with path [/sp] threw exception
java.lang.IllegalStateException: You cannot call getResultList() on this stored procedure query since it does not return a result set.
        at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.getResultList(StoredProcedureQueryImpl.java:544)
        at servlet.EditProfileController.doGet(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at filter.CacheControlFilter.doFilter(Unknown Source)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1739)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1698)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)

       
... if I on the other hand try to use executeUpdate() instead (of course making sure that I'm in a transaction), I end up with an error that signals that I am indeed looking at a result set and using executeUpdate() is wrong to use (should be used for non-result-set queries).

feb 01, 2016 3:12:16 EM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [EditProfileController] in context with path [/sp] threw exception
java.lang.IllegalStateException: You cannot call executeUpdate() on this stored procedure query since it returns a result set and not only an update count.
        at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.executeUpdate(StoredProcedureQueryImpl.java:377)
        at servlet.EditProfileController.doGet(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at filter.CacheControlFilter.doFilter(Unknown Source)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1739)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1698)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)

       
I am fine using the alternatives, but I suspect this is somehow a bug in EclipseLink? I had a look at the implementation and both executeUpdate() and getResultList() throws an exception if DatabaseQuery object returns false from isResultSetMapping() and this method always returns false. I suspect however, that since the other queries manage to deliver a result set (when using the XML data either as a regular named query or when issuing the query inline), either another query class is used for these or isResultSetMapping() must sometimes be able to return tru somehow (overridden?).
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Possible bug when returning a result set from a named StoredProcedureQuery?

Peter Ondruška-5
You are not returning any result from procedure. Google EclipseLink stored function will give you hints.

On 1 February 2016 at 17:14, fast-reflexes <[hidden email]> wrote:
Hi!

I am using EclipseLink 2.6.2 together with a MySQL 5.5.24 and I have run
into a problem that I find very peculiar...

I have this stored procedure defined in the database:

*CREATE PROCEDURE sp.getStates() BEGIN SELECT * FROM states; END;*

Calling it in the database gets me the proper result with 22 entries. In JPA
I have defined this named stored procedure in XML:

*<named-stored-procedure-query name="sp.p_getStates"
procedure-name="sp.getStates" result-class="bean.State"
returns-result-set="true" />*

Now, when using this from within a servlet, I am successful when doing
either of these two things:

/(EntityManager em, fully working in all other contexts)/

*TypedQuery<State> sq1 = em.createNamedQuery("sp.p_getStates", State.class);
List<State> sl1 = sq1.getResultSet();

StoredProcedureQuery sq2 = em.createStoredProcedureQuery("sp.getStates",
State.class);
List<State> sl2 = sq2.getResultSet();*

However, when executing the following:

*StoredProcedureQuery sq3 =
em.createNamedStoredProcedureQuery("sp.p_getStates");
List<State> sl3 = sq3.getResultSet();*

... I get the following error and stack trace:

*feb 01, 2016 2:11:50 EM org.apache.catalina.core.StandardWrapperValve
invoke
SEVERE: Servlet.service() for servlet [EditProfileController] in context
with path [/sp] threw exception
java.lang.IllegalStateException: You cannot call getResultList() on this
stored procedure query since it does not return a result set.
        at
org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.getResultList(StoredProcedureQueryImpl.java:544)
        at servlet.EditProfileController.doGet(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at filter.CacheControlFilter.doFilter(Unknown Source)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
        at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
        at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
        at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
        at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
        at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1739)
        at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1698)
        at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)*

... if I on the other hand try to use executeUpdate() instead (of course
making sure that I'm in a transaction), I end up with an error that signals
that I am indeed looking at a result set and using executeUpdate() is wrong
to use (should be used for non-result-set queries).

*feb 01, 2016 3:12:16 EM org.apache.catalina.core.StandardWrapperValve
invoke
SEVERE: Servlet.service() for servlet [EditProfileController] in context
with path [/sp] threw exception
java.lang.IllegalStateException: You cannot call executeUpdate() on this
stored procedure query since it returns a result set and not only an update
count.
        at
org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.executeUpdate(StoredProcedureQueryImpl.java:377)
        at servlet.EditProfileController.doGet(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at filter.CacheControlFilter.doFilter(Unknown Source)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
        at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
        at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
        at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
        at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
        at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1739)
        at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1698)
        at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)*

I am fine using the alternatives, but I suspect this is somehow a bug in
EclipseLink? I had a look at the implementation and both executeUpdate() and
getResultList() throws an exception if DatabaseQuery object returns false
from isResultSetMapping() and this method always returns false. I suspect
however, that since the other queries manage to deliver a result set (when
using the XML data either as a regular named query or when issuing the query
inline), either another query class is used for these or
isResultSetMapping() must sometimes be able to return tru somehow
(overridden?).



--
View this message in context: http://eclipse.1072660.n5.nabble.com/Possible-bug-when-returning-a-result-set-from-a-named-StoredProcedureQuery-tp179302.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


kaibo, s.r.o., ID 28435036, registered with the commercial register administered by the Municipal Court in Prague, Section C, insert 141269
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, Prague 3, 130 00, Czech Republic
https://www.kaibo.eu

_______________________________________________
eclipselink-users mailing list
[hidden email]
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Possible bug when returning a result set from a named StoredProcedureQuery?

fast-reflexes
How do you mean? It does return a result set and it works when using the first two described methods. I don't intend to use a stored function but a stored procedure and MySQL supports returning result sets from stored procedures (no return statement required).
Loading...