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>
-- Nic Ferrier http://www.tapsellferrier.co.uk
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match