Thread: How to retieve binary data (bytea) without problem ?

Hi friends, greetings :-)

I'm using PostgreSQL v7.4.7 with JDBC driver version 8.0 Build 312 in a Java Web Deveploment.

I have a table named 'attachedfiles' in which there is a column used to store binary data (bytea type) (pdf's files, doc, png, jpg, what ever...).

I have stored, without troubles, binary data reaching 30 MB.

To retrieve the binary data I'm using the following Java code:

PreparedStatement ps = this.con.prepareStatement("SELECT contentfile from attachedfiles where filename = ? ", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setString(1,"UsingJDBC.pdf");          
bytes filebinary[] = null;                       
ResultSet rs = ps.executeQuery();
if (rs != null)
{
      while (rs.next()) //Only one row
      {                                            
                filebinary = rs.getBytes(1);  //Obtain the file...
       }
      rs.close();
}
ps.close();


Problem:
If the binary data that I want to retrieve, is up to 12 MB, I obtain a java.lang.OutOfMemoryError: Java Heap Space.

I'm using NetBeans 4.1 and a I have increased the JVM heap size uing the following comand at momento to start Netbeans IDE

$ ./netbeans -J-Xmx300m

but this don't work.

How could I overcome this error ?

Thanks in advance by your help and support.

Kind Regards.-
Alfredo Rico.

Alfredo Rico wrote:
> Hi friends, greetings :-)
>
> I'm using PostgreSQL v7.4.7 with JDBC driver version 8.0 Build 312 in a Java
> Web Deveploment.
>
> I have a table named 'attachedfiles' in which there is a column used to
> store binary data (bytea type) (pdf's files, doc, png, jpg, what ever...).
>
> I have stored, without troubles, binary data reaching 30 MB.
>
> To retrieve the binary data I'm using the following Java code:
>
> PreparedStatement ps = this.con.prepareStatement("SELECT contentfile from
> attachedfiles where filename = ? ", ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_READ_ONLY);
> ps.setString(1,"UsingJDBC.pdf");
> bytes filebinary[] = null;
> ResultSet rs = ps.executeQuery();
> if (rs != null)
> {
> while (rs.next()) //Only one row
> {
> filebinary = rs.getBytes(1); //Obtain the file...
> }
> rs.close();
> }
> ps.close();
>
>
> Problem:
> If the binary data that I want to retrieve, is up to 12 MB, I obtain a
> java.lang.OutOfMemoryError: Java Heap Space.

Hi Alfredo,

I am not an expert and am relatively new to this list but perhaps a
different approach rather than using getBytes which loads the whole 12M
into memory would work.  Have you tried something like this?

...
int bytes_read = 0;
byte[] buf = new byte[ 8192 ];
ServletOutputStream outs = servletRequest.getOutputStream();
InputStream ins = rs.getBinaryStream( 1 );

while( ( bytes_read = ins.read( buf ) ) != -1 )
{
    outs.write( buf, 0, bytes_read );
}

outs.close();
ins.close();
...

This may be less memory intensive than pulling the whole binary field
into memory at once.

Cheers,
Russ

Hi, Russell.
Your suggest is very correct and in fact it works very well but only if the file size is under 10 MB,
The problem happens just at moment to execute:
InputStream ins = rs.getBinaryStream( 1 );

I don't know how to overcome this :-(

On Thu, 29 Sep 2005, Alfredo Rico wrote:

> Your suggest is very correct and in fact it works very well but only if the
> file size is under 10 MB,
> The problem happens just at moment to execute:
> InputStream ins = rs.getBinaryStream( 1 );
>

The JDBC driver can stream data going to the server (setBinaryStream), but
cannot stream data returning from the server (getBinaryStream) because of
protocol limitations.  You have two options:

Switch from using bytea to large objects (Blobs).  Large objects were
designed to work with much larger data sizes and have a complete streaming
interface.

Increase the JVM's heap size.  Since you are using netbeans and you
haven't received many helpful suggestions here, you might try a netbeans
specific list for suggestions on how to accomplish this.

Kris Jurka

Thank you Kris, I was absolutely confused about this situation.

I'll switch to Blob objects because increasing the JVM heap could harm perfromance...

Kind Regards.
Alfredo Rico,