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:[email protected]] Sent: lunedì 23 settembre 2013 10.53 To: Mattaboni Francesco Cc: [email protected]; 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 <[email protected]> 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:[email protected]] > Sent: lunedì 9 settembre 2013 20.23 > To: [email protected] > 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 <[email protected]> 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.
