Re: bytea or blobs? - Mailing list pgsql-sql

Generally this is the task of various drivers.
The postgresql jdbc for instance makes this task
easy with the ResultSet.getBytes() method.

The servlet code to display the contents of any mimetype
bytea column looks like:

PreparedStatement st = 
con.prepareStatement("select mimetype,image from images where id=?");

st.setInt(1,id);
ResultSet rs = st.executeQuery();
if (!rs.next()) throw new Exception("Error Getting this image. Contact IT DEPT.");
String mimetype = rs.getString(1);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
byte[] img = rs.getBytes(2);        
baos.write(img);
res.setContentType(mimetype);        
baos.writeTo(out);
out.flush();
out.close();

Besides backups, a reason for sticking with bytea fields
is the ability to even replicate an image column across
databases in the usual manner that holds for the rest
of the datatypes, but then again these decisions are
dominated by the special conditions of each case.

Maybe if bandwidth is a restriction the base64 solution
saves some bandwith, since base64 file is ~ 1.3 times larger
than the original, whereas the escaped octal representation
will be ~ 4 times larger.

O kyrios Dana Hudes egrapse stis Feb 18, 2004 :

> The documentation indicates that bytea will -store- binary data
> The problem is getting the data into the column through SQL.
> The Postgresql SQL is character data only (side note: what about unicode? 
> is this USASCII only?). You cannot just wrap your binary stream in a pair
> of quotes and off you go. You must encode before saving and decode after 
> retrieving. The encoded form is stored in the column.
> 
> What I have not figured out is how to pass my data to encode.
> This is a builtin function but it doesn't take a filename so how can
> you use it!
> At least with base64 I have ample libraries and can convert my data
> before sending to sql or after receiving from sql. It becomes my
> application's issue. Mind, this bloats the data considerably.
> escape is less bloat but I have to recreate the encode/decode in my app,
> so far as I see.
> 
> 
> On Wed, 18 
> Feb 2004, sad wrote:
> 
> > On Tuesday 17 February 2004 18:08, you wrote:
> > 
> > > I'd recommend to let the application convert the binary
> > > data to and from base64,
> > 
> > Don't, please don't !
> > 
> > Since you have the good bytea rule to convert so called "binary" data into so 
> > called "text". You have no need another encoding at all.
> > 
> > Generally, the problem is to represent zero (0x00) when input/output. Any 
> > other byte might be stored, dumped, input, output without any problem. Then 
> > why to avoid 8bit chars ?
> > 
> > Bytea notation rule completely resolve the problem of zeroes.
> > (and also apostrophes :-) naturally) 
> > 
> > 
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> >       joining column's datatypes do not match
> > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
-Achilleus