Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D12F264FC01 for ; Thu, 25 Sep 2008 16:38:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 88382-10 for ; Thu, 25 Sep 2008 16:38:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.dansknet.dk (unknown [85.233.228.239]) by postgresql.org (Postfix) with ESMTP id DF40C64FC31 for ; Thu, 25 Sep 2008 16:38:28 -0300 (ADT) Received: from omnibook (localhost [127.0.0.1]) by smtp1.dansknet.dk (Postfix) with SMTP id C9CFE21C074 for ; Thu, 25 Sep 2008 21:19:38 +0200 (CEST) Date: Thu, 25 Sep 2008 21:19:38 +0200 From: Rico Secada To: pgsql-php@postgresql.org Subject: Can't get PHP PDO LOB working with PostgreSQL Message-Id: <20080925211938.68acab45.coolzone@it.dk> X-Mailer: Sylpheed version 2.3.0beta5 (GTK+ 2.8.20; i486-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200809/4 X-Sequence-Number: 3890 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.