pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pgsql-sql <pgsql-sql@fc.emc.com.ph>
To: laurent@presenceweb.com
Cc: pgsql-sql@postgresql.org
Subject: Re: lo_import for storing Blobs
Date: Sat, 03 Mar 2001 17:33:41 +0800
Message-ID: <fc.000f567200a0b449000f567200a0b449.a0b462@fc.emc.com.ph> (raw)

You can use 'DBI'

from test.pl of DBD::Pg

# create large object from binary file

my ($ascii, $pgin);
foreach $ascii (0..255) {
    $pgin .= chr($ascii);
};

my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close PGIN;

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

my $lobjId;
( $lobjId = $dbh->func($PGIN, 'lo_import') )
    and print "\$dbh->func(lo_import) ...... ok\n"
    or  print "\$dbh->func(lo_import) ...... not ok\n";

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

unlink $PGIN;
  

or you can use 'Perl5 extension for PostgreSQL' ...
    note: i didn't test the script

use strict;
use Pg;   

my $dbname = 'your dbname';
my $lo_path = 'path/to/you/binaryfile';
my ($tbl, $fld) = ('your table', 'oid field');

my $conn = Pg::connectdb("dbname=$dbname");
die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status;   

  my $result = $conn->exec("BEGIN");
  die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;

  # import  large object and get its oid
  my $new_oid = $conn->lo_import($lo_path) or die $conn->errorMessage;

  $result = $conn->exec("END");
  die $conn->errorMessage unless PGRES_COMMAND_OK eq
$result->resultStatus; 

# insert the oid of the lobj
  my $sql = sprintf("INSERT INTO %s (%s) VALUES (%ld)",
    $tbl, $fld, $new_oid);
 
  $result = $conn->exec($sql);
  die $conn->errorMessage unless PGRES_COMMAND_OK eq
$result->resultStatus;  

undef $conn;     


Sherwin

laurent@presenceweb.com writes:
>I need to store a binary file in a database. I use a cgi writed in shell
>to do it. So I can use Postgres user to execute the cgi.
>
>How can I store a binary file in a database with a cgi ?
>
>Thanks a lot.
>
>Laurent.
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




view thread (2+ messages)

Message-ID: <fc.000f567200a0b449000f567200a0b449.a0b462@fc.emc.com.ph>
Permalink:  ../fc.000f567200a0b449000f567200a0b449.a0b462@fc.emc.com.ph/
Also on:    postgresql.org/message-id/fc.000f567200a0b449000f567200a0b449.a0b462@fc.emc.com.ph

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pgsql-sql@fc.emc.com.ph, laurent@presenceweb.com
  Subject: Re: lo_import for storing Blobs
  In-Reply-To: <fc.000f567200a0b449000f567200a0b449.a0b462@fc.emc.com.ph>
  References: <fc.000f567200a0b449000f567200a0b449.a0b462@fc.emc.com.ph>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox