Skip to main content

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

  • From: Steve Ebersole <steve.ebersole@...>
  • To: jsr338-experts@...
  • Cc: Kevin Sutter <sutter@...>
  • Subject: [jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification
  • Date: Sun, 11 Aug 2013 22:05:16 -0500
  • List-id: <jsr338-experts.jpa-spec.java.net>

Well to be honest, I think my confusion comes from the fact that (in retrospect) we were not very clear in the spec about the expectations for usage of this API in the mixed case. IMO we described the granular pieces of the API, but not the overall expectation.

That and I made the mistake of trying to use the TCK as a guide for interpreting the intent/expectation ;)

Since the API was explicitly modeled after the JDBC API for accessing procedures, I went to the JDBC spec to try and get clarification. They did a great job in the JDBC spec of setting the usage expectation by inclusion of a simple example (CODE EXAMPLE 13-27, Section 13.3.3.3):

CallableStatement cs = ...;
boolean isResults = cs.execute();
ResultSet rs;
int count;
do {
    if ( !isResults ) {
        count = cs.getUpdateCount();
        if (count == -1) {
            // no more results
            break;
        }
        else {
            // process update count
        }
    }
    else {
        rs = cs.getResultSet();
        // process ResultSet
    }
    isResults = cs.getMoreResults();
} while (true)


So in JDBC, the "trigger event" to move on to the "next return" is a call to either:
1) execute() - only valid initially; moves to the first return
2) hasMoreResults() - only valid after initial execute() call.

The use of the phrase "return" is tricky here. It means either a result (ResultSet) or an update-count. Both JDBC and JPA are both allowing for inter-mixed returns, meaning, both report to allow (for example) the first "return" to be a ResultSet, the second to be a updateCount, the third to be a ResultSet, the fourth to be a updateCount, etc... At some point we exhaust all "returns", whereupon getMoreResults() returns false and getUpdateCount() returns -1.

In JDBC it is very clear that getMoreResults goes to the next "return" and returns a value indicating whether that "return" is a ResultSet or an updateCount. Calling getResultSet() or getUpdateCount() is referring to the current "return" (as positioned by the previous call to either execute() or getMoreResults()). Since the whole idea to reuse the JDBC API in the JPA storced procedure API was consistency and familiarity, I don't see how we can then argue that it should not operate like the JDBC API.

So I actually now read the JPA spec in a similar light:

StoredProcedureQuery q = ...;
boolean isResults = q.execute();
do {
    if ( isResults ) {
        processResults( q.getResultList() );
    }
    else {
        int updateCount = q.getUpdateCount();
        if ( updateCount <= -1 ) {
            break;
        }
        else {
            processUpdateCount( updateCount );
        }
    }

    isResults = q.hasMoreResults();
} while (true)




On Tue 06 Aug 2013 03:34:28 PM CDT, Kevin Sutter wrote:

Hi Linda,
So, here's my follow-up... :-) Then, why does the javadoc for
getUpdateCount allow the throwing of QueryTimeoutException and/or
PersistenceException as they relate to the execution of the stored
procedure? I think this is where Steve's inconsistency question is
coming from.

-- Kevin

Linda DeMichiel <linda.demichiel@...> wrote on 08/06/2013
03:10:15 PM:

> From: Linda DeMichiel <linda.demichiel@...>
> To: jsr338-experts@...,
> Date: 08/06/2013 03:11 PM
> Subject: [jsr338-experts] Re: StoredProcedure clarification
>
> Hi Kevin,
>
> getUpdateCount should not implicitly execute the query.
>
> -Linda
>
>
> On 8/6/2013 1:06 PM, Kevin Sutter wrote:
> > Linda,
> > Now I'm confused between your two responses on this topic...
> Should getUpdateCount implicitly execute the prepared
> > statement (similar to getSingleResult and getResultList)? Or,
> should the user be required to call execute() before
> > calling getUpdateCount()? Depending on your answer, I may have
> follow observations... :-) Thanks.
> >
> > -- Kevin
> >
> >
> > Linda DeMichiel <linda.demichiel@...> wrote on 08/06/2013
> 02:48:00 PM:
> >
> > > From: Linda DeMichiel <linda.demichiel@...>
> > > To: jsr338-experts@...,
> > > Date: 08/06/2013 02:49 PM
> > > Subject: [jsr338-experts] Re: StoredProcedure clarification
> > >
> > > Gordon is right here, so please excuse my jet-lagged response.
> > > I had misread getUpdateCount for executeUpdate (which should have
> > > been called).
> > >
> > > -Linda
> > >
> > >
> > > On 8/6/2013 12:31 PM, gordon yorke wrote:
> > > > The practical thing to do for users in this case is to
execute the
> > > stored procedure but the specification does not
> > > > define what should happen if getUpdateCount() is called on an
> > > unexecuted StoredProcedureQuery.
> > > > --Gordon
> > > >
> > > > On 26/07/2013 7:16 PM, Steve Ebersole wrote:
> > > >> EntityManager em = ...;
> > > >> StoredProcedureQuery query = em.createStoredProcedureQuery(...);
> > > >> query.getUpdateCount();
> > > >>
> > > >> What should happen above when getUpdateCount() is called? The
> > > "result" corollaries (getResultList, getSingleResult)
> > > >> specifically say that the query will be executed when they are
> > > called. getUpdateCount conspicuously says nothing on
> > > >> the subject. Nor does it call out being allowed to throw an
> > > exception in this case. So should I assume that
> > > >> getUpdateCount() should just return -1 here?
> > > >>
> > > >> Yes, its a silly case, but the TCK does it. Many times...
> > > >>
> > > >>
> > > >
> > >
>


[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

Steve Ebersole 08/02/2013

<Possible follow-up(s)>

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

gordon yorke 08/06/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

Linda DeMichiel 08/06/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

Kevin Sutter 08/06/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

Linda DeMichiel 08/06/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

Kevin Sutter 08/06/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

Steve Ebersole 08/12/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

Linda DeMichiel 08/06/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

gordon yorke 08/06/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedure clarification

gordon yorke 08/06/2013
 
 
Close
loading
Please Confirm
Close