Re: JDBC stored procs doc patch - Mailing list pgsql-jdbc

Patch applied.

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 majordomo@postgresql.org)