Skip site navigation (1) Skip section navigation (2)

Possible bug with BYTEA and JDBC

From: Gregory Kotsaftis <gregkotsaftis(at)yahoo(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Possible bug with BYTEA and JDBC
Date: 2010-02-13 11:48:55
Message-ID: 111275.59136.qm@web35301.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
(ALSO POSTED AT pgsql-bugs(at)postgresql(dot)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 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

pgsql-jdbc by date

Next:From: dmpDate: 2010-02-13 18:10:40
Subject: Re: Possible bug with BYTEA and JDBC
Previous:From: Gregory KotsaftisDate: 2010-02-13 11:41:25
Subject: Possible bug with BYTEA and JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group