SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How to get Images or blobs into SQL table
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

largehotmail
Starting Member

12 Posts

Posted - 07/27/2004 :  19:26:59  Show Profile
Looking for a couple of new methods of inserting Images into SQL. Don't tell me it can't be done!

Edited by - largehotmail on 07/28/2004 08:31:01

tkizer
Almighty SQL Goddess

USA
35954 Posts

Posted - 07/27/2004 :  19:30:40  Show Profile  Visit tkizer's Homepage
It just can't be done with T-SQL. There are examples over at www.asp.net on how to insert images into SQL Server.

BTW, it is recommended that you only store the path to the image in the database, then have the images on the file system.

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/27/2004 :  19:31:15  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
We won't tell you it couldn't be done. We will tell you it shouldn't be done though in MOST instances. If you go to www.asp.net, they have some several examples that insert images into a SQL Server database. You must insert the images from an application.

Have fun. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/27/2004 :  19:31:55  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Man, that was quick Tara. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/27/2004 :  19:38:08  Show Profile
I know of two methods.

1. Textcopy - Not easy to use, but works. There a trick to it! Can not move image into database unless you do something first!

2. A super program that I'm currently using that can transfer any format. Example: Pictures, movies, almost anything

3. Looking for third methed, and forth

Edited by - largehotmail on 07/27/2004 19:38:28
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35954 Posts

Posted - 07/27/2004 :  19:40:51  Show Profile  Visit tkizer's Homepage
3rd and 4th methods can be found at www.asp.net

Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/27/2004 :  19:53:11  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Why don't you post your results on here when you're done. :)

We also have blogs if you're interested.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/27/2004 :  20:02:49  Show Profile
quote:
Originally posted by derrickleggett

Why don't you post your results on here when you're done. :)

We also have blogs if you're interested.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.



Can I see some of those blogs?
What type of results are you looking for?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/27/2004 :  20:07:32  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Just show us some example. I think it would make an interesting blog. The blogs are at http://weblogs.sqlteam.com .

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/27/2004 :  20:53:26  Show Profile
quote:
Originally posted by derrickleggett

Just show us some example. I think it would make an interesting blog. The blogs are at http://weblogs.sqlteam.com .

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.



An example of what, a big blob?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/27/2004 :  20:58:34  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
I think you're a little confused.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/28/2004 :  01:56:41  Show Profile
Perhaps he meant "an example of putting a big blob in the database" Derrick?!

Kristen
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/28/2004 :  08:02:33  Show Profile
quote:
Originally posted by Kristen

Perhaps he meant "an example of putting a big blob in the database" Derrick?!

Kristen


Has any anyone on here ever put a blob into a database before, and if so, what types of programs are you using??




Edited by - largehotmail on 07/28/2004 08:05:25
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/28/2004 :  08:17:10  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
All of us have probably put blobs into a database before. :) The technical definition of a blob is a binary large object. This is how SQL Server stores anything that is text, ntext, or image. Image is any file basically. What SQL Server is actually doing is storing the data in "data blocks". It uses an API to access this data one block at a time. When the data is declare as text or ntext, you can use pointers to look at and manipulate the individual blocks of data. When you deal with files (image datatype), you have an API that assembles the blocks basically and reads the file.

blah, blah, blah

Then there's the fun information about text pointers. In the current iteration of SQL Server, all it really is doing is looking at a particular block. You can also put "text in row" which is kind of interesting. Read Books Online and Kalen Delaney's books if you want to know more. There's all kinds of juicy details for you to learn.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/28/2004 :  08:27:41  Show Profile
quote:
Originally posted by derrickleggett

All of us have probably put blobs into a database before. :) The technical definition of a blob is a binary large object. This is how SQL Server stores anything that is text, ntext, or image. Image is any file basically. What SQL Server is actually doing is storing the data in "data blocks". It uses an API to access this data one block at a time. When the data is declare as text or ntext, you can use pointers to look at and manipulate the individual blocks of data. When you deal with files (image datatype), you have an API that assembles the blocks basically and reads the file.

blah, blah, blah

Then there's the fun information about text pointers. In the current iteration of SQL Server, all it really is doing is looking at a particular block. You can also put "text in row" which is kind of interesting. Read Books Online and Kalen Delaney's books if you want to know more. There's all kinds of juicy details for you to learn.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.



I read some time ago that Microsoft never documented the textcopy command, why would they have such a feature, and never tell how to properly use it. I'm not sure if this story is really true, but that's what I read. It took months before I found how to use textcopy a DOS command to put images into the database.

Secondly, the program am using today is called TECA IMAGE it supports these formats. See below!

- XML files (.htm, .xml, .xsd, .html, .plg)

- Image files (.bmp, .gif, .pcx, .tif, .jpeg)

- Audio files (.wav, .mp3, .wmf)

- Executable files (.exe, .dll, .ocx)

- Application files (.zip, .doc, .xls, .ppt)

- Text files (.txt, .csv)


The people at TECA were really great, when I had a format that was not supported, I sent them that IMAGE and they fix the program.

Note: I went to book store and found nothing on how to use Textcopy properly with Images!

Edited by - largehotmail on 07/28/2004 08:49:22
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/28/2004 :  08:42:44  Show Profile  Visit jsmith8858's Homepage
The question is: why are you doing this? why not just store the image as a file in the filesystem, and store the filename in SQL Server? If you need unique filenames, let an identity or GUID column in SQL generate that for you.

Any time you wish to display the image, you need to copy it to the filesystem anyway -- why not just leave it there? I guess I just cannot see an advantage of storing blobs in SQL Server other than large text fields.

- Jeff
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/28/2004 :  08:44:27  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
I'll have to check out TECA. How much does it cost? Also, MS not documenting stuff should NOT surprise you. :) Look at all the views and procedures in SQL Server you can't find ANY documentation for. The textcopy command is a nice one to know about. Look at all the programs that are included in the Resource Kit though. There probably needs to be a book on all these utilities that make the life of a DBA and SQL Developer easier.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/28/2004 :  08:50:57  Show Profile
quote:
Originally posted by derrickleggett

I'll have to check out TECA. How much does it cost? Also, MS not documenting stuff should NOT surprise you. :) Look at all the views and procedures in SQL Server you can't find ANY documentation for. The textcopy command is a nice one to know about. Look at all the programs that are included in the Resource Kit though. There probably needs to be a book on all these utilities that make the life of a DBA and SQL Developer easier.


MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.



The program costs $40 at www.Teca.com

Cheers

Edited by - largehotmail on 07/28/2004 08:51:18
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/28/2004 :  09:00:25  Show Profile
quote:
Originally posted by jsmith8858

The question is: why are you doing this? why not just store the image as a file in the filesystem, and store the filename in SQL Server? If you need unique filenames, let an identity or GUID column in SQL generate that for you.

Any time you wish to display the image, you need to copy it to the filesystem anyway -- why not just leave it there? I guess I just cannot see an advantage of storing blobs in SQL Server other than large text fields.

- Jeff



Well, originally, I wanted to write a template for my webpage that contained images. At the time, it seemed nicer to have the images stored in the database with all the other information, and not keep track of URL's pointer's where the images were really stored.

I still want to write that template, can anyone point somewhere or give me some pointers on how to write it? I'll keep it simple, a description and an Image, what do I need to write it and incorporated into html, does anyone know any code?

Edited by - largehotmail on 07/28/2004 09:01:56
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/28/2004 :  09:59:11  Show Profile
We looked at storing images in the database for our web application.

Our tests showed that getting an image to a user was slower using the DB than having the file on disk.

Also, nothing downstream was going to cache the image - not the user's browser; not the user's ISP. So we decided that would place an unnecessary burden on our servers and ditched the idea in favour of storing on the disk.

We had a couple of other spin-off "wins" from this too. We found a nice little utility that allows image manipulation - so now we allow "admins" to upload images, via a web page, and then reduce them to acceptable filesize, rotate, that sort of stuff, which would have been harder if they were sources in the DB rather than files.

Kristen
Go to Top of Page

largehotmail
Starting Member

12 Posts

Posted - 07/28/2004 :  10:21:20  Show Profile
quote:
Originally posted by Kristen

We had a couple of other spin-off "wins" from this too. We found a nice little utility that allows image manipulation - so now we allow "admins" to upload images, via a web page, and then reduce them to acceptable filesize, rotate, that sort of stuff, which would have been harder if they were sources in the DB rather than files.

Kristen



What is needed to upload images into a web page by user input image by uploading it, what utility is needed or needs to be created, is it expensive?

Edited by - largehotmail on 07/28/2004 10:22:31
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000