l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
November 4: Social gathering
Next Installfest:
TBD
Latest News:
Oct. 24: LUGOD election season has begun!
Page last updated:
2001 Dec 30 17:08

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
[vox-tech] blobs, php and postgres
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[vox-tech] blobs, php and postgres



I'm writing a php front end to a postgres database to import/export
large objects. I snagged an example from "Building Database Applications
on the Web Using PHP3" by Hilton and Willis, but have run into
wierdness.

My example table has this structure:
sagres=> \d employees
Table    = employees
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4                            
|     4 |
| picture                          | oid                             
|     4 |
+----------------------------------+----------------------------------+-------+ 

with these permissions:
| employees               | {"=","nobody=arwR","ekengelhard=arwR"} |

User "nobody" has superuser privledges for this example.

first script:
<html>
<body>
<title>BLOB step 1</title>
<form enctype=multipart/form-data action=step2.php3 method=post>
        <input type=text name=id value=1234><br>
        <input type=hidden name=max_file_size value=100000>
        Send this file: <input name=userfile type=file><br>
        <input type=submit name =submit value="Send File">
</form>
</body>
</html>

step2.php3:
<html>
<title>Step2</title>
<body>
<?PHP
$conn=pg_Connect("dbname=sagres port=5432");
if(!$conn){
        print "Error Could not connect to the database.<br>\n";
        exit;
}
 
pg_Exec($conn, "BEGIN");
 
$oid=pg_locreate($conn);
$handle=pg_loopen($conn, $oid, "w");
pg_lowrite($handle, $userfile);
 
pg_loclose($handle);
 
pg_Exec($conn, "INSERT into employees (id, picture) VALUES
('$id','$oid')");
pg_Exec($conn, "COMMIT");
 
pg_close($conn);
?> 

If I use the form to upload a local text file
(/home/ekengelhard/AZ336365) from my workstation to the server and then
peek at the employee table, then all appears well:

sagres=> select * from employees;
  id|picture
----+-------
1234| 338497
(1 row)

But when I call up the following script with id=1234
(http://henry/bioit/step3.php3?id=1234)

<html>
<title>step 3</title>
<body>
<?PHP
#Header("Content-type:image/jpg");
 
$conn=pg_Connect("dbname=sagres port=5432");
if(!$conn){
        print "Error: Could not connect to the database.<br>\n";
        exit;
}
 
$result=pg_Exec($conn, "SELECT picture FROM employees WHERE id='$id'");
$oid = pg_Result($result, 0, 'picture');
$handle =pg_loopen($conn, $oid, "r");
pg_loreadall($handle);
 
pg_close($conn);
?>
</body>
</html> 

... I get these error messages:

id=1234

Warning: Unable to open PostgresSQL large object in
/home/httpd/html/bioit/step3.php3 on line 15

Warning: 0 is not a PostgresSQL large object index in
/home/httpd/html/bioit/step3.php3 on line 16

The second error is a result of the first ($oid is evaluated as "0").

I then tried exporting with this command:
sagres=> select lo_export(picture, '/tmp/text_sample') from employees
where id='1234';
lo_export
---------
        1
(1 row)

...to find that the content of /tmp/text_sample was "/tmp/phpd7YXkS".
WTF?!

If I try to insert directly through psql I get this:

sagres=> insert into employees (id, picture) values ('1',
lo_import('/home/ekengelhard/test.gif'));
ERROR:  lo_import: can't open unix file "/home/ekengelhard/test.gif":
Permission denied

... but if I import from /tmp everything is aok:

sagres=> insert into employees (id, picture) values ('1',
lo_import('/tmp/bill-borg.gif'));
INSERT 338544 1
sagres=> select lo_export(picture,'/tmp/1.gif') from employees where
id=1;
lo_export
---------
        1
(1 row)  

... and I get Gates' smiling face back.

Any php/postgres/BLOB gurus able to help me sort out this issue?

PS. I'll be at the meeting tonight.
-- 
Eric Engelhard


LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
O'Reilly and Associates
For numerous book donations.