Re: [Fwd: Re: [Fwd: Re: Problems with truncated BLOB]] - Mailing list pgsql-jdbc

Barry,

This program perfectly illustrates the bug for me (using both latest
drivers from website - uploaded 25/11 - and 7.1 drivers).

Here's some sample output:

[mike@frodo tmp]$ java BlobTest postgresql.jar
Connected to database system.

file.name: postgresql.jar file.length: 95059
File.length(int): 95059
Trying to write: postgresql.jartest
We have a result!
We have a result!
bytes written: 476

(used the postgresql.jar as a test binary file ;)).

It seems to work ok with text files, but not with binary files. I'd say
there's definitely a bug here somewhere.

Postgres DB version is 7.1.3, Linux 2.4, RH 7.2

-mike

On Tue, 2001-11-27 at 08:18, Barry Lind wrote:
> Mihael,
>
> I'll include the attachments this time.
>
> thanks,
> --Barry
>
> -------- Original Message --------
> Subject: Re: [Fwd: Re: Problems with truncated BLOB]
> Date: Mon, 26 Nov 2001 12:27:58 -0800
> From: Barry Lind <barry@xythos.com>
> To: miqster@gmx.net
> CC: pgsql-jdbc@postgresql.org
> References: <3C028151.8020301@xythos.com>
>
> Mihael,
>
> I have been able to reproduce your problem with the 7.1 drivers, but not
> with the 7.2 drivers.  It appears that this is bug is already fixed in
> current sources.
>
> Note however that the functionality around get/setBinaryStream() has
> changed in 7.2.  In 7.2 the default behavior is that binary data is
> assumed to be in a column of type bytea.  So you would create a table
> as: create table blobs (filename text, data bytea) instead of using the
> OID datatype as in 7.1 (create table blobs (filename text, data OID)).
> For backward compatibility you can revert the driver back to the old
> behavior of using LargeObjects (i.e. OIDs) by passing the compatible=7.1
> parameter to the connection. (This can be done by addint
> '?compatible=7.1' to the end of the jdbc connection url.
>
> Attached are modified versions of the test case you sent that work for
> me using the 7.2 drivers (but do show the bug using the 7.1 drivers).
> The first (BlobTest.java) uses the new functionality and requires that
> the table 'blobs' have a column named 'data' of type 'bytea'.  The
> second (BlobTest2.java) uses the backward compatible mode and requires
> that the table 'blobs2' have a column named 'data' of type 'oid'.
>
> thanks,
> --Barry
>
> Barry Lind wrote:
>
>  > Forwarding to the jdbc list in case someone else has time to look into
>  > this bug before I get around to it.
>  >
>  > thanks,
>  > --Barry
>  >
>  > -------- Original Message --------
>  > Subject: Re: [JDBC] Problems with truncated BLOB
>  > Date: 26 Nov 2001 17:37:05 +0100
>  > From: Mihael Vrbanec <miqster@gmx.net>
>  > To: Barry Lind <barry@xythos.com>
>  > References: <1006737949.367.2.camel@Caprice>
> <3C01A2AA.2030902@xythos.com>
>  >
>  > Hi Barry,
>  >
>  >  > Could you send a test case that demonstrates the problem?  Ideally a
>  >  > sample java program that will upload one of the problem files, and
> then
>  >  > another program that tries to download the file showing the
> problem you
>  >  > have having.
>  >
>  > I have attached a sample program that does both. It uses a table called
>  > blobs with two columns (filename, data). I experience the problem with
>  > all binary files I have tried (.doc, .png, .bmp, .tar.gz).
>  > HTML, and .txt do not have these problems.
>  >
>  >  > Also, which version of the jdbc driver are you using?  (the
> version that
>  >  > came with 7.1, or something newer)
>  >
>  > The jdbc-driver is the one shipped with 7.1.2/3 (i compiled the whole
>  > thing from source).
>  >
>  > many thanx in advance
>  >
>  > bxe Miq
>  >
>  >
>  > ------------------------------------------------------------------------
>  >
>  > import java.io.*;
>  > import java.util.*;
>  > import java.sql.*;
>  >
>  >
>  > public class BlobTest {
>  >
>  >     private Connection con = null;
>  >
>  >     public BlobTest() {
>  >         try {
>  >             Class.forName("org.postgresql.Driver");
>  >             con = DriverManager.getConnection(
>  >                     "jdbc:postgresql:repository", "candle", "");
>  >             if (con != null) {
>  >                System.out.println("Connected to database system.\n");
>  >             }
>  >         } catch (SQLException e) {
>  >             System.out.println(e.getMessage());
>  >             System.out.println("Could not connect to database
> system.\n");
>  >         } catch (ClassNotFoundException e) {
>  >             System.out.println("Class not found...:-(\n");
>  >         }
>  >     }
>  >
>  >     private void store(String filename) {
>  >         PreparedStatement ps = null;
>  >         ResultSet r = null;
>  >         File file = new File(filename);
>  >         System.out.println("file.name:" + file.getName() +
> "file.length:"+file.length());
>  >         try {
>  >             FileInputStream fis = new FileInputStream(file);
>  >             con.setAutoCommit(false);
>  >             ps = con.prepareStatement(
>  >                 "INSERT INTO blobs VALUES (?, ?)");
>  >             ps.setString(1, filename);
>  >             System.out.println("File.length(int): " + (int)
> file.length());
>  >             ps.setBinaryStream(2, fis, (int) file.length());
>  >             ps.executeUpdate();
>  >             ps.close();
>  >             fis.close();
>  >             con.commit();
>  >         } catch (SQLException sqle) {
>  >             System.err.println("Store content: " + sqle.getMessage());
>  >         } catch (IOException ioe) {
>  >         }
>  >     }
>  >
>  >     private void retrieve(String filename) {
>  >         Statement s = null;
>  >         ResultSet r = null;
>  >         int byteSum = 0;
>  >         int bytesRead = 0;
>  >         byte[] buffer = new byte[8 * 1924];
>  >         try {
>  >             System.out.println("Trying to write: " +filename + "test");
>  >             FileOutputStream out = new FileOutputStream(filename +
> "test");
>  >             con.setAutoCommit(false);
>  >             s = con.createStatement();
>  >             r = s.executeQuery("SELECT data FROM blobs WHERE filename
> = '"
>  >                                + filename +"'");
>  >             if (r != null) {
>  >                 while (r.next()) {
>  >                     System.out.println("We have a result!");
>  >                     InputStream is = r.getBinaryStream(1);
>  >                     while ((bytesRead = is.read(buffer)) != -1) {
>  >                         byteSum += bytesRead;
>  >                         out.write(buffer, 0, bytesRead);
>  >                     }
>  >                     is.close();
>  >                 }
>  >             }
>  >             out.close();
>  >             System.out.println("bytes written: " + byteSum);
>  >             con.commit();
>  >         } catch (SQLException sqle) {
>  >             System.err.println("Retrieve content: " + sqle.getMessage());
>  >         } catch (Exception ioe) {
>  >             System.err.println("Writing stuff: " + ioe.getMessage());
>  >         }
>  >     }
>  >
>  >     public static void main(String[] args) {
>  >         BlobTest bt = new BlobTest();
>  >         bt.store(args[0]);
>  >         bt.retrieve(args[0]);
>  >     }
>  >
>  > }
>  >
>  >
>  > ------------------------------------------------------------------------
>  >
>  >
>  > ---------------------------(end of broadcast)---------------------------
>  > TIP 6: Have you searched our list archives?
>  >
>  > http://archives.postgresql.org
>  >
>
>
> ----
>

> import java.io.*;
> import java.util.*;
> import java.sql.*;
>
>
> public class BlobTest {
>
>     private Connection con = null;
>
>     public BlobTest() {
>         try {
>             Class.forName("org.postgresql.Driver");
>             con = DriverManager.getConnection(
>                     "jdbc:postgresql://localhost:5432/files", "blind", "");
>             if (con != null) {
>                System.out.println("Connected to database system.\n");
>             }
>         } catch (SQLException e) {
>             System.out.println(e.getMessage());
>             System.out.println("Could not connect to database system.\n");
>         } catch (ClassNotFoundException e) {
>             System.out.println("Class not found...:-(\n");
>         }
>     }
>
>     private void store(String filename) {
>         PreparedStatement ps = null;
>         ResultSet r = null;
>         File file = new File(filename);
>         System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
>         try {
>             FileInputStream fis = new FileInputStream(file);
>             con.setAutoCommit(false);
>             ps = con.prepareStatement(
>                 "INSERT INTO blobs VALUES (?, ?)");
>             ps.setString(1, filename);
>             System.out.println("File.length(int): " + (int) file.length());
>             ps.setBinaryStream(2, fis, (int) file.length());
>             ps.executeUpdate();
>             ps.close();
>             fis.close();
>             con.commit();
>         } catch (SQLException sqle) {
>             System.err.println("Store content: " + sqle.getMessage());
>         } catch (IOException ioe) {
>         }
>     }
>
>     private void retrieve(String filename) {
>         Statement s = null;
>         ResultSet r = null;
>         int byteSum = 0;
>         int bytesRead = 0;
>         byte[] buffer = new byte[8 * 1924];
>         try {
>             System.out.println("Trying to write: " +filename + "test");
>             FileOutputStream out = new FileOutputStream(filename + "test");
>             con.setAutoCommit(false);
>             s = con.createStatement();
>             r = s.executeQuery("SELECT data FROM blobs WHERE filename = '"
>                                + filename +"'");
>             if (r != null) {
>                 while (r.next()) {
>                     System.out.println("We have a result!");
>                     InputStream is = r.getBinaryStream(1);
>                     while ((bytesRead = is.read(buffer)) != -1) {
>                         byteSum += bytesRead;
>                         out.write(buffer, 0, bytesRead);
>                     }
>                     is.close();
>                 }
>             }
>             out.close();
>             System.out.println("bytes written: " + byteSum);
>             con.commit();
>         } catch (SQLException sqle) {
>             System.err.println("Retrieve content: " + sqle.getMessage());
>         } catch (Exception ioe) {
>             System.err.println("Writing stuff: " + ioe.getMessage());
>         }
>     }
>
>     public static void main(String[] args) {
>         BlobTest bt = new BlobTest();
>         bt.store(args[0]);
>         bt.retrieve(args[0]);
>     }
>
> }
> ----
>

> import java.io.*;
> import java.util.*;
> import java.sql.*;
>
>
> public class BlobTest2 {
>
>     private Connection con = null;
>
>     public BlobTest2() {
>         try {
>             Class.forName("org.postgresql.Driver");
>             con = DriverManager.getConnection(
>                     "jdbc:postgresql://localhost:5432/files?compatible=7.1", "blind", "");
>             if (con != null) {
>                System.out.println("Connected to database system.\n");
>             }
>         } catch (SQLException e) {
>             System.out.println(e.getMessage());
>             System.out.println("Could not connect to database system.\n");
>         } catch (ClassNotFoundException e) {
>             System.out.println("Class not found...:-(\n");
>         }
>     }
>
>     private void store(String filename) {
>         PreparedStatement ps = null;
>         ResultSet r = null;
>         File file = new File(filename);
>         System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
>         try {
>             FileInputStream fis = new FileInputStream(file);
>             con.setAutoCommit(false);
>             ps = con.prepareStatement(
>                 "INSERT INTO blobs2 VALUES (?, ?)");
>             ps.setString(1, filename);
>             System.out.println("File.length(int): " + (int) file.length());
>             ps.setBinaryStream(2, fis, (int) file.length());
>             ps.executeUpdate();
>             ps.close();
>             fis.close();
>             con.commit();
>         } catch (SQLException sqle) {
>             System.err.println("Store content: " + sqle.getMessage());
>         } catch (IOException ioe) {
>         }
>     }
>
>     private void retrieve(String filename) {
>         Statement s = null;
>         ResultSet r = null;
>         int byteSum = 0;
>         int bytesRead = 0;
>         byte[] buffer = new byte[8 * 1924];
>         try {
>             System.out.println("Trying to write: " +filename + "test");
>             FileOutputStream out = new FileOutputStream(filename + "test");
>             con.setAutoCommit(false);
>             s = con.createStatement();
>             r = s.executeQuery("SELECT data FROM blobs2 WHERE filename = '"
>                                + filename +"'");
>             if (r != null) {
>                 while (r.next()) {
>                     System.out.println("We have a result!");
>                     InputStream is = r.getBinaryStream(1);
>                     while ((bytesRead = is.read(buffer)) != -1) {
>                         byteSum += bytesRead;
>                         out.write(buffer, 0, bytesRead);
>                     }
>                     is.close();
>                 }
>             }
>             out.close();
>             System.out.println("bytes written: " + byteSum);
>             con.commit();
>         } catch (SQLException sqle) {
>             System.err.println("Retrieve content: " + sqle.getMessage());
>         } catch (Exception ioe) {
>             System.err.println("Writing stuff: " + ioe.getMessage());
>         }
>     }
>
>     public static void main(String[] args) {
>         BlobTest2 bt = new BlobTest2();
>         bt.store(args[0]);
>         bt.retrieve(args[0]);
>     }
>
> }
> ----
>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Cheers,
Mike

--
Mike Cannon-Brookes :: mike@atlassian.com

Atlassian :: http://www.atlassian.com
     Supporting YOUR J2EE World