<HTML
><HEAD
><TITLE
>Storing Binary Data</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.73
"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 7.3.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="JDBC Interface"
HREF="jdbc.html"><LINK
REL="PREVIOUS"
TITLE="Creating and Modifying Database Objects"
HREF="jdbc-ddl.html"><LINK
REL="NEXT"
TITLE="PostgreSQL Extensions to the
JDBC API"
HREF="jdbc-ext.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
NAME="creation"
CONTENT="2003-02-03T20:17:34"></HEAD
><BODY
CLASS="SECT1"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL 7.3.2 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="jdbc-ddl.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 5. <SPAN
CLASS="ACRONYM"
>JDBC</SPAN
> Interface</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="jdbc-ext.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="JDBC-BINARY-DATA"
>5.6. Storing Binary Data</A
></H1
><P
> <SPAN
CLASS="APPLICATION"
>PostgreSQL</SPAN
> provides two distinct ways to
store binary data. Binary data can be stored in a table using
<SPAN
CLASS="APPLICATION"
>PostgreSQL's</SPAN
> binary data type
<TT
CLASS="TYPE"
>bytea</TT
>, or by using the <I
CLASS="FIRSTTERM"
>Large Object</I
>
feature which stores the binary data in a separate table in a special
format, and refers to that table by storing a value of type
<TT
CLASS="TYPE"
>OID</TT
> in your table.
</P
><P
> In order to determine which method is appropriate you
need to understand the limitations of each method. The
<TT
CLASS="TYPE"
>bytea</TT
> data type is not well suited for storing very
large amounts of binary data. While a column of type
<TT
CLASS="TYPE"
>bytea</TT
> can hold up to 1 GB of binary data, it would
require a huge amount of memory (<SPAN
CLASS="ACRONYM"
>RAM</SPAN
>) to
process such a large value. The Large Object method for
storing binary data is better suited to storing very large values,
but it has its own limitations. Specifically deleting a row
that contains a Large Object does not delete the Large Object.
Deleting the Large Object is a separate operation that needs to
be performed. Large Objects also have some security
issues since anyone connected to the database case view
and/or modify any Large Object, even if they don't have
permissions to view/update the row containing the Large Object.
</P
><P
> 7.2 is the first release of the <SPAN
CLASS="ACRONYM"
>JDBC</SPAN
> Driver
that supports the <TT
CLASS="TYPE"
>bytea</TT
> data type. The introduction of
this functionality in 7.2 has introduced a change in behavior
as compared to previous releases. In 7.2 the methods
<TT
CLASS="FUNCTION"
>getBytes()</TT
>, <TT
CLASS="FUNCTION"
>setBytes()</TT
>,
<TT
CLASS="FUNCTION"
>getBinaryStream()</TT
>, and
<TT
CLASS="FUNCTION"
>setBinaryStream()</TT
> operate on
the <TT
CLASS="TYPE"
>bytea</TT
> data type. In 7.1 these methods operated
on the <TT
CLASS="TYPE"
>OID</TT
> data type associated with Large Objects.
It is possible to revert the driver back to the old 7.1 behavior
by setting the <TT
CLASS="PARAMETER"
><I
>compatible</I
></TT
> property on
the <TT
CLASS="CLASSNAME"
>Connection</TT
> to a value of
<TT
CLASS="LITERAL"
>7.1</TT
>
</P
><P
> To use the <TT
CLASS="TYPE"
>bytea</TT
> data type you should simply use
the <TT
CLASS="FUNCTION"
>getBytes()</TT
>, <TT
CLASS="FUNCTION"
>setBytes()</TT
>,
<TT
CLASS="FUNCTION"
>getBinaryStream()</TT
>, or
<TT
CLASS="FUNCTION"
>setBinaryStream()</TT
> methods.
</P
><P
> To use the Large Object functionality you can use either the
<TT
CLASS="CLASSNAME"
>LargeObject</TT
> <SPAN
CLASS="ACRONYM"
>API</SPAN
>
provided by the <SPAN
CLASS="APPLICATION"
>PostgreSQL</SPAN
>
<SPAN
CLASS="ACRONYM"
>JDBC</SPAN
> Driver, or by using the
<TT
CLASS="FUNCTION"
>getBLOB()</TT
> and <TT
CLASS="FUNCTION"
>setBLOB()</TT
>
methods.
</P
><DIV
CLASS="IMPORTANT"
><BLOCKQUOTE
CLASS="IMPORTANT"
><P
><B
>Important: </B
> For <SPAN
CLASS="APPLICATION"
>PostgreSQL</SPAN
>, you must access Large
Objects within an <SPAN
CLASS="ACRONYM"
>SQL</SPAN
> transaction. You would
open a transaction by using the
<TT
CLASS="FUNCTION"
>setAutoCommit()</TT
> method with an input
parameter of <TT
CLASS="LITERAL"
>false</TT
>.
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>In a future release of the
<SPAN
CLASS="ACRONYM"
>JDBC</SPAN
> Driver, the <TT
CLASS="FUNCTION"
>getBLOB()</TT
>
and <TT
CLASS="FUNCTION"
>setBLOB()</TT
> methods may no longer
interact with Large Objects and will instead work on
<TT
CLASS="TYPE"
>bytea</TT
> data types. So it is recommended that you
use the <TT
CLASS="CLASSNAME"
>LargeObject</TT
> <SPAN
CLASS="ACRONYM"
>API</SPAN
>
if you intend to use Large Objects.
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="EXAMPLE"
><A
NAME="JDBC-BINARY-DATA-EXAMPLE"
></A
><P
><B
>Example 5-4. Binary Data Examples</B
></P
><P
> For example, suppose you have a table containing the file name of
an image and you also want to store the image in a <TT
CLASS="TYPE"
>bytea</TT
>
column:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE images (imgname text, img bytea);</PRE
><P>
</P
><P
> To insert an image, you would use:
</P><PRE
CLASS="PROGRAMLISTING"
>File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, file.length());
ps.executeUpdate();
ps.close();
fis.close();</PRE
><P>
Here, <TT
CLASS="FUNCTION"
>setBinaryStream()</TT
> transfers a set number
of bytes from a stream into the column of type <TT
CLASS="TYPE"
>bytea</TT
>.
This also could have been done using the <TT
CLASS="FUNCTION"
>setBytes()</TT
>
method if the contents of the image was already in a
<TT
CLASS="CLASSNAME"
>byte[]</TT
>.
</P
><P
> Retrieving an image is even easier. (We use
<TT
CLASS="CLASSNAME"
>PreparedStatement</TT
> here, but the
<TT
CLASS="CLASSNAME"
>Statement</TT
> class can equally be used.)
</P><PRE
CLASS="PROGRAMLISTING"
>PreparedStatement ps = con.prepareStatement("SELECT img FROM images WHERE imgname=?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
while(rs.next()) {
byte[] imgBytes = rs.getBytes(1);
// use the stream in some way here
}
rs.close();
}
ps.close();</PRE
><P>
</P
><P
> Here the binary data was retrieved as an
<TT
CLASS="CLASSNAME"
>byte[]</TT
>. You could have used a
<TT
CLASS="CLASSNAME"
>InputStream</TT
> object instead.
</P
><P
> Alternatively you could be storing a very large file and want to use
the <TT
CLASS="CLASSNAME"
>LargeObject</TT
> <SPAN
CLASS="ACRONYM"
>API</SPAN
> to
store the file:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE imagesLO (imgname text, imgOID OID);</PRE
><P>
</P
><P
> To insert an image, you would use:
</P><PRE
CLASS="PROGRAMLISTING"
>// All LargeObject API calls must be within a transaction
conn.setAutoCommit(false);
// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
//create a new large object
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
//open the large object for write
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
// Now open the file
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
// copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0)
{
obj.write(buf, 0, s);
tl += s;
}
// 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();</PRE
><P></P
><P
> Retrieving the image from the Large Object:
</P><PRE
CLASS="PROGRAMLISTING"
>// All LargeObject API calls must be within a transaction
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();</PRE
><P>
</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="jdbc-ddl.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="jdbc-ext.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Creating and Modifying Database Objects</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="jdbc.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><SPAN
CLASS="APPLICATION"
>PostgreSQL</SPAN
> Extensions to the
<SPAN
CLASS="ACRONYM"
>JDBC</SPAN
> <SPAN
CLASS="ACRONYM"
>API</SPAN
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
