Postgres, Perl, and Large Objects
От | S Vahey |
---|---|
Тема | Postgres, Perl, and Large Objects |
Дата | |
Msg-id | 20010119151348.20622.qmail@web1004.mail.yahoo.com обсуждение исходный текст |
Ответы |
Re: Postgres, Perl, and Large Objects
|
Список | pgsql-novice |
Hi- As practice before I begin a larger project, I'm trying to create a simple photo album. I want to be able to save and retrieve images in a postgres database. I can insert image data as large objects (data type oid) using the psql utility and I can write perl CGI scripts to retrieve the image data and display it in a web browser. The thing that is giving me trouble is writing a perl CGI to load the image data into the database. My initial attempt was to use a regular insert statement, like the one I used with psql. When I do that I get messages saying: You must have Postgres superupser privilege to use server-side lo_import(). Anyone can use the client-side lo_import provided by libpq. and please use DBI functions for transaction handling at /usr/lib/perl5/site_perl/5.6.0/i386-linux/DBD/Pg.pm line 114. After sifting through many FAQs, man pages, "Programming the Perl DBI", and the PostgreSQL Programmer's Guide, it seems that my problem may be that I need to put the insert statement into a transaction block. I am not sure I am doing that correctly. The code I've used to do this includes the following lines: $dbname = "photo_album"; $username = "nobody"; $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $username, undef, {AutoCommit => 0,RaiseError => 1,}) || die "$!"; eval { $querytext = "INSERT INTO image (image_trip_id, image_scene, image_thumb_mime_type, image_thumb_length, image_thumb_image, image_original_mime_type, image_original_length, image_original_image) VALUES (3, 1, 'image/jpeg', 2368, lo_import('/home/stacey/training/images/buffalo.tmb'), 'image/jpeg', 29822, lo_import('/home/stacey/training/images/buffalo.JPG') ); "; $sth = $dbh->prepare( $querytext ) or die "$mod_name $cur_time Can't prepare SQL statement for insert to image: $DBI::errstr\n"; $num_returned_rows = $sth->execute() or die "$mod_name $cur_time Can't execute SQL statement for insert to image: $DBI::errstr\n"; print "<BR>num returned rows from insert: $num_returned_rows<BR>\n"; $dbh->commit; # commit the changes if we get this far }; if ($@) { warn "Transaction aborted because $@"; $dbh->rollback; # undo the incomplete changes # add other application on-error-clean-up code here } This didn't work. I also tried adding the following in the eval block, before the insert statement. It didn't work either. $querytext = "BEGIN WORK"; $sth = $dbh->prepare( $querytext ) or die "$mod_name $cur_time Can't prepare SQL statement BEGIN WORK: $DBI::errstr\n"; $num_returned_rows = $sth->execute() or die "$mod_name $cur_time Can't execute SQL statement BEGIN WORK: $DBI::errstr\n"; Trying this line of code in the eval block, before the insert statement also didn't work. $dbh->do("BEGIN WORK"); Do I need a transaction block? If so, what is the correct syntax for establishing it? Do I need to do something else? If so, what? It seems like it should be so simple... I'm using Postgres 7.0.2, Perl 5.6.0, and Linux Mandrake 7.1. Thanks in advance for any help. Stacey __________________________________________________ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
В списке pgsql-novice по дате отправления: