Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Question Unanswered: How would I go about storing pictures in a SQL Server database?

    I'm constructing an image gallery for my site and was wondering how to store the pictures in the database? Would the best way to do it, by storing the link instead of the image in the database?

    How would I use the insert and select with it?

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by comn8u
    I'm constructing an image gallery for my site and was wondering how to store the pictures in the database? Would the best way to do it, by storing the link instead of the image in the database?

    How would I use the insert and select with it?
    Whether you maintain the image in a DB or as a pointer, the image is still stored on the disk, so it's up to you how you want to do it. Do you want the difficult, more secure, more scalable solution, or do you want the easy, quick, dirty method?

    Chances are, since this is an image gallery, you'll want the former solution. There are a few things to keep in mind if you are creating an image gallery though. Are these images secure? Does someone have to have a certain level of access to view them? Are you worried about someone simply bookmarking an image? If any of these are the case, you probably want to keep them in the database. This way you can control who can see them and when.

    To do this in MS SQL Server, you would simply create a table with a column of type "image". That's the easy part.

    The challenge is getting the data in and out of the table.

    What language do you plan to use?

    I'm assuming that if the user is uploading the image, and there is security involved, that you have first validated that the user has the rights to upload an image.

    In HTML there's a form type called "file". That is what you would use as the front end for your file upload. On the back end, you would either need to write some code to receive the file, or get some pre-developed code to do it for you. In ASP, you can do both, but the easiest is to get a COM object to handle the work of retreiving the file from the client browser (but you may have to pay for it). Look up SAFileUp if you're using ASP, this is what I use (but it can be done through strictly ASP code, no COM required, and it's free).

    Next, you need to make sure the file (now a temp file on your web server) is actually an image. There are many code examples out there for doing this. They will tell you if it's a JPEG, GIF, etc, the dimensions (height and width), and the file size. This is great information if you want to have limits (a logo for example may not exceed certain X and Y dimensions, or a personal photo may not be more then a certain amount of KB).

    Once you have validated that this file is in fact a valid image, you can push it into the DB as a BLOB through your standard updatable recordset. How this is accomplished is language specific.

    Now that it's in your DB, you'll want to get it back out. This is where the security comes into play. Normally a URL to an image is straight to a GIF or a JPEG. In this case, it's going to be to a webpage, passing some type of URL query string parameter identifying this image. This page has the sole job of retreiving this image, nothing else. So when the main webpage loads, and encounters an image with our URL pointing to the second page, it simply requests the image from that page, passing along the query string parameters.

    That page takes the parameter you passed and should first determine if the user is eligible for the image through whatever security you implement. Then you use the query string parameter passed from the URL to query the DB for your image. If it exists, you clear the response headers, buffer the output (you want to send the image as a chunk of data, and not as it becomes available), change the content type of the page (which is by default text/html, but is now empty since we cleared the headers) to your image type (image/jpeg, image/gif, etc), and you write the binary data to the page, and close the page. Of course, this is all language specific again.

    So to the browser, it requested an image, and it got an image. It could care less that we requested it with a URL that included query string parameters instead of directly for a GIF or a JPEG.

    If you plan to do this coding in ASP, and need additional help, post in the ASP forum here, and I can provide you with more detailed code examples.
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •