Re: Store base64 in database. Use bytea or text?

2011/1/28 Andre Lopes <lopes80andre(at)gmail(dot)com>

> Hi,
>
> Another question about this subject.
>
> It is possible to cache this images from the database? In the future I
> will need to cache the pictures.
>
> If you have any knowledge about this, please give me a clue.
>
> Best Regards,
>
How would you like to cache them ? On a file system of client (e.g.,
WEB-server) ?
Why would you like to cache them ?

>
>
>
>
> On Wed, Jan 26, 2011 at 2:09 PM, Bill Moran <wmoran(at)potentialtech(dot)com>
> wrote:
> > In response to Dmitriy Igrishin <dmitigr(at)gmail(dot)com>:
> >
> >> 2011/1/26 Andre Lopes <lopes80andre(at)gmail(dot)com>
> >>
> >> > Thanks for the reply.
> >> >
> >> > I will mainly store files with 100kb to 250kb not bigger than this.
> >> >
> >> > PQescapeByteaConn is not available in a default installation of
> >> > PostgreSQL? My hosting account hava a standard installation of
> >> > PostgreSQL. There are other options for escaping binary files?
> >> >
> >> > Best Regards,
> >> >
> >> PQescapeByteConn is a function of libpq - native C client library.
> >> In you case (PHP) you should use its functions to encode binary
> >> data before including it into a query (e.g., in base64) and use
> >> built-in decode() function of Postgres:
> >> -- Pseudo-code
> >> INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP, 'base64'));
> >>
> >> where dat column of table img of type bytea.
> >
> > More specifically:
> >
> > $query = "INSERT INTO image_data (bytea_field) VALUES ('" .
> > pg_escape_bytea($binary_data) . "')";
> > pg_query($query);
> >
> > And to get it back out:
> > $query = "SELECT bytea_field FROM image_data";
> > $rs = pg_query($query);
> > $row = pg_fetch_assoc($rs);
> > $binary_data = pg_unescape_bytea($row['bytea_field']);
> >
> > (note that I may have omitted some steps for clarity)
> >
> > DO NOT use parametrized queries with PHP and bytea (I hate to say that,
> > because parametrized fields are usually a very good idea). PHP has a
> > bug that mangles bytea data when pushed through parametrized fields.
> >
> > PHP bug #35800
> >
> > --
> > Bill Moran
> > http://www.potentialtech.com
> > http://people.collaborativefusion.com/~wmoran/<http://people.collaborativefusion.com/%7Ewmoran/>
> >
>

--
// Dmitriy.