Re: getBytes() returning too much data

Hi,

I'm having a problem retreiving images from a postgreSQL database using
ResultSet.getBytes() The image is stored in the database as a bytea
type. I'm running java version 1.4.1 and postgreSQL version 7.3.4

When I insert a file it seems to be the correct size inside the database
(checked using SQL - length(image))
However when I use getBytes() or even getBinaryStream() to get the data
back from the database it always returns too much data.
The returned bytes contain at least some of the correct data (perhaps
all) with a load of extra bytes throughout the file. The size increase
is consistent for each file.

Does anyone know what I'm doing wrong and/or how I might go about making
it work correctly?

Thanks,
Conor

I've inserted an image like this: -

File file = new File("myimage.gif");
try {
FileInputStream fis = new FileInputStream(file);
int length = (int)file.length();
byte[] bytes = new byte[length];
fis.read(bytes);
fis.close();
PreparedStatement ps = db.prepareStatement("INSERT INTO images
(image) VALUES (?)");
ps.setBytes(1, bytes);
} catch () { }

And I try to get the image back like this: -

try {
Statement stmt = db.createStatement();
rs = stmt.executeQuery("SELECT image, length(image) FROM images");
while (rs.next()) {
byte[] imgBytes = rs.getBytes(1);
}
} catch () { }

Conor,

You are running into a bug in the server where it is doing character set
conversion on the bytea data. There are a number of threads in the
email archives that talk about this bug. The workaround is to use
'unicode' as the encoding of your database.

thanks,
--Barry

Conor Beverland wrote:
> Hi,
>
> I'm having a problem retreiving images from a postgreSQL database using
> ResultSet.getBytes() The image is stored in the database as a bytea
> type. I'm running java version 1.4.1 and postgreSQL version 7.3.4
>
> When I insert a file it seems to be the correct size inside the database
> (checked using SQL - length(image))
> However when I use getBytes() or even getBinaryStream() to get the data
> back from the database it always returns too much data.
> The returned bytes contain at least some of the correct data (perhaps
> all) with a load of extra bytes throughout the file. The size increase
> is consistent for each file.
>
> Does anyone know what I'm doing wrong and/or how I might go about making
> it work correctly?
>
> Thanks,
> Conor
>
> I've inserted an image like this: -
>
> File file = new File("myimage.gif");
> try {
> FileInputStream fis = new FileInputStream(file);
> int length = (int)file.length();
> byte[] bytes = new byte[length];
> fis.read(bytes);
> fis.close();
> PreparedStatement ps = db.prepareStatement("INSERT INTO images
> (image) VALUES (?)");
> ps.setBytes(1, bytes);
> } catch () { }
>
> And I try to get the image back like this: -
>
> try {
> Statement stmt = db.createStatement();
> rs = stmt.executeQuery("SELECT image, length(image) FROM images");
> while (rs.next()) {
> byte[] imgBytes = rs.getBytes(1);
> }
> } catch () { }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>