Possible bug with BYTEA and JDBC

From: Gregory Kotsaftis <gregkotsaftis(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Possible bug with BYTEA and JDBC
Date: 2010-02-13 11:41:25
Message-ID: 245984.68614.qm@web35304.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-jdbc

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 Manager for 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-editor for 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 corruption issue?

Regards
Greg--

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kris Jurka 2010-02-14 14:42:31 Re: Possible bug with BYTEA and JDBC
Previous Message Tom Lane 2010-02-13 00:14:49 Re: BUG #5325: Timestamp w/ timezone + interval not functioning correctly

Browse pgsql-jdbc by date

  From Date Subject
Next Message Gregory Kotsaftis 2010-02-13 11:48:55 Possible bug with BYTEA and JDBC
Previous Message Oliver Jowett 2010-02-12 21:40:01 Re: Timestamp confusion