OpenJPA
  1. OpenJPA
  2. OPENJPA-918

Stored procedures not handling returns properly

    Details

    • Type: New Feature New Feature
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-M2
    • Fix Version/s: 2.0.0-M3
    • Component/s: query
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.

      1. OPENJPA-918-test.patch
        33 kB
        B.J. Reed
      2. TORC.patch
        4 kB
        B.J. Reed

        Activity

        Hide
        B.J. Reed added a comment -

        After futher testing...

        It appears that procedures that return no data work properly when called by:

        Query query = em.createNativeQuery(sql);
        query.executeUpdate();

        Procedures that return one ResultSet of data also seem to work properly when the kind of object is defined when creating the Query.

        Query query = em.createNativeQuery(sql, Applicant.class);
        List<Applicant> appList = query.getResultList();

        (query.getSingleResult() works similar to getResultList())

        However, stored procedures also have the ability to return multiple types of objects in separate ResultSet of data. When trying to get a ResultList(), I use

        Query query = em.createNativeQuery(sql);
        List<Applicant> appList = query.getResultList();

        and get:

        Statement.executeQuery() cannot be called with a statement that returns a row count. Odd message since a row count is not being returned, but multiple ResultSets

        Show
        B.J. Reed added a comment - After futher testing... It appears that procedures that return no data work properly when called by: Query query = em.createNativeQuery(sql); query.executeUpdate(); Procedures that return one ResultSet of data also seem to work properly when the kind of object is defined when creating the Query. Query query = em.createNativeQuery(sql, Applicant.class); List<Applicant> appList = query.getResultList(); (query.getSingleResult() works similar to getResultList()) However, stored procedures also have the ability to return multiple types of objects in separate ResultSet of data. When trying to get a ResultList(), I use Query query = em.createNativeQuery(sql); List<Applicant> appList = query.getResultList(); and get: Statement.executeQuery() cannot be called with a statement that returns a row count. Odd message since a row count is not being returned, but multiple ResultSets
        Hide
        Craig L Russell added a comment -

        > However, stored procedures also have the ability to return multiple types of objects in separate ResultSet of data.

        This use case is not covered by the specification. The only cases covered are getResultList() and getSingleResult() that expect the query to return a ResultSet, and executeUpdate(), that expects an int returned from the query.

        So, getResultList() and getSingleResult() are mapped to a Statement.executeQuery(), and executeUpdate() is mapped to executeUpdate().

        > When trying to get a ResultList(), I use

        > Query query = em.createNativeQuery(sql);
        > List<Applicant> appList = query.getResultList();

        > and get:

        > Statement.executeQuery() cannot be called with a statement that returns a row count. Odd message since a row count is not being returned, but multiple ResultSets

        Considering that the error description for Statement.executeQuery() says "if a database access error occurs; this method is called on a closed PreparedStatement or the SQL statement does not return a ResultSet object", the error message might better read "Statement.executeQuery() cannot be called with a statement that does not return a ResultSet object".

        But in any case, I don't see how to handle a native query that returns multiple result sets without changing the specification to deal with it.

        Show
        Craig L Russell added a comment - > However, stored procedures also have the ability to return multiple types of objects in separate ResultSet of data. This use case is not covered by the specification. The only cases covered are getResultList() and getSingleResult() that expect the query to return a ResultSet, and executeUpdate(), that expects an int returned from the query. So, getResultList() and getSingleResult() are mapped to a Statement.executeQuery(), and executeUpdate() is mapped to executeUpdate(). > When trying to get a ResultList(), I use > Query query = em.createNativeQuery(sql); > List<Applicant> appList = query.getResultList(); > and get: > Statement.executeQuery() cannot be called with a statement that returns a row count. Odd message since a row count is not being returned, but multiple ResultSets Considering that the error description for Statement.executeQuery() says "if a database access error occurs; this method is called on a closed PreparedStatement or the SQL statement does not return a ResultSet object", the error message might better read "Statement.executeQuery() cannot be called with a statement that does not return a ResultSet object". But in any case, I don't see how to handle a native query that returns multiple result sets without changing the specification to deal with it.
        Hide
        B.J. Reed added a comment -

        Thanks Craig for the info. I'll remove the last two test cases and add other DB support and resubmit the patch to have better test case coverage.

        Show
        B.J. Reed added a comment - Thanks Craig for the info. I'll remove the last two test cases and add other DB support and resubmit the patch to have better test case coverage.
        Hide
        B.J. Reed added a comment -

        I've narrowed the problem down a little further. Not sure if this is something that OpenJPA is supposed to (or planning on) support or not...

        I have attached TestOracleRefCursor.java as TORC.patch

        The Oracle function is a function that returns a SYS_REFCURSOR

        create or replace function selectAllApplicants
        return SYS_REFCURSOR
        as
        cuApplicants SYS_REFCURSOR;
        begin
        open cuApplicants for
        select id, name from applicant;
        return cuApplicants;
        end selectAllApplicants;
        /

        When I get the ResultList from the NativeQuery, I get an exception:
        Missing IN or OUT parameter at index:: 1 {callstmnt 32580443 { ? = call selectAllApplicants() }} [code=17041, state=null]

        When I try the NativeQuery without the ? = , I get an exception that the procedure doesn't exist.

        Show
        B.J. Reed added a comment - I've narrowed the problem down a little further. Not sure if this is something that OpenJPA is supposed to (or planning on) support or not... I have attached TestOracleRefCursor.java as TORC.patch The Oracle function is a function that returns a SYS_REFCURSOR create or replace function selectAllApplicants return SYS_REFCURSOR as cuApplicants SYS_REFCURSOR; begin open cuApplicants for select id, name from applicant; return cuApplicants; end selectAllApplicants; / When I get the ResultList from the NativeQuery, I get an exception: Missing IN or OUT parameter at index:: 1 {callstmnt 32580443 { ? = call selectAllApplicants() }} [code=17041, state=null] When I try the NativeQuery without the ? = , I get an exception that the procedure doesn't exist.

          People

          • Assignee:
            B.J. Reed
            Reporter:
            B.J. Reed
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development