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

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]);
    }

}