Received: from miranda.emc.com.ph (miranda.emc.com.ph [203.167.64.7]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f239Xvx87654 for ; Sat, 3 Mar 2001 04:33:57 -0500 (EST) (envelope-from pgsql-sql@fc.emc.com.ph) Received: from fc.emc.com.ph (mnl.emc.com.ph [203.167.64.16]) by miranda.emc.com.ph (Postfix) with ESMTP id 39B316B025; Sat, 3 Mar 2001 17:33:41 +0800 (PHT) Message-id: Date: Sat, 03 Mar 2001 17:33:41 +0800 Subject: Re: lo_import for storing Blobs To: laurent@presenceweb.com Cc: pgsql-sql@postgresql.org From: "pgsql-sql" MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200103/35 X-Sequence-Number: 1680 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) >