jdbc.sgml

PostgreSQL7.4.6 for Linux

// Close the large object
obj.close();

// Now insert the row into imageslo
PreparedStatement ps = conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setInt(2, oid);
ps.executeUpdate();
ps.close();
fis.close();
</programlisting>
   </para>

   <para>
    Retrieving the image from the Large Object:

<programlisting>
// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();

PreparedStatement ps = con.prepareStatement("SELECT imgoid FROM imageslo WHERE imgname = ?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
    while (rs.next()) {
        // Open the large object for reading
        int oid = rs.getInt(1);
        LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

        // Read the data
        byte buf[] = new byte[obj.size()];
        obj.read(buf, 0, obj.size());
        // Do something with the data read here

        // Close the object
        obj.close();
    }
    rs.close();
}
ps.close();
</programlisting>
   </para>

  </example>
 </sect1>


 <sect1 id="jdbc-ext">
  <title><application>PostgreSQL</application> Extensions to the
    <acronym>JDBC</acronym> <acronym>API</acronym></title>

  <para>
   <productname>PostgreSQL</productname> is an extensible database
   system.  You can add your own functions to the server, which can
   then be called from queries, or even add your own data types.  As
   these are facilities unique to <productname>PostgreSQL</productname>,
   we support them from Java, with a set of extension
   <acronym>API</acronym>'s. Some features within the core of the
   standard driver actually use these extensions to implement Large
   Objects, etc.
  </para>

  <sect2>
   <title>Accessing the Extensions</title>

   <para>
    To access some of the extensions, you need to use some extra
    methods in the <classname>org.postgresql.PGConnection</classname>
    class. In this case, you would need to case the return value of
    <function>Driver.getConnection()</function>.  For example:
<programlisting>
Connection db = Driver.getConnection(url, username, password);
// ...
// later on
Fastpath fp = ((org.postgresql.PGConnection)db).getFastpathAPI();
</programlisting>
   </para>

   <sect3>
    <title>Class <classname>org.postgresql.PGConnection</classname></title>

<synopsis>
public class PGConnection 
</synopsis>

    <para>
     These are the extra methods used to gain access to
     <productname>PostgreSQL</productname>'s extensions. 
    </para>

    <sect4>
     <title>Methods</title>

     <itemizedlist>
      <listitem>
<synopsis>
public Fastpath getFastpathAPI() throws SQLException
</synopsis>
       <para>
        This returns the fast-path <acronym>API</acronym> for the
        current connection.  It is primarily used by the Large Object
        <acronym>API</acronym>.
       </para>

       <para>
        The best way to use this is as follows:
<programlisting>
import org.postgresql.fastpath.*;
...
Fastpath fp = ((org.postgresql.PGConnection)myconn).getFastpathAPI();
</programlisting>
        where <varname>myconn</> is an open <classname>Connection</> to <productname>PostgreSQL</productname>.
       </para>

       <formalpara>
        <title>Returns:</title>
        <para>
         <classname>Fastpath</> object allowing access to functions on the
         <productname>PostgreSQL</productname> server.
        </para>
       </formalpara>

       <formalpara>
        <title>Throws:</title>
        <para>
         <classname>SQLException</> by <classname>Fastpath</> when initializing for first time
        </para>
       </formalpara>
      </listitem>

      <listitem>
       <para>
<synopsis>
public LargeObjectManager getLargeObjectAPI() throws SQLException
</synopsis>
        This returns the Large Object <acronym>API</acronym> for the
        current connection.
       </para>

       <para>
        The best way to use this is as follows:
<programlisting>
import org.postgresql.largeobject.*;
...
LargeObjectManager lo = ((org.postgresql.PGConnection)myconn).getLargeObjectAPI();
</programlisting>
        where <varname>myconn</> is an open <classname>Connection</> to
        <productname>PostgreSQL</productname>.
       </para>

       <formalpara>
        <title>Returns:</title>
        <para>
         <classname>LargeObject</classname> object that implements the <acronym>API</acronym>
        </para>
       </formalpara>

       <formalpara>
        <title>Throws:</title>
        <para>
         <classname>SQLException</classname> by <classname>LargeObject</classname> when initializing for first time
        </para>
       </formalpara>
      </listitem>

      <listitem>
       <para>
<synopsis>
public void addDataType(String type, String name)
</synopsis>
        This allows client code to add a handler for one of
        <productname>PostgreSQL</productname>'s more unique data types. Normally, a data type not
        known by the driver is returned by <literal>ResultSet.getObject()</literal> as a
        <classname>PGobject</> instance.  This method allows you to write a class
        that extends <classname>PGobject</>, and tell the driver the type name, and
        class name to use. The down side to this, is that you must
        call this method each time a connection is made.
       </para>

       <para>
        The best way to use this is as follows:
<programlisting>
 ...
((org.postgresql.PGConnection)myconn).addDataType("mytype","my.class.name");
 ...
</programlisting>
        where <varname>myconn</varname> is an open <classname>Connection</> to
        <productname>PostgreSQL</productname>.  The handling class must
        extend <classname>org.postgresql.util.PGobject</classname>.
       </para>
      </listitem>
     </itemizedlist>
    </sect4>
   </sect3>


   <sect3>
    <title>Class <classname>org.postgresql.Fastpath</classname></title>

<synopsis>
public class Fastpath extends Object

java.lang.Object
   |
   +----org.postgresql.fastpath.Fastpath
</synopsis>

    <para>
     <classname>Fastpath</classname> is an <acronym>API</acronym> that
     exists within the <application>libpq</application> C interface, and allows a client machine
     to execute a function on the database server.  Most client code
     will not need to use this method, but it is provided because the
     Large Object <acronym>API</acronym> uses it.
    </para>

    <para>
     To use, you need to import the
     <classname>org.postgresql.fastpath</classname> package, using the
     line:
<programlisting>
import org.postgresql.fastpath.*;
</programlisting>
     Then, in your code, you need to get a
     <classname>FastPath</classname> object:
<programlisting>
Fastpath fp = ((org.postgresql.PGConnection)conn).getFastpathAPI();
</programlisting>
     This will return an instance associated with the database
     connection that you can use to issue commands. The casing of
     <classname>Connection</classname> to
     <classname>org.postgresql.PGConnection</classname> is required, as
     the <function>getFastpathAPI()</function> is an extension method,
     not part of <acronym>JDBC</acronym>.  Once you have a
     <classname>Fastpath</classname> instance, you can use the
     <function>fastpath()</function> methods to execute a server
     function.
    </para>

    <formalpara>
     <title>See Also:</title>
     <para>
      <classname>FastpathFastpathArg</classname>, <classname>LargeObject</classname>
     </para>
    </formalpara>

    <sect4>
     <title>Methods</title>

     <itemizedlist>
      <listitem>
<synopsis>
public Object fastpath(int fnid,
                       boolean resulttype,
                       FastpathArg args[]) throws SQLException
</synopsis>
       <para>
        Send a function call to the <productname>PostgreSQL</productname> server.
       </para>

       <formalpara>
        <title>Parameters:</title>
        <para>
                <parameter>fnid</> - Function id
                <parameter>resulttype</> - True if the result is an integer, false 
for
                other results
                <parameter>args</> - <classname>FastpathArguments</classname> to pass to fast-path call
        </para>
       </formalpara>

       <formalpara>
        <title>Returns:</title>
        <para>
         null if no data, Integer if an integer result, or byte[]
         otherwise
        </para>
       </formalpara>
      </listitem>

      <listitem>
<synopsis>
public Object fastpath(String name,
                       boolean resulttype,
                       FastpathArg args[]) throws SQLException
</synopsis>
       <para>
        Send a function call to the <productname>PostgreSQL</productname> server by name.
       </para>

       <note>
        <para>
         The mapping for the procedure name to function id needs to
         exist, usually to an earlier call to <function>addfunction()</function>. This is
         the preferred method to call, as function id's can/may change
         between versions of the server. For an example of how this
         works, refer to org.postgresql.LargeObject
        </para>
       </note>

       <formalpara>
        <title>Parameters:</title>
        <para>
                <parameter>name</> - Function name
                <parameter>resulttype</> - True if the result is an integer, false 
for
                other results
                <parameter>args</> - <classname>FastpathArguments</classname> to pass to fast-path call
        </para>
       </formalpara>

       <formalpara>
        <title>Returns:</title>
        <para>
         null if no data, Integer if an integer result, or byte[]
         otherwise
        </para>
       </formalpara>

       <formalpara>
        <title>See Also:</title>
        <para><classname>LargeObject</classname></para>
       </formalpara>
      </listitem>

      <listitem>
<synopsis>          
public int getInteger(String name,
                      FastpathArg args[]) throws SQLException
</synopsis>
       <para>
        This convenience method assumes that the return value is an Integer
       </para>

       <formalpara>
        <title>Parameters:</title>
        <para>
                <parameter>name</parameter> - Function name
                <parameter>args</parameter> - Function arguments
        </para>
       </formalpara>

       <formalpara>
        <title>Returns:</title>
        <para>integer result</para>
       </formalpara>

       <formalpara>
        <title>Throws:</title>
        <para>
         <classname>SQLException</classname> if a database-access error occurs or no result
        </para>
       </formalpara>
      </listitem>

      <listitem>
<synopsis>
public byte[] getData(String name,
                      FastpathArg args[]) throws SQLException
</synopsis>
       <para>
        This convenience method assumes that the return value is binary
        data.
       </para>

       <formalpara>
        <title>Parameters:</title>
        <para>
                <parameter>name</parameter> - Function name
                <parameter>args</parameter> - Function arguments
        </para>
       </formalpara>

       <formalpara>
        <title>Returns:</title>
        <para>byte[] array containing result</para>
       </formalpara>

       <formalpara>
        <title>Throws:</title>
        <para>
         <classname>SQLException</classname> if a database-access error occurs or no result
        </para>
       </formalpara>
      </listitem>

      <listitem>
<synopsis>
public void addFunction(String name,
                        int fnid)
</synopsis>
       <para>
        This adds a function to our look-up table.  User code should
        use the <function>addFunctions</function> method, which is based upon a query,
        rather than hard coding the OID. The OID for a function is not
        guaranteed to remain static, even on different servers of the
        same version.
       </para>
      </listitem>

      <listitem>
<synopsis>
public void addFunctions(ResultSet rs) throws SQLException
</synopsis>
       <para>
        This takes a <classname>ResultSet</classname> containing two columns. Column 1
        contains the function name, Column 2 the OID.  It reads the
        entire <classname>ResultSet</classname>, loading the values into the function table.
       </para>

       <important>
        <para>
         Remember to <function>close()</function> the
         <classname>ResultSet</classname> after calling this!
        </para>
       </important>

       <note>
        <title>Implementation note about function name look-ups</title>

        <para>
         <productname>PostgreSQL</productname> stores the function id's and their corresponding
         names in the <classname>pg_proc</> table. To speed things up locally,
         instead of querying each function from that table when
         required, a <classname>Hashtable</classname> is used. Also, only the function's
         required are entered into this table, keeping connection
         times as fast as possible.
        </para>

        <para>
         The <classname>org.postgresql.LargeObject</classname> class
         performs a query upon its start-up, and passes the returned
         <classname>ResultSet</classname> to the
         <function>addFunctions()</function> method here.  Once this
         has been done, the Large Object <acronym>API</acronym> refers
         to the functions by name.
        </para>

        <para>
         Do not think that manually converting them to the OIDs will
         work. OK, they will for now, but they can change during
         development (there was some discussion about this for V7.0),
         so this is implemented to prevent any unwarranted headaches
         in the future.
        </para>
       </note>