in reply to DBI and BLOBs

You should really be using placeholders, specify your table entries explicitly, and check the return value of $dbh->prepare and $dbh->execute:

use strict;
my $boo = $dbh->prepare('INSERT INTO patblob (varchar, id) VALUES (?, 
+?)') or die "Database error: " . $dbh->errstr;
$boo->execute('Yo', $blobid) or die "Database error: " . $dbh->errstr;
$dbh->commit;
$boo->finish();

[download]

The above code is safer and should give you proper error messages on failure. It's hard to debug code when you don't have proper error messages.

Update: Forgot something. It appears that $blobid is a BLOB, but you're inserting it into a numeric field. Huh?

----
Reinvent a rounder wheel.

Note: All code is untested, unless otherwise stated

Replies are listed 'Best First'.
Re^2: DBI and BLOBs
by diotalevi (Canon) on Mar 24, 2003 at 15:51 UTC

    That's a PostgreSQL thing. If you instert a large object into the database you get a OID identifier. It refer to this later you need to stash the OID somewhere and that is exactly what the original poster is doing. OIDs look like integers but aren't exactly that hence my explicit cast to the oid type. Of course, the original poster should be dumping the insert statements somewhere so the syntax can be verified.

[reply]
Re: Re: DBI and BLOBs
by Improv (Pilgrim) on Mar 24, 2003 at 15:56 UTC
    Here's the whole block with your changes (it still fails): 
    sub do_blob
{
my $myblob = shift;
my $blobid = $dbh->func($dbh->{pg_INV_WRITE}, 'lo_creat');
print "Created $blobid\n";
$dbh->{AutoCommit} = 0;
my $blobfd = $dbh->func($blobid, $dbh->{pg_INV_WRITE}, 'lo_open');
print "Blobfd $blobfd\n";
my $nb = $dbh->func($blobfd, $myblob, length($myblob), 'lo_write');
$dbh->commit;
if($nb == 0) {warn "Why?!?! : $!\n";}
$dbh->func($blobfd, 'lo_close');
$blobid += 0;
print "Try: insert into patblob values ('Yo', $blobid)\n";
$boo = $dbh->prepare("insert into patblob (name,id) values (?, ?)") or
+ die "DB Err: " . $dbh->errstr;
print "Prepped\n";
$boo->execute('Yo', $blobid) or die "Execute error: " . $dbh->errstr;
print "Executed\n";
$dbh->commit;
$boo->finish();
}

    [download]
    I think $blobid is numeric -- here's the output: 
    Created 186811
Blobfd 0
Try: insert into patblob values ('Yo', 186811)
Prepped
NOTICE:  current transaction is aborted, queries ignored until end of 
+transaction block
Executed

    [download]
    I added that += 0 part recently to make sure that perl knows that $blobid is meant to be numeric.. Thanks for your help..
[reply]
[d/l]
[select]
      Apparently, it's not at all kosher to have your lo_close in a seperate transaction from your other lo_functions, and that arrangement (which was accidental) broke things. In case anyone's interested, the simple fix is to scoot the $dbh->func($blobfd, 'lo_close'); and the line before it up above that $dbh->commit;
[reply]

In Section Seekers of Perl Wisdom