Thread: Getting a ResultSet for a refcursor element.
I'm another one of those refugees from oracle who like to return
cursors from stored procs.
I was getting annoyed at PostgreSQL's solution, which seems to be
less elegant than Oracle's, so I've added code to the JDBC driver to
implement the same solution as is possible with Oracle.
In brief, getting a returned cursor can now be done like this:
Statement st = con.createStatement();
// Has to be done within a single transaction.
con.setAutoCommit(false);
ResultSet rs
= st.executeQuery("select some_func_returning_refcursor();");
if (! rs.next())
throw new SQLException("whoops! there were no rows.");
try
{
ResultSet v = (ResultSet) rs.getObject(1);
while (rs2.next())
System.out.println(rs2.getString(1));
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
// This causes the refcursor to be closed.
con.commit();
st.close();
The change wasn't difficult to make, it's a few changes but mainly
it's not much more than this:
else if (type.equals("refcursor"))
{
String cursorName = getString(columnIndex);
Statement st = new Statement(this.statement.connection);
return st.executeQuery("FETCH ALL IN \""
+ cursorName
+ "\";");
}
in the default handling for the JDBC type in the jdbc2/ResultSet
class.
Would a diff for the patch be appreciated here? If so, what sort of
diff?
Nic Ferrier
Nic,
send the patch as a context diff
Dave
On Mon, 2002-09-30 at 19:33, Nic Ferrier wrote:
> I'm another one of those refugees from oracle who like to return
> cursors from stored procs.
>
> I was getting annoyed at PostgreSQL's solution, which seems to be
> less elegant than Oracle's, so I've added code to the JDBC driver to
> implement the same solution as is possible with Oracle.
>
> In brief, getting a returned cursor can now be done like this:
>
>
> Statement st = con.createStatement();
> // Has to be done within a single transaction.
> con.setAutoCommit(false);
> ResultSet rs
> = st.executeQuery("select some_func_returning_refcursor();");
> if (! rs.next())
> throw new SQLException("whoops! there were no rows.");
> try
> {
> ResultSet v = (ResultSet) rs.getObject(1);
> while (rs2.next())
> System.out.println(rs2.getString(1));
> }
> catch (Exception e)
> {
> System.out.println(e.getMessage());
> }
> // This causes the refcursor to be closed.
> con.commit();
> st.close();
>
>
> The change wasn't difficult to make, it's a few changes but mainly
> it's not much more than this:
>
> else if (type.equals("refcursor"))
> {
> String cursorName = getString(columnIndex);
> Statement st = new Statement(this.statement.connection);
> return st.executeQuery("FETCH ALL IN \""
> + cursorName
> + "\";");
> }
>
> in the default handling for the JDBC type in the jdbc2/ResultSet
> class.
>
>
> Would a diff for the patch be appreciated here? If so, what sort of
> diff?
>
>
>
>
> Nic Ferrier
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
I was trying to build 7.3 on my ppc/gnu-linux box. It's just a little
iMac (perfectly all right for most stuff). Builds work ok but the ANT
installation target fails (the relevant console messages are below).
This could be for several reasons:
- maybe I really don't have enough memory to copy the target
directory to the destination
- maybe my java (blackdown 1.3 for PPC) is broken somewhere
- maybe there's a problem with ANT
However, I fixed this problem by altering the Makefile so that it did
the installation on behalf of the Java code. Here's the new
installation target:
# The install target used to do this:
# $(ANT) -buildfile $(srcdir)/build.xml install \
# -Dinstall.directory=$(javadir) $(properties)
install: installdirs
cp jars/postgresql.jar jars/postgresql-examples.jar $(javadir)
If the installation mechanism must rely on the build mechanism then
I'm not sure how to fix this.
Nic Ferrier
"make install" console messages >>>>>
/usr/local/bin/ant -buildfile ./build.xml install \
-Dinstall.directory=/usr/local/share/postgresql/java -Dmajor=7 -Dminor=3 -Dfullversion=7.3b2 -Ddef_pgport=5432
-Denable_debug=no
Buildfile: ./build.xml
all:
prepare:
BUILD FAILED
java.lang.OutOfMemoryError
at java.lang.reflect.Method.copy(Method.java:278)
at java.lang.reflect.Method.getParameterTypes(Method.java:90)
at org.apache.tools.ant.IntrospectionHelper.<init>(IntrospectionHelper.java:212)
at org.apache.tools.ant.IntrospectionHelper.getHelper(IntrospectionHelper.java:384)
at org.apache.tools.ant.ProjectHelper.storeChild(ProjectHelper.java:380)
at org.apache.tools.ant.RuntimeConfigurable.maybeConfigure(RuntimeConfigurable.java:255)
at org.apache.tools.ant.RuntimeConfigurable.maybeConfigure(RuntimeConfigurable.java:202)
at org.apache.tools.ant.Task.maybeConfigure(Task.java:257)
at org.apache.tools.ant.Task.perform(Task.java:316)
at org.apache.tools.ant.Target.execute(Target.java:309)
at org.apache.tools.ant.Target.performTasks(Target.java:334)
at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:371)
at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:143)
at org.apache.tools.ant.Task.perform(Task.java:317)
at org.apache.tools.ant.Target.execute(Target.java:309)
at org.apache.tools.ant.Target.performTasks(Target.java:334)
at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
at org.apache.tools.ant.Project.executeTargets(Project.java:1250)
at org.apache.tools.ant.Main.runBuild(Main.java:610)
at org.apache.tools.ant.Main.start(Main.java:196)
at org.apache.tools.ant.Main.main(Main.java:235)
Total time: 7 seconds
java.lang.OutOfMemoryError
at java.lang.reflect.Method.copy(Method.java:278)
at java.lang.reflect.Method.getParameterTypes(Method.java:90)
at org.apache.tools.ant.IntrospectionHelper.<init>(IntrospectionHelper.java:212)
at org.apache.tools.ant.IntrospectionHelper.getHelper(IntrospectionHelper.java:384)
at org.apache.tools.ant.ProjectHelper.storeChild(ProjectHelper.java:380)
at org.apache.tools.ant.RuntimeConfigurable.maybeConfigure(RuntimeConfigurable.java:255)
at org.apache.tools.ant.RuntimeConfigurable.maybeConfigure(RuntimeConfigurable.java:202)
at org.apache.tools.ant.Task.maybeConfigure(Task.java:257)
at org.apache.tools.ant.Task.perform(Task.java:316)
at org.apache.tools.ant.Target.execute(Target.java:309)
at org.apache.tools.ant.Target.performTasks(Target.java:334)
at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:371)
at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:143)
at org.apache.tools.ant.Task.perform(Task.java:317)
at org.apache.tools.ant.Target.execute(Target.java:309)
at org.apache.tools.ant.Target.performTasks(Target.java:334)
at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
.
.
.
<<<<< "make install" console messages.
Here's my context diff for getting ResultSet's whole from another
ResultSet (via a proc returning a refcursor).
Here's some example code:
import java.sql.*;
public class proctest
{
public static void main (String[] argv) throws Exception
{
Class driver = Class.forName("org.postgresql.Driver");
Connection con
= DriverManager.getConnection("jdbc:postgresql:test",
"someone",
"something");
Statement st = con.createStatement();
con.setAutoCommit(false);
// f() is a function that returns a refcursor.
ResultSet rs = st.executeQuery("select f();");
if (! rs.next())
throw new SQLException("whoops! there were no rows.");
try
{
Object v = rs.getObject(1);
if (v instanceof ResultSet) {
ResultSet rs2 = (ResultSet) v;
while (rs2.next()) {
System.out.println(rs2.getString(1));
}
}
}
catch (Exception e) {
System.out.println(e.getMessage());
}
con.commit();
st.close();
con.close();
}
}
Do I need to do a documentation patch? Does anybody else have a good
idea for how this should be described in the doc?
Nic
Here's the diff:
Index: src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
retrieving revision 1.8
diff -c -r1.8 AbstractJdbc2ResultSet.java
*** src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 2002/09/11 05:38:45 1.8
--- src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 2002/10/09 01:21:13
***************
*** 142,147 ****
--- 142,158 ----
{
return getString(columnIndex);
}
+ else if (type.equals("refcursor"))
+ {
+ // We must return a ResultSet with the results packaged.
+ // We should probably check that auto commit is turned off.
+ String cursorName = getString(columnIndex);
+ Statement st
+ = new Jdbc2Statement((Jdbc2Connection)this.connection);
+ return st.executeQuery("FETCH ALL IN \""
+ + cursorName
+ + "\";");
+ }
else
{
return connection.getObject(field.getPGType(), getString(columnIndex));
im working whit apache and postgres, with java, i have this exception An error occured while getting the authentification request Dos some body know why this is hapennig __________________________________________________ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com
Fabio, You're gonna have to give us more than that. Example code?? Dave On Tue, 2002-10-08 at 21:50, fabio viquez wrote: > im working whit apache and postgres, with java, i > have this exception > > An error occured while getting the authentification > request > > > Dos some body know why this is hapennig > > > > > > > > > > > __________________________________________________ > Do you Yahoo!? > Faith Hill - Exclusive Performances, Videos & More > http://faith.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Nic,
I don't think this is the correct approach. I think a better approach
would be to return a pg specific object (lets call it PGrefcursor). The
object would have at least the following two methods: getRefCursorName()
and getResultSet(). The reason I think this is a better approach is
then you can turn around and use the PGrefcursor object on a setObject()
call to bind the refcursor to a different function call. So you can
have a function that returns a refcursor and another that takes a
refcursor and you can get the refcursor object from one call and pass it
onto the other.
Now it is true that you could do this today using
getString()/setString() but that isn't very intuitive.
The other reason I don't like returning a result set directly from
getObject is that it doesn't seem to follow the same pattern as all the
other objects that are being returned. You are losing the distinction
that the refcursor is a pointer to a result set, not the actual result
set itself.
Finally, does anyone know how other databases' jdbc drivers deal with
this type of functionality? I would rather try to follow an existing
example of how someone else has done this then to go it alone and build
our own mechanism. Since I know Oracle has refcursors, how does oracle
expose them through jdbc?
thanks,
--Barry
Nic Ferrier wrote:
> Here's my context diff for getting ResultSet's whole from another
> ResultSet (via a proc returning a refcursor).
>
> Here's some example code:
>
> import java.sql.*;
>
>
> public class proctest
> {
> public static void main (String[] argv) throws Exception
> {
> Class driver = Class.forName("org.postgresql.Driver");
> Connection con
> = DriverManager.getConnection("jdbc:postgresql:test",
> "someone",
> "something");
> Statement st = con.createStatement();
> con.setAutoCommit(false);
> // f() is a function that returns a refcursor.
> ResultSet rs = st.executeQuery("select f();");
> if (! rs.next())
> throw new SQLException("whoops! there were no rows.");
> try
> {
> Object v = rs.getObject(1);
> if (v instanceof ResultSet) {
> ResultSet rs2 = (ResultSet) v;
> while (rs2.next()) {
> System.out.println(rs2.getString(1));
> }
> }
> }
> catch (Exception e) {
> System.out.println(e.getMessage());
> }
> con.commit();
> st.close();
> con.close();
> }
> }
>
>
> Do I need to do a documentation patch? Does anybody else have a good
> idea for how this should be described in the doc?
>
>
> Nic
>
>
> Here's the diff:
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
> retrieving revision 1.8
> diff -c -r1.8 AbstractJdbc2ResultSet.java
> *** src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 2002/09/11 05:38:45 1.8
> --- src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java 2002/10/09 01:21:13
> ***************
> *** 142,147 ****
> --- 142,158 ----
> {
> return getString(columnIndex);
> }
> + else if (type.equals("refcursor"))
> + {
> + // We must return a ResultSet with the results packaged.
> + // We should probably check that auto commit is turned off.
> + String cursorName = getString(columnIndex);
> + Statement st
> + = new Jdbc2Statement((Jdbc2Connection)this.connection);
> + return st.executeQuery("FETCH ALL IN \""
> + + cursorName
> + + "\";");
> + }
> else
> {
> return connection.getObject(field.getPGType(), getString(columnIndex));
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
Barry Lind <barry@xythos.com> writes: > The other reason I don't like returning a result set directly from > getObject is that it doesn't seem to follow the same pattern as all the > other objects that are being returned. You are losing the distinction > that the refcursor is a pointer to a result set, not the actual result > set itself. > > Finally, does anyone know how other databases' jdbc drivers deal with > this type of functionality? I would rather try to follow an existing > example of how someone else has done this then to go it alone and build > our own mechanism. Since I know Oracle has refcursors, how does oracle > expose them through jdbc? Like I've done it for pgsql. Ordinarily one register's the out parameter of the proc you are calling with the Oracle ResultSet implementation class. > I think a better approach > would be to return a pg specific object (lets call it PGrefcursor). The > object would have at least the following two methods: getRefCursorName() > and getResultSet(). The reason I think this is a better approach is > then you can turn around and use the PGrefcursor object on a setObject() > call to bind the refcursor to a different function call. So you can > have a function that returns a refcursor and another that takes a > refcursor and you can get the refcursor object from one call and pass it > onto the other. Of course, that's a good approach too... but doesn't getting the ResultSet directly makes it clear(er) that the new ResultSet is part of the same transaction. Nic
how Oracle does it:
> Ordinarily one register's the out parameter of the proc you are calling
> with the Oracle ResultSet implementation class.
Here's my earlier example re-written for the more conventional
style. Unfortunately this doesn't work out of the box on postgresql
jdbc because the CallableStatement handling is not setup yet.
However, here's what the code should look like:
import java.sql.*;
public class proctest
{
public static void main (String[] argv) throws Exception
{
Class driver = Class.forName("org.postgresql.Driver");
Connection con
= DriverManager.getConnection("jdbc:postgresql:test",
"someuser",
"somepassword");
con.setAutoCommit(false);
CallableStatement st = con.prepareCall("{ ? = call f() }");
// With Oracle at this point you'd do:
// st.registerOutParameter(1,
// oracle.jdbc.driver.OracleTypes.CURSOR);
// see my comment below.
st.registerOutParameter(1, Types.JAVA_OBJECT);
st.execute();
ResultSet rs = (ResultSet) st.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(1));
}
con.commit();
st.close();
con.close();
}
}
The use of "OracleTypes" by oracle is interesting. Obviously, I
haven't looked at the code, but I imagine it would have to be based
on java.sql.Types. That could be done I guess, something like:
java/sql/Types.java:
final static int INTEGER = 0;
final static int LONG = INTEGER + 1;
.
.
.
final static int STRING = ... + 1;
org/postgresql/PGTypes.java:
final static int REFCURSOR = java.sql.Types.STRING + 1;
But of course then you guys would either have to distribute your own
java.sql or at least be confident that it always worked in the same
way (maybe, via the build process?).
That's why I plumped for using getObject() and the cast. It seemed to
work quite well.
Nic
Nic,
What do you mean by "the CallableStatement handling is not setup yet"?
Current code should support CallableStatements such that the example you
give should work (except of course for returning a ResultSet object :-)
thanks,
--Barry
Nic Ferrier wrote:
> how Oracle does it:
>
>>Ordinarily one register's the out parameter of the proc you are calling
>>with the Oracle ResultSet implementation class.
>
>
> Here's my earlier example re-written for the more conventional
> style. Unfortunately this doesn't work out of the box on postgresql
> jdbc because the CallableStatement handling is not setup yet.
>
> However, here's what the code should look like:
>
>
>
> import java.sql.*;
>
>
> public class proctest
> {
> public static void main (String[] argv) throws Exception
> {
> Class driver = Class.forName("org.postgresql.Driver");
> Connection con
> = DriverManager.getConnection("jdbc:postgresql:test",
> "someuser",
> "somepassword");
> con.setAutoCommit(false);
> CallableStatement st = con.prepareCall("{ ? = call f() }");
> // With Oracle at this point you'd do:
> // st.registerOutParameter(1,
> // oracle.jdbc.driver.OracleTypes.CURSOR);
> // see my comment below.
> st.registerOutParameter(1, Types.JAVA_OBJECT);
> st.execute();
> ResultSet rs = (ResultSet) st.getObject(1);
> while (rs.next()) {
> System.out.println(rs.getString(1));
> }
> con.commit();
> st.close();
> con.close();
> }
> }
>
>
> The use of "OracleTypes" by oracle is interesting. Obviously, I
> haven't looked at the code, but I imagine it would have to be based
> on java.sql.Types. That could be done I guess, something like:
>
>
> java/sql/Types.java:
>
> final static int INTEGER = 0;
> final static int LONG = INTEGER + 1;
> .
> .
> .
> final static int STRING = ... + 1;
>
> org/postgresql/PGTypes.java:
>
> final static int REFCURSOR = java.sql.Types.STRING + 1;
>
>
> But of course then you guys would either have to distribute your own
> java.sql or at least be confident that it always worked in the same
> way (maybe, via the build process?).
>
>
> That's why I plumped for using getObject() and the cast. It seemed to
> work quite well.
>
>
> Nic
>
>
Alledgedly 7.3 now supports returning result sets from a function
Dave
On Thu, 2002-10-10 at 11:33, Barry Lind wrote:
> Nic,
>
> What do you mean by "the CallableStatement handling is not setup yet"?
> Current code should support CallableStatements such that the example you
> give should work (except of course for returning a ResultSet object :-)
>
> thanks,
> --Barry
>
> Nic Ferrier wrote:
> > how Oracle does it:
> >
> >>Ordinarily one register's the out parameter of the proc you are calling
> >>with the Oracle ResultSet implementation class.
> >
> >
> > Here's my earlier example re-written for the more conventional
> > style. Unfortunately this doesn't work out of the box on postgresql
> > jdbc because the CallableStatement handling is not setup yet.
> >
> > However, here's what the code should look like:
> >
> >
> >
> > import java.sql.*;
> >
> >
> > public class proctest
> > {
> > public static void main (String[] argv) throws Exception
> > {
> > Class driver = Class.forName("org.postgresql.Driver");
> > Connection con
> > = DriverManager.getConnection("jdbc:postgresql:test",
> > "someuser",
> > "somepassword");
> > con.setAutoCommit(false);
> > CallableStatement st = con.prepareCall("{ ? = call f() }");
> > // With Oracle at this point you'd do:
> > // st.registerOutParameter(1,
> > // oracle.jdbc.driver.OracleTypes.CURSOR);
> > // see my comment below.
> > st.registerOutParameter(1, Types.JAVA_OBJECT);
> > st.execute();
> > ResultSet rs = (ResultSet) st.getObject(1);
> > while (rs.next()) {
> > System.out.println(rs.getString(1));
> > }
> > con.commit();
> > st.close();
> > con.close();
> > }
> > }
> >
> >
> > The use of "OracleTypes" by oracle is interesting. Obviously, I
> > haven't looked at the code, but I imagine it would have to be based
> > on java.sql.Types. That could be done I guess, something like:
> >
> >
> > java/sql/Types.java:
> >
> > final static int INTEGER = 0;
> > final static int LONG = INTEGER + 1;
> > .
> > .
> > .
> > final static int STRING = ... + 1;
> >
> > org/postgresql/PGTypes.java:
> >
> > final static int REFCURSOR = java.sql.Types.STRING + 1;
> >
> >
> > But of course then you guys would either have to distribute your own
> > java.sql or at least be confident that it always worked in the same
> > way (maybe, via the build process?).
> >
> >
> > That's why I plumped for using getObject() and the cast. It seemed to
> > work quite well.
> >
> >
> > Nic
> >
> >
>
>
>
>
Dave,
True, but that has nothing to do with refcursor's. refcursors and SRFs
(set returning functions) are two different features. I thought this
thread was all about refcursors, so I am still unsure what Nic meant by
the comment below.
thanks,
--Barry
Dave Cramer wrote:
> Alledgedly 7.3 now supports returning result sets from a function
>
> Dave
> On Thu, 2002-10-10 at 11:33, Barry Lind wrote:
>
>>Nic,
>>
>>What do you mean by "the CallableStatement handling is not setup yet"?
>>Current code should support CallableStatements such that the example you
>>give should work (except of course for returning a ResultSet object :-)
>>
>>thanks,
>>--Barry
>>
>>Nic Ferrier wrote:
>> > how Oracle does it:
>> >
>> >>Ordinarily one register's the out parameter of the proc you are calling
>> >>with the Oracle ResultSet implementation class.
>> >
>> >
>> > Here's my earlier example re-written for the more conventional
>> > style. Unfortunately this doesn't work out of the box on postgresql
>> > jdbc because the CallableStatement handling is not setup yet.
>> >
>> > However, here's what the code should look like:
>> >
>> >
>> >
>> > import java.sql.*;
>> >
>> >
>> > public class proctest
>> > {
>> > public static void main (String[] argv) throws Exception
>> > {
>> > Class driver = Class.forName("org.postgresql.Driver");
>> > Connection con
>> > = DriverManager.getConnection("jdbc:postgresql:test",
>> > "someuser",
>> > "somepassword");
>> > con.setAutoCommit(false);
>> > CallableStatement st = con.prepareCall("{ ? = call f() }");
>> > // With Oracle at this point you'd do:
>> > // st.registerOutParameter(1,
>> > // oracle.jdbc.driver.OracleTypes.CURSOR);
>> > // see my comment below.
>> > st.registerOutParameter(1, Types.JAVA_OBJECT);
>> > st.execute();
>> > ResultSet rs = (ResultSet) st.getObject(1);
>> > while (rs.next()) {
>> > System.out.println(rs.getString(1));
>> > }
>> > con.commit();
>> > st.close();
>> > con.close();
>> > }
>> > }
>> >
>> >
>> > The use of "OracleTypes" by oracle is interesting. Obviously, I
>> > haven't looked at the code, but I imagine it would have to be based
>> > on java.sql.Types. That could be done I guess, something like:
>> >
>> >
>> > java/sql/Types.java:
>> >
>> > final static int INTEGER = 0;
>> > final static int LONG = INTEGER + 1;
>> > .
>> > .
>> > .
>> > final static int STRING = ... + 1;
>> >
>> > org/postgresql/PGTypes.java:
>> >
>> > final static int REFCURSOR = java.sql.Types.STRING + 1;
>> >
>> >
>> > But of course then you guys would either have to distribute your own
>> > java.sql or at least be confident that it always worked in the same
>> > way (maybe, via the build process?).
>> >
>> >
>> > That's why I plumped for using getObject() and the cast. It seemed to
>> > work quite well.
>> >
>> >
>> > Nic
>> >
>> >
>>
>>
>>
>>
>
>
>
Barry Lind <barry@xythos.com> writes: > Dave, > > True, but that has nothing to do with refcursor's. refcursors and SRFs > (set returning functions) are two different features. I thought this > thread was all about refcursors, so I am still unsure what Nic meant by > the comment below. > > thanks, > --Barry > > Dave Cramer wrote: > > Alledgedly 7.3 now supports returning result sets from a function > > > > Dave > > On Thu, 2002-10-10 at 11:33, Barry Lind wrote: > > > >>Nic, > >> > >>What do you mean by "the CallableStatement handling is not setup yet"? > >>Current code should support CallableStatements such that the example you > >>give should work (except of course for returning a ResultSet object :-) On my machine it fails. I just built 7.3 out of CVS. I presume there is a bug somewhere. I'll take a look at it and give you an update. Nic
Did you have any more thoughts on this Barry? I didn't see a response to my last email about it. Here's some more thoughts on the points you razed. Barry Lind <barry@xythos.com> writes: > Nic, > > I don't think this is the correct approach. I think a better approach > would be to return a pg specific object (lets call it PGrefcursor). The > object would have at least the following two methods: getRefCursorName() > and getResultSet(). The reason I think this is a better approach is > then you can turn around and use the PGrefcursor object on a setObject() > call to bind the refcursor to a different function call. So you can > have a function that returns a refcursor and another that takes a > refcursor and you can get the refcursor object from one call and pass it > onto the other. Why couldn't we do that with a ResultSet? > Now it is true that you could do this today using > getString()/setString() but that isn't very intuitive. > > The other reason I don't like returning a result set directly from > getObject is that it doesn't seem to follow the same pattern as all the > other objects that are being returned. You are losing the distinction > that the refcursor is a pointer to a result set, not the actual result > set itself. I'm not sure about the validity of this claim. IMO the ResultSet object "represents" the results of a query. That doesn't include any implementation expectation. eg: pgsql retrieves all values returned from the query for each RS but Oracle doesn't, it uses a cursor and fetches the results in batches (this is the approach I'm playing with for postgres, though I actually prefer the "get it all at once" system). As such, using a ResultSet to represent a cursor's seems to me just as valid as using a ResultSet to respresent a non-cursor's resutls. > Finally, does anyone know how other databases' jdbc drivers deal with > this type of functionality? I would rather try to follow an existing > example of how someone else has done this then to go it alone and build > our own mechanism. Since I know Oracle has refcursors, how does oracle > expose them through jdbc? As I said before, oracle does it as I have suggested. I think there's an important porting issue here. One of the reasons I wrote the patch is that I have some code that I want to port from ora to pgsql and it uses cursor based procs extensively. The P*SQL is easy to move, but the Java was impossible (until my patch). Nic
Nic, I am a bit swamped right now. So I haven't had a chance to look at this in any greater detail. However in response to your comments see below. Nic Ferrier wrote: > Did you have any more thoughts on this Barry? > > I didn't see a response to my last email about it. Here's some more > thoughts on the points you razed. > > > Barry Lind <barry@xythos.com> writes: > >>Nic, >> >>I don't think this is the correct approach. I think a better approach >>would be to return a pg specific object (lets call it PGrefcursor). The >>object would have at least the following two methods: getRefCursorName() >>and getResultSet(). The reason I think this is a better approach is >>then you can turn around and use the PGrefcursor object on a setObject() >>call to bind the refcursor to a different function call. So you can >>have a function that returns a refcursor and another that takes a >>refcursor and you can get the refcursor object from one call and pass it >>onto the other. > > > Why couldn't we do that with a ResultSet? > It can't be done with a result set since the point of a refcursor it to pass around the pointer to the cursor. It is the final function that will take the pointer and do the fetching. If the rows have already been fetched that code will not work. So you have function a() that returns a refcursor. It has some black box implementation that is creating a query and returning the refcursor to that query. Then you have function b(refcursor) that takes a refcursor and fetches the results and processes them. So when the refcursor is passed to function b() the assumption is that function b() can get the rows from the refcursor. > > >>Now it is true that you could do this today using >>getString()/setString() but that isn't very intuitive. >> >>The other reason I don't like returning a result set directly from >>getObject is that it doesn't seem to follow the same pattern as all the >>other objects that are being returned. You are losing the distinction >>that the refcursor is a pointer to a result set, not the actual result >>set itself. > > > I'm not sure about the validity of this claim. IMO the ResultSet > object "represents" the results of a query. That doesn't include any > implementation expectation. eg: pgsql retrieves all values returned > from the query for each RS but Oracle doesn't, it uses a cursor and > fetches the results in batches (this is the approach I'm playing > with for postgres, though I actually prefer the "get it all at once" > system). > > As such, using a ResultSet to represent a cursor's seems to me just > as valid as using a ResultSet to respresent a non-cursor's resutls. > I agree that a ResultSet represents the results of a query, but a refcursor is not the results of a query. It is a pointer to the query itself. Thus the 'ref' in the name. The point to a refcursor is the ability to only pass the pointer around and to only at the end use the pointer to get the results. It can't be assumed that the caller who gets a refcursor actually wants the results, he may just want the pointer so that it can be passed on to other functions. It has been a while, but I beleive that Oracle has two different types of cursors in plsql, one that is similar in nature to refcursors (i.e. a pointer to a query) and a second that more or less is the result set. I want to spend some time going through the Oracle doc to understand the different functionality in this area. > > >>Finally, does anyone know how other databases' jdbc drivers deal with >>this type of functionality? I would rather try to follow an existing >>example of how someone else has done this then to go it alone and build >>our own mechanism. Since I know Oracle has refcursors, how does oracle >>expose them through jdbc? > > > As I said before, oracle does it as I have suggested. I think there's > an important porting issue here. One of the reasons I wrote the patch > is that I have some code that I want to port from ora to pgsql and it > uses cursor based procs extensively. The P*SQL is easy to move, but > the Java was impossible (until my patch). > Just because Oracle does it one way doesn't mean that is the correct way to do it. However, it certainly does suggest that the Oracle way should given a lot of consideration. > > Nic > > Basically I need to spend some more time investigating. However I am still leaning towards a wrapper object that is just the pointer to the query (the 'ref' in refcursor) that has a method on it to get the result set. So instead of the implementation you have suggested: ResultSet rset2 = (ResultSet)set.getObject(x); it would be: ResultSet rset2 = ((PGRefCursor)rset.getObject(x)).getResultSet(); or possibly: ResultSet rset2 = ((PGResultSet)rset).getRefCursor(x).getResultSet(); thanks, --Barry
Barry Lind <blind@xythos.com> writes:
> > Why couldn't we do that with a ResultSet?
> >
> It can't be done with a result set since the point of a refcursor it to
> pass around the pointer to the cursor. It is the final function that
> will take the pointer and do the fetching. If the rows have already
> been fetched that code will not work.
>
> So you have function a() that returns a refcursor. It has some black
> box implementation that is creating a query and returning the refcursor
> to that query. Then you have function b(refcursor) that takes a
> refcursor and fetches the results and processes them. So when the
> refcursor is passed to function b() the assumption is that function b()
> can get the rows from the refcursor.
But you could do it inside the CallableStatement by converting a
refcursor based ResultSet into the original refcursor again.
So you'd have this:
CallableStatement getCurs = con.prepareCall("{ ? = pl.get_cur() }");
getCurs.registerOutParameter(1, Types.OBJECT);
getCurs.execute();
ResultSet rs = (ResultSet) getCurs.getObject(1);
// Now pass to another proc.
CallableStatement sendCurs = con.prepareCall("{ pl.send_cur( ? } }");
sendCurs.setObject(1, rs);
sendCurs.execute();
And the implementation of the result set is completly hidden.
> It has been a while, but I beleive that Oracle has two different types
> of cursors in plsql, one that is similar in nature to refcursors (i.e. a
> pointer to a query) and a second that more or less is the result set. I
> want to spend some time going through the Oracle doc to understand the
> different functionality in this area.
There are two different types, and that is (more or less) the
difference. But only the 2nd type can be returned from a function
(you can return the first type by assigning it to a variable of the
second type).
Nic
Nic Ferrier wrote:
> Barry Lind <blind@xythos.com> writes:
>
>
>>>Why couldn't we do that with a ResultSet?
>>>
>>
>>It can't be done with a result set since the point of a refcursor it to
>>pass around the pointer to the cursor. It is the final function that
>>will take the pointer and do the fetching. If the rows have already
>>been fetched that code will not work.
>>
>>So you have function a() that returns a refcursor. It has some black
>>box implementation that is creating a query and returning the refcursor
>>to that query. Then you have function b(refcursor) that takes a
>>refcursor and fetches the results and processes them. So when the
>>refcursor is passed to function b() the assumption is that function b()
>>can get the rows from the refcursor.
>
>
> But you could do it inside the CallableStatement by converting a
> refcursor based ResultSet into the original refcursor again.
>
> So you'd have this:
>
> CallableStatement getCurs = con.prepareCall("{ ? = pl.get_cur() }");
> getCurs.registerOutParameter(1, Types.OBJECT);
> getCurs.execute();
> ResultSet rs = (ResultSet) getCurs.getObject(1);
>
> // Now pass to another proc.
> CallableStatement sendCurs = con.prepareCall("{ pl.send_cur( ? } }");
> sendCurs.setObject(1, rs);
> sendCurs.execute();
>
>
> And the implementation of the result set is completly hidden.
>
>
But now we essentially have two types of ResultSets, regular ones and
refcursor ones. Refcursor ones need to be treated differently, because
if you want to do the above the refcursor based result set can't
populate its results until you attempt to get the results (i.e. call
next()), this is because if you do intend to pass it on you can't get
the results because then the called function will have nothing to fetch
since the data will already be fetched.
Also it wouldn't be appropriate to pass any old result set in as a bind
since only refcursor result sets could be passed on.
But perhaps there is a middle ground between our two view points of view
that combines them.
You are suggesting getting a refcursor returns a regular result set, I
am suggesting it returns a special object that retains the pointer
characteristics of a refcuror.
By combining these ideas we could do the following. Introduce a new
object RefCursorResultSet that extends ResultSet. It basically acts as
my wrapper object and can be passed on to other function calls. However
at the same time it can also fully implement ResultSet. So that once
you call next() it will actually go out and fetch the data from the
refcursor. So I would see the implementation of this object as having a
method like String getCursorID() that would return the refcursor name.
It would also have a member variable that would be the real result set.
The implementation of next() would initialize the member the first
time it was called and then it and all other methods from ResultSet
would simply call the corresponding methods on the internal ResultSet
object.
How does this sound?
thanks,
--Barry
>
>>It has been a while, but I beleive that Oracle has two different types
>>of cursors in plsql, one that is similar in nature to refcursors (i.e. a
>>pointer to a query) and a second that more or less is the result set. I
>>want to spend some time going through the Oracle doc to understand the
>>different functionality in this area.
>
>
> There are two different types, and that is (more or less) the
> difference. But only the 2nd type can be returned from a function
> (you can return the first type by assigning it to a variable of the
> second type).
>
>
>
> Nic
>
>
Barry Lind <blind@xythos.com> writes: > But now we essentially have two types of ResultSets, regular ones and > refcursor ones. Refcursor ones need to be treated differently, because > if you want to do the above the refcursor based result set can't > populate its results until you attempt to get the results (i.e. call > next()), this is because if you do intend to pass it on you can't get > the results because then the called function will have nothing to fetch > since the data will already be fetched. > > Also it wouldn't be appropriate to pass any old result set in as a bind > since only refcursor result sets could be passed on. > > But perhaps there is a middle ground between our two view points of view > that combines them. > > You are suggesting getting a refcursor returns a regular result set, I > am suggesting it returns a special object that retains the pointer > characteristics of a refcuror. > > By combining these ideas we could do the following. Introduce a new > object RefCursorResultSet that extends ResultSet. It basically acts as > my wrapper object and can be passed on to other function calls. However > at the same time it can also fully implement ResultSet. So that once > you call next() it will actually go out and fetch the data from the > refcursor. So I would see the implementation of this object as having a > method like String getCursorID() that would return the refcursor name. > It would also have a member variable that would be the real result set. > The implementation of next() would initialize the member the first > time it was called and then it and all other methods from ResultSet > would simply call the corresponding methods on the internal ResultSet > object. > > How does this sound? That sounds great. That is very like what Oracle does. Note that I am not opposed to your idea, I think it's a good idea. However, I have a need to keep Oracle compatibility in this area (because I'm porting lots of code). But if you're happy with the above, then I'll resubmit the patch on that basis. Nic
Nic Ferrier wrote: > Barry Lind <blind@xythos.com> writes: > > >>But now we essentially have two types of ResultSets, regular ones and >>refcursor ones. Refcursor ones need to be treated differently, because >>if you want to do the above the refcursor based result set can't >>populate its results until you attempt to get the results (i.e. call >>next()), this is because if you do intend to pass it on you can't get >>the results because then the called function will have nothing to fetch >>since the data will already be fetched. >> >>Also it wouldn't be appropriate to pass any old result set in as a bind >>since only refcursor result sets could be passed on. >> >>But perhaps there is a middle ground between our two view points of view >>that combines them. >> >>You are suggesting getting a refcursor returns a regular result set, I >>am suggesting it returns a special object that retains the pointer >>characteristics of a refcuror. >> >>By combining these ideas we could do the following. Introduce a new >>object RefCursorResultSet that extends ResultSet. It basically acts as >>my wrapper object and can be passed on to other function calls. However >>at the same time it can also fully implement ResultSet. So that once >>you call next() it will actually go out and fetch the data from the >>refcursor. So I would see the implementation of this object as having a >>method like String getCursorID() that would return the refcursor name. >>It would also have a member variable that would be the real result set. >> The implementation of next() would initialize the member the first >>time it was called and then it and all other methods from ResultSet >>would simply call the corresponding methods on the internal ResultSet >>object. >> >>How does this sound? > > > That sounds great. That is very like what Oracle does. > > Note that I am not opposed to your idea, I think it's a good > idea. However, I have a need to keep Oracle compatibility in this > area (because I'm porting lots of code). > > But if you're happy with the above, then I'll resubmit the patch on > that basis. > > Go ahead. I look forward to your patch. --Barry