thanks, --Barry
Nic wrote:
The changes to JDBC stored proc calls (made a while ago) highlighted some bits missing in the manual.
This fills them in.
------------------------------------------------------------------------
Index: doc/src/sgml/jdbc.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/jdbc.sgml,v
retrieving revision 1.45
diff -u -r1.45 jdbc.sgml
--- doc/src/sgml/jdbc.sgml 30 Jun 2003 16:39:42 -0000 1.45
+++ doc/src/sgml/jdbc.sgml 29 Jul 2003 21:48:44 -0000
@@ -323,8 +323,9 @@
a <classname>Statement</classname> or <classname>PreparedStatement</classname>, you can use issue a
query. This will return a <classname>ResultSet</classname>
- instance, which contains the entire result. <xref
- linkend="jdbc-query-example"> illustrates this process.
+ instance, which contains the entire result (see <xref linkend="jdbc-query-with-cursor">
+ here for how to alter this behaviour).
+ <xref linkend="jdbc-query-example"> illustrates this process.
</para>
<example id="jdbc-query-example">
@@ -364,6 +365,50 @@
</para>
</example>
+ <sect2 id="query-with-cursor">
+ <title>Getting results based on a cursor</title>
+
+ <para>By default the driver collects all the results for the
+ query at once. This can be inconvieniant for large data sets so
+ the JDBC driver provides a means of basing
+ a <classname>ResultSet</classname> on a database cursor and
+ only fetching a small number of rows.</para>
+
+ <para>A small number of rows are cached on the
+ client side of the connection and when exhausted the next
+ block of rows is retrieved by repositioning the cursor.
+ </para>
+
+ <example>
+ <title>Setting fetch size to turn cursors on and off.</title>
+
+ <para>Changing code to cursor mode is as simple as setting the
+ fetch size of the <classname>Statement</classname> to the
+ appropriate size. Setting the fecth size back to 0 will cause
+ all rows to be cached (the default behaviour).
+
+<programlisting>
+Statement st = db.createStatement();
+// Turn use of the cursor on.
+st.setFetchSize(50);
+ResultSet rs = st.executeQuery("SELECT * FROM mytable");
+while (rs.next()) {
+ System.out.print("a row was returned.");
+}
+rs.close();
+// Turn the cursor off.
+st.setFetchSize(0);
+ResultSet rs = st.executeQuery("SELECT * FROM mytable");
+while (rs.next()) {
+ System.out.print("many rows were returned.");
+}
+rs.close();
+// Close the statement.
+st.close();
+</programlisting>
+ </para>
+
+
<sect2>
<title>Using the <classname>Statement</classname> or <classname>PreparedStatement</classname> Interface</title>
@@ -493,6 +538,120 @@
</para>
</example>
</sect1>
+
+
+ <sect1 id="jdbc-callproc">
+ <title>Calling Stored Functions</title>
+
+ <para><productname>PostgreSQL's</productname> jdbc driver fully
+ supports calling <productname>PostgreSQL</productname> stored
+ functions.</para>
+
+ <example id="jdbc-call-function">
+ <title>Calling a built in stored function</title>
+
+ <para>This example shows how to call
+ a <productname>PostgreSQL</productname> built in
+ function, <command>upper</command>, which simply converts the
+ supplied string argument to uppercase.
+
+<programlisting>
+// Turn transactions off.
+con.setAutoCommit(false);
+// Procedure call.
+CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }");
+upperProc.registerOutParameter(1, Types.VARCHAR);
+upperProc.setString(2, "lowercase to uppercase");
+upperProc.execute();
+String upperCased = upperProc.getString(1);
+upperProc.close();
+</programlisting>
+ </para>
+ </example>
+
+ <sect2>
+ <title>Using the <classname>CallableStatement</classname> Interface</title>
+
+ <para>
+ All the considerations that apply
+ for <classname>Statement</classname>
+ and <classname>PreparedStatement</classname> apply
+ for <classname>CallableStatement</classname> but in addition
+ you must also consider one extra restriction:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>You can only call a stored function from within a
+ transaction.</para>
+ </listitem>
+ </itemizedlist>
+
+ </sect2>
+
+ <sect2>
+ <title>Obtaining <classname>ResultSet</classname> from a stored function</title>
+ + <para><productname>PostgreSQL's</productname> stored function
+ can return results by means of a <type>refcursor</type>
+ value. A <type>refcursor</type>.</para>
+
+ <para>As an extension to JDBC,
+ the <productname>PostgreSQL</productname> JDBC driver can
+ return <type>refcursor</type> values
+ as <classname>ResultSet</classname> values.</para>
+
+ <example id="get-refcursor-from-function-call">
+ <title>Gettig <type>refcursor</type> values from a
+ function</title>
+
+ <para>When calling a function that returns
+ a <type>refcursor</type> you must cast the return type
+ of <methodname>getObject</methodname> to
+ a <classname>ResultSet</classname></para>
+
+<programlisting>
+// Turn transactions off.
+con.setAutoCommit(false);
+// Procedure call.
+CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
+proc.registerOutParameter(1, Types.Other);
+proc.setInt(2, -1);
+proc.execute();
+ResultSet results = (ResultSet) proc.getObject(1);
+while (results.next()) {
+ // do something with the results...
+}
+results.close();
+proc.close();
+</programlisting>
+ </example>
+
+ <para>It is also possible to treat the <type>refcursor</type>
+ return value as a distinct type in itself. The JDBC driver
+ provides
+ the <classname>org.postgresql.PGRefCursorResultSet</classname>
+ class for this purpose.</para>
+
+ <example>
+ <title>Treating <type>refcursor</type> as a distinct
+ type</title>
+
+<programlisting>
+con.setAutoCommit(false);
+CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
+proc.registerOutParameter(1, Types.Other);
+proc.setInt(2, 0);
+org.postgresql.PGRefCursorResultSet refcurs + = (PGRefCursorResultSet) con.getObject(1);
+String cursorName = refcurs.getRefCursor();
+proc.close();
+</programlisting>
+ </example>
+ </sect2>
+
+ </sect1>
+
<sect1 id="jdbc-ddl">
<title>Creating and Modifying Database Objects</title>
------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
