Re: [GENERAL] Handling of large objects in DBD::Pg? (fwd)

[garth]

Attached are large object read and write functions I wrote based on
information I gleened from the DBD::Pg install test script.
        They read and write to buffers rather than files because my
information wasn't comming from a file and wasn't going to one.  However
if you need the data to go to a file you can either open a file handle
yourself in these methods and read into a scalar or write out from one, OR
you can scan the test.pl file in the DBD::Pg install to see how to use the
proper file based read and write Pg calls.

M. Tavasti <[EMAIL PROTECTED]> wrote:
>
> How do I handle large objects in DBD:Pg (perl DBI interface to
> postgresql)?
>
> I've tried to do like this, but not successfull, it looks like there
> is no data inserted. I tried to see in psql is there something, doing
> SELECT lo_export(data,"/tmp/foofaa.txt") from foofaa where id=XXXX;
>
>   $obj_ins = $dbh->prepare(q{
>    INSERT INTO foofaa
>        (id,entry,data)
>      VALUES
>        (?,?,?)});
>
>     $obj = $dbh->func("./$dir/obj.txt", 'lo_import');
>     $obj_ins->execute($id,$ent,$obj);
>
>
>
>
> Manual page of DBD:Pg if confusing for lo_export:
>
>              $ret = $dbh->func($lobjId, 'lo_export');
>
>            Exports a large object into a Unix file.  Returns
>            false upon failure, true otherwise.
>
> To what file....
>
> Any help welcome.
>
>

#!/usr/bin/perl -w

use strict;

use DBI;
use DBD::Pg;

my $dsn = "dbname=p1";
my $dbh = DBI->connect('dbi:Pg:dbname=p1', undef, undef, { AutoCommit => 1 });

my $buf = 'abcdefghijklmnopqrstuvwxyz' x 400;

my $id = write_blob($dbh, undef, $buf);

my $dat = read_blob($dbh, $id);

print "Done\n";

sub write_blob {
    my ($dbh, $lobj_id, $data) = @_;
    
    # begin transaction
    $dbh->{AutoCommit} = 0;
    
    # Create a new lo if we are not passed an lo object ID.
    unless ($lobj_id) {
        # Create the object.
        $lobj_id = $dbh->func($dbh->{'pg_INV_WRITE'}, 'lo_creat');
    }    

    # Open it to get a file descriptor.
    my $lobj_fd = $dbh->func($lobj_id, $dbh->{'pg_INV_WRITE'}, 'lo_open');

    $dbh->func($lobj_fd, 0, 0, 'lo_lseek');
    
    # Write some data to it.
    my $len = $dbh->func($lobj_fd, $data, length($data), 'lo_write');
    
    die "Errors writing lo\n" if $len != length($data);

    # Close 'er up.
    $dbh->func($lobj_fd, 'lo_close') or die "Problems closing lo object\n";
 
    # end transaction
    $dbh->{AutoCommit} = 1;
    
    return $lobj_id;
}

sub read_blob {
    my ($dbh, $lobj_id) = @_;
    my $data = '';
    my $read_len = 256;
    my $chunk = '';

    # begin transaction
    $dbh->{AutoCommit} = 0;

    my $lobj_fd = $dbh->func($lobj_id, $dbh->{'pg_INV_READ'}, 'lo_open');
    
    $dbh->func($lobj_fd, 0, 0, 'lo_lseek');

    # Pull out all the data.
    while ($dbh->func($lobj_fd, $chunk, $read_len, 'lo_read')) {
        $data .= $chunk;
    }

    $dbh->func($lobj_fd, 'lo_close') or die "Problems closing lo object\n";

    # end transaction
    $dbh->{AutoCommit} = 1;
       
    return $data;
}