PostgreSQL JDBC - How to retieve binary data (bytea) without problem ?

Author

How to retieve binary data (bytea) without problem ?

Alfredo Rico

2005-09-28, 8:25 pm

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.

Russell Francis

2005-09-29, 7:25 am

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

Alfredo Rico

2005-09-29, 9:24 am

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 9/29/05, Russell Francis <rfrancis@ev.net> wrote:
>
> Alfredo Rico wrote:
> Java
> ever...).
> from
>
> 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
>
>
>

Kris Jurka

2005-09-29, 1:23 pm

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alfredo Rico

2005-09-29, 8:24 pm

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,

On 9/29/05, Kris Jurka <books@ejurka.com> wrote:
>
>
>
> On Thu, 29 Sep 2005, Alfredo Rico wrote:
>
> the
>
> 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
>

Sponsored Links