Thread: ...

Got a presumably very quick question.

I'm writing a perl script. lets say i have a variable that's > 8k
, for arguments sake it's 64k.

How can i insert this into a database, do i need large objects ?
and if so, do i use the lo_import ? for some reason it seems that 
this is only for files..

below is what i have but it doesn't seem to work..

jeff=> \d test
Table    = test
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| blurb                            | text                             |   var |
| message                          | oid                              |     4 |
+----------------------------------+----------------------------------+-------+

***************** code ********************

#!/usr/bin/perl

use strict;
use DBI;


my $dbh = DBI->connect("dbname=narf host=narf port=narf",'narf','','Pg',{'AutoCommit' => 0});
if (!$dbh) {       exit print "Could not connect: $DBI::errstr\n";
}


## lets create the large value here...
## this string is 64K (2^16)


my $largestring = 'x';
for (my $i=0;$i < 16 ;$i++) {       $largestring = $largestring . $largestring;       }


my $statement = $dbh->prepare("INSERT INTO test values('something',lo_import($largestring));");  $statement->execute();
$statement->finish();
 

$dbh->commit;
$dbh->disconnect;

thanks for the help.

======================================================
Jeff MacDonaldjeff@hub.org    webpage: http://hub.org/~jeffjeff@pgsql.com    irc: bignose on EFnet
======================================================

Jeff,
The builtin functions lo_import and lo_export are
for files only. Because of the 8k limit, it wouldn't
be possible to insert larger values.

You need to create your own routine which stores the value in a
file and then imports/exports it.

Something like ...
(not tested, just for an idea.)

sub writetofile {
    my $data = shift;
    my $tmpfile = &newtmpfile || return "";
    open (FILE, ">$tmpfile") || return "";
    print FILE $data;
    close (FILE);
    return $tmpfile;
}

Then replace the following lines

> my $statement = $dbh->prepare("INSERT INTO test values('something',lo_import($largestring));");
>    $statement->execute();
>    $statement->finish();
>
> $dbh->commit;
> $dbh->disconnect;

with

my $largestring = &writetofile($largestring) || die("bad data.");
my $statement = $dbh->prepare("INSERT INTO test values('something',lo_import($largestring));");
    $statement->execute();
    $statement->finish();

 $dbh->commit;
 $dbh->disconnect;
unlink $largestring;

exit (0);


Troy


>
> Got a presumably very quick question.
>
> I'm writing a perl script. lets say i have a variable that's > 8k
> , for arguments sake it's 64k.
>
> How can i insert this into a database, do i need large objects ?
> and if so, do i use the lo_import ? for some reason it seems that
> this is only for files..
>
> below is what i have but it doesn't seem to work..
>
> jeff=> \d test
> Table    = test
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | blurb                            | text                             |   var |
> | message                          | oid                              |     4 |
> +----------------------------------+----------------------------------+-------+
>
> ***************** code ********************
>
> #!/usr/bin/perl
>
> use strict;
> use DBI;
>
>
> my $dbh = DBI->connect("dbname=narf host=narf port=narf",'narf','','Pg',{'AutoCommit' => 0});
> if (!$dbh) {
>         exit print "Could not connect: $DBI::errstr\n";
> }
>
>
> ## lets create the large value here...
> ## this string is 64K (2^16)
>
>
> my $largestring = 'x';
> for (my $i=0;$i < 16 ;$i++) {
>         $largestring = $largestring . $largestring;
>         }
>
>
> my $statement = $dbh->prepare("INSERT INTO test values('something',lo_import($largestring));");
>    $statement->execute();
>    $statement->finish();
>
> $dbh->commit;
> $dbh->disconnect;
>
> thanks for the help.
>
> ======================================================
> Jeff MacDonald
>     jeff@hub.org    webpage: http://hub.org/~jeff
>     jeff@pgsql.com    irc: bignose on EFnet
> ======================================================
>
>
> ************
>
>