<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML
><HEAD
><TITLE
>Storing Binary Data</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 7.4.1 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-12-22T03:48:47"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 7.4.1 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="jdbc-ddl.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="jdbc.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 31. <ACRONYM
CLASS="ACRONYM"
>JDBC</ACRONYM
> Interface</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="jdbc.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><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"
>31.7. Storing Binary Data</A
></H1
><A
NAME="AEN24434"
></A
><A
NAME="AEN24437"
></A
><P
> <SPAN
CLASS="APPLICATION"
>PostgreSQL</SPAN
> provides two distinct ways to
store binary data. Binary data can be stored in a table using
the data type <TT
CLASS="TYPE"
>bytea</TT
> or by using the Large Object
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 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 reference 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 can view
and/or modify any Large Object, even if they don't have
permissions to view/update the row containing the Large Object reference.
</P
><P
> Version 7.2 was the first release of the <ACRONYM
CLASS="ACRONYM"
>JDBC</ACRONYM
> 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. Since 7.2, the methods
<CODE
CLASS="FUNCTION"
>getBytes()</CODE
>, <CODE
CLASS="FUNCTION"
>setBytes()</CODE
>,
<CODE
CLASS="FUNCTION"
>getBinaryStream()</CODE
>, and
<CODE
CLASS="FUNCTION"
>setBinaryStream()</CODE
> operate on
the <TT
CLASS="TYPE"
>bytea</TT
> data type. In 7.1 and earlier, 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 property <TT
CLASS="LITERAL"
>compatible</TT
> on
the <CODE
CLASS="CLASSNAME"
>Connection</CODE
> object to the value
<TT
CLASS="LITERAL"
>7.1</TT
>.
</P
><P
> To use the <TT
CLASS="TYPE"
>bytea</TT
> data type you should simply use
the <CODE
CLASS="FUNCTION"
>getBytes()</CODE
>, <CODE
CLASS="FUNCTION"
>setBytes()</CODE
>,
<CODE
CLASS="FUNCTION"
>getBinaryStream()</CODE
>, or
<CODE
CLASS="FUNCTION"
>setBinaryStream()</CODE
> methods.
</P
><P
> To use the Large Object functionality you can use either the
<CODE
CLASS="CLASSNAME"
>LargeObject</CODE
> class
provided by the <SPAN
CLASS="APPLICATION"
>PostgreSQL</SPAN
>
<ACRONYM
CLASS="ACRONYM"
>JDBC</ACRONYM
> driver, or by using the
<CODE
CLASS="FUNCTION"
>getBLOB()</CODE
> and <CODE
CLASS="FUNCTION"
>setBLOB()</CODE
>
methods.
</P
><DIV
CLASS="IMPORTANT"
><BLOCKQUOTE
CLASS="IMPORTANT"
><P
><B
>Important: </B
> You must access Large Objects within an <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
>
transaction block. You can start a transaction block by calling
<CODE
CLASS="FUNCTION"
>setAutoCommit(false)</CODE
>.
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> In a future release of the
<ACRONYM
CLASS="ACRONYM"
>JDBC</ACRONYM
> driver, the <CODE
CLASS="FUNCTION"
>getBLOB()</CODE
>
and <CODE
CLASS="FUNCTION"
>setBLOB()</CODE
> methods may no longer
interact with Large Objects and will instead work on the data type
<TT
CLASS="TYPE"
>bytea</TT
>. So it is recommended that you
use the <CODE
CLASS="CLASSNAME"
>LargeObject</CODE
> <ACRONYM
CLASS="ACRONYM"
>API</ACRONYM
>
if you intend to use Large Objects.
</P
></BLOCKQUOTE
></DIV
><P
> <A
HREF="jdbc-binary-data.html#JDBC-BINARY-DATA-EXAMPLE"
>Example 31-8</A
> contains some examples on
how to process binary data using the PostgreSQL <ACRONYM
CLASS="ACRONYM"
>JDBC</ACRONYM
> driver.
</P
><DIV
CLASS="EXAMPLE"
><A
NAME="JDBC-BINARY-DATA-EXAMPLE"
></A
><P
><B
>Example 31-8. Processing Binary Data in <ACRONYM
CLASS="ACRONYM"
>JDBC</ACRONYM
></B
></P
><P
> For example, suppose you have a table containing the file names of
images 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, <CODE
CLASS="FUNCTION"
>setBinaryStream()</CODE
> 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 <CODE
CLASS="FUNCTION"
>setBytes()</CODE
>
method if the contents of the image was already in a
<CODE
CLASS="CLASSNAME"
>byte[]</CODE
>.
</P
><P
> Retrieving an image is even easier. (We use
<CODE
CLASS="CLASSNAME"
>PreparedStatement</CODE
> here, but the
<CODE
CLASS="CLASSNAME"
>Statement</CODE
> 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 data in some way here
}
rs.close();
}
ps.close();</PRE
><P>
</P
><P
> Here the binary data was retrieved as an
<CODE
CLASS="CLASSNAME"
>byte[]</CODE
>. You could have used a
<CODE
CLASS="CLASSNAME"
>InputStream</CODE
> object instead.
</P
><P
> Alternatively you could be storing a very large file and want to use
the <CODE
CLASS="CLASSNAME"
>LargeObject</CODE
> <ACRONYM
CLASS="ACRONYM"
>API</ACRONYM
> 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 block
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 writing
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 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();</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
<ACRONYM
CLASS="ACRONYM"
>JDBC</ACRONYM
> <ACRONYM
CLASS="ACRONYM"
>API</ACRONYM
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
