pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rico Secada <coolzone@it.dk>
To: pgsql-php@postgresql.org
Subject: Can't get PHP PDO LOB working with PostgreSQL (WRONG CODE)
Date: Thu, 25 Sep 2008 21:32:17 +0200
Message-ID: <20080925213217.f2d80566.coolzone@it.dk> (raw)

I am very sorry I submitted the wrong code in the original
email. The email below is the right one! Please disregard my first
email to this list.

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";

}

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();
$oid = $pdo->pgsqlLOBCreate();
$stream = $pdo->pgsqlLOBOpen($oid, 'w');
$local = fopen($tmpfilename, 'rb');
stream_copy_to_stream($local, $stream);
$local = null;
$stream = null;

$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.





view thread (8+ messages)  latest in thread

Message-ID: <20080925213217.f2d80566.coolzone@it.dk>
Permalink:  ../20080925213217.f2d80566.coolzone@it.dk/
Also on:    postgresql.org/message-id/20080925213217.f2d80566.coolzone@it.dk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: coolzone@it.dk
  Subject: Re: Can't get PHP PDO LOB working with PostgreSQL (WRONG CODE)
  In-Reply-To: <20080925213217.f2d80566.coolzone@it.dk>
  References: <20080925213217.f2d80566.coolzone@it.dk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox