Re: Can't get PHP PDO LOB working with PostgreSQL (WRONG CODE)

On Thu, 2008-09-25 at 22:06 +0200, Rico Secada wrote:
> On Thu, 25 Sep 2008 21:32:17 +0200
> Rico Secada <coolzone(at)it(dot)dk> wrote:
>
> I don't know how I got the code input for this email mixed up, but off
> course I am not inserting the $attachment into the database but in fact
> the $oid variable.
>
> I have changed the code to this, and this is when I am faced with
> problems:
>
> $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
> $pdo->beginTransaction();
> $oid = $pdo->pgsqlLOBCreate();
> $stream = $pdo->pgsqlLOBOpen($oid, 'w');

Are you doing a:
$stream = $pdo->pgsqlLOBOpen($oid, 'r');

when you are trying to read the object out, or are you expecting it from
the SELECT columns? Large objects are not in the table - they are kind
of files off to the side of the database with structured mechanisms for
referencing them.

If you want to store the data actually *in* the table then you need to
use a different approach (i.e. data type of bytea for the column), but
large objects may well be more efficient in any case.

Cheers,
Andrew.

> $local = fopen($tmpfilename, 'rb');
> stream_copy_to_stream($local, $stream);
> $local = null;
> $stream = null;
> $stmt = $pdo->prepare("INSERT INTO attachments (blob_type, filename,
> attachment, filesize) VALUES (?, ?, ?, ?)");
>
> $stmt->execute(array ($blob_type, $filename, $oid, $filesize));
>
> $pdo->commit();
>
>
> > 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.
> >
> >
> >
> > --
> > Sent via pgsql-php mailing list (pgsql-php(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-php
> >
>
>
>