Java Database Connectivity (JDBC)

Hi all,

I have a problem with PostgreSQL and BLOBs.

Small BLOBs work, but with BLOBs of a certain size my code throws a java.lang.OutOfMemoryError.
The file I tried has about 2-3 MB.


Environment:
- PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
- Mandrake 8.2
- 192 MB RAM
- blob column created as datatype "bytea" (maybe that is the problem)
- code posted below (BlobPostgresTest.java)
- console output posted below

- java command to run program:

    java -verbose:gc -Xmx128m -classpath .:pgjdbc2.jar BlobPostgresTest

- java version as follows from "java -version" command:

    java version "1.4.0_01"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.0_01-b03)
    Java HotSpot(TM) Client VM (build 1.4.0_01-b03, mixed mode)

do you have an idea how to solve this problem ?


Console output (including GC activities):
=========================================

java -verbose:gc -Xmx128m -classpath .:pgjdbc2.jar BlobPostgresTest

BLOB/PostgreSQL Demo started
[GC 511K->230K(1984K), 0.0163708 secs]
preparing statement
setting binary stream
[GC 402K->273K(1984K), 0.0092325 secs]
[Full GC 273K->273K(1984K), 0.0771294 secs]
[GC 4351K->4058K(4908K), 0.0046329 secs]
[Full GC 4058K->3770K(4908K), 0.0831070 secs]
[GC 4922K->4922K(6860K), 0.0009556 secs]
[Full GC 4922K->4346K(6860K), 0.0890570 secs]
[GC 6650K->6650K(7820K), 0.0011110 secs]
[Full GC 6650K->5496K(7820K), 0.1420860 secs]
[GC 10104K->10104K(14480K), 0.0012827 secs]
[Full GC 10104K->7800K(14480K), 0.1236744 secs]
[GC 17016K->17016K(24084K), 0.0015421 secs]
[Full GC 17016K->12408K(24084K), 0.1688843 secs]
[GC 30840K->30840K(43224K), 0.0020800 secs]
[Full GC 30840K->21624K(43224K), 0.2547274 secs]
[GC 76920K->74616K(81500K), 0.0041685 secs]
[Full GC 74616K->49272K(81500K), 0.5688448 secs]
[GC 67704K->67704K(88332K), 0.0033407 secs]
[Full GC 67704K->58488K(88332K), 0.2558231 secs]
executing update
[GC 95352K->95352K(104844K), 0.0932741 secs]
[Full GC 95352K->40056K(104844K), 0.9644251 secs]
[GC 69245K->69245K(104844K), 0.0036631 secs]
[Full GC 69245K->69245K(104844K), 0.0814962 secs]
[Full GC 69245K->66324K(129728K), 1.1439123 secs]
Exception in thread "main" java.lang.OutOfMemoryError



BlobPostgresTest.java
=====================

import java.sql.*;
import java.io.*;

/*

drop table blobdemo;

drop sequence blobdemo_id_seq;

create table blobdemo(
  id serial not null primary key,
  name varchar(50),
  content bytea);

*/

public final class BlobPostgresTest {
    private final static String NAME = "TEST";
    //private final static String FILE_NAME = "/tmp/blob/2mb.xxx";
    private final static String FILE_NAME = "BlobPostgresTest.java";

    public final static void main(String[] args) throws Throwable {
	Connection con = null;
	Statement statement = null;
	PreparedStatement insertStatement = null;
	ResultSet rs = null;
	File file = null;
	FileInputStream fis = null;
	BufferedInputStream bis = null;

	try {
	    System.out.println("BLOB/PostgreSQL Demo started");

	    Class.forName("org.postgresql.Driver");

	    con = DriverManager.getConnection
		("jdbc:postgresql://localhost/template1",
		 "postgres", "");
	    con.setAutoCommit(true);

	    statement = con.createStatement();

	    // cleanup
	    statement.executeUpdate("delete from blobdemo");

	    // file
	    file = new File(FILE_NAME);
	    fis = new FileInputStream(file);
	    bis = new BufferedInputStream(fis);

	    // insert one record
	    System.out.println("preparing statement");
	    insertStatement = con.prepareStatement
		("insert into blobdemo ( name, content ) values ( ?, ? )");

	    insertStatement.setString(1, NAME);

	    System.out.println("setting binary stream");
	    insertStatement.setBinaryStream(2, bis, (int)file.length());

	    System.out.println("executing update");
	    insertStatement.executeUpdate();

	    // retrieve
	    rs = statement.executeQuery
		("select id, name, content from blobdemo");

	    while(rs.next()) {
		System.out.println("id=" + rs.getObject(1));
		System.out.println("name=" + rs.getObject(2));

		byte[] bytes = rs.getBytes(3);
		String content = new String(bytes);

		//System.out.println(content);
		System.out.println("retrieved " + bytes.length +  " bytes");
	    }
	} finally {

	    if(rs != null) rs.close();
	    if(statement != null) statement.close();
	    if(insertStatement != null) insertStatement.close();
	    if(con != null) con.close();

	    if(fis != null) fis.close();
	    if(bis != null) bis.close();
	}

	System.out.println("BLOB/PostgreSQL Demo complete");
    }
}