| CATEGORII DOCUMENTE |
| Asp | Autocad | C | Dot net | Excel | Fox pro | Html | Java |
| Linux | Mathcad | Photoshop | Php | Sql | Visual studio | Windows | Xml |
Tutorial - Importing Files in PostgreSQL
This part of the tutorial will show you how to use files within the database. We'll explore the advantages and disadvantages of
doing this.
Files outside a database
Advantages
- If your database server is over more than one computer, it's a lot easier to retrieve the files Retrieval and uploading files
both slow the database down.
Disadvantages
- For each BLOB two files are created in the database (not sure about this one for PostgreSQL v7.1 though).
- Can introduce large bottlenecks if you have a lot of files coming out of the database at once.
For this example, we're using a different database, this time called 'fruit'. Here's the details on how to create it :
postgres@designmagick:/usr/local/pgsql > createdb fruit
CREATE DATABASE
We now have the database, now we have to create the table inside the database.
Copy this into a file called 'fruit_table' :
CREATE TABLE fruit (id serial, filename varchar(50), image oid);
Now, run this command :
postgres@designmagick:/usr/local/pgsql > psql -d fruit -f fruit_table
This should be the response,
psql:fruit_table:1: NOTICE: CREATE TABLE will create implicit sequence 'fruit_id_seq' for SERIAL column 'fruit.id'
psql:fruit_table:1: NOTICE: CREATE TABLE/UNIQUE will create implicit index 'fruit_id_key' for table 'fruit'
CREATE
postgres@designmagick:/usr/local/pgsql >
We have to grant access to our webserver to this table. Create the file 'fruit_access' and put this in there:
GRANT ALL ON fruit TO htdocs;
GRANT ALL ON fruit_id_seq to htdocs;
Now, grant the access:
postgres@designmagick:/usr/local/pgsql > psql -d fruit -f fruit_access
Now we have the table to use. Here's a simple form that we need to use to upload the file.
<html>
<head>
<title>File Testing</title>
</head>
<body bgcolor='white' text='black'>
<form action='file_insert.php' method='post' enctype='multipart/form-data'>
File : <input type=file name=upload_file><br>
<input type=submit name=submit>
</form>
</body>
</html>
Note the form tag, 'enctype=multipart/form-data' is needed to tell the script (file_insert.php) it's a file. The
file_insert.php script looks like this.
<?
$db = pg_connect('dbname=fruit user=postgres');
chmod($upload_file,0644);
$query = 'INSERT INTO fruit (id, filename, image) values (nextval('fruit_id_seq'), '$upload_file_name',
lo_import('$upload_file'))';
$result = pg_exec($db, $query);
if (!$result) else
pg_close($db);
?>
First, note the query statement. The nextval('fruit_id_seq') should be familiar from the other scripts. The rest is a bit
different
The user in this case has to be a postgresql 'superuser', otherwise you can't import into the database. (It will come
up with an error message saying you have to do this as superuser). This also applies to lo_export, which is used below.
In the form, we named our file tag 'upload_file'. '$upload_file' is the temporary filename on the server. The
'$upload_file_name' is the name of the file that is on your computer. This is automatically set, so you don't have to
worry about working this out. For further information on this, check out https://www.php.net/manual/features.file-upload.php. The
next line (chmod) changes the file's permissions, so that anyone can read it (check your unix or linux system administration
guide for more information about file permissions).
The 'lo_import' function copies a file into the database. In our case, it copies the temporary file ($upload_file) into
our database. We're also keeping the original filename with the '$upload_file_name' variable. This will come in handy
later when we extract the file.
Of course, the $result tells us if there was an error or not. For testing, please use GIF, JPG, or PNG image types as these are
commonly recognised by modern browsers. Other file types will upload ok, but displaying them won't work with the script below.
This copies the file into the database, give it a try a few times and next we'll work on getting the files back out again.
<?
$db = pg_connect('dbname=fruit user=postgres');
$output_directory = '/home/tomcat/public_html/tmp/';
$output_url = 'https://elrond.designmagick.com/~tomcat/tmp/';
if (!$id)
$numrows = pg_numrows($result);
$row=0;
do while ($row < $numrows);
if ($id)
$myrow=pg_fetch_array($result,0);
$retrieve_query = 'select lo_export($myrow[image],'$output_directory$myrow[filename]')';
$retrieve_result = pg_exec($db,$retrieve_query);
if (!$retrieve_result)
echo '<img src='.$output_url.$myrow[filename].'>';
pg_close($db);
?>
The $output_directory variable tells the script where to save the exported image (or file), it has to be accessible by your web
server, and the directory must have the correct directory permissions.
The $output_url variable tells us where the file will be accessed by our web browser. Save this file as 'file_list.php'
& now view it in your browser.
It will bring up a list of links called the filename. To show the image, just click on the link & it should display the image.
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 845
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
