Re: How to retieve binary data (bytea) without problem ? - Mailing list pgsql-jdbc

From Alfredo Rico
Subject Re: How to retieve binary data (bytea) without problem ?
Date
Msg-id 89cc056d05092906521f72f367@mail.gmail.com
Whole thread Raw
In response to Re: How to retieve binary data (bytea) without problem ?  (Russell Francis <rfrancis@ev.net>)
Responses Re: How to retieve binary data (bytea) without problem ?  (Kris Jurka <books@ejurka.com>)
List pgsql-jdbc
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:
> 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



pgsql-jdbc by date:

Previous
From: Russell Francis
Date:
Subject: queries against CIDR fail against 8.0.3?
Next
From: Kris Jurka
Date:
Subject: Re: setObject(col, blob) throws PSQLException