Read Multiple Resultset from Stored Proc call using Open JPA

[chintan4181 <ch...@gmail.com>]

Hi, 

I am using openJPA 2.0 and calling SQL sever stored procedure using
CreateNativeQuery. My stored proc is returning multiple resultset. When i
use Query.getResultList(), it returns me only first result set. rest of the
result sets i can not see. 

Is there a way to get the multiple result set return by Stored procedure
from OpenJPA api? 

Your help will be highly appreicated. 

Thanks 
Chintan 

--
View this message in context: http://openjpa.208410.n2.nabble.com/Read-Multiple-Resultset-from-Stored-Proc-call-using-Open-JPA-tp6223507p6223507.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Read Multiple Resultset from Stored Proc call using Open JPA

[Michael Dick <mi...@gmail.com>]

You're welcome.

I belatedly found that we cover getting a connection manually in the user
manual too :
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_dbsetup_sqlconn
.

-mike

On Thu, Mar 31, 2011 at 6:43 PM, chintan4181 <ch...@gmail.com> wrote:

> thanks Michale. Got you!!
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Read-Multiple-Resultset-from-Stored-Proc-call-using-Open-JPA-tp6223507p6228982.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

Re: Read Multiple Resultset from Stored Proc call using Open JPA

[chintan4181 <ch...@gmail.com>]

thanks Michale. Got you!!

--
View this message in context: http://openjpa.208410.n2.nabble.com/Read-Multiple-Resultset-from-Stored-Proc-call-using-Open-JPA-tp6223507p6228982.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Read Multiple Resultset from Stored Proc call using Open JPA

[Michael Dick <mi...@gmail.com>]

No problem - we were all new once.

I probably shouldn't have suggested using the same connection as the
EntityManager. Getting the connection from an EntityManager instance is kind
of hack and it will tie you to a specific JPA vendor (OpenJPA in your case).
Generally a better solution when you're running in a JEE container is to
obtain a connection from a managed datasource.

Say you have a persistence.xml like this :

<persistence-unit name="foo">
     . . .
     <jta-data-source>jdbc/myDataSource</jta-data-source>
      . . .
</persistence-unit>

Then in your application where you have the persistence context you would do
something like this :

InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/myDataSource");  // same name
as persistence.xml
Connection con = ds.getConnection();
con.prepareCall(. . . );
con.close();

The reason this works is because most JEE containers will attempt to share a
single connection within a JTA transaction. So the EntityManager you
injected, and the connection from the DataSource will _usually_ be the same.
There are some conditions where the container can't share a connection (e.g.
if the isolation level has been changed), and each application server may
handle these cases slightly differently.

If you still want to use the same connection as the EntityManager there are
instructions on this thread: http://markmail.org/message/pwoare2l76rqonws

Best Regards,

-mike


On Thu, Mar 31, 2011 at 2:59 PM, chintan4181 <ch...@gmail.com> wrote:

> Thnak Michale.
>
> When you say Manually, you mean to get the connection from EntityManager
> and
> use callableStatement?
> If yes, can you show me how to get connection from EntityManagerFactory?
>
> I am using @persistentContext and get the EntityManager out of it. I don't
> see any method for getting JDBC conenction.
>
> I am new to JPA so sorry for the simple question.
>
> Thanks
> Chintan
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Read-Multiple-Resultset-from-Stored-Proc-call-using-Open-JPA-tp6223507p6228576.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

Re: Read Multiple Resultset from Stored Proc call using Open JPA

[chintan4181 <ch...@gmail.com>]

Thnak Michale.

When you say Manually, you mean to get the connection from EntityManager and
use callableStatement?
If yes, can you show me how to get connection from EntityManagerFactory?

I am using @persistentContext and get the EntityManager out of it. I don't
see any method for getting JDBC conenction.

I am new to JPA so sorry for the simple question.

Thanks
Chintan


--
View this message in context: http://openjpa.208410.n2.nabble.com/Read-Multiple-Resultset-from-Stored-Proc-call-using-Open-JPA-tp6223507p6228576.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Read Multiple Resultset from Stored Proc call using Open JPA

[Michael Dick <mi...@gmail.com>]

Hi Chintan,

This has come up before and the net was that we could not support multiple
result sets without some help (or changes) from the specification.

There's a bit more information in
OPENJPA-918<https://issues.apache.org/jira/browse/OPENJPA-918>,
but unfortunately right now you're best advice is to run the stored
procedure manually.

Some people have had luck by obtaining the connection used by the
EntityManager and executing the stored procedure there (in particular take a
look at this thread<http://openjpa.208410.n2.nabble.com/Open-JPA-Persistence-td6224283.html>from
earlier today).

Hope this helps
-mike


On Wed, Mar 30, 2011 at 10:12 AM, chintan4181 <ch...@gmail.com> wrote:

> Hi,
>
> I am using openJPA 2.0 and calling SQL sever stored procedure using
> CreateNativeQuery. My stored proc is returning multiple resultset. When i
> use Query.getResultList(), it returns me only first result set. rest of the
> result sets i can not see.
>
> Is there a way to get the multiple result set return by Stored procedure
> from OpenJPA api?
>
> Your help will be highly appreicated.
>
> Thanks
> Chintan
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Read-Multiple-Resultset-from-Stored-Proc-call-using-Open-JPA-tp6223507p6223507.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>