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

Hi,

In means of database, it is impossible. If you want to cache, add
version or last modified column, then ask for changes and cache data
locally.

Kind regards,
Radosław Smogura
http://softperience.eu

On Fri, 28 Jan 2011 13:32:31 +0000, Andre Lopes wrote:
> 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,
>
>
>
>
> 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/
>>