Thread: Problem with bytea

Hi

When a fetch binary data from a bytea column the data gets messed up.

Se the attached code, postgresql returns 13744 bytes when it should be only 10000. After studing the output I saw that
bytesrepresenting common characters are fetched ok but bytes representing unusual characters such as special swedish
charactersare messed up. Is the binary data somewhere treated as character data? Could something else be the problem? 

I use RedHat 8.0, Postgresql 7.3.2 and jdbc driver pg73jdbc3.jar released 26:th of may.

Code:

    Connection c = DriverManager.getConnection(jdbc);
    Statement s = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet r;

    byte b[] = new byte[10000];

    for(int i=0;i<b.length;i++)
        b[i] = (byte)(i%256);

    /* Update bytea with 10000 bytes */
    s.executeUpdate("update document set file_=null where documentid=12");
    r = s.executeQuery("select file_,documentid from document where documentid=17");
    r.next();
    r.updateBytes(1,b);
    r.updateRow();

    /* Fetch the bytea value */
    r = s.executeQuery("select file_,documentid from document where documentid=17");
    r.next();
    b = r.getBytes(1);

    /* This outputs 13744 !?!? */
    System.out.println("Length: " + b.length);



Med vänliga hälsningar

Daniel Carlsson
Gimlisoft AB
Email: daniel.carlsson@gimlisoft.se
Tel: 0709-744570, 031-189024

Daniel,

There is a bug in the 7.3 server where it incorectly encodes the binary
data set from the server to the client when the CLIENT_ENCODING
parameter is set.  There are two work arounds for this:

1) Use a database character set of ASCII or UNICODE.  In these character
sets the encoding is a noop and therefore no problem.
2) Use the 7.2 jdbc driver (which didn't rely on the CLIENT_ENCODING
parameter).

thanks,
--Barry


Daniel Carlsson wrote:
> Hi
>
> When a fetch binary data from a bytea column the data gets messed up.
>
> Se the attached code, postgresql returns 13744 bytes when it should be only 10000. After studing the output I saw
thatbytes representing common characters are fetched ok but bytes representing unusual characters such as special
swedishcharacters are messed up. Is the binary data somewhere treated as character data? Could something else be the
problem?
>
> I use RedHat 8.0, Postgresql 7.3.2 and jdbc driver pg73jdbc3.jar released 26:th of may.
>
> Code:
>
>     Connection c = DriverManager.getConnection(jdbc);
>     Statement s = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
>     ResultSet r;
>
>     byte b[] = new byte[10000];
>
>     for(int i=0;i<b.length;i++)
>         b[i] = (byte)(i%256);
>
>     /* Update bytea with 10000 bytes */
>     s.executeUpdate("update document set file_=null where documentid=12");
>     r = s.executeQuery("select file_,documentid from document where documentid=17");
>     r.next();
>     r.updateBytes(1,b);
>     r.updateRow();
>
>     /* Fetch the bytea value */
>     r = s.executeQuery("select file_,documentid from document where documentid=17");
>     r.next();
>     b = r.getBytes(1);
>
>     /* This outputs 13744 !?!? */
>     System.out.println("Length: " + b.length);
>
>
>
> Med vänliga hälsningar
>
> Daniel Carlsson
> Gimlisoft AB
> Email: daniel.carlsson@gimlisoft.se
> Tel: 0709-744570, 031-189024
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Daniel Carlsson wrote:> Hi
>
> When a fetch binary data from a bytea column the data gets messed up.
>
> Se the attached code, postgresql returns 13744 bytes when it should be only 10000. After studing the output I saw
thatbytes representing common characters are fetched ok but bytes representing unusual characters such as special
swedishcharacters are messed up. Is the binary data somewhere treated as character data? Could something else be the
problem?
>

I believe it does.  Can you try and make your client encoding the same as your
server encoding and see if the problem goes away?

Fernando


P.S.: I guess you've meant documentid=17 in the line below:
 >     s.executeUpdate("update document set file_=null where documentid=12");

> I use RedHat 8.0, Postgresql 7.3.2 and jdbc driver pg73jdbc3.jar released 26:th of may.
>
> Code:
>
>     Connection c = DriverManager.getConnection(jdbc);
>     Statement s = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
>     ResultSet r;
>
>     byte b[] = new byte[10000];
>
>     for(int i=0;i<b.length;i++)
>         b[i] = (byte)(i%256);
>
>     /* Update bytea with 10000 bytes */
>     s.executeUpdate("update document set file_=null where documentid=12");
>     r = s.executeQuery("select file_,documentid from document where documentid=17");
>     r.next();
>     r.updateBytes(1,b);
>     r.updateRow();
>
>     /* Fetch the bytea value */
>     r = s.executeQuery("select file_,documentid from document where documentid=17");
>     r.next();
>     b = r.getBytes(1);
>
>     /* This outputs 13744 !?!? */
>     System.out.println("Length: " + b.length);
>
>
>
> Med vänliga hälsningar
>
> Daniel Carlsson
> Gimlisoft AB
> Email: daniel.carlsson@gimlisoft.se
> Tel: 0709-744570, 031-189024
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>



--
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9