Hod do I store an image/photo/picture in a database? 1

[Guest_imported]

How do I store an image/photo/picture in a database?

I have a database called database1 with a table called table1 like:

Table1:
Personid Name1 Name2 Address

and I want to stora a picture of the person in the database (SQL Sever 7) c:\picture.jpg. How could I do that? I would like to use a stored procedure to do it.

Thanks for you help!

//Carl

 

[TomSark]

It's better to store a path to an image and use that path to display the image in an image control as opposed to using the image datatype and attemtpting to do it that way because: how are you going to point the controls image file to a binary stream of bits (coming from the image)?

Besides, image datatypes are a pain in the rear to maintain in a database...

Food for thought...

Tom [sig][/sig]

 

[crystalized]

I would like to thank Tom for the opinion on images, which seem to be a difficult topic to solicit opinions and information on. Are they that terrible? LOL [sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[DougP]

If you are going to use MS Access to create a front end for your SQL db then here is some code I used which falls in line with saving just the name.
------------------------------------------

Add an "Image" control to the form

Private Sub Form_Current()
    If Me![ID] <> 0 Then
        Me!Image14.Picture = &quot;p:\ViewPhotos\Photos\&quot; & Me![ID] & &quot;.jpg&quot;
        
    End If
End Sub

Now in my case all photos were in the same folder and I just renamed each photo the key field in the table &quot;ID&quot;
So when it came time to load it, it just found the ID
EXAMPLE: Property ID 439843 had a matching 439843.jpg in the p:\ViewPhotos\Photos\ folder.
The code was in the form current event so each time the user clicked the navigation buttons to move to a new record it automatically loaded the .jpg in the image control on the form.

It will work in Visual Basic too. [sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[rdemotsis]

In my opinion(which everyone knows what opinions are like). I have found that you can be limited with linked objects rather than embedded objects. With the Linked object you are at the mercy of your network connections if your images or objects are stored on a different server. Linked objects on the same server are relatively quick depending on the processing power of your server.
On the other hand, embedded objects are always local and seem to run pretty quick. You also have only one area to maintain (your database) where the images or objects are stored as opposed to maintaining the links within your database and the location path of the image or object files.
[sig][/sig]

 

[TomSark]

DougP is talking about a solution that also limits you because even in his example, the control points to a image FILE, not a stream of bits that you get when you retrieve an image from SQL Server.

If in VB or Access, you have to have an image file to point the control to. If you use SQL Server and store the image datatype in it, then you have to read a stream of image bits, save it as a file, then point the control to the file.

It's much cleaner and a better design to store the image files on a file server and put the path to the files in a varchar column in a table, then point the image control path to the path you read in....

Tom [sig][/sig]

 

[toejam]

Tom Sark -

you have not answered the original question. either you failed to actually read the question, or assumed the writer was too ill-informed to actually know what he was asking.

Either way, the question wasn't &quot;should I use paths instead of image data&quot;. the question was, how does he set store image data types into a database. this is a good question, as there are any number of circumstances where stoing image data is the only way to accomplish certain types of tasks.
MS puts an image data type into their northwind sample database that comes with SQL 7.0. Why do you think they did that?

I too am interested in this topic. Not only in how to store image data (say using an html form) but the best way to retrieve and display the image data as well.

I would like to know if anyone reading this thread has constructed a page that allows end users to insert and retrieve image data from a table. Hopefully, someone will take the time to read this message who actually has done this, and not offer me an opinion on why it is/isn't a good idea.

thanks

sir_toejam

 

[TomasDill]

Hi!

Nice discussion.

DougP is talking about a solution that also limits you because even in his example, the control points to a image FILE, not a stream of bits that you get when you retrieve an image from SQL Server.[color]

TomSark, I agree with you completely.
toejam, when you store images in files: What if we want to protect pictures on SQL Server from access? When we will have all pictures in the same folder, everyone that have access to that folder will be able to see all pictures. In addition, when you have a lot of images (>200), probably the access to that folder will be quite slow because very nature of the file system/networking.
Finally, implementation of image storing on the SQL Server is very depended on the front-end application. For example, for Internet application in ASP you can just store images in binary field in ADO object that have image-type field in SQL Server backend. On the other side, you can read that image into binary field and return it as data just on request from HTML page like following:
<IMG SRC= ...\MyImage.ASP>
(you will have to correct HTML Header in ASP for that, however).
In many other application there are no way to do this except to store binary data of image into file and than bind control to that file. It causes a lot of problems when you want to print report with images that are on server.
Now I'm working with VFP6 and I have no problems either when displaying images or printing report with images that are stored in the SQL Server image field.
There are also some articles in the MSDN about this topic.



Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[Guest_imported]

What I mean with my questions is:
how do I store a picture (photo) in an SQL Server 7 database. Not a path to an image or something with Access cause I don't use Access. I have a software that reades the SQL Server databases. The file (picture) it self should be stored in the database so that people later could read the data from there- in binary form.

There's something about BLOB and BULK-insert but I din't figure that yet.

 

[TomSark]

Hi,

I'm saying that most controls in the front-end require a path to a bitmap image. If you use the image datatype in SQL Server, you will have to transmit a binary stream of bytes to your front-end from SQL Server and the front-end will have to somehow display them. Most controls I know of require a path to an image, not a reference to a variable or whatever that you can use to display the image.

To find out more, look up READTEXT, TEXTPTR, etc in Books Online. Run the example in query analyzer. You will see that you get a long binary value back to the client. But, when you need to use that in your application, how will you display it?

Instead, store the path to the image and set the controls image path property to the path you read in from the server. Much easier, much cleaner...

With all of that said, there is a control out there that will do this, but I believe it's a third-party control and I don't remember who makes it and I don't know anything about it...


Tom

 

[carloalexi]

I had the same problem, the solution occurred to me after reading another post from someone who got it to work with BMPs but could not with JPGs or GIFs.

Since I was storing image data only from JPG files (which are smaller), I tried storing binary data from BMPs instead and it worked. For confirmation, I tried storing some BMPs and some JPGs and Crystal Reports only displayed the BMPs.

I have only tested this using an ODBC connection to SQL Server 7 (it should work with other ODBC sources)

 

[JimHorton]

Carl
A quick and dirty way is using a blob field, and using the appendChunk to store disk files, then the GetChunk to fetch.

You'd use GetChunk and extract, then write (from a byte-array variable) to a disk file (either temporary or permanent) and then use whatever method you like for viewing the file--it doesn't matter if it's jpg, bmp or whatever.

Since it's binary, you can store any file of any kind--.exe, .dlls, etc. For example, a poor-man's 'setup.exe' I had used with MS Access was to store, say MSCal.ocx (the calendar control--notoriously not a standard control) in a blob field, then upon a new client using my Access app, mscal.ocx was fetched, written to winsys, then a dllRegisterServer call was made and a Reference object set, and the calendar was available.
--Jim