1

I recently switched from MySQL to PostgreSQL. I have one problem left however.

Previously, I would store small images in the BLOB format in MySQL.

PostgreSQL doesn't know such thing as a BLOB.

I tried using BYTEA field type instead. This actually inserts an large (hexadecimal?) string I guess, but now I'm stuck trying to get this string back to displaying an actual image in PHP..

Any ideas? Thanks in advance.

Here is a piece of code I use to save the image in the database:

$data = bin2hex(file_get_contents('php://input'));

if (!empty($data)) {
  $sql = "UPDATE asset SET data = X'%s' WHERE uuid = '%s'";
  $args = array($data, $asset_uuid);
}

psql (9.1.3) and php 5.3.6 are used

Improve this question
6
  • Any code examples? What have you tried? Commented Jun 22, 2012 at 19:19
  • I've tried just outputting it by printing the string, and using the correct MIME type header... doesn't work. Commented Jun 22, 2012 at 19:20
  • 1
    @edwardmp: You're doing bin2hex when you store it. Try using hex2bin before echoing it. Commented Jun 22, 2012 at 19:42
  • The function hex2bin does not exist in PHP5. But I see your point, will try something similar. Commented Jun 22, 2012 at 19:51
  • 1
    The text format for bytea depends on the bytea_output configuration parameter that was introduced with PG 9.0 at the same time than the default format changed from escape to hex. You should update your question with the versions of PostgreSQL server, PHP, and PostgreSQL client. Commented Jun 22, 2012 at 22:05

3 Answers 3

Reset to default
3

Bytea is a byte array. It's not a bit pattern. See section 4.2.1.5 of PostgreSQL Lexical Structure.

The correct way to enter bytea is '\x...' with hex values. So what you want is SET data = '\x%s'.

You might also want to look into prepared statements with pg_prepare.

Edit: I was able to insert a (text) file into a bytea with this:

$source = file_get_contents( 'hello.php' );
$insert = pg_prepare( $conn, '', 'insert into t (name, data) values($1,$2)' );
pg_execute( $conn, '', array( 'hello.php', $source ) );

3rd Edit: This works fine to insert the file into the database. However, the pgsql driver in PHP is quite impolite. The only way to retrieve the actual data back is using the old bytea escape mechanism, as detailed here: pg_unescape_bytea.

pg_query('SET bytea_output = "escape";');

$result = pg_query( 'select data from t' );

while ( $row = pg_fetch_row( $result ) ) {
    echo pg_unescape_bytea( $row[0] );
}

I'm sorry about how annoying this is. The PostgreSQL interface in PHP can do with some major overhaul for binary values.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

@edwardmp What does "it doesn't work either" mean? Do you get an SQL error or junk in your database?
I get a large string beginning with x. The same as before. I also tried just inserting as SET data = '%s'. This produces the same result. I've tried so many things but I can't get any image produced back.. Is there any other way to store the image data in PSQL?
hmm whenever I manually insert a hex like 74657374, PostgreSQL somehow stores it as x7833373334333633353337333333373334 ?? I really don't get the hexadecimal and binary stuff. I just need to know how i can return the original value.
Have you tried SET data = '\x%s'::bytea to use explicit text-to-bytea conversion?
Yep, I've tried SET data = '\x%s'::bytea, SET data = '\x%s', SET data = '%s'
|
3

To insert bytea contents with the pg_* API, the binary value should always be run through the pg_escape_bytea() function, even if it's passed to the pg_execute or pg_query_params functions. This is because the pg_* layer doesn't "know" that a particular parameter has binary contents, and it does not implement any real support for parameter types anyway. So the text representation must be used. It can either be in the escape form or the hex form, it doesn't matter to the PG server, and it's independant of the value of bytea_output, which is meaningful only for values read from the server.

Example:

$esc=pg_escape_bytea("\000\001\002");
pg_query_params('INSERT INTO some_table(some_col) VALUES($1)', array($esc));

To read bytea contents with the pg_* API, the value must be run through pg_unescape_bytea() after the fetch. Assuming the client library is not older than 9.0 (libq.so.5.3 or higher), it can decode the contents whether it's in hex form or escape form and it will autodetect it. Only with an older library would it be necessary to force bytea_output to escape for it to decode properly, either dynamically with SET or statically for the whole database (ALTER DATABASE SET bytea_output=escape) or in postgresql.conf for the whole instance.

Example:

  $p=pg_query("SELECT some_col FROM some_table WHERE...");
  $r=pg_fetch_array($p);
  $contents = pg_unescape_bytea($r[0]);
Improve this answer

1 Comment

Hi Daniel, thanks very much for you answer. Your answer makes total sense, but it did not immediately solve my problem. I just finally solved it by doing what I will post in my own answer. But thanks for the suggestions!
3

Both answers posted here gave me some thoughts, but none were 100% of the answer.

So, I will explain in this answer what I did to get it to work.

When displaying the image, I used this:

header('Content-Type: image/jpeg');

$data = pack("H*", pg_unescape_bytea($data));

echo $data;

I'm running PHP 5.3.8, in PHP 5.4.0 it turns out you can use hex2bin instead of pack.

When adding the image to the database, I used this:

$data = pg_escape_bytea($data); // Escape input for PostgreSQL
$sql  = "UPDATE asset SET data = '%s'WHERE uuid = '%s'";

I'm glad it is working now. Thank you both Daniel and Johann!

Improve this answer

3 Comments

I don't know what's in $data, but generally applying pack("H"*) on top of pg_unescape_bytea makes no sense. See the manpage for pg_unescape_bytea which has sample code to output a jpeg image SELECT'ed from the db, just like you do except no pack().
I will try again without pack, but I think I've tried that before, and only pg_unescape_bytea won't work. I guess that I need to use it because when the image is inserted I use bin2hex, when the image is retrieved, you need to do the opposite, like hex2bin or pack("H*")
if bin2hex is applied before insertion, I guess what is inserted is not the raw data but its hex representation (=2 characters in the range '0'..'9','A'..'F' for each original byte). You can verify that by comparing the bytea length (select octet_length(col)) against the size of the original image file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.