Results 1 to 9 of 9
  1. #1

    Question sql command to save image

    hi..

    I have found some post here about the better ways on how to save image into database and how to retrieve.

    my question now:
    I am using SQL server 2005.
    what the sql command to save the image to the database?

    many thanks for any reply.....

  2. #2
    Hello,

    I haven't done this in years, mainly because I have something against storing images and files to a database. Anyways, I had to go take a quick google search for the answer but what it looks like is this:

    You'll need to open up a stream of the image and read the stream to an array of bytes, and then it's just a regular insert from there into the database, ensuring that the column your inserting into is of the type BLOB (when you created your table).

    Basically:

    INSERT INTO pictures_table (pic_name_col,pic_data_col) VALUES ('Pic Name','<ARRAY OF BYTES>');

    Hope this is somewhat helpful.

    Cheers,
    Brian

  3. #3
    Join Date
    Mar 2004
    Posts
    1,301
    I thought the better way was just to save the file's name and its location if needed.

  4. #4
    Yes. I agree. Storing just the file's name and location in the database, and then having the file stored in the filesystem on the server would be a much better way of doing it.

    But there are some circumstances when storing the images in the databases presents itself as a better solution for the problem.

    Cheers,

  5. #5
    Hey VG chatter, could you explain a situation in which storing the file in the database would be better? Just so i know for future reference. Thanks.

  6. #6
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    Here is one. Not having word writable directories for storing files.
    But everyone should avoid storing image file data in the database.

  7. #7
    Quote Originally Posted by datguru View Post
    Hey VG chatter, could you explain a situation in which storing the file in the database would be better? Just so i know for future reference. Thanks.
    Datguru,

    First is like what Steve_Arm said: Not having to worry about making sure you have an upload directory and that directory is writable (a small concern not really an issue).

    Another case is the way the files are hosted. A situation may prevent itself where a company has very small amount of space for the physical files, but have a large database servers. In this case it may present itself as a better opportunity instead of changing the existing structure.

    Not a great example I realize. Just know that, in general storing a file to the filesystem is a better way to go than to the database. What I meant by "some circumstances" is basically when you're working with existing architecture.

    Cheers,

  8. #8
    Ahh thats great. They were great examples, thanks very much. So it's important to go file system when you can. I was just curios because I just completed a website which had a files directory and upload feature and I was just wondering if there would of been any benefit of using the DB to store the files.

  9. #9
    Also, if the number of images per record set was very small (like 1) it would be far more tempting to utilize the database to store it.

    As far as I have been able to see the most common retrieval method used to get that image out of the database involve 1 mySQL query per img tag. This can be highly inefficient if you are trying to retrieve multiple images or even displaying the same image more than once on the page.

Posting Permissions

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