Обсуждение: bytea problem
Hello, this is my problem :
I use java + postgres and I want to store image files into a table
my table : (id int, image bytea)
the code :
byte bytes[] = new byte[(int)file.length()];
sqlq ="UPDATE table set image= ? where id=?";
ps = .....prepareStatement(sqlq);
ps.setBytes(2,bytes);
ps.setString(2,id);
put the file into my table, stream of 41054 bytes, OK.
but when I retrieve it from the database resultSet.getBytes("image") gives
an array of 82108 bytes!!
worst of all those bytes I get have no sense at all, like 00 00 00 or 11
11....
thanks for the help!
--
View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-problem-tp5014176p5014176.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
Is this a typo or is the code like this ?
ps.setBytes(2,bytes);
ps.setString(2,id);
----- Original Message -----
From: "alkampfer" <kazucat@gmail.com>
To: <pgsql-jdbc@postgresql.org>
Sent: Tuesday, November 22, 2011 6:37 PM
Subject: [JDBC] bytea problem
> Hello, this is my problem :
> I use java + postgres and I want to store image files into a table
>
> my table : (id int, image bytea)
>
> the code :
> byte bytes[] = new byte[(int)file.length()];
>
> sqlq ="UPDATE table set image= ? where id=?";
> ps = .....prepareStatement(sqlq);
> ps.setBytes(2,bytes);
> ps.setString(2,id);
>
> put the file into my table, stream of 41054 bytes, OK.
>
> but when I retrieve it from the database resultSet.getBytes("image") gives
> an array of 82108 bytes!!
> worst of all those bytes I get have no sense at all, like 00 00 00 or 11
> 11....
>
> thanks for the help!
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/bytea-problem-tp5014176p5014176.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
it's s typo, sorry
ps.setBytes(2,bytes);
ps.setString(1,id);
--
View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-problem-tp5014176p5014263.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
alkampfer wrote on 22.11.2011 18:37:
> Hello, this is my problem :
> I use java + postgres and I want to store image files into a table
>
> my table : (id int, image bytea)
>
> the code :
> byte bytes[] = new byte[(int)file.length()];
>
> sqlq ="UPDATE table set image= ? where id=?";
> ps = .....prepareStatement(sqlq);
> ps.setBytes(2,bytes);
> ps.setString(2,id);
>
> put the file into my table, stream of 41054 bytes, OK.
>
> but when I retrieve it from the database resultSet.getBytes("image") gives
> an array of 82108 bytes!!
> worst of all those bytes I get have no sense at all, like 00 00 00 or 11
> 11....
How do you read the data into the bytes[] array?
I usually use setBinaryStream() instead of setBytes(), that saves me from "manually" reading the file content into an
array:
InputStream in = new FileInputStream(file);
sqlq ="UPDATE table set image= ? where id=?";
ps = .....prepareStatement(sqlq);
ps.setBinarayStream(1, in, (int)file.length());
ps.setString(2,id);
I had this problem recently.
I had just upgraded from postgres 8 to 9, but did not get the jdbc
driver updated.
I read a posting that the bytea datatype was refactored in version 9,
and one indication was that twice as many bytes came out as went in...
this seems to be the case for you, so try looking at the version of the
jdbc driver and make sure you have a matching driver for your instance.
your code for the prepareStatement is how I am doing it and it now works
perfectly, since I updated my driver.
hope this helps.
On 11/22/2011 11:25 AM, Thomas Kellerer wrote:
> alkampfer wrote on 22.11.2011 18:37:
>> Hello, this is my problem :
>> I use java + postgres and I want to store image files into a table
>>
>> my table : (id int, image bytea)
>>
>> the code :
>> byte bytes[] = new byte[(int)file.length()];
>>
>> sqlq ="UPDATE table set image= ? where id=?";
>> ps = .....prepareStatement(sqlq);
>> ps.setBytes(2,bytes);
>> ps.setString(2,id);
>>
>> put the file into my table, stream of 41054 bytes, OK.
>>
>> but when I retrieve it from the database resultSet.getBytes("image")
>> gives
>> an array of 82108 bytes!!
>> worst of all those bytes I get have no sense at all, like 00 00 00
>> or 11
>> 11....
>
> How do you read the data into the bytes[] array?
>
> I usually use setBinaryStream() instead of setBytes(), that saves me
> from "manually" reading the file content into an array:
>
> InputStream in = new FileInputStream(file);
> sqlq ="UPDATE table set image= ? where id=?";
> ps = .....prepareStatement(sqlq);
> ps.setBinarayStream(1, in, (int)file.length());
> ps.setString(2,id);
>
>
>
>
I tried using setBinarayStream too, but the result is the same : when I try to get the data from the table I get always an array of 82108 bytes without sense... -- View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-problem-tp5014176p5014586.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
> I read a posting that the bytea datatype was refactored in version 9, and > one indication was that twice as many bytes came out as went in... I think the change in this default is what Chris is referring to: http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-BYTEA-OUTPUT This certainly could affect what you're doing. --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
I looked at the types of ways to store the data, and ensuring it was hex, I could see the '\x' at the beginning of the string... Once I matched the driver with the db all was happy. May want to update the jdbc driver to know what version of db it is talking to and either make the code compatable between the versions, or reject the connection if it is the wrong (incompatable) version.... Once things started working for me, I got lazy and stopped thinking about the problem.... On 11/22/2011 1:15 PM, Maciek Sakrejda wrote: >> I read a posting that the bytea datatype was refactored in version 9, and >> one indication was that twice as many bytes came out as went in... > I think the change in this default is what Chris is referring to: > > http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-BYTEA-OUTPUT > > This certainly could affect what you're doing. > --- > Maciek Sakrejda | System Architect | Truviso > > 1065 E. Hillsdale Blvd., Suite 215 > Foster City, CA 94404 > (650) 242-3500 Main > www.truviso.com >
I've just upgraded the jdbc to 9.1 and now works fine :) Thanks for your help! -- View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-problem-tp5014176p5014823.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.