From jeremy@hksys.com Thu Jul  22 18:02:50 1999
Received: from hksys.com (IDENT:root@dns.hksys.com [206.222.220.16])
	by hub.org (8.9.3/8.9.3) with ESMTP id OAA38103
	for <pgsql-interfaces@postgresql.org>;
	Thu, 22 Jul 1999 14:02:50 -0400 (EDT)
	(envelope-from jeremy@hksys.com)
Received: from silver (silver.hksys.com [10.0.1.13])
	by hksys.com (8.9.3/8.8.7) with SMTP id NAA30664
	for <pgsql-interfaces@postgresql.org>; Thu, 22 Jul 1999 13:02:39 -0500
Message-ID: <000e01bed46c$62fc4910$0d01000a@hksys.com>
From: "Jeremy Bettis" <jeremy@hksys.com>
To: <pgsql-interfaces@postgresql.org>
Subject: Perl and large objects
Date: Thu, 22 Jul 1999 13:02:38 -0500
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000B_01BED442.7974F270"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2014.211
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211
X-Logged: Logged by hksys.com as NAA30664 at Thu Jul 22 13:02:39 1999

This is a multi-part message in MIME format.

------=_NextPart_000_000B_01BED442.7974F270
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I have a script that reads and writes some large objects;

Write works fine, read doesn't. It gets this error:

Could not open lo ERROR: lo_lseek: invalid large obj descriptor (0)=20

I have confirmed that the oid that I get in the script is the same as =
what psql reports.

Here are some fragments of code:

sub doselect{    my $query =3D $_[0];
    my ($result, $status, $i, $j);
    my (@data);
    if (!$conn) {
        $conn =3D Pg::connectdb("dbname=3Djeremy");
        if ( PGRES_CONNECTION_OK !=3D $conn->status ) {
            print "<pre>",$conn->errorMessage, "\n", $query, =
"\n</pre>\n";
            print end_html;
            exit(1);
        }
    }
    @data=3D();
    if ($result =3D $conn->exec($query)) {
        if (PGRES_TUPLES_OK =3D=3D ($status =3D $result->resultStatus)) =
{
            for $i (0..$result->ntuples-1) {
                for $j (0..$result->nfields-1) {
                    =
$data[$i]->{$result->fname($j)}=3D$result->getvalue($i,$j);
                }
            }
            return \@data;
        }
    }
    print "<pre>",$conn->errorMessage, "\n", $query, "\n</pre>\n";
    print end_html;
    exit(1);
}




        $emails =3D doselect("select long_body,short_body from =
email_body where email_id =3D $email_id");
        if ($$emails[0]->{long_body} > 0) {
            $loid =3D $$emails[0]->{long_body};
            print "Long #", $loid;
            $long=3D$conn->lo_open($loid, PGRES_INV_READ);
            if ($long=3D=3D-1) {
                print("Could not open lo ",$conn->errorMessage) ;
            } else {
            $buf=3D"";
            while ($conn->lo_read($long, $buf, 2048) > 0) {
                $body.=3D$buf;
                $buf=3D"";
            }
            $conn->lo_close($long);
            }
}
--
Jeremy Bettis
jeremy@hksys.com

------=_NextPart_000_000B_01BED442.7974F270
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2614.3401" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>I have a script that reads and writes some large=20
objects;</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>Write works fine, read doesn't. It gets this=20
error:</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>Could not open lo ERROR: lo_lseek: invalid large obj =

descriptor (0) </FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>I have confirmed that the oid that I get in the =
script is the=20
same as what psql reports.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>Here are some fragments of code:</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>sub doselect{&nbsp;&nbsp;&nbsp; my $query =3D=20
$_[0];<BR>&nbsp;&nbsp;&nbsp; my ($result, $status, $i,=20
$j);<BR>&nbsp;&nbsp;&nbsp; my (@data);<BR>&nbsp;&nbsp;&nbsp; if (!$conn) =

{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $conn =3D=20
Pg::connectdb("dbname=3Djeremy");<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp; if=20
( PGRES_CONNECTION_OK !=3D $conn-&gt;status )=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
print=20
"&lt;pre&gt;",$conn-&gt;errorMessage, "\n", $query,=20
"\n&lt;/pre&gt;\n";<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;=20
print=20
end_html;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;=20
exit(1);<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
}<BR>&nbsp;&nbsp;&nbsp;=20
}<BR>&nbsp;&nbsp;&nbsp; @data=3D();<BR>&nbsp;&nbsp;&nbsp; if ($result =
=3D=20
$conn-&gt;exec($query)) {<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
if=20
(PGRES_TUPLES_OK =3D=3D ($status =3D $result-&gt;resultStatus))=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
for $i=20
(0..$result-&gt;ntuples-1)=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;=20
for $j (0..$result-&gt;nfields-1)=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
$data[$i]-&gt;{$result-&gt;fname($j)}=3D$result-&gt;getvalue($i,$j);<BR>&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;=20
}<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
}<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
return=20
\@data;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
}<BR>&nbsp;&nbsp;&nbsp;=20
}<BR>&nbsp;&nbsp;&nbsp; print "&lt;pre&gt;",$conn-&gt;errorMessage, =
"\n",=20
$query, "\n&lt;/pre&gt;\n";<BR>&nbsp;&nbsp;&nbsp; print=20
end_html;<BR>&nbsp;&nbsp;&nbsp; exit(1);<BR>}<BR></FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $emails =
=3D=20
doselect("select long_body,short_body from email_body where email_id =3D =

$email_id");<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if=20
($$emails[0]-&gt;{long_body} &gt; 0)=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
$loid =3D=20
$$emails[0]-&gt;{long_body};<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;=20
print "Long #",=20
$loid;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
$long=3D$conn-&gt;lo_open($loid,=20
PGRES_INV_READ);<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;=20
if ($long=3D=3D-1)=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;=20
print("Could not open lo ",$conn-&gt;errorMessage)=20
;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
} else=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
$buf=3D"";<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
while ($conn-&gt;lo_read($long, $buf, 2048) &gt; 0)=20
{<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;=20
$body.=3D$buf;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
$buf=3D"";<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
}<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
$conn-&gt;lo_close($long);<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;=20
}<BR>}</FONT></DIV>
<DIV><FONT size=3D2>--<BR>Jeremy Bettis<BR><A=20
href=3D"mailto:jeremy@hksys.com">jeremy@hksys.com</A></FONT></DIV></BODY>=
</HTML>

------=_NextPart_000_000B_01BED442.7974F270--




From sdavis2@mail.nih.gov Wed May  18 22:36:57 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 07A3152892
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Wed, 18 May 2005 19:36:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 96806-03
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Wed, 18 May 2005 22:36:51 +0000 (GMT)
Received: from mx5.net.nih.gov (mailfwd.nih.gov [165.112.130.36])
	by svr1.postgresql.org (Postfix) with ESMTP id 9799F5288B
	for <pgsql-interfaces@postgresql.org>;
	Wed, 18 May 2005 19:36:48 -0300 (ADT)
Received: from mx5.net.nih.gov (localhost [127.0.0.1])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j4IMaoaq008602
	for <pgsql-interfaces@postgresql.org>; Wed, 18 May 2005 18:36:51 -0400
Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j4IMaoKd008599
	for <pgsql-interfaces@postgresql.org>; Wed, 18 May 2005 18:36:50 -0400
Mime-Version: 1.0 (Apple Message framework v622)
Content-Transfer-Encoding: 7bit
Message-Id: <9ba996673af22c512313dc025914263a@mail.nih.gov>
Content-Type: text/plain; charset=US-ASCII; format=flowed
To: pgsql-interfaces <pgsql-interfaces@postgresql.org>
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: perl and large objects
Date: Wed, 18 May 2005 18:36:50 -0400
X-Mailer: Apple Mail (2.622)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200505/39
X-Sequence-Number: 5542

I am new to large objects and have a simple test script that dies (at 
the lo_import line) and I don't know why (no descriptive error given).  
Any suggestions (or a quick tutorial)?  The DBD manual just wasn't 
quite enough for me, I guess.

Thanks,
Sean


#!/usr/bin/perl
use strict;
use DBI;

my $dbh = DBI->connect('dbi:Pg:dbname=test2',
		       undef,
		       undef,
		       {AutoCommit=>1},) || die $@;

$dbh->{AutoCommit}=0;
my $lobjID = $dbh->func("./out.png",'lo_import') || die $DBI::errstr;
print $lobjID . "\n";

my $sth=$dbh->prepare('insert into lo_store (obj) values (?)');
$sth->execute($lobjID);
$dbh->commit();

$dbh->func($lobjID,"./lo.pl.copy",'lo_export') || die $@;
$dbh->commit();




From daniel@manitou-mail.org Wed May  18 23:07:36 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9F9085288B
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Wed, 18 May 2005 20:07:36 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00530-10
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Wed, 18 May 2005 23:07:34 +0000 (GMT)
Received: from brasil.brainstorm.fr (brasil.brainstorm.fr [193.56.58.33])
	by svr1.postgresql.org (Postfix) with ESMTP id 10D2052890
	for <pgsql-interfaces@postgresql.org>;
	Wed, 18 May 2005 20:07:33 -0300 (ADT)
Received: by brasil.brainstorm.fr (Postfix, from userid 1005)
	id 70D0426B08D; Thu, 19 May 2005 01:07:34 +0200 (CEST)
Content-Type: text/plain; charset="iso-8859-15"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
MIME-Version: 1.0
X-Mailer: Manitou v0.9.6
From: "Daniel Verite" <daniel@manitou-mail.org>
Subject: Re: perl and large objects
To: "pgsql-interfaces" <pgsql-interfaces@postgresql.org>
In-Reply-To: <9ba996673af22c512313dc025914263a@mail.nih.gov>
Date: Thu, 19 May 2005 01:07:27 +0200
Message-Id: <20050519010331.7693010@localhost>
X-Face: 0N#]; "Miw2ss`WySq(ZLLy[=.ai4D}yI#'n*PU:{%/ZVSUd*G$EuN$v@T<dMY'f,|G;
	>yBnpW5x=-m*Jc?Cm&=kXN#l; pePLb!,P.9D4M]fHU+XMPTe(>>'(
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200505/40
X-Sequence-Number: 5543

	Sean Davis wrote:

> I am new to large objects and have a simple test script that dies (at 
> the lo_import line) and I don't know why (no descriptive error given).  
> Any suggestions (or a quick tutorial)?  The DBD manual just wasn't 
> quite enough for me, I guess.

The lack of error message feels like what happens when you call lo_*
functions outside of a transaction. IIRC you just get 0 as the resulting
oid to a lo_import, for instance.

You could try adding $dbh->do("select 1") just after $dbh->{AutoCommit}=0
to ensure that a transaction is started before the lo_import.

Also, your code snippet runs fine for me, unmodified, with DBD::Pg 1.21
Are you running an older version?

-- 
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org



From sdavis2@mail.nih.gov Thu May  19 10:05:37 2005
X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 0BCA952888
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 19 May 2005 07:05:37 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 77444-04
	for <pgsql-interfaces-postgresql.org@localhost.postgresql.org>;
	Thu, 19 May 2005 10:05:33 +0000 (GMT)
Received: from mx5.net.nih.gov (mailfwd.nih.gov [165.112.130.36])
	by svr1.postgresql.org (Postfix) with ESMTP id 5CE7D5288E
	for <pgsql-interfaces@postgresql.org>;
	Thu, 19 May 2005 07:05:32 -0300 (ADT)
Received: from mx5.net.nih.gov (localhost [127.0.0.1])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j4JA5bas027654
	for <pgsql-interfaces@postgresql.org>; Thu, 19 May 2005 06:05:38 -0400
Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j4JA5bKd027651;
	Thu, 19 May 2005 06:05:37 -0400
In-Reply-To: <20050519010331.7693010@localhost>
References: <20050519010331.7693010@localhost>
Mime-Version: 1.0 (Apple Message framework v622)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <4dc77e1fc370855bf943a592b665e88c@mail.nih.gov>
Content-Transfer-Encoding: 7bit
Cc: "pgsql-interfaces" <pgsql-interfaces@postgresql.org>
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: Re: perl and large objects
Date: Thu, 19 May 2005 06:05:36 -0400
To: "Daniel Verite" <daniel@manitou-mail.org>
X-Mailer: Apple Mail (2.622)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200505/41
X-Sequence-Number: 5544


On May 18, 2005, at 7:07 PM, Daniel Verite wrote:

> 	Sean Davis wrote:
>
>> I am new to large objects and have a simple test script that dies (at
>> the lo_import line) and I don't know why (no descriptive error given).
>> Any suggestions (or a quick tutorial)?  The DBD manual just wasn't
>> quite enough for me, I guess.
>
> The lack of error message feels like what happens when you call lo_*
> functions outside of a transaction. IIRC you just get 0 as the 
> resulting
> oid to a lo_import, for instance.
>
> You could try adding $dbh->do("select 1") just after 
> $dbh->{AutoCommit}=0
> to ensure that a transaction is started before the lo_import.
>
> Also, your code snippet runs fine for me, unmodified, with DBD::Pg 1.21
> Are you running an older version?
>

DBD::Pg-1.41

It was indeed that the transaction was not started.  Putting in a 
couple of "select 1" worked just fine.

Thanks for the help.

Sean