Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2011/1/28 Andre Lopes <lopes80andre@xxxxxxxxx>
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@xxxxxxxxxxxxxxxxx> wrote:
> In response to Dmitriy Igrishin <dmitigr@xxxxxxxxx>:
>
>> 2011/1/26 Andre Lopes <lopes80andre@xxxxxxxxx>
>>
>> > 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/
>



--
// Dmitriy.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux