Обсуждение: problem with storing BLOBs larger then 2MB

Hello,
I have this problem. I need to store some files into my DB and there is (I mean) a problem with last (from
jdbc.postgresql.org)stable jdbc driver (stable driver for pgsql 7.2, java2). When I try to store file larger than 2MB
(2132 220), exception 

java.lang.OutOfMemoryError
        <<no stack trace available>>
Exception in thread "main"

occured on line "executeUpdate()".


Thanks for help

Michal Josifek




Client computer:
JDBC driver: last stable from jdbc.postgresql.org
OS: w2k
JDK: Sun 1.3.1_01

Server computer:
DB: stable 7.2
OS: Linux RH 7.2





Table SQL:

CREATE TABLE "files" (
"id" int4 DEFAULT nextval('"files_id_seq"'::text) NOT NULL,
"name" varchar(250),
"size" int4,
"file" bytea,
"note" varchar(50),
CONSTRAINT "files_pkey" PRIMARY KEY ("id")
) WITHOUT OIDS;






Sample code:

/*
 * db_test.java
 *
 */

package tests;

/**
 *
 * @author  Michal
 * @version
 */

import java.util.*;
import java.util.zip.*;
import java.sql.*;
import java.io.*;

public class db_test {
 private Connection conn;

 /** Creates new db_test */
    public db_test() {
    }


 public void writeFile(String strFileName,String strNote) throws java.lang.Exception {

  File fil = new File(strFileName);
  FileInputStream fis = new FileInputStream(fil);
  System.out.println(fil.length());
  PreparedStatement ps;

   ps = this.conn.prepareStatement("insert into files (name,size,file,note) values(?, ?, ?, ?)");
   ps.setString(1,fil.getName());
   ps.setInt(2,(int)fil.length());
   ps.setBinaryStream(3,fis,(int)fil.length());
   ps.setString(4,strNote);

  ps.executeUpdate();  // EXCEPTION LINE !!!!!
  ps.close();
  fis.close();
  this.conn.commit();
 }

 /**
   * @param args the command line arguments
  */
    public static void main (String args[]) throws java.lang.Exception {
  Class.forName("org.postgresql.Driver");

  db_test dbts = new db_test();

  dbts.conn = DriverManager.getConnection("jdbc:postgresql://192.168.30.190:5432/postgres","user","pass");
  dbts.conn.setAutoCommit(false);

  dbts.writeFile("d:/java/openssl.tar.gz","Note");

  dbts.conn.close();


   }

}

Might be a JVM error. There are usually flags to control how
much stack and heap is avail to the JVM. I have set these
on Sun JVM before to higher than default values.

At 02:19 PM 2/13/2002 +0100, Michal Josifek wrote:
>Hello,
>I have this problem. I need to store some files into my DB and there is (I
>mean) a problem with last (from jdbc.postgresql.org) stable jdbc driver
>(stable driver for pgsql 7.2, java2). When I try to store file larger than
>2MB (2 132 220), exception
>
>java.lang.OutOfMemoryError
>         <<no stack trace available>>
>Exception in thread "main"
>
>occured on line "executeUpdate()".
>
>
>Thanks for help
>
>Michal Josifek
>
>
>
>
>Client computer:
>JDBC driver: last stable from jdbc.postgresql.org
>OS: w2k
>JDK: Sun 1.3.1_01
>
>Server computer:
>DB: stable 7.2
>OS: Linux RH 7.2
>
>
>
>
>
>Table SQL:
>
>CREATE TABLE "files" (
>"id" int4 DEFAULT nextval('"files_id_seq"'::text) NOT NULL,
>"name" varchar(250),
>"size" int4,
>"file" bytea,
>"note" varchar(50),
>CONSTRAINT "files_pkey" PRIMARY KEY ("id")
>) WITHOUT OIDS;
>
>
>
>
>
>
>Sample code:
>
>/*
>  * db_test.java
>  *
>  */
>
>package tests;
>
>/**
>  *
>  * @author  Michal
>  * @version
>  */
>
>import java.util.*;
>import java.util.zip.*;
>import java.sql.*;
>import java.io.*;
>
>public class db_test {
>  private Connection conn;
>
>  /** Creates new db_test */
>     public db_test() {
>     }
>
>
>  public void writeFile(String strFileName,String strNote) throws
> java.lang.Exception {
>
>   File fil = new File(strFileName);
>   FileInputStream fis = new FileInputStream(fil);
>   System.out.println(fil.length());
>   PreparedStatement ps;
>
>    ps = this.conn.prepareStatement("insert into files
> (name,size,file,note) values(?, ?, ?, ?)");
>    ps.setString(1,fil.getName());
>    ps.setInt(2,(int)fil.length());
>    ps.setBinaryStream(3,fis,(int)fil.length());
>    ps.setString(4,strNote);
>
>   ps.executeUpdate();  // EXCEPTION LINE !!!!!
>   ps.close();
>   fis.close();
>   this.conn.commit();
>  }
>
>  /**
>    * @param args the command line arguments
>   */
>     public static void main (String args[]) throws java.lang.Exception {
>   Class.forName("org.postgresql.Driver");
>
>   db_test dbts = new db_test();
>
>   dbts.conn =
> DriverManager.getConnection("jdbc:postgresql://192.168.30.190:5432/postgres","user","pass");
>   dbts.conn.setAutoCommit(false);
>
>   dbts.writeFile("d:/java/openssl.tar.gz","Note");
>
>   dbts.conn.close();
>
>
>    }
>
>}
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Try increasing the memory available to the jvm. the switch
is -Xmx<memorysize> as in: "java -Xmx56m myClass". This is a non-standard
option, but I've never encountered a jvm that it didin't work for.

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/


> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Michal Josifek
> Sent: Wednesday, February 13, 2002 8:19 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] problem with storing BLOBs larger then 2MB
>
>
> Hello,
> I have this problem. I need to store some files into my DB and
> there is (I mean) a problem with last (from jdbc.postgresql.org)
> stable jdbc driver (stable driver for pgsql 7.2, java2). When I
> try to store file larger than 2MB (2 132 220), exception
>
> java.lang.OutOfMemoryError
>         <<no stack trace available>>
> Exception in thread "main"
>
> occured on line "executeUpdate()".
>
>
> Thanks for help
>
> Michal Josifek
>
>
>
>
> Client computer:
> JDBC driver: last stable from jdbc.postgresql.org
> OS: w2k
> JDK: Sun 1.3.1_01
>
> Server computer:
> DB: stable 7.2
> OS: Linux RH 7.2
>
>
>
>
>
> Table SQL:
>
> CREATE TABLE "files" (
> "id" int4 DEFAULT nextval('"files_id_seq"'::text) NOT NULL,
> "name" varchar(250),
> "size" int4,
> "file" bytea,
> "note" varchar(50),
> CONSTRAINT "files_pkey" PRIMARY KEY ("id")
> ) WITHOUT OIDS;
>
>
>
>
>
>
> Sample code:
>
> /*
>  * db_test.java
>  *
>  */
>
> package tests;
>
> /**
>  *
>  * @author  Michal
>  * @version
>  */
>
> import java.util.*;
> import java.util.zip.*;
> import java.sql.*;
> import java.io.*;
>
> public class db_test {
>  private Connection conn;
>
>  /** Creates new db_test */
>     public db_test() {
>     }
>
>
>  public void writeFile(String strFileName,String strNote) throws
> java.lang.Exception {
>
>   File fil = new File(strFileName);
>   FileInputStream fis = new FileInputStream(fil);
>   System.out.println(fil.length());
>   PreparedStatement ps;
>
>    ps = this.conn.prepareStatement("insert into files
> (name,size,file,note) values(?, ?, ?, ?)");
>    ps.setString(1,fil.getName());
>    ps.setInt(2,(int)fil.length());
>    ps.setBinaryStream(3,fis,(int)fil.length());
>    ps.setString(4,strNote);
>
>   ps.executeUpdate();  // EXCEPTION LINE !!!!!
>   ps.close();
>   fis.close();
>   this.conn.commit();
>  }
>
>  /**
>    * @param args the command line arguments
>   */
>     public static void main (String args[]) throws java.lang.Exception {
>   Class.forName("org.postgresql.Driver");
>
>   db_test dbts = new db_test();
>
>   dbts.conn =
> DriverManager.getConnection("jdbc:postgresql://192.168.30.190:5432
> /postgres","user","pass");
>   dbts.conn.setAutoCommit(false);
>
>   dbts.writeFile("d:/java/openssl.tar.gz","Note");
>
>   dbts.conn.close();
>
>
>    }
>
> }
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>