You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by "mr.abanjo" <mr...@gmail.com> on 2013/09/09 15:03:40 UTC
Cayenne with Postgress : how to get data from cursor
Hi all,
we need to switch the database from Oracle to Postgres.
Our implementation use Cayenne to map a store procedure that return single
type data and a cursor.
With single type data ( number, varchar .... ) there's no problem. All
works fine.
The problem happens when we try to get data from a cursor. We mapped it in
data-map xml file as "OTHER". When we execute it the cursor is empty ( but
it must have a list of elements ).
We suppose that the "missing" part for Postgress is this one :
Procedure proc = getProcedure();
Iterator it = proc.getCallOutParameters().iterator();
while (it.hasNext()) {
ProcedureParameter param = (ProcedureParameter) it.next();
if (param.getType() == Types.OTHER) {
param.setType(OracleAdapter.getOracleCursorType());
}
}
With Oracle we set the OTHER parameter type as "OracleCursorType". There is
a corresponding type for Postgress?
Maybe this is the problem, maybe no ... someone have any idea?
Thanks!
Re: Cayenne with Postgress : how to get data from cursor
Posted by Andrus Adamchik <an...@objectstyle.org>.
Also, Cayenne already has a few PostgreSQL functions included in the test suite:
http://svn.apache.org/repos/asf/cayenne/main/trunk/framework/cayenne-core-unpublished/src/test/resources/ddl/postgresql/
If you can build a test case based on the existing Cayenne test entities demonstrating the problem, maybe we can do something about it.
Andrus
On Sep 9, 2013, at 9:22 PM, Andrus Adamchik <an...@objectstyle.org> wrote:
> While I don't have much recent production experience with PostgreSQL, I recently did some research for a stackoverflow answer [1] (maybe that was your question?). My overall experience was that Cayenne (and PostgreSQL JDBC driver) would support pretty much any kind of PG function. Although coding those functions is somewhat arcane (to me at least), I never had to resort to any Oracle-like hacks.
>
> Sorry if this is not very specific. Maybe you can post the example of your PostgreSQL function, and someone will have a better idea.
>
> Andrus
>
> [1] http://stackoverflow.com/questions/16921942/porting-apache-cayenne-from-oracle-to-postgresql
>
> On Sep 9, 2013, at 4:03 PM, mr.abanjo <mr...@gmail.com> wrote:
>
>> Hi all,
>> we need to switch the database from Oracle to Postgres.
>> Our implementation use Cayenne to map a store procedure that return single
>> type data and a cursor.
>> With single type data ( number, varchar .... ) there's no problem. All
>> works fine.
>> The problem happens when we try to get data from a cursor. We mapped it in
>> data-map xml file as "OTHER". When we execute it the cursor is empty ( but
>> it must have a list of elements ).
>>
>> We suppose that the "missing" part for Postgress is this one :
>>
>> Procedure proc = getProcedure();
>> Iterator it = proc.getCallOutParameters().iterator();
>> while (it.hasNext()) {
>> ProcedureParameter param = (ProcedureParameter) it.next();
>> if (param.getType() == Types.OTHER) {
>> param.setType(OracleAdapter.getOracleCursorType());
>> }
>> }
>>
>> With Oracle we set the OTHER parameter type as "OracleCursorType". There is
>> a corresponding type for Postgress?
>> Maybe this is the problem, maybe no ... someone have any idea?
>>
>> Thanks!
>
>
Re: Cayenne with Postgress : how to get data from cursor
Posted by Andrus Adamchik <an...@objectstyle.org>.
Can't say offhand why the conversion doesn't happen, but appreciate if you could Jira that with an example stored procedure that we could use in our unit tests. I am planning to look at a distantly related issue soon (https://issues.apache.org/jira/browse/CAY-1874), so would be cool if I could test yours as well.
Andrus
On Sep 23, 2013, at 12:16 PM, Pirola Davide <da...@italiaonline.it> wrote:
> Hi Andrus,
> this is a little bit strange because with Oracle ( same code ) after store procedure execution we have a list of DataRow instead of JDBC Resultset.
> At the moment we have introduced a difference specific for Postgress in order to transform the data from resultset in a List of DataRow, because our astraction layer need to pass to the calling java object this kind of data. Then we do a things like this :
>
> Datacontext.objectFromDataRow( ... );
>
> So, with Oracle seems that cayenne already transform the Resultset in List of Datarow, with Postgress this not happen.
> Do you have any explanation about this?
>
> Thanks
> Davide
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: lunedì 23 settembre 2013 10.53
> To: Mattaboni Francesco
> Cc: user@cayenne.apache.org; Pirola Davide
> Subject: Re: Cayenne with Postgress : how to get data from cursor
>
> AFAIK ref cursors can only be accessed as ResultSets. ResultSet is essentially a JDBC abstraction of a cursor.
>
> Andrus
>
> On Sep 20, 2013, at 3:34 PM, Mattaboni Francesco <fr...@italiaonline.it> wrote:
>
>> Thanks for the answer!
>> However our problem is a little different.
>> In stackoverflow example the result is following DataRow:
>>
>> {o_rc_source=org.postgresql.jdbc4.Jdbc4ResultSet@4bd27069,
>> o_s_exitmsg=Ok,
>> o_n_exitflag=11}
>>
>> and refcursor is accessible as a ResultSet.
>> With the statement "param.setType(OracleAdapter.getOracleCursorType())" we set the OTHER parameter type as "OracleCursorType" and what we get is a cursor that is already mapped to a list of DataRow.
>> Then we access the cursor directly as a collection of Cayenne DataRow objects and not as a ResultSet.
>>
>> Can we get the same behavior with Postgres or refcursors are accessible only as a ResultSet?
>>
>> Thanks in advance.
>>
>> Francesco
>>
>>
>> -----Original Message-----
>> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
>> Sent: lunedì 9 settembre 2013 20.23
>> To: user@cayenne.apache.org
>> Cc: Mattaboni Francesco
>> Subject: Re: Cayenne with Postgress : how to get data from cursor
>>
>> While I don't have much recent production experience with PostgreSQL, I recently did some research for a stackoverflow answer [1] (maybe that was your question?). My overall experience was that Cayenne (and PostgreSQL JDBC driver) would support pretty much any kind of PG function. Although coding those functions is somewhat arcane (to me at least), I never had to resort to any Oracle-like hacks.
>>
>> Sorry if this is not very specific. Maybe you can post the example of your PostgreSQL function, and someone will have a better idea.
>>
>> Andrus
>>
>> [1]
>> http://stackoverflow.com/questions/16921942/porting-apache-cayenne-fro
>> m-oracle-to-postgresql
>>
>> On Sep 9, 2013, at 4:03 PM, mr.abanjo <mr...@gmail.com> wrote:
>>
>>> Hi all,
>>> we need to switch the database from Oracle to Postgres.
>>> Our implementation use Cayenne to map a store procedure that return
>>> single type data and a cursor.
>>> With single type data ( number, varchar .... ) there's no problem.
>>> All works fine.
>>> The problem happens when we try to get data from a cursor. We mapped
>>> it in data-map xml file as "OTHER". When we execute it the cursor is
>>> empty ( but it must have a list of elements ).
>>>
>>> We suppose that the "missing" part for Postgress is this one :
>>>
>>> Procedure proc = getProcedure();
>>> Iterator it = proc.getCallOutParameters().iterator();
>>> while (it.hasNext()) {
>>> ProcedureParameter param = (ProcedureParameter) it.next();
>>> if (param.getType() == Types.OTHER) {
>>> param.setType(OracleAdapter.getOracleCursorType());
>>> }
>>> }
>>>
>>> With Oracle we set the OTHER parameter type as "OracleCursorType".
>>> There is a corresponding type for Postgress?
>>> Maybe this is the problem, maybe no ... someone have any idea?
>>>
>>> Thanks!
>>
>>
>> ________________________________
>>
>> Le informazioni contenute nella presente e-mail potrebbero essere confidenziali e sono dirette unicamente ai destinatari sopra indicati. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chi riceva questo messaggio per errore è pregato di inoltrarlo al mittente e di distruggere questa e-mail.
>>
>> This e-mail may contain confidential information and is intended only for the use of the addressee(s) named above. If the reader of this message is not the intended recipient of this message, please note that distribution or copying of this communication is forbidden. Anyone who receives this communication in error should return it immediately to the sender and destroy the message.
>>
>
>
> ________________________________
>
> Le informazioni contenute nella presente e-mail potrebbero essere confidenziali e sono dirette unicamente ai destinatari sopra indicati. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chi riceva questo messaggio per errore è pregato di inoltrarlo al mittente e di distruggere questa e-mail.
>
> This e-mail may contain confidential information and is intended only for the use of the addressee(s) named above. If the reader of this message is not the intended recipient of this message, please note that distribution or copying of this communication is forbidden. Anyone who receives this communication in error should return it immediately to the sender and destroy the message.
>
RE: Cayenne with Postgress : how to get data from cursor
Posted by Pirola Davide <da...@italiaonline.it>.
Hi Andrus,
this is a little bit strange because with Oracle ( same code ) after store procedure execution we have a list of DataRow instead of JDBC Resultset.
At the moment we have introduced a difference specific for Postgress in order to transform the data from resultset in a List of DataRow, because our astraction layer need to pass to the calling java object this kind of data. Then we do a things like this :
Datacontext.objectFromDataRow( ... );
So, with Oracle seems that cayenne already transform the Resultset in List of Datarow, with Postgress this not happen.
Do you have any explanation about this?
Thanks
Davide
-----Original Message-----
From: Andrus Adamchik [mailto:andrus@objectstyle.org]
Sent: lunedì 23 settembre 2013 10.53
To: Mattaboni Francesco
Cc: user@cayenne.apache.org; Pirola Davide
Subject: Re: Cayenne with Postgress : how to get data from cursor
AFAIK ref cursors can only be accessed as ResultSets. ResultSet is essentially a JDBC abstraction of a cursor.
Andrus
On Sep 20, 2013, at 3:34 PM, Mattaboni Francesco <fr...@italiaonline.it> wrote:
> Thanks for the answer!
> However our problem is a little different.
> In stackoverflow example the result is following DataRow:
>
> {o_rc_source=org.postgresql.jdbc4.Jdbc4ResultSet@4bd27069,
> o_s_exitmsg=Ok,
> o_n_exitflag=11}
>
> and refcursor is accessible as a ResultSet.
> With the statement "param.setType(OracleAdapter.getOracleCursorType())" we set the OTHER parameter type as "OracleCursorType" and what we get is a cursor that is already mapped to a list of DataRow.
> Then we access the cursor directly as a collection of Cayenne DataRow objects and not as a ResultSet.
>
> Can we get the same behavior with Postgres or refcursors are accessible only as a ResultSet?
>
> Thanks in advance.
>
> Francesco
>
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: lunedì 9 settembre 2013 20.23
> To: user@cayenne.apache.org
> Cc: Mattaboni Francesco
> Subject: Re: Cayenne with Postgress : how to get data from cursor
>
> While I don't have much recent production experience with PostgreSQL, I recently did some research for a stackoverflow answer [1] (maybe that was your question?). My overall experience was that Cayenne (and PostgreSQL JDBC driver) would support pretty much any kind of PG function. Although coding those functions is somewhat arcane (to me at least), I never had to resort to any Oracle-like hacks.
>
> Sorry if this is not very specific. Maybe you can post the example of your PostgreSQL function, and someone will have a better idea.
>
> Andrus
>
> [1]
> http://stackoverflow.com/questions/16921942/porting-apache-cayenne-fro
> m-oracle-to-postgresql
>
> On Sep 9, 2013, at 4:03 PM, mr.abanjo <mr...@gmail.com> wrote:
>
>> Hi all,
>> we need to switch the database from Oracle to Postgres.
>> Our implementation use Cayenne to map a store procedure that return
>> single type data and a cursor.
>> With single type data ( number, varchar .... ) there's no problem.
>> All works fine.
>> The problem happens when we try to get data from a cursor. We mapped
>> it in data-map xml file as "OTHER". When we execute it the cursor is
>> empty ( but it must have a list of elements ).
>>
>> We suppose that the "missing" part for Postgress is this one :
>>
>> Procedure proc = getProcedure();
>> Iterator it = proc.getCallOutParameters().iterator();
>> while (it.hasNext()) {
>> ProcedureParameter param = (ProcedureParameter) it.next();
>> if (param.getType() == Types.OTHER) {
>> param.setType(OracleAdapter.getOracleCursorType());
>> }
>> }
>>
>> With Oracle we set the OTHER parameter type as "OracleCursorType".
>> There is a corresponding type for Postgress?
>> Maybe this is the problem, maybe no ... someone have any idea?
>>
>> Thanks!
>
>
> ________________________________
>
> Le informazioni contenute nella presente e-mail potrebbero essere confidenziali e sono dirette unicamente ai destinatari sopra indicati. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chi riceva questo messaggio per errore è pregato di inoltrarlo al mittente e di distruggere questa e-mail.
>
> This e-mail may contain confidential information and is intended only for the use of the addressee(s) named above. If the reader of this message is not the intended recipient of this message, please note that distribution or copying of this communication is forbidden. Anyone who receives this communication in error should return it immediately to the sender and destroy the message.
>
________________________________
Le informazioni contenute nella presente e-mail potrebbero essere confidenziali e sono dirette unicamente ai destinatari sopra indicati. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chi riceva questo messaggio per errore è pregato di inoltrarlo al mittente e di distruggere questa e-mail.
This e-mail may contain confidential information and is intended only for the use of the addressee(s) named above. If the reader of this message is not the intended recipient of this message, please note that distribution or copying of this communication is forbidden. Anyone who receives this communication in error should return it immediately to the sender and destroy the message.
Re: Cayenne with Postgress : how to get data from cursor
Posted by Andrus Adamchik <an...@objectstyle.org>.
AFAIK ref cursors can only be accessed as ResultSets. ResultSet is essentially a JDBC abstraction of a cursor.
Andrus
On Sep 20, 2013, at 3:34 PM, Mattaboni Francesco <fr...@italiaonline.it> wrote:
> Thanks for the answer!
> However our problem is a little different.
> In stackoverflow example the result is following DataRow:
>
> {o_rc_source=org.postgresql.jdbc4.Jdbc4ResultSet@4bd27069,
> o_s_exitmsg=Ok,
> o_n_exitflag=11}
>
> and refcursor is accessible as a ResultSet.
> With the statement "param.setType(OracleAdapter.getOracleCursorType())" we set the OTHER parameter type as "OracleCursorType" and what we get is a cursor that is already mapped to a list of DataRow.
> Then we access the cursor directly as a collection of Cayenne DataRow objects and not as a ResultSet.
>
> Can we get the same behavior with Postgres or refcursors are accessible only as a ResultSet?
>
> Thanks in advance.
>
> Francesco
>
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: lunedì 9 settembre 2013 20.23
> To: user@cayenne.apache.org
> Cc: Mattaboni Francesco
> Subject: Re: Cayenne with Postgress : how to get data from cursor
>
> While I don't have much recent production experience with PostgreSQL, I recently did some research for a stackoverflow answer [1] (maybe that was your question?). My overall experience was that Cayenne (and PostgreSQL JDBC driver) would support pretty much any kind of PG function. Although coding those functions is somewhat arcane (to me at least), I never had to resort to any Oracle-like hacks.
>
> Sorry if this is not very specific. Maybe you can post the example of your PostgreSQL function, and someone will have a better idea.
>
> Andrus
>
> [1] http://stackoverflow.com/questions/16921942/porting-apache-cayenne-from-oracle-to-postgresql
>
> On Sep 9, 2013, at 4:03 PM, mr.abanjo <mr...@gmail.com> wrote:
>
>> Hi all,
>> we need to switch the database from Oracle to Postgres.
>> Our implementation use Cayenne to map a store procedure that return
>> single type data and a cursor.
>> With single type data ( number, varchar .... ) there's no problem.
>> All works fine.
>> The problem happens when we try to get data from a cursor. We mapped
>> it in data-map xml file as "OTHER". When we execute it the cursor is
>> empty ( but it must have a list of elements ).
>>
>> We suppose that the "missing" part for Postgress is this one :
>>
>> Procedure proc = getProcedure();
>> Iterator it = proc.getCallOutParameters().iterator();
>> while (it.hasNext()) {
>> ProcedureParameter param = (ProcedureParameter) it.next();
>> if (param.getType() == Types.OTHER) {
>> param.setType(OracleAdapter.getOracleCursorType());
>> }
>> }
>>
>> With Oracle we set the OTHER parameter type as "OracleCursorType".
>> There is a corresponding type for Postgress?
>> Maybe this is the problem, maybe no ... someone have any idea?
>>
>> Thanks!
>
>
> ________________________________
>
> Le informazioni contenute nella presente e-mail potrebbero essere confidenziali e sono dirette unicamente ai destinatari sopra indicati. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chi riceva questo messaggio per errore è pregato di inoltrarlo al mittente e di distruggere questa e-mail.
>
> This e-mail may contain confidential information and is intended only for the use of the addressee(s) named above. If the reader of this message is not the intended recipient of this message, please note that distribution or copying of this communication is forbidden. Anyone who receives this communication in error should return it immediately to the sender and destroy the message.
>
Re: Cayenne with Postgress : how to get data from cursor
Posted by Andrus Adamchik <an...@objectstyle.org>.
While I don't have much recent production experience with PostgreSQL, I recently did some research for a stackoverflow answer [1] (maybe that was your question?). My overall experience was that Cayenne (and PostgreSQL JDBC driver) would support pretty much any kind of PG function. Although coding those functions is somewhat arcane (to me at least), I never had to resort to any Oracle-like hacks.
Sorry if this is not very specific. Maybe you can post the example of your PostgreSQL function, and someone will have a better idea.
Andrus
[1] http://stackoverflow.com/questions/16921942/porting-apache-cayenne-from-oracle-to-postgresql
On Sep 9, 2013, at 4:03 PM, mr.abanjo <mr...@gmail.com> wrote:
> Hi all,
> we need to switch the database from Oracle to Postgres.
> Our implementation use Cayenne to map a store procedure that return single
> type data and a cursor.
> With single type data ( number, varchar .... ) there's no problem. All
> works fine.
> The problem happens when we try to get data from a cursor. We mapped it in
> data-map xml file as "OTHER". When we execute it the cursor is empty ( but
> it must have a list of elements ).
>
> We suppose that the "missing" part for Postgress is this one :
>
> Procedure proc = getProcedure();
> Iterator it = proc.getCallOutParameters().iterator();
> while (it.hasNext()) {
> ProcedureParameter param = (ProcedureParameter) it.next();
> if (param.getType() == Types.OTHER) {
> param.setType(OracleAdapter.getOracleCursorType());
> }
> }
>
> With Oracle we set the OTHER parameter type as "OracleCursorType". There is
> a corresponding type for Postgress?
> Maybe this is the problem, maybe no ... someone have any idea?
>
> Thanks!