Scrigroup - Documente si articole


HomeDocumenteUploadResurseAlte limbi doc
AccessAdobe photoshopAlgoritmiAutocadBaze de dateCC sharp
CalculatoareCorel drawDot netExcelFox proFrontpageHardware
HtmlInternetJavaLinuxMatlabMs dosPascal
PhpPower pointRetele calculatoareSqlTutorialsWebdesignWindows

AspAutocadCDot netExcelFox proHtmlJava
LinuxMathcadPhotoshopPhpSqlVisual studioWindowsXml

Tutorial - Importing Files in PostgreSQL


+ Font mai mare | - Font mai mic

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


- 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.


- 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


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 ''

psql:fruit_table:1: NOTICE: CREATE TABLE/UNIQUE will create implicit index 'fruit_id_key' for table 'fruit'


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.



<title>File Testing</title>


<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>




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');


$query = 'INSERT INTO fruit (id, filename, image) values (nextval('fruit_id_seq'), '$upload_file_name',


$result = pg_exec($db, $query);

if (!$result) else



First, note the query statement. The nextval('fruit_id_seq') should be familiar from the other scripts. The rest is a bit


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 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 = '';

if (!$id)

$numrows = pg_numrows($result);


do while ($row < $numrows);

if ($id)


$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].'>';



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: 857
Importanta: rank

Comenteaza documentul:

Te rugam sa te autentifici sau sa iti faci cont pentru a putea comenta

Creaza cont nou

Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved