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

Mike,

I am really at a loss to explain what is going on here.  I just
resurected a 7.1.2 database and ran the test and it worked fine (for
both the bytea, and LargeObject versions using the 7.2 driver).

thanks,
--Barry

Mike Cannon-Brookes wrote:

> Barry,
>
> I'm definitely just using the new driver, here's my classpath:
>
> /usr/java/jdk1.3.1/lib/tools.jar:.:/opt/orion/lib/jdbc7.2dev-1.2.jar
>
> (downloaded the jdbc jar from jdbc.postgresql.org this morning)
>
> Might I suggest it's something to do with bytea support from 7.1 to
> 7.2b3? ;)
>
> -mike
>
> On Tue, 2001-11-27 at 13:13, Barry Lind wrote:
>
>>Mike,
>>
>>When testing with the latest 7.2 drivers are you sure you are using the
>>latest drivers and not picking up an older version of the driver
>>somewhere else in your classpath?  I can't reproduce the bug using the
>>latest drivers but can reproduce with 7.1 drivers (see below for my
>>successful results)
>>
>>thanks,
>>--Barry
>>
>>[blind@barry work]$ CLASSPATH=.:./postgresql.jar; export CLASSPATH
>>[blind@barry work]$ java BlobTest postgresql.jar
>>Connected to database system.
>>
>>file.name:postgresql.jarfile.length:116228
>>File.length(int): 116228
>>Trying to write: postgresql.jartest
>>We have a result!
>>bytes written: 116228
>>[blind@barry work]$
>>
>>The only thing different in my environment is that I am running 7.2b2 on
>>the server instead of 7.1.3.  Otherwise I am also using RedHat 7.2 and
>>2.4 kernel.
>>
>>
>>
>>
>>
>>
>>Mike Cannon-Brookes wrote:
>>
>>
>>>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)
>>>>
>>>>
>>