Thread: Possible bug with BYTEA and JDBC

(ALSO POSTED AT pgsql-bugs@postgresql.org)
------------------------------------------

Hi,

I am using:

Windows XP Pro SP3 EN
JDK 1.6.0_u18
postgresql-8.4-701.jdbc4.jar
postgresql-8.5alpha3

I followed the steps in the blob tutorial and tried to test the BYTEA example:
http://jdbc.postgresql.org/documentation/head/binary-data.html#binary-data-example
I found out that the BYTEA data that is persisted in the db is corrupt. This is the details:

CREATE TABLE PERSON (
    PERSON_ID    INTEGER        PRIMARY KEY,
    LASTNAME     VARCHAR(32)    NOT NULL,
    FIRSTNAME    VARCHAR(32)    NOT NULL,
    FACE         BYTEA
);
INSERT INTO PERSON(PERSON_ID,LASTNAME,FIRSTNAME,FACE)
VALUES(1,'KOTSAFTIS','GREGORY',NULL);


public byte[] readBinaryFile(File f)
    throws IOException
{
    byte[] bytes = null;
    FileInputStream fin = new FileInputStream(f);
    try
    {
        long length = f.length();
        if( length > Integer.MAX_VALUE )
        {
            throw new IOException("File is too large: " +
                f.getAbsolutePath());
        }

        bytes = new byte[(int)length];
        int numBytes = fin.read(bytes);
        if( numBytes!= length )
        {
            throw new IOException("Could not completely read file: " +
                f.getAbsolutePath());
        }
    }
    finally
    {
        fin.close();
    }

    return( bytes );
}


public void saveBinaryFile(byte[] bytes, File f)
    throws IOException
{
    FileOutputStream fout = new FileOutputStream(f);
    try
    {
        fout.write(bytes);
    }
    finally
    {
        fout.close();
    }
}


// STEP 1: change a person's face photo
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement("UPDATE PERSON SET FACE=? WHERE PERSON_ID=?");
ps.setBytes(1, readBinaryFile(new File("c:/1.jpg")));
ps.setInt(2, 1);
ps.executeUpdate();
ps.close();
con.close();

// STEP 2: export person's face photo
Connection con = Globals.DBMANAGER.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT FACE FROM PERSON WHERE PERSON_ID=?");
ps.setInt(1, 1);
ResultSet rs = ps.executeQuery();
rs.next();
saveBinaryFile(rs.getBytes(1), new File("c:/2.jpg"));
rs.close();
ps.close();
con.close();

After executing the STEP 1, the data in the BYTEA field is completely different than the original. I used "EMS SQL
Managerfor PostgreSQL" to verify this. 

After executing STEP 2, the output file is also corrupt. I used a hex-editor to verify this.

When using "EMS SQL Manager for PostgreSQL" to enter the BYTEA field from a source photo file, the bytes match
(hex-editorfor the source file and EMS hex-edit on the db). 

Can anyone verify this, as I am new this BYTEA/BLOB issue. Am I doing something wrong or is there some JDBC driver
corruptionissue? 

Regards
Greg--

Greg wrote:

>Hi,
>
>I am using:
>
>Windows XP Pro SP3 EN
>JDK 1.6.0_u18
>postgresql-8.4-701.jdbc4.jar
>postgresql-8.5alpha3
>
>I followed the steps in the blob tutorial and tried to test the BYTEA example:
>http://jdbc.postgresql.org/documentation/head/binary-data.html#binary-data-example
>I found out that the BYTEA data that is persisted in the db is corrupt. This is the details:
>

Tested successfully on:

Linux Test
JRE 1.5.0_12
postgresql-8.4-701.jdbc3.jar
PostgreSQL 8.3.3  & PostgreSQL 8.4.1 on Linux

Windows XP Pro Test
JRE 1.6.0
postgresql-8.4-701.jdbc3.jar & postgresql-8.4-701.jdbc4.jar
PostgreSQL 8.3.3 on & PostgreSQL 8.4.1 on Linux

Attached test source and sql files for perhaps someone else to quickly
test in an environment that duplicates your exactly.  The XP case
most closely matches yours beside SP3 and my PostgreSQL server
is not on the Windows & 8.4.5alpha3. Assume yours is on the XP
machine.

danap


--
-- MyJSQLView SQL Dump
-- Version: 3.06
-- WebSite: http://myjsqlview.org
--
-- Host: cindy
-- Generated On: 2010.02.13 AD at 10:52:45 MST
-- SQL version: PostgreSQL 8.4.1
-- Database: key_tables
--

-- ------------------------------------------

--
-- Table structure for table "public"."person"
--

DROP TABLE IF EXISTS "public"."person";
CREATE TABLE "public"."person" (
    "person_id" integer NOT NULL,
    "lastname" varchar(32) NOT NULL,
    "firstname" varchar(32) NOT NULL,
    "face" bytea DEFAULT NULL,
    PRIMARY KEY ("person_id")
);
--
-- MyJSQLView SQL Dump
-- Version: 3.06
-- WebSite: http://myjsqlview.org
--
-- Host: cindy
-- Generated On: 2010.02.13 AD at 10:55:37 MST
-- SQL version: PostgreSQL 8.4.1
-- Database: key_tables
--

-- ------------------------------------------

--
-- Dumping data for table "public"."person"
--

LOCK TABLE "public"."person";
INSERT INTO "public"."person" ("person_id", "lastname", "firstname", "face") VALUES
('1', 'KOTSAFTIS', 'GREGORY', NULL);
//=================================================================
//                   PostgreSQL_JDBC Class
//=================================================================
//
//    This class is used to control the running of the a generic
// class to access the PostgreSQL database.
//
//                  << PostgreSQL_JDBC.java >>
//
//=================================================================
// Copyright (C) 2005-2010 Dana M. Proctor
// Version 1.01 02/13/2010
//
// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License
// as published by the Free Software Foundation; either version
// 2 of the License, or (at your option) any later version. This
// program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty
// of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
// the GNU General Public License for more details. You should
// have received a copy of the GNU General Public License along
// with this program; if not, write to the Free Software Foundation,
// Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
// (http://opensource.org)
//
//=================================================================
// Revision History
// Changes to the code should be documented here and reflected
// in the present version number. Author information should
// also be included with the original copyright author.
//=================================================================
// Version 1.0 12/23/2008 PostgreSQL Connection Main Application.
//         1.1 02/13/2010 Generalized to be Used With Any Test Case.
//
//-----------------------------------------------------------------
//                 danap@dandymadeproductions.com
//=================================================================

//=================================================================
//                PostgreSQL_JDBC Application
//=================================================================

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.io.*;
import java.sql.*;


/**
 * The PostgreSQL class is used to control the running of the a generic class to
 * access the PostgreSQL database. Arguments -debug.
 * @author Dana M. Proctor
 * @version 1.1 02/13/2010
 */

class PostgreSQL_JDBC
{
   // ============================================
   // Creation of the necessary class instance.
   // =============================================

   Connection dbConnection;

   // ==============================================================
   // PostgreSQL_JDBC Constructor
   // ==============================================================

   protected PostgreSQL_JDBC(Connection con)
   {
      // Constructor Instances.
      String userDirectory, osSeparator;
      String inputFileName, outputFileName;
      PreparedStatement ps;

      // Perform Test.
      userDirectory = System.getProperty("user.home");
      osSeparator = System.getProperty("file.separator");
      inputFileName = "1.jpg";
      outputFileName = "2.jpg";

      try
      {
         // Do Insert.

         System.out.println("Reading File From: " + userDirectory + osSeparator);
         ps = con.prepareStatement("UPDATE PERSON SET FACE=? WHERE PERSON_ID=?");
         ps.setBytes(1, readBinaryFile(new File(userDirectory + osSeparator + inputFileName)));
         ps.setInt(2, 1);
         ps.executeUpdate();
         ps.close();


         // Save Bytea.

         System.out.println("Saving File To: " + userDirectory + osSeparator);
         ps = con.prepareStatement("SELECT FACE FROM PERSON WHERE PERSON_ID=?");
         ps.setInt(1, 1);
         ResultSet rs = ps.executeQuery();
         rs.next();
         saveBinaryFile(rs.getBytes(1), new File(userDirectory + osSeparator + outputFileName));
         rs.close();
         ps.close();
         con.close();
      }
      catch (SQLException sqle)
      {
         System.out.println("SQL Exeception" + sqle);
      }
      catch (IOException ioe)
      {
         System.out.println("Failed to Open File." + ioe);
      }
   }

   // ==============================================================
   // Test Methods
   // ==============================================================

   public byte[] readBinaryFile(File f) throws IOException
   {
      byte[] bytes = null;
      FileInputStream fin = new FileInputStream(f);
      try
      {
         long length = f.length();
         if (length > Integer.MAX_VALUE)
         {
            throw new IOException("File is too large: " + f.getAbsolutePath());
         }

         bytes = new byte[(int) length];
         int numBytes = fin.read(bytes);
         if (numBytes != length)
         {
            throw new IOException("Could not completely read file: " + f.getAbsolutePath());
         }
      }
      finally
      {
         fin.close();
      }

      return (bytes);
   }

   public void saveBinaryFile(byte[] bytes, File f) throws IOException
   {
      FileOutputStream fout = new FileOutputStream(f);
      try
      {
         fout.write(bytes);
      }
      finally
      {
         fout.close();
      }
   }

   // ============================================================
   // Main public access point method for instantiating the
   // PostgreSQL_JDBC application. Arguments: database, username,
   // & password.
   // ==============================================================

   public static void main(String[] args) throws SQLException, InstantiationException,
         IllegalAccessException, ClassNotFoundException, InterruptedException
   {
      String host, database, username, password;
      Connection dbConnection;

      // Collect connection properties. and setup connection.

      //host = "cindy";
      host = "localhost";

      if (args.length != 0)
      {
         database = args[0];
         username = (args.length > 1) ? args[1] : null;
         password = (args.length > 2) ? args[2] : null;
      }
      else
      {
         database = "key_tables";
         username = "danap";
         password = "";
      }

      dbConnection = null;
      Class.forName("org.postgresql.Driver").newInstance();
      dbConnection = DriverManager.getConnection("jdbc:postgresql://" + host + "/" + database, username,
         password);
      System.out.println("Connection Created");

      new PostgreSQL_JDBC(dbConnection);

      // Close.
      dbConnection.close();
      System.out.println("Connection Closed");
   }
}

dmp, thanks for your time to make a complete example.
So the only difference is that I use postgresql-8.5alpha3-windows
instead of 8.3 or 8.4 versions. Could anyone else verify that this is a bug of the new version 8.5 which is under
development.I rely heavily on 8.5 due to unaccent() and would appreciate if anyone could verify the bug, so we can
notifypostgres dev team. 
Thanks
Greg--

Greg wrote:

> dmp, thanks for your time to make a complete example.
> So the only difference is that I use postgresql-8.5alpha3-windows
> instead of 8.3 or 8.4 versions. Could anyone else verify that this
> is a bug of the new version 8.5 which is under development. I rely
> heavily on 8.5 due to unaccent() and would appreciate if anyone
> could verify the bug, so we can notify postgres dev team.
> Thanks
> Greg--


Off hand I would said it is either that, 8.5alpha, or with the Win
Platform. Perhaps you should post over to the psql-general, database
list, or search that list for similar problem.

danap.