Re: Possible bug with BYTEA and JDBC - Mailing list pgsql-jdbc

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