Re: Possible bug with BYTEA and 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--

On Sat, 13 Feb 2010, Gregory Kotsaftis wrote:

> postgresql-8.4-701.jdbc4.jar
> postgresql-8.5alpha3
>
> I followed the steps in the blob tutorial and tried to test the BYTEA
> example:

The 8.5/9.0 release has changed the default bytea output format and the
8.4 JDBC driver does not support it. You've got a couple of options:

1) Change the bytea format back to the 8.4 supported format. Set
bytea_output = escape in your postgresql.conf.

2) Build the JDBC driver from CVS which does support the new format.

3) Use an 8.4 server.

Kris Jurka

I saw this on the bugs list first and replied there, but I figured I ought
to repost it here as well...

On Sun, 14 Feb 2010, Kris Jurka wrote:

> On Sat, 13 Feb 2010, Gregory Kotsaftis wrote:
>
>> postgresql-8.4-701.jdbc4.jar
>> postgresql-8.5alpha3
>>
>> I followed the steps in the blob tutorial and tried to test the BYTEA
>> example:
>
> The 8.5/9.0 release has changed the default bytea output format and the 8.4
> JDBC driver does not support it. You've got a couple of options:
>
> 1) Change the bytea format back to the 8.4 supported format. Set
> bytea_output = escape in your postgresql.conf.
>
> 2) Build the JDBC driver from CVS which does support the new format.
>
> 3) Use an 8.4 server.
>
> Kris Jurka
>

Kris you are without a doubt a life saver!
Being new to blobs altogether, this issue gave me a really hard time for more than a week! Thanks for the possible solutions (I will probably go with 2 or 1).
Thank you very much (and I REALLY mean it!)
Greg :)

--- On Sun, 2/14/10, Kris Jurka <books(at)ejurka(dot)com> wrote:

> From: Kris Jurka <books(at)ejurka(dot)com>
> Subject: Re: [BUGS] Possible bug with BYTEA and JDBC
> To: "Gregory Kotsaftis" <gregkotsaftis(at)yahoo(dot)com>
> Cc: pgsql-bugs(at)postgresql(dot)org
> Date: Sunday, February 14, 2010, 4:42 PM
>
>
> On Sat, 13 Feb 2010, Gregory Kotsaftis wrote:
>
> > postgresql-8.4-701.jdbc4.jar
> > postgresql-8.5alpha3
> >
> > I followed the steps in the blob tutorial and tried to
> test the BYTEA example:
>
> The 8.5/9.0 release has changed the default bytea output
> format and the 8.4 JDBC driver does not support it. 
> You've got a couple of options:
>
> 1) Change the bytea format back to the 8.4 supported
> format.  Set bytea_output = escape in your
> postgresql.conf.
>
> 2) Build the JDBC driver from CVS which does support the
> new format.
>
> 3) Use an 8.4 server.
>
> Kris Jurka
>