Обсуждение: 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@crusaders.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@crusaders.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@crusaders.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@crusaders.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@crusaders.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@postgresql.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@postgresql.org
>[mailto:pgsql-jdbc-owner@postgresql.org] Im Auftrag von Anders
>Hermansen
>Gesendet: Sonntag, 2. März 2003 19:08
>An: pgsql-jdbc@postgresql.org
>Betreff: [JDBC] Testcase for bytea datatype
>
>
>* Uwe Kubosch (donv@crusaders.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@tzi.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@postgresql.org
>[mailto:pgsql-jdbc-owner@postgresql.org] Im Auftrag von Dirk Bromberg
>Gesendet: Dienstag, 4. März 2003 21:02
>An: 'Anders Hermansen'; pgsql-jdbc@postgresql.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@postgresql.org
>>[mailto:pgsql-jdbc-owner@postgresql.org] Im Auftrag von Anders
>>Hermansen
>>Gesendet: Sonntag, 2. März 2003 19:08
>>An: pgsql-jdbc@postgresql.org
>>Betreff: [JDBC] Testcase for bytea datatype
>>
>>
>>* Uwe Kubosch (donv@crusaders.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@postgresql.org
>
>

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.

> 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.