Storing pictures in database

Hy, could someone help me in this:

In design mode, i want to put pictures in database. I made column named
pics, and its type as image. How can I put pictures or some address of these
pictures in that column, or i maybe need to put pictures in Add_Data folder
and make reference from there, or what I need to do?

could someone explain me that process of putting at least one picture in
database, I'm using Microsoft SQL Server 2005

thanks everyone

Re: Storing pictures in database

"Zeljko Bilandzija" wrote in message
news:fcb9oh$50d$1@ss408.t-com.hr...
> Hy, could someone help me in this:
>
> In design mode, i want to put pictures in database. I made column named
> pics, and its type as image. How can I put pictures or some address of
> these pictures in that column, or i maybe need to put pictures in Add_Data
> folder and make reference from there, or what I need to do?

Personally, I prefer to store files in the file system and links to them in
the DB.

On top of that I prefer to use two fields, one a directory and one the name
of the file itself.

Makes it a tad easier to do a mass update if I need to move the directory
(say to a different drive).


>
> could someone explain me that process of putting at least one picture in
> database, I'm using Microsoft SQL Server 2005
>
> thanks everyone
>



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Re: Storing pictures in database

Zeljko Bilandzija (zac9393@email.t-com.hr) writes:
> Hy, could someone help me in this:
>
> In design mode, i want to put pictures in database. I made column named
> pics, and its type as image. How can I put pictures or some address of
> these pictures in that column, or i maybe need to put pictures in
> Add_Data folder and make reference from there, or what I need to do?
>
> could someone explain me that process of putting at least one picture in
> database, I'm using Microsoft SQL Server 2005

The normal way of adding pictures, or any other data for that matter, is
through an application.

But you could use the BULK provider with OPENROWSET. Look at OPENROWSET
in Books Online, and particularly at example D.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Storing pictures in database

Thanks to both of you on help

but i give you example what i'm interesing

i want to put some pictures on server or local disk, and i want make, for
example, column named "Pictures", and store in that column url or path on
local disk, to get that pictures when i refer to particular id in query on
web page. If I can do that in this way, which type of data to choose for
that column (how make that links or paths)

thanks


"Erland Sommarskog" wrote in message
news:Xns99AB27A321B1Yazorman@127.0.0.1...
> Zeljko Bilandzija (zac9393@email.t-com.hr) writes:
>> Hy, could someone help me in this:
>>
>> In design mode, i want to put pictures in database. I made column named
>> pics, and its type as image. How can I put pictures or some address of
>> these pictures in that column, or i maybe need to put pictures in
>> Add_Data folder and make reference from there, or what I need to do?
>>
>> could someone explain me that process of putting at least one picture in
>> database, I'm using Microsoft SQL Server 2005
>
> The normal way of adding pictures, or any other data for that matter, is
> through an application.
>
> But you could use the BULK provider with OPENROWSET. Look at OPENROWSET
> in Books Online, and particularly at example D.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Storing pictures in database

Zeljko Bilandzija (zac9393@email.t-com.hr) writes:
> Thanks to both of you on help
>
> but i give you example what i'm interesing
>
> i want to put some pictures on server or local disk, and i want make, for
> example, column named "Pictures", and store in that column url or path on
> local disk, to get that pictures when i refer to particular id in query on
> web page. If I can do that in this way, which type of data to choose for
> that column (how make that links or paths)

If you store a URL, then your application will have to connect to a web
service or somesuch to get the picture. Whatever, it's beyond the scope
for this newsgroup. The data type would simply be varchar(500) or what
upper length you want to place on the URL.

If you store the picture on disk, the application must be able to
access the disk the file is on, and normally the client in an client-
server setup does not have access to the disk on the server, only the
server application, in this case SQL Server. And SQL Server is not going
to read the files from disk for you.

Overall, I disagree with Greg's recommendation to store the pictures in
the file system. In SQL 2000 and before, this solution had some attraction
that the data types for large objects (text, next and image) were difficult
to work with. But SQL 2005 introduced replacement for these that are
very simple to work with. And in about all other parts, storing the
images as files in the file system means more work. As I've mentioned,
you need to ensure that clients can access the files. Backups are more
tricky, because you need to backup database + directory. There is also
bigger risks for corruption in the sense that a file could be deleted
from disk, while there is still a path to it in the database.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx