Thread: Problem with bytea getBytes and setBytes
I am trying to use a bytea column to store binary data, but the getBytes
and setBytes methods aren't working as expected.
I suspect a strange escaping occuring.
I ran the following code :
// insert test value
PreparedStatement stmtInsert connection.prepareStatement("insert into
test_blob (colblob) values(?)");
byte testBytes[] = new byte[255];
for (int i=0;i<255;i++)
testBytes[i]=(byte)(i-128);
stmtInsert.setBytes(1,testBytes);
int count = stmtInsert.executeUpdate();
stmtInsert.close();
stmtInsert = null;
// read and compare test value
PreparedStatement stmtSelect =connection.prepareStatement("select
colblob from test_blob");
ResultSet rs = stmtSelect.executeQuery();
while (rs.next()){
byte resByte[]=rs.getBytes(1);
for (int i=0;i<255;i++){
if (resByte[i]!=testBytes[i])
System.out.println("Problem with byte "+i + " "+resByte[i]+"
different from original " + testBytes[i] );
}
}
rs.close();
The results (truncated) :
Problem with byte 32 -62 different from original -96
Problem with byte 33 -96 different from original -95
Problem with byte 34 -62 different from original -94
Problem with byte 35 -95 different from original -93
Problem with byte 36 -62 different from original -92
Problem with byte 37 -94 different from original -91
Problem with byte 38 -62 different from original -90
...
Xavier Poinsard.
Xavier,
You don't mention what versions you are using, but I suspect you are
using a 7.3 server. If so this is a known bug in the 7.3 database. The
bug is that the server is incorrectly doing character set translation
for binary values (i.e. the bytea data). The work around for this bug
is to set the character set of your database to UNICODE. Unfortunately
you can't change the character set after the database is created, so you
will need to recreate your database.
thanks,
--Barry
Xavier Poinsard wrote:
> I am trying to use a bytea column to store binary data, but the getBytes
> and setBytes methods aren't working as expected.
> I suspect a strange escaping occuring.
> I ran the following code :
>
> // insert test value
> PreparedStatement stmtInsert connection.prepareStatement("insert into
> test_blob (colblob) values(?)");
> byte testBytes[] = new byte[255];
> for (int i=0;i<255;i++)
> testBytes[i]=(byte)(i-128);
> stmtInsert.setBytes(1,testBytes);
> int count = stmtInsert.executeUpdate();
> stmtInsert.close();
> stmtInsert = null;
>
> // read and compare test value
> PreparedStatement stmtSelect =connection.prepareStatement("select
> colblob from test_blob");
> ResultSet rs = stmtSelect.executeQuery();
> while (rs.next()){
> byte resByte[]=rs.getBytes(1);
> for (int i=0;i<255;i++){
> if (resByte[i]!=testBytes[i])
> System.out.println("Problem with byte "+i + " "+resByte[i]+"
> different from original " + testBytes[i] );
> }
> }
> rs.close();
>
> The results (truncated) :
> Problem with byte 32 -62 different from original -96
> Problem with byte 33 -96 different from original -95
> Problem with byte 34 -62 different from original -94
> Problem with byte 35 -95 different from original -93
> Problem with byte 36 -62 different from original -92
> Problem with byte 37 -94 different from original -91
> Problem with byte 38 -62 different from original -90
> ...
>
> Xavier Poinsard.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
Barry Lind wrote:
> Xavier,
>
> You don't mention what versions you are using, but I suspect you are
> using a 7.3 server. If so this is a known bug in the 7.3 database. The
> bug is that the server is incorrectly doing character set translation
> for binary values (i.e. the bytea data). The work around for this bug
> is to set the character set of your database to UNICODE. Unfortunately
> you can't change the character set after the database is created, so you
> will need to recreate your database.
You are right, I am using a 7.3.2 server and a 7.4beta4 jdbc driver.
>
> thanks,
> --Barry
>
>
> Xavier Poinsard wrote:
>
>> I am trying to use a bytea column to store binary data, but the getBytes
>> and setBytes methods aren't working as expected.
>> I suspect a strange escaping occuring.
>> I ran the following code :
>>
>> // insert test value
>> PreparedStatement stmtInsert connection.prepareStatement("insert into
>> test_blob (colblob) values(?)");
>> byte testBytes[] = new byte[255];
>> for (int i=0;i<255;i++)
>> testBytes[i]=(byte)(i-128);
>> stmtInsert.setBytes(1,testBytes);
>> int count = stmtInsert.executeUpdate();
>> stmtInsert.close();
>> stmtInsert = null;
>>
>> // read and compare test value
>> PreparedStatement stmtSelect =connection.prepareStatement("select
>> colblob from test_blob");
>> ResultSet rs = stmtSelect.executeQuery();
>> while (rs.next()){
>> byte resByte[]=rs.getBytes(1);
>> for (int i=0;i<255;i++){
>> if (resByte[i]!=testBytes[i])
>> System.out.println("Problem with byte "+i + " "+resByte[i]+"
>> different from original " + testBytes[i] );
>> }
>> }
>> rs.close();
>>
>> The results (truncated) :
>> Problem with byte 32 -62 different from original -96
>> Problem with byte 33 -96 different from original -95
>> Problem with byte 34 -62 different from original -94
>> Problem with byte 35 -95 different from original -93
>> Problem with byte 36 -62 different from original -92
>> Problem with byte 37 -94 different from original -91
>> Problem with byte 38 -62 different from original -90
>> ...
>>
>> Xavier Poinsard.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faqs/FAQ.html
>>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
Xavier Poinsard xavier.poinsard@openpricer.com
OpenPricer http://www.openpricer.com/
34, boulevard haussmann 75009 PARIS FRANCE
tél: 33-(0)172712514