You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "B.J. Reed (JIRA)" <ji...@apache.org> on 2009/02/10 16:21:49 UTC
[jira] Created: (OPENJPA-918) Stored procedures not handling
returns properly
Stored procedures not handling returns properly
-----------------------------------------------
Key: OPENJPA-918
URL: https://issues.apache.org/jira/browse/OPENJPA-918
Project: OpenJPA
Issue Type: New Feature
Components: query
Affects Versions: 2.0.0
Reporter: B.J. Reed
Assignee: B.J. Reed
Fix For: 2.0.0
Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-918) Stored procedures not handling
returns properly
Posted by "B.J. Reed (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
B.J. Reed updated OPENJPA-918:
------------------------------
Attachment: TORC.patch
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.
> Stored procedures not handling returns properly
> -----------------------------------------------
>
> Key: OPENJPA-918
> URL: https://issues.apache.org/jira/browse/OPENJPA-918
> Project: OpenJPA
> Issue Type: New Feature
> Components: query
> Affects Versions: 2.0.0
> Reporter: B.J. Reed
> Assignee: B.J. Reed
> Fix For: 2.0.0
>
> Attachments: OPENJPA-918-test.patch, TORC.patch
>
>
> Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-918) Stored procedures not handling
returns properly
Posted by "Tim McConnell (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tim McConnell updated OPENJPA-918:
----------------------------------
Patch Info: [Patch Available]
> Stored procedures not handling returns properly
> -----------------------------------------------
>
> Key: OPENJPA-918
> URL: https://issues.apache.org/jira/browse/OPENJPA-918
> Project: OpenJPA
> Issue Type: New Feature
> Components: query
> Affects Versions: 2.0.0
> Reporter: B.J. Reed
> Assignee: B.J. Reed
> Fix For: 2.0.0
>
> Attachments: OPENJPA-918-test.patch, TORC.patch
>
>
> Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-918) Stored procedures not handling
returns properly
Posted by "Craig Russell (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12674069#action_12674069 ]
Craig Russell commented on OPENJPA-918:
---------------------------------------
> 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.
> Stored procedures not handling returns properly
> -----------------------------------------------
>
> Key: OPENJPA-918
> URL: https://issues.apache.org/jira/browse/OPENJPA-918
> Project: OpenJPA
> Issue Type: New Feature
> Components: query
> Affects Versions: 2.0.0
> Reporter: B.J. Reed
> Assignee: B.J. Reed
> Fix For: 2.0.0
>
> Attachments: OPENJPA-918-test.patch
>
>
> Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (OPENJPA-918) Stored procedures not handling
returns properly
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick resolved OPENJPA-918.
----------------------------------
Resolution: Fixed
> Stored procedures not handling returns properly
> -----------------------------------------------
>
> Key: OPENJPA-918
> URL: https://issues.apache.org/jira/browse/OPENJPA-918
> Project: OpenJPA
> Issue Type: New Feature
> Components: query
> Affects Versions: 2.0.0-M2
> Reporter: B.J. Reed
> Assignee: B.J. Reed
> Fix For: 2.0.0
>
> Attachments: OPENJPA-918-test.patch, TORC.patch
>
>
> Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-918) Stored procedures not handling
returns properly
Posted by "B.J. Reed (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
B.J. Reed updated OPENJPA-918:
------------------------------
Attachment: OPENJPA-918-test.patch
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
> Stored procedures not handling returns properly
> -----------------------------------------------
>
> Key: OPENJPA-918
> URL: https://issues.apache.org/jira/browse/OPENJPA-918
> Project: OpenJPA
> Issue Type: New Feature
> Components: query
> Affects Versions: 2.0.0
> Reporter: B.J. Reed
> Assignee: B.J. Reed
> Fix For: 2.0.0
>
> Attachments: OPENJPA-918-test.patch
>
>
> Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-918) Stored procedures not handling
returns properly
Posted by "B.J. Reed (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12674218#action_12674218 ]
B.J. Reed commented on OPENJPA-918:
-----------------------------------
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.
> Stored procedures not handling returns properly
> -----------------------------------------------
>
> Key: OPENJPA-918
> URL: https://issues.apache.org/jira/browse/OPENJPA-918
> Project: OpenJPA
> Issue Type: New Feature
> Components: query
> Affects Versions: 2.0.0
> Reporter: B.J. Reed
> Assignee: B.J. Reed
> Fix For: 2.0.0
>
> Attachments: OPENJPA-918-test.patch
>
>
> Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-918) Stored procedures not handling
returns properly
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-918:
---------------------------------
Fix Version/s: (was: 2.0.0)
2.0.0-M3
> Stored procedures not handling returns properly
> -----------------------------------------------
>
> Key: OPENJPA-918
> URL: https://issues.apache.org/jira/browse/OPENJPA-918
> Project: OpenJPA
> Issue Type: New Feature
> Components: query
> Affects Versions: 2.0.0-M2
> Reporter: B.J. Reed
> Assignee: B.J. Reed
> Fix For: 2.0.0-M3
>
> Attachments: OPENJPA-918-test.patch, TORC.patch
>
>
> Stored procedures with output parameters are not being handled properly. Patch will be attached shortly with test cases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.