Hi.
I have spent about two working days trying to get PostgreSQL working
with PDO inserting binary content and pulling it back out without
success.
I have tested this on Debian Etch using PHP 5.2.0 and PostgreSQL(libpq)
8.1.11. I have enabled the PostgreSQL PDO driver.
I have set up a simple table to hold the content:
id serial
blob_type character varying
attachment oid
I am using a simple form to process the upload, and my PHP upload
script looks like this (modified a little from the PHP manual):
try {
$pdo = new PDO ("$pdo_database:host=$pdo_hostname;dbname=
$pdo_dbname","$pdo_username","$pdo_password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
} catch (Exception $e) {
echo 'Caught exception: ',$e->getMessage(), "\n";
}
ini_set('max_execution_time', '0');
ini_set('memory_limit', '128M');
require_once ("knl_mime_type.php"); // Gets the correct mime type.
$mime_type = new knl_mime_type();
$blob_type = $mime_type->getMimeType($tmpfilename);
$pdo->beginTransaction();
$attachment = fopen($_FILES['file']['tmp_name'], "rb");
$filename = $_FILES['file']['name'];
$stmt = $pdo->prepare("INSERT INTO attachments (blob_type, filename,
attachment) VALUES (:blob_type, :filename, :attachment)");
$stmt->bindParam(':blob_type', $blob_type, PDO::PARAM_STR);
$stmt->bindParam(':filename', $filename, PDO::PARAM_STR);
$stmt->bindParam(':attachment', $attachment, PDO::PARAM_LOB);
$stmt->execute();
$pdo->commit();
When I submit the form, I can see (using PHPPgAdmin) the binary file, in
this test case a PNG image being inserted.
From the table I see this info using PHPPgAdmin:
id blob_type filename attachment
25 image/png shot2.png 16441
I don't know how the binary data are supposed to look like since I am
migrating from MySQL, and only have that as a comparison.
If I understand the PostgreSQL manual correctly the above number
"16441" is a OID reference number to the binary data.
I don't know if the above is correct or if PostgreSQL has received the
binary data correctly, maybe someone can confirm this for me please?
Anyway, when I try to pull that data from the database (again using the
example from the PHP manual) I just get the reference number "16441"
back to the browser.
I am using the following code to retrieve the data:
$stmt = $pdo->prepare("SELECT blob_type, attachment FROM attachments
WHERE id = :id LIMIT 1");
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_STR);
$stmt->execute();
$results = $stmt->fetchAll();
foreach ($results as $row) {
$blob_type = $row['blob_type'];
$attachment = $row['attachment'];
}
header("Content-type: $blob_type");
echo $attachment;
If I use Firefox and take a look at the source code behind the page I
get served, I just see the number "16441" and nothing else.
Please notice that the above example are taken directly from the PHP
manual just modified a little. I have also tried using the examples
directly without any modifications, but the result is the same.
http://dk.php.net/manual/en/function.pdo-pgsqllobcreate.php
What am I missing or doing wrong here?
Best regards.
Rico.
