binary data

Hi all!

I'm trying to store image data in a postgresql 7.3.2 data using the 7.3.2
jdbc3 drivers. The table "tfile" contains an integer field "id" and a bytea
field "content".

I store the image with code similar to this:

InputStream is;
PreparedStatement statement;
int rowsAffected;

is = new FileInputStream("image.png");
statement = getConnection().prepareStatement("UPDATE tfile SET content=?
WHERE id=4");
getConnection().setAutoCommit(false);
statement.setBinaryStream(1, is, MAX_FILE_SIZE);
rowsAffected = statement.executeUpdate();
getConnection().commit();
getConnection().setAutoCommit(true);
is.close();
}

This works fine, and the size of the "content" field in the database is
equal to the size of the file, 4342 bytes.

I fetch the image back from the database with code similar to this:

String query;
ResultSet resultSet;

query = "SELECT content FROM tfile WHERE id=4";
resultSet = null;

try {
boolean more;

resultSet = executeTheQuery(query);
more = resultSet.next();

if (more) {
InputStream is;

is = resultSet.getBinaryStream("content");
setContent(is);
}
} catch (SQLException anSQLException) {
}

I've read and re-read the documentation, and this should work. However,
checking the size of the content InputStream shows that the size is 5527
bytes instead of 4342. Doing a compare with the original image reveals what
seems like unicode character sequences. For example "å" is replaced with
"Ã¥".

Is this correct behaviour? Must I convert binary data from a bytea field?

If I have missed something obvious, I apologize, but I hope someone can help
me, please. Any help is greatly appreciated.

With kind regards,
Uwe Kubosch

_________________________________________
Uwe Kubosch
Adviser eCommerce
ICQ#: 71437007
More ways to contact me: http://wwp.icq.com/71437007
_________________________________________

* Uwe Kubosch (donv(at)crusaders(dot)no) wrote:
> Hi all!

Hi

> I fetch the image back from the database with code similar to this:
>
> String query;
> ResultSet resultSet;
>
> query = "SELECT content FROM tfile WHERE id=4";
> resultSet = null;
>
> try {
> boolean more;
>
> resultSet = executeTheQuery(query);
> more = resultSet.next();
>
> if (more) {
> InputStream is;
>
> is = resultSet.getBinaryStream("content");
> setContent(is);

Why don't you show us the setContent method?

> }
> } catch (SQLException anSQLException) {
> }

Anders

--
Anders Hermansen
YoYo Mobile as

Hi!

Thanks for the quick answer!

<code snipped>

> Why don't you show us the setContent method?

I doesn't do much, but here it is:

private InputStream content;

/**
* Set content for this file.
* @param newValue InputStream
*/
public void setContent(InputStream newValue) {
this.content = newValue;
}

Hope you can make sense of it.

With kind regards,
Uwe Kubosch

* Uwe Kubosch (donv(at)crusaders(dot)no) wrote:
> > Why don't you show us the setContent method?
>
> I doesn't do much, but here it is:
>
> private InputStream content;
>
> /**
> * Set content for this file.
> * @param newValue InputStream
> */
> public void setContent(InputStream newValue) {
> this.content = newValue;
> }
>
> Hope you can make sense of it.

That's not at much help. Let me rephrase that:

Show us the code that saves the InputStream to a file.

Anders

--
Anders Hermansen
YoYo Mobile as

Hi!

> Show us the code that saves the InputStream to a file.

This is a web-application that streams the InpuStream back to the browser.
The code that does this is like this:

byte[] buffer;
int bytesRead;

buffer = new byte[is.available()];

while (is.available() > 0) {
bytesRead = is.read(buffer);
response.getOutputStream().write(buffer, 0, bytesRead);
}

response.getOutputStream().flush();
is.close();

Uwe

* Uwe Kubosch (donv(at)crusaders(dot)no) wrote:
> This is a web-application that streams the InpuStream back to the browser.
> The code that does this is like this:
>
> byte[] buffer;
> int bytesRead;
>
> buffer = new byte[is.available()];
>
> while (is.available() > 0) {
> bytesRead = is.read(buffer);
> response.getOutputStream().write(buffer, 0, bytesRead);
> }
>
> response.getOutputStream().flush();
> is.close();

I can't see anything in your code that is not binary safe, or I'm
missing something obvious just like you. Maybe the gurus know.

Anders

--
Anders Hermansen
YoYo Mobile as

> I can't see anything in your code that is not binary safe, or I'm
> missing something obvious just like you. Maybe the gurus know.

I hope so. I really need to fix this quickly. Maybe there are settings
that influence this?

Uwe

* Uwe Kubosch (donv(at)crusaders(dot)no) wrote:
> This is a web-application that streams the InpuStream back to the browser.
> The code that does this is like this:

Can you try the following? Maybe the result slipped through the driver
without being decoded?

// I think this will work because the is is a ByteArrayInputStream
byte[] buffer = new byte[is.available()];
is.read(buffer);

byte[] buffer2 = org.postgresql.util.PGbytea.toBytes(buffer);

response.getOutputStream().write(buffer2, 0, buffer2.length);
response.getOutputStream().flush();
is.close();

Anders

--
Anders Hermansen
YoYo Mobile as

> Can you try the following? Maybe the result slipped through the driver
> without being decoded?
>
> // I think this will work because the is is a ByteArrayInputStream
> byte[] buffer = new byte[is.available()];
> is.read(buffer);
>
> byte[] buffer2 = org.postgresql.util.PGbytea.toBytes(buffer);
>
> response.getOutputStream().write(buffer2, 0, buffer2.length);
> response.getOutputStream().flush();
> is.close();

OK, tried it. buffer is 5527 bytes. The PGbytea.toBytes(buffer) call
returned an array of size 5485. Original file size was 4342 bytes.

Uwe

* Uwe Kubosch (donv(at)crusaders(dot)no) wrote:
> OK, tried it. buffer is 5527 bytes. The PGbytea.toBytes(buffer) call
> returned an array of size 5485. Original file size was 4342 bytes.

I don't know what it is that is wrong. But I have made a test case, to
test the database with bytea field. The test passes fine here. Although
the default testdata is build.xml the test passes here with binary data
too.

JDBC maintainers: Can you add this test case to the distribution?

Anders

--
Anders Hermansen
YoYo Mobile as

Uwe,

Can you submit a test case that demonstrates the problem? I use the
bytea type in my apps without problems, so there must be something
different about how you are doing this.

thanks,
--Barry

Uwe Kubosch wrote:
> Hi all!
>
> I'm trying to store image data in a postgresql 7.3.2 data using the 7.3.2
> jdbc3 drivers. The table "tfile" contains an integer field "id" and a bytea
> field "content".
>
> I store the image with code similar to this:
>
> InputStream is;
> PreparedStatement statement;
> int rowsAffected;
>
> is = new FileInputStream("image.png");
> statement = getConnection().prepareStatement("UPDATE tfile SET content=?
> WHERE id=4");
> getConnection().setAutoCommit(false);
> statement.setBinaryStream(1, is, MAX_FILE_SIZE);
> rowsAffected = statement.executeUpdate();
> getConnection().commit();
> getConnection().setAutoCommit(true);
> is.close();
> }
>
> This works fine, and the size of the "content" field in the database is
> equal to the size of the file, 4342 bytes.
>
> I fetch the image back from the database with code similar to this:
>
> String query;
> ResultSet resultSet;
>
> query = "SELECT content FROM tfile WHERE id=4";
> resultSet = null;
>
> try {
> boolean more;
>
> resultSet = executeTheQuery(query);
> more = resultSet.next();
>
> if (more) {
> InputStream is;
>
> is = resultSet.getBinaryStream("content");
> setContent(is);
> }
> } catch (SQLException anSQLException) {
> }
>
> I've read and re-read the documentation, and this should work. However,
> checking the size of the content InputStream shows that the size is 5527
> bytes instead of 4342. Doing a compare with the original image reveals what
> seems like unicode character sequences. For example "å" is replaced with
> "Ã¥".
>
> Is this correct behaviour? Must I convert binary data from a bytea field?
>
> If I have missed something obvious, I apologize, but I hope someone can help
> me, please. Any help is greatly appreciated.
>
>
> With kind regards,
> Uwe Kubosch
>
> _________________________________________
> Uwe Kubosch
> Adviser eCommerce
> ICQ#: 71437007
> More ways to contact me: http://wwp.icq.com/71437007
> _________________________________________
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

I've insert the code to mine and if i take an image the test is not ok.
the byte[] are different.

Don't use plain text to test!!

greets

Dirk Bromberg

>-----Ursprüngliche Nachricht-----
>Von: pgsql-jdbc-owner(at)postgresql(dot)org
>[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] Im Auftrag von Anders
>Hermansen
>Gesendet: Sonntag, 2. März 2003 19:08
>An: pgsql-jdbc(at)postgresql(dot)org
>Betreff: [JDBC] Testcase for bytea datatype
>
>
>* Uwe Kubosch (donv(at)crusaders(dot)no) wrote:
>> OK, tried it. buffer is 5527 bytes. The
>PGbytea.toBytes(buffer) call
>> returned an array of size 5485. Original file size was 4342 bytes.
>
>I don't know what it is that is wrong. But I have made a test
>case, to test the database with bytea field. The test passes
>fine here. Although the default testdata is build.xml the test
>passes here with binary data too.
>
>JDBC maintainers: Can you add this test case to the distribution?
>
>
>Anders
>
>--
>Anders Hermansen
>YoYo Mobile as
>

* Dirk Bromberg (bromberg(at)tzi(dot)de) wrote:
> I've insert the code to mine and if i take an image the test is not ok.
> the byte[] are different.
>
> Don't use plain text to test!!

Please read what I wrote:
<quote>
Although the default testdata is build.xml the test
passes here with binary data too.
</quote>

I let it default with build.xml so it should be easier for JDBC
maintainers to intergrate the test into the distribution.

But I have some more information about the failures, I can now reproduce it
here too. If I create the database with encoding SQLASCII the tests run
fine, if I create it with UNICODE the tests run fine. But if I create it
with ISO-8859-1 the test fails, complaining that more bytes than there
should be is available from the db.

In short, to reproduce the problem you need to:
Create the test database with ISO-8859-1 encoding and run my testcase
with a binary file (not the default build.xml).

I hope this helps to further solve the problem.

Anders

--
Anders Hermansen
YoYo Mobile as

Hey, the binary problem works with the pg72jdbc2.jar!!!

so not with the:
- pg73jdbc3.jar
- devpgjdbc3.jar
but it works with:
- pg72jdbc2.jar

are there sources form the 72 jar somewhere ??

Greets

Dirk Bromberg

>-----Ursprüngliche Nachricht-----
>Von: pgsql-jdbc-owner(at)postgresql(dot)org
>[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] Im Auftrag von Dirk Bromberg
>Gesendet: Dienstag, 4. März 2003 21:02
>An: 'Anders Hermansen'; pgsql-jdbc(at)postgresql(dot)org
>Betreff: Re: [JDBC] Testcase for bytea datatype
>
>
>I've insert the code to mine and if i take an image the test
>is not ok. the byte[] are different.
>
>Don't use plain text to test!!
>
>greets
>
>Dirk Bromberg
>
>>-----Ursprüngliche Nachricht-----
>>Von: pgsql-jdbc-owner(at)postgresql(dot)org
>>[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] Im Auftrag von Anders
>>Hermansen
>>Gesendet: Sonntag, 2. März 2003 19:08
>>An: pgsql-jdbc(at)postgresql(dot)org
>>Betreff: [JDBC] Testcase for bytea datatype
>>
>>
>>* Uwe Kubosch (donv(at)crusaders(dot)no) wrote:
>>> OK, tried it. buffer is 5527 bytes. The
>>PGbytea.toBytes(buffer) call
>>> returned an array of size 5485. Original file size was 4342 bytes.
>>
>>I don't know what it is that is wrong. But I have made a test
>>case, to test the database with bytea field. The test passes
>>fine here. Although the default testdata is build.xml the test
>>passes here with binary data too.
>>
>>JDBC maintainers: Can you add this test case to the distribution?
>>
>>
>>Anders
>>
>>--
>>Anders Hermansen
>>YoYo Mobile as
>>
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to
>majordomo(at)postgresql(dot)org
>
>

> String query;
> ResultSet resultSet;
>
> query = "SELECT content FROM tfile WHERE id=4";
> resultSet = null;
>
> try {
> boolean more;
>
> resultSet = executeTheQuery(query);
> more = resultSet.next();
>
> if (more) {
> InputStream is;
>
> is = resultSet.getBinaryStream("content");
> setContent(is);
> }
> } catch (SQLException anSQLException) {
> }

Hi,

I have the same problem. I'm using PostgreSQL 9.0.1, which came with
postgresql-8.4-701.jdbc4.jar. The database is using the UTF8-encoding.
The getInputStream() method seems to return the encoded byte stream
instead of the decoded one.

Best regards,

Werner.

Hi,

I replaced the driver version with postgresql-9.0-801.jdbc4.jar and now it works
correctly. The version that comes with the PostgreSQL 9.0.1 installation package
for Mac OS X should not be used.

Best regards,

Werner.