CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Need help with retrieving and displaying images from db

 



terrykhatri531
User

May 31, 2014, 3:08 AM

Post #1 of 15 (8934 views)
Need help with retrieving and displaying images from db Can't Post

Hi

I need to retrieve and display images from the db on the web page and I don't know how,

will appreciate if can help me with the code :



Here is my script :


Code
 
#!/usr/local/bin/perl

use DBI;
use CGI;
use strict;
use HTML::Template;

my $dbh = DBI->connect("DBI:Pg:dbname=northwind;host=localhost", "postgres", "postgres", {'RaiseError' => 1});

my $CGI = CGI->new();

# grab the stuff from the database
my $sth = $dbh->prepare('
SELECT
"EmployeeID", "FirstName", "LastName", "Photo"
FROM
"Employees"
');


$dbh->do("SET search_path to northwind") or die;
$sth->execute();


# prepare a data structure for HTML::Template
my $rows;
push @{$rows}, $_ while $_ = $sth->fetchrow_hashref();

# instantiate the template and substitute the values
my $template = HTML::Template->new(filename => 'Employees2.tmpl');
$template->param(ROWS => $rows);

print $CGI->header();

print $template->output();


$dbh->disconnect();




Here is the template :


Code
 
<!-- Employees.tmpl -->
<html>
<head>
<title>Employees Listing</title>
</head>
<body>
<h1>Company's Employees</h1>
<table>
<!-- TMPL_LOOP NAME=ROWS -->
<tr>
<td><!-- TMPL_VAR NAME=EmployeeID --></td>
<td><!-- TMPL_VAR NAME=FirstName --></td>
<td><!-- TMPL_VAR NAME=LastName --></td>
<td><!-- TMPL_VAR NAME=Photo --></td>
</tr>
<!-- /TMPL_LOOP -->
</table>
</body>
</html>




The Photo field is of type Bytea



Thanks very much for your help in advance.



Rgds

Terry


Zhris
Enthusiast

May 31, 2014, 12:10 PM

Post #2 of 15 (8741 views)
Re: [terrykhatri531] Need help with retrieving and displaying images from db [In reply to] Can't Post

Hi,


Quote
I need to retrieve and display images from the db on the web page and I don't know how,


From reading your script / template, I guess that the raw binary of the image data is being displayed, therefore your script is working as expected but not desired.

Your problem is that you have printed a content type of text/html to display the HTML template, but need a different content type to display the image.

I would create a standalone script i.e. image.pl, which takes the EmployeeID as a parameter, fetches the image from the database, prints the appropriate image content type, then prints the image.

All of the below is untested.

Your template:

Code
<td><img src="image.pl?employeeid=<!-- TMPL_VAR NAME=EmployeeID -->" /></td>


image.pl

Code
#!/usr/local/bin/perl  
use strict;
use warnings;
use CGI;
use DBI;

##########

my $cgi = CGI->new();

my $employeeid = $cgi->param('employeeid');

# validate employeeid here.

##########

my $dbh = DBI->connect("DBI:Pg:dbname=northwind;host=localhost", "postgres", "postgres", {'RaiseError' => 1});

$dbh->do("SET search_path to northwind") or die;

my $sth = $dbh->prepare('
SELECT
"Photo"
FROM
"Employees"
WHERE
"EmployeeID" = ?
');

$sth->execute( $employeeid );

my $image_data = $sth->fetchrow_arrayref->[0];

$dbh->disconnect();

##########

print $cgi->header( -type => 'image/gif' );

print $image_data;


If there are alot of employees, then consider pagination.

Chris


(This post was edited by Zhris on May 31, 2014, 12:20 PM)


FishMonger
Veteran / Moderator

May 31, 2014, 1:23 PM

Post #3 of 15 (8705 views)
Re: [terrykhatri531] Need help with retrieving and displaying images from db [In reply to] Can't Post

I'd take a different and more efficient approach. Instead of storing the raw image data in the db, I'd store the images in an "image" directory and the db would store the filenames (possibly including the full path).

The only advantage I see with storing the raw image data in the db is that it MIGHT save some disc space. But it also has several downsides. 1) It's slower to retrieve and display the image. 2) Uses more memory and cpu. 3) If there are lots of images to be retrieved, using Chris's suggestion, would generate a lot of extra and unnecessary perl processes and db calls.

Chris's suggestion is perfectly fine when there is a limited number if images, but that approach doesn't scale well.


(This post was edited by FishMonger on May 31, 2014, 1:27 PM)


Zhris
Enthusiast

May 31, 2014, 2:16 PM

Post #4 of 15 (8682 views)
Re: [FishMonger] Need help with retrieving and displaying images from db [In reply to] Can't Post

I should have expanded on what I meant by "If there are alot of employees, then consider pagination". As you state, alot of employees = alot of perl processes / db calls. Paging the results would limit this pressure. My approach above was written with consideration of the OPs existing approach i.e. no separate image files.

You are spot on describing the better approach, it is the approach I would also use. It wouldn't be difficult to convert to this approach either.

Chris


(This post was edited by Zhris on May 31, 2014, 2:22 PM)


Zhris
Enthusiast

May 31, 2014, 10:55 PM

Post #5 of 15 (8499 views)
Re: [terrykhatri531] Need help with retrieving and displaying images from db [In reply to] Can't Post

I have just considered, why is the image data is being stored in the database in the first place, is it for security purposes i.e. in the completed script, only logged in users can view these images. Other than my original approach, perhaps you could create long / random named symlink to the "out of web root" image directory, which is deleted fairly quickly i.e. give more than enough time for the images to load in the users browser (http://stackoverflow.com/questions/1214231/using-random-folder-name-on-web-server-to-restrict-access-to-it-bad-idea). But in all honesty, I'm not sure how best to handle if this is the case.

Chris


(This post was edited by Zhris on May 31, 2014, 11:35 PM)


Laurent_R
Veteran / Moderator

Jun 1, 2014, 2:08 AM

Post #6 of 15 (8443 views)
Re: [FishMonger] Need help with retrieving and displaying images from db [In reply to] Can't Post


In Reply To
I'd take a different and more efficient approach. Instead of storing the raw image data in the db, I'd store the images in an "image" directory and the db would store the filenames (possibly including the full path).


I definitely agree. Storing large binary objects in a relational DB is a mess in many respects, especially in terms of performances (speed, memory utilization, etc.) and database management (commit rates, transaction management, size of rollback segments and redo logs, backups, data migration, etc.). It is generally much better to store the address of the object (such as file name and path). It is not always possible or easy to do, depending on the nature of the object being stored, but for images it is really easy since images are very naturally stored as files.


terrykhatri531
User

Jun 22, 2014, 6:29 AM

Post #7 of 15 (6007 views)
Re: [Laurent_R] Need help with retrieving and displaying images from db [In reply to] Can't Post

Thanks every body very very much, I fully agree to having images stored in a directory rather than in a database which I will consider in future, however for now I need to retrieve it from the database and the way I have posted my script and template above, I was wondering is there way that I can just modify my script or template to get the images being displayed as image not just garbage ( together with other related data ), may be someone can show me by editing my script or may be just editing the template would do ??



I really really Appreciate your kind help.



Here is the template again




Code
 
<!-- Employees.tmpl -->
<html>
<head>
<title>Employees Listing</title>
</head>
<body>
<h1>Company's Employees</h1>
<table>
<!-- TMPL_LOOP NAME=ROWS -->
<tr>
<td><!-- TMPL_VAR NAME=EmployeeID --></td>
<td><!-- TMPL_VAR NAME=FirstName --></td>
<td><!-- TMPL_VAR NAME=LastName --></td>
<td><!-- TMPL_VAR NAME=Photo --></td>
</tr>
<!-- /TMPL_LOOP -->
</table>
</body>
</html>




Regards

Terry


FishMonger
Veteran / Moderator

Jun 22, 2014, 8:45 AM

Post #8 of 15 (5920 views)
Re: [terrykhatri531] Need help with retrieving and displaying images from db [In reply to] Can't Post

Did you try the approach that Chris showed?

If so, what were the results?

If not, then why didn't you?


terrykhatri531
User

Jun 22, 2014, 9:54 AM

Post #9 of 15 (5864 views)
Re: [FishMonger] Need help with retrieving and displaying images from db [In reply to] Can't Post

HI,

Sorry about not explaining the problem, I tried what Chris so kindly suggested and it does work except for the problem that it also displays the raw/binary data for the Photo field as well.



All the scripts are same including what Chris wrote i.e. image.pl.



Template now looks like this :




Code
 
<!-- Employees.tmpl -->
<html>
<head>
<title>Employees Listing</title>
</head>
<body>
<h1>Company's Employees</h1>
<table>
<!-- TMPL_LOOP NAME=ROWS -->
<tr>
<td><!-- TMPL_VAR NAME=EmployeeID --></td>
<td><!-- TMPL_VAR NAME=FirstName --></td>
<td><!-- TMPL_VAR NAME=LastName --></td>
<td><!-- TMPL_VAR NAME=Photo --></td>
<td><img src="image.pl?employeeid=<!-- TMPL_VAR NAME=EmployeeID -->" /></td>
</tr>
<!-- /TMPL_LOOP -->
</table>
</body>
</html>




If I take out just :




Code
 
<td><!-- TMPL_VAR NAME=Photo --></td>




Nothing is displayed, therefore apparently I need to do something to make it work without the raw/garbage data.



Please do help !!!



Thanks all specially Chris !!!



Rgds.

Terry


FishMonger
Veteran / Moderator

Jun 22, 2014, 10:11 AM

Post #10 of 15 (5852 views)
Re: [terrykhatri531] Need help with retrieving and displaying images from db [In reply to] Can't Post

Are you saying that the images are being displayed after you added the <img ...> tag but only if you keep the output of the raw image data and that you only get a completely blank page if you remove that raw data output?


terrykhatri531
User

Jun 22, 2014, 10:16 AM

Post #11 of 15 (5846 views)
Re: [FishMonger] Need help with retrieving and displaying images from db [In reply to] Can't Post

Exactly !!

Thanks


FishMonger
Veteran / Moderator

Jun 22, 2014, 10:23 AM

Post #12 of 15 (5840 views)
Re: [terrykhatri531] Need help with retrieving and displaying images from db [In reply to] Can't Post

That doesn't make sense.

Please re-post both scripts and the template. Also, take a look at the web server error log and post any/all related errors.

I'm tied up for the next couple hours but will review you next post when I can.


terrykhatri531
User

Jun 22, 2014, 10:36 AM

Post #13 of 15 (5828 views)
Re: [FishMonger] Need help with retrieving and displaying images from db [In reply to] Can't Post

Hi,

Ok what did now is to take out the Photo data field from my original perl script then took out the raw data tag from the template and it works now !!

Thank you all for your kind help, I love this forum!!!!

Rgds

Terry


Zhris
Enthusiast

Jun 22, 2014, 12:54 PM

Post #14 of 15 (5710 views)
Re: [terrykhatri531] Need help with retrieving and displaying images from db [In reply to] Can't Post

From reading your description of the problem that you have now solved, it looks as though it was caused by the susceptibility of HTML::Template when die_on_bad_params => 1. Your template params must correlate exactly with those used in the template i.e. the photo data field has to be taken out of the data structure AND the template. I would have thought you would have received an error as per the example below:


Code
#!/usr/bin/perl 
use strict;
use warnings;
use CGI::Carp qw/fatalsToBrowser/;
use HTML::Template;

my $rows =
[
{
one => 1,
two => 2,
three => 3
}
];

my $template = HTML::Template->new( filehandle => \*DATA );
$template->param( rows => $rows );

print "content-type: text/plain\n\n";
print $template->output( );

__DATA__
<!-- TMPL_LOOP NAME=rows -->
<!-- TMPL_VAR NAME=one -->
<!-- TMPL_VAR NAME=two -->
we do not use three.
<!-- /TMPL_LOOP -->



Code
HTML::Template->output() : fatal error in loop output : HTML::Template : Attempt to set nonexistent 
parameter 'three' - this parameter name doesn't match any declarations in the template file :
(die_on_bad_params => 1) at /usr/share/perl5/HTML/Template.pm line 3068 at
/path/to/htdocs/htmltemplate.pl line 20


Chris


(This post was edited by Zhris on Jun 22, 2014, 1:00 PM)


terrykhatri531
User

Jun 22, 2014, 11:40 PM

Post #15 of 15 (5228 views)
Re: [Zhris] Need help with retrieving and displaying images from db [In reply to] Can't Post

Thanks Chris you have been a great help and thank you moderator as well !!

Rgds

Terry

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives