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


---------------------------(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

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
>
>
>

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

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:
>
> > 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
>