Perl Mysql uploading a image in a database.
niels
I'm trying to upload a image from a html form to a mysql database.
For some reason i can't manage to do this right. Don't tell me to save
the image in a directory and save a url in the database. I just don't
want that.
Here's the code:
local $/=undef;
$afbeelding=<$reclame_image>;#(Reclame_image is the variable from the
form)
$afbeelding=$dbh->quote($afbeelding);
$sql="insert into imgarchief(image)values('$afbeelding')";
$sth=Execute_Query($sql);#just a sub that executes the query
Sometimes, he saves the image right. sometimes i get an error.
To show the image i use this.
$reclame_imgno=shift;
print "Content-Type: image/jpeg\n\n";
print "$reclame_imgno";
my $reclamesth=Execute_Query("select image from imgarchief where
img_id=$reclame_imgno");
while($return=$reclamesth->fetchrow)
{
print $return;
}
What's wrong with this.
Thanks in advance.
Niels Bond
Simon Andrews
>
> Hello there,
>
> I'm trying to upload a image from a html form to a mysql database.
>
> For some reason i can't manage to do this right. Don't tell me to save
> the image in a directory and save a url in the database. I just don't
> want that.
Make sure that both your incoming and outgoing filehandles have had
binmode applied to them (perldoc -f binmode), also make sure that the
field you are storing your data in is of a type which can safely store
binary data (probably some kind of blob, but see your database manual
for this).
> $afbeelding=$dbh->quote($afbeelding);
When you're doing your updates you should be using placeholders and not
the separate quote method. From perldoc DBI:
"When trying to insert long or binary values, placeholders
should be used since there are often limits on the maximum
size of an `INSERT' statement and the the quote entry
elsewhere in this document method generally can't cope
with binary data. See the Placeholders and Bind Values
entry elsewhere in this document."
> Sometimes, he saves the image right. sometimes i get an error.
..and that error would be?? You don't show us the actual sql execution,
but make sure that when you run your insert statement you check for
errors, and print $dbh->errstr() if it fails.
> To show the image i use this.
>
> $reclame_imgno=shift;
>
> print "Content-Type: image/jpeg\n\n";
> print "$reclame_imgno";
This will corrupt your image. First, no binmode on STDOUT, and second
you print $reclame_imgno as part of the image. After you've put out
your content header you must ONLY print your binary data.
> my $reclamesth=Execute_Query("select image from imgarchief where
> img_id=$reclame_imgno");
No quoting of $reclame_imgno, and we don't see the sql execution code.
> while($return=$reclamesth->fetchrow)
> {
> print $return;
> }
Eh? Why the while loop if you only want to ever return one image?
Also, assuming that you're using DBI, then fetchrow on its own isn't a
method. Maybe you meant something like...
my $sth = $dbh->prepare("select image from imarchief where img_id=?");
$sth->execute($reclame_imgno) || die $dbh->errstr();
my ($return) = $sth -> fetchrow_array();
unless ($return) {
die "No image returned";
}
binmode STDOUT;
print $return;
Hope this helps
Simon.
ctc...@hotmail.com
> Hello there,
>
> I'm trying to upload a image from a html form to a mysql database.
>
> For some reason i can't manage to do this right. Don't tell me to save
> the image in a directory and save a url in the database. I just don't
> want that.
>
> Here's the code:
>
> local $/=undef;
> $afbeelding=<$reclame_image>;#(Reclame_image is the variable from the
> form)
I'll assume this part is working, although you should probably
binmode it. You are not using strict, it looks like. Shame on
you.
>
> $afbeelding=$dbh->quote($afbeelding);
Don't do that.
> $sql="insert into imgarchief(image)values('$afbeelding')";
use place holders,
my $sth=$dbh->prepare(
"insert into imgarchief(image)values(?)"
);
> $sth=Execute_Query($sql);#just a sub that executes the query
Good lord, why? You write a single subroutine of your own to encapsulate
a single method call. Why not just use the method call itself? This
unnecessary proliferation of subroutines is the 2nd most annoying thing
my coworkers do. (2nd only to not using strict on large programs.)
> Sometimes, he saves the image right. sometimes i get an error.
What error?
> To show the image i use this.
>
> $reclame_imgno=shift;
>
> print "Content-Type: image/jpeg\n\n";
> print "$reclame_imgno";
You just printed some text into what is supposed to be a binary image
stream. (and also didn't binmode the stream.).
> my $reclamesth=Execute_Query("select image from imgarchief where
> img_id=$reclame_imgno");
Again with the Execute_Query? Again no place holders! Use them.
> while($return=$reclamesth->fetchrow)
> {
> print $return;
> }
Why would you expect to get more than one row back from the query?
If you did, why would you want to concatenate them altogether? I don't
think jpeg images work that way.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service New Rate! $9.95/Month 50GB
Martien Verbruggen
ctc...@hotmail.com <ctc...@hotmail.com> wrote:
> niels...@hotmail.com (niels) wrote:
>>
>> I'm trying to upload a image from a html form to a mysql database.
>> local $/=undef;
>> $afbeelding=<$reclame_image>;#(Reclame_image is the variable from the
>> form)
>
> I'll assume this part is working, although you should probably
> binmode it. You are not using strict, it looks like. Shame on
> you.
Not should (anymore). Must. Even on Unix platforms, you have to use
binmode to read binary data. Under perl 5.6.1 and before (on unix) it
probably will work, but under 5.8.0, leaving binmode off, even on unix
systems, can cause problems, due to the possible UTF-8 interpretation
of data, and depending on which PerlIO layers are active.
Martien
--
|
Martien Verbruggen | Useful Statistic: 75% of the people make up
Trading Post Australia | 3/4 of the population.
|
niels
The execute_query I use to define the sort of method to use: DBI or
mysql direct. It depends on the location where the script is.
For now, i managed to save the image to the database. I don't get any
errors any more. But moreover I show the images. Some images show
themselves partly.
I'm sure that the placeholders aren't set well. I think perl thinks
that the end of the image is reached in the middle of the file.
Niels
Martien Verbruggen
niels <niels...@hotmail.com> wrote:
> Thanks for your advice.
Please, leave some context in next time.
Did you follow the advice on using binmode? Have you read up on
binmode to see why you should be using it? Are your database columns
wide enough to store all the data?
Martien
--
|
Martien Verbruggen |
Trading Post Australia | 42.6% of statistics is made up on the spot.
|
niels
Sorry for the lack of good information. At first i forgot to mention
that perl and apache, mysql is saved on a win98 system. The computer
is too slow for win2000.
I was in a bit of hurry. But here's a complete description:
Mysql:
CREATE TABLE images (
img_id int(11) NOT NULL auto_increment,
image longblob NOT NULL,
PRIMARY KEY (img_id)
)
Perl
Code om op te slaan:
my($reclame_image)=$query->param("reclame_image");
my($afbeelding);
{
local $/=undef;
$afbeelding=<$reclame_image>;
}
close $reclame_image;
if($afbeelding)
{
$afbeelding=Quote_Chars($afbeelding);
$afbeelding=Escape_Chars($afbeelding);
binmode($afbeelding);
$sql="insert into imgarchief(image)values(\"$afbeelding\")";
$sth=Execute_Query($sql); (Dit is een eigen functie, werkt goed)
#He saves the image, but i don't know if he does it right. Maybe he
saves a part of the image.
}
Methode om het plaatje te tonen:
print "Content-Type: image/jpeg\n\n";
my $reclamesth=Execute_Query("select image from images where
img_id=$reclame_imgno");
#$sth->execute($reclame_imgno) || die $dbh->errstr();
my ($return) = $reclamesth -> fetchrow_array();
unless ($return) {
die "No image returned";
#Sometimes it showes a bit of the image
}
binmode STDOUT;
$return=Escape_Chars($return);
print $return;
Thanks in advance.
Niels
niels
Sorry for the lack of good information. At first i forgot to mention
that perl and apache, mysql is saved on a win98 system. The computer
is too slow for win2000.
I was in a bit of hurry. But here's a complete description:
Mysql:
CREATE TABLE images (
img_id int(11) NOT NULL auto_increment,
image longblob NOT NULL,
PRIMARY KEY (img_id)
)
Perl
Code om op te slaan:
my($reclame_image)=$query->param("reclame_image");
my($afbeelding);
{
local $/=undef;
$afbeelding=<$reclame_image>;
}
close $reclame_image;
if($afbeelding)
{
$afbeelding=Quote_Chars($afbeelding);
$afbeelding=Escape_Chars($afbeelding);
binmode($afbeelding);
$sql="insert into imgarchief(image)values(\"$afbeelding\")";
$sth=Execute_Query($sql); (Dit is een eigen functie, werkt goed)
#He saves the image, but i don't know if he does it right. Maybe he
saves a part of the image.
}
Methode om het plaatje te tonen:
print "Content-Type: image/jpeg\n\n";
my $reclamesth=Execute_Query("select image from images where
Martien Verbruggen
niels <niels...@hotmail.com> wrote:
> my($afbeelding);
> {
> local $/=undef;
You need to open the image, and use binmode.
> $afbeelding=<$reclame_image>;
This, confusingly, does not read from the file, but will treat
$reclame_image as a glob pattern (see the glob entry in perlfunc).
> }
> close $reclame_image;
You don't need this.
Replace all that came before with something like:
my ($afbeelding);
{
local $/;
open my $handle, $reclame_image or
die "Cannot open $reclame_image: $!\n"
binmode $handle;
$afbeelding = <$handle>;
# No need to close. $handle will go out of scope, and will be
# closed automatically.
}
If you have a Perl before 5.6, you may need to use a localised
old-fashioned file handle:
my ($afbeelding);
{
local ($/, *HANDLE);
open HANDLE, $reclame_image or
die "Cannot open $reclame_image: $!\n"
binmode HANDLE;
$afbeelding = <HANDLE>;
}
There are other ways to do this, but this is closest to what you seem to
be trying.
> if($afbeelding)
> {
> $afbeelding=Quote_Chars($afbeelding);
> $afbeelding=Escape_Chars($afbeelding);
Wait... Wait.. What are you doing here? You're calling escpaing
functions on the image data? $afbeelding is supposed to contain image
data, right?
> binmode($afbeelding);
And why the binmode here?
> $sql="insert into imgarchief(image)values(\"$afbeelding\")";
> $sth=Execute_Query($sql); (Dit is een eigen functie, werkt goed)
I wouldn't store image data in a database, so I won't try to comment on
whether this is the right way to do it. I would never use bare SQL like
this anyway, but would use DBI's functionality. I believe others have
already commented on that.
> #He saves the image, but i don't know if he does it right. Maybe he
> saves a part of the image.
I suspect that it never even read in the complete image..
Martien
--
|
Martien Verbruggen | My friend has a baby. I'm writing down all
| the noises the baby makes so later I can ask
| him what he meant - Steven Wright
Simon Andrews
> my($reclame_image)=$query->param("reclame_image");
In newer versions of CGI.pm this should be..
my $reclame_image) = $query->upload("reclame_image");
..otherwise you can't run under "use strict" (which you are - aren't
you?!)
Have you set a POST_MAX limit within your CGI? Could some of your
images be larger than this?
You should apply binmode to your filehandle at this point.
binmode $reclame_image;
> my($afbeelding);
> {
> local $/=undef;
> $afbeelding=<$reclame_image>;
> }
> close $reclame_image;
>
> if($afbeelding)
> {
> $afbeelding=Quote_Chars($afbeelding);
> $afbeelding=Escape_Chars($afbeelding);
Go back to my earlier post in this thread an re-read the section from
perldoc DBI about quoting. You can't use quote methods on binary data
reliably. You MUST use placeholders to ensure your data isn't
corrupted. Sometimes your current approach will work, but not always!
> binmode($afbeelding);
As Martien pointed out, this is wrong. Binmode applies to filehandles
not ordinary scalars. If you binmoded $reclame_image earlier on, that's
all you need.
Simon.
Simon Andrews
>
> On 7 Mar 2003 niels <niels...@hotmail.com> wrote:
> > my($afbeelding);
> > {
> > local $/=undef;
>
> You need to open the image, and use binmode.
>
> > $afbeelding=<$reclame_image>;
>
> This, confusingly, does not read from the file, but will treat
> $reclame_image as a glob pattern (see the glob entry in perlfunc).
I don't think this is the case (I could be wrong!), $reclame_image comes
from a CGI upload field, so the param call returns a filehandle, which
can be read as set out above. He should of course be using the CGI.pm
upload method really, but param will still work (albeit not under
strictures).
Your code would be correct if the reclame_image field contained the path
to a locally stored image file, but I didn't think it did.
Simon.
Martien Verbruggen
Simon Andrews <simon....@bbsrc.ac.uk> wrote:
> Martien Verbruggen wrote:
>>
>> On 7 Mar 2003 niels <niels...@hotmail.com> wrote:
>> > my($afbeelding);
>> > {
>> > local $/=undef;
>>
>> You need to open the image, and use binmode.
>>
>> > $afbeelding=<$reclame_image>;
>>
>> This, confusingly, does not read from the file, but will treat
>> $reclame_image as a glob pattern (see the glob entry in perlfunc).
>
> I don't think this is the case (I could be wrong!), $reclame_image comes
> from a CGI upload field, so the param call returns a filehandle, which
> can be read as set out above. He should of course be using the CGI.pm
> upload method really, but param will still work (albeit not under
> strictures).
You're most likely right. I sort of assumed that a file name was being
passed in, but it's probably the image data itself, as a result from
some upload thingie.
Martien
--
|
Martien Verbruggen |
Trading Post Australia | Curiouser and curiouser, said Alice.
|