Images in MySQL

Hello all:
Just a quick question concerning putting images in a database-
I want to add a field for images within my mysql database: how should I
go about doing this?
I have tried 
--
ALTER TABLE project
ADD(proj_img blob binary);
--
....to no avail. What is the correct syntax?
Also, is this even a good idea? Would it be better to just store a link
to an image within another directory? The images contained within should
be under 100k.

Heres the existing target table:
Database metonym_metonym - table project
Field	Type	Attributes	Null	Default	Extra
proj_id 	int(3) 	 	No 	 auto_increment 
fk_user_name 	varchar(20) 	 	No 	 	 
proj_title 	varchar(70) 	 	Yes 	 	 
proj_abstract 	text 	 	Yes 	 	 
init_date 	datetime 	 	Yes 	 	 

Keyname	Unique	Field
PRIMARY	Yes	proj_id

-- 
�����������������������������������������������
�	( > ) http://www.metonym.org          �  
�      	( ? ) Metonym Arts Collective         �  
�       ( & ) Launching Late Sept 2001        � 
�	( + ) metonym@metonym.org             �
�����������������������������������������������
0
metonym
9/7/2001 4:32:57 PM
perl.dbi.users 11085 articles. 1 followers. Follow

5 Replies
310 Views

Similar Articles

[PageSpeed] 56

This is sort of OT and not.

BLOB is binary, don't think you need to specify twice:-)  Take off the
binary part.

It doesn't matter on how you go about storing you images, both ways are
fine.  The only think you might want to think about is your service
provider, if you are not running your own server.  How much space due =
they
allocate for your files and how much are you allowed to store in your =
db.
From my experience, providers (at least the ones I've dealt with) =
always
limit your directory size, though they are not as limiting on your db, =
so
you must evaluate these things and see what's a more efficient long =
term
solution for you. =20

Don't forget to set LongReadLen and LongTruncOk when retrieving images, =
and
you must use placeholders when inserting them.

Ilya




-----Original Message-----
From: Todd Moy
To: dbi-users
Sent: 9/7/01 10:32 AM
Subject: Images in MySQL

Hello all:
Just a quick question concerning putting images in a database-
I want to add a field for images within my mysql database: how should I
go about doing this?
I have tried=20
--
ALTER TABLE project
ADD(proj_img blob binary);
--
....to no avail. What is the correct syntax?
Also, is this even a good idea? Would it be better to just store a link
to an image within another directory? The images contained within =
should
be under 100k.

Heres the existing target table:
Database metonym_metonym - table project
Field	Type	Attributes	Null	Default	Extra
proj_id 	int(3) 	 	No 	 auto_increment=20
fk_user_name 	varchar(20) 	 	No 	 	=20
proj_title 	varchar(70) 	 	Yes 	 	=20
proj_abstract 	text 	 	Yes 	 	=20
init_date 	datetime 	 	Yes 	 	=20

Keyname	Unique	Field
PRIMARY	Yes	proj_id

--=20
=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=
=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4
=A4	( > ) http://www.metonym.org          =A4 =20
=A4      	( ? ) Metonym Arts Collective         =A4 =20
=A4       ( & ) Launching Late Sept 2001        =A4=20
=A4	( + ) metonym@metonym.org             =A4
=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=
=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4=A4
0
Isterin
9/7/2001 1:58:28 PM
> This is sort of OT and not.
> 
> BLOB is binary, don't think you need to specify twice:-)  Take off the
> binary part.
> 
> It doesn't matter on how you go about storing you images, both ways are
> fine.

As long as we are sort of on/off topic there is a question about
retrieving images that are stored in databases that I've always wanted
to ask.

Can you retrieve a database stored image and mix it in with regular
HTML?

It seems to me that if you are going to print an image, you would have
to print the Content type for images and would then be unable to print
regular HTML. Am I wrong there?

Jeff 

>  The only think you might want to think about is your service
> provider, if you are not running your own server.  How much space due they
> allocate for your files and how much are you allowed to store in your db.
> >From my experience, providers (at least the ones I've dealt with) always
> limit your directory size, though they are not as limiting on your db, so
> you must evaluate these things and see what's a more efficient long term
> solution for you.
> 
> Don't forget to set LongReadLen and LongTruncOk when retrieving images, and
> you must use placeholders when inserting them.
> 
> Ilya
> 
> -----Original Message-----
> From: Todd Moy
> To: dbi-users
> Sent: 9/7/01 10:32 AM
> Subject: Images in MySQL
> 
> Hello all:
> Just a quick question concerning putting images in a database-
> I want to add a field for images within my mysql database: how should I
> go about doing this?
> I have tried
> --
> ALTER TABLE project
> ADD(proj_img blob binary);
> --
> ...to no avail. What is the correct syntax?
> Also, is this even a good idea? Would it be better to just store a link
> to an image within another directory? The images contained within should
> be under 100k.
0
cyberjeff
9/7/2001 4:05:06 PM
You are not wrong and not right.  Since this is now off topic, this
discussion should not continue, though you should look into html <IMG> tag.

Ilya

-----Original Message-----
From: Jeff Thies
Cc: 'dbi-users '
Sent: 9/7/01 10:05 AM
Subject: Re: Images in MySQL

> This is sort of OT and not.
> 
> BLOB is binary, don't think you need to specify twice:-)  Take off the
> binary part.
> 
> It doesn't matter on how you go about storing you images, both ways
are
> fine.

As long as we are sort of on/off topic there is a question about
retrieving images that are stored in databases that I've always wanted
to ask.

Can you retrieve a database stored image and mix it in with regular
HTML?

It seems to me that if you are going to print an image, you would have
to print the Content type for images and would then be unable to print
regular HTML. Am I wrong there?

Jeff 

>  The only think you might want to think about is your service
> provider, if you are not running your own server.  How much space due
they
> allocate for your files and how much are you allowed to store in your
db.
> >From my experience, providers (at least the ones I've dealt with)
always
> limit your directory size, though they are not as limiting on your db,
so
> you must evaluate these things and see what's a more efficient long
term
> solution for you.
> 
> Don't forget to set LongReadLen and LongTruncOk when retrieving
images, and
> you must use placeholders when inserting them.
> 
> Ilya
> 
> -----Original Message-----
> From: Todd Moy
> To: dbi-users
> Sent: 9/7/01 10:32 AM
> Subject: Images in MySQL
> 
> Hello all:
> Just a quick question concerning putting images in a database-
> I want to add a field for images within my mysql database: how should
I
> go about doing this?
> I have tried
> --
> ALTER TABLE project
> ADD(proj_img blob binary);
> --
> ...to no avail. What is the correct syntax?
> Also, is this even a good idea? Would it be better to just store a
link
> to an image within another directory? The images contained within
should
> be under 100k.
0
Isterin
9/7/2001 4:32:42 PM
On Fri, 7 Sep 2001, Jeff Thies wrote:

> 
> As long as we are sort of on/off topic there is a question about
> retrieving images that are stored in databases that I've always wanted
> to ask.
> 
> Can you retrieve a database stored image and mix it in with regular
> HTML?
> 

The answer is no. You have to deliver an HTML page in which you
include a
call to a CGI program that retrives the image from the database, and
delivers it with the appropiate header (content type, and so).
You can't mix HTML and the image binary information.
Suppose you are using a database backend for the creation of dynamic HTML
pages. Then first you generate a page with the HTML code and deliver it.
And included in this HTML code is the code to retrive the image.
For example, suppose thar the CGI program to retrive images is called
getimage, and that each image is identified with a index number, something
like imgid=xxx. Then you would have to include HTML code like this in your
dinamically created HTML page:

<img src="/cgi-bin/getimage?imgid=1362">

Hope you get te idea.

Cheers
		Kenneth


> It seems to me that if you are going to print an image, you would have
> to print the Content type for images and would then be unable to print
> regular HTML. Am I wrong there?
> 
> Jeff 
> 
> >  The only think you might want to think about is your service
> > provider, if you are not running your own server.  How much space due they
> > allocate for your files and how much are you allowed to store in your db.
> > >From my experience, providers (at least the ones I've dealt with) always
> > limit your directory size, though they are not as limiting on your db, so
> > you must evaluate these things and see what's a more efficient long term
> > solution for you.
> > 
> > Don't forget to set LongReadLen and LongTruncOk when retrieving images, and
> > you must use placeholders when inserting them.
> > 
> > Ilya
> > 
> > -----Original Message-----
> > From: Todd Moy
> > To: dbi-users
> > Sent: 9/7/01 10:32 AM
> > Subject: Images in MySQL
> > 
> > Hello all:
> > Just a quick question concerning putting images in a database-
> > I want to add a field for images within my mysql database: how should I
> > go about doing this?
> > I have tried
> > --
> > ALTER TABLE project
> > ADD(proj_img blob binary);
> > --
> > ...to no avail. What is the correct syntax?
> > Also, is this even a good idea? Would it be better to just store a link
> > to an image within another directory? The images contained within should
> > be under 100k.
> 

0
ken
9/7/2001 4:52:29 PM
Didn't know you had another one on the shelf, Paul.  I'll definitelly get it
today.  You should of posted an ANNOUNCEMENT or something to the list when
it was published, don't think that would of been OT, especially after the
first successful one:-)

Ilya

-----Original Message-----
From: Paul DuBois
To: Jeff Thies
Cc: dbi-users@perl.org
Sent: 9/7/01 10:54 AM
Subject: Re: Images in MySQL

>  > This is sort of OT and not.
>>
>>  BLOB is binary, don't think you need to specify twice:-)  Take off
the
>>  binary part.
>>
>>  It doesn't matter on how you go about storing you images, both ways
are
>>  fine.
>
>As long as we are sort of on/off topic there is a question about
>retrieving images that are stored in databases that I've always wanted
>to ask.
>
>Can you retrieve a database stored image and mix it in with regular
>HTML?
>
>It seems to me that if you are going to print an image, you would have
>to print the Content type for images and would then be unable to print
>regular HTML. Am I wrong there?

Although it's my book, and therefore I'm being self-serving in
mentioning
it, the chapter that describes how to do what you want using DBI and
CGI.pm
is online. There's a pointer to the chapter at the book's home page:

http://www.kitebird.com/mysql-perl/

>
>Jeff
>
>>   The only think you might want to think about is your service
>>  provider, if you are not running your own server.  How much space
due they
>>  allocate for your files and how much are you allowed to store in
your db.
>>  >From my experience, providers (at least the ones I've dealt with)
always
>>  limit your directory size, though they are not as limiting on your
db, so
>>  you must evaluate these things and see what's a more efficient long
term
>>  solution for you.
>>
>>  Don't forget to set LongReadLen and LongTruncOk when retrieving
images, and
>>  you must use placeholders when inserting them.
>>
>>  Ilya
>>
>>  -----Original Message-----
>>  From: Todd Moy
>>  To: dbi-users
>>  Sent: 9/7/01 10:32 AM
>>  Subject: Images in MySQL
>>
>>  Hello all:
>>  Just a quick question concerning putting images in a database-
>>  I want to add a field for images within my mysql database: how
should I
>>  go about doing this?
>>  I have tried
>>  --
>>  ALTER TABLE project
>>  ADD(proj_img blob binary);
>>  --
>>  ...to no avail. What is the correct syntax?
>>  Also, is this even a good idea? Would it be better to just store a
link
>>  to an image within another directory? The images contained within
should
>>  be under 100k.


-- 
Paul DuBois, paul@snake.net
0
Isterin
9/7/2001 5:05:35 PM
Reply: