Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    24

    Unanswered: Importing binary files into sql server database

    I work for a company that makes heat transfers for the imprinted apparel market. We're developing a database of merchandise images for all of our non-design inventory. Using Access we're going to be inserting thumbnails of psd (photoshop) files. We're wondering if there is any way to import multiple psd's into the sql server database into matching records like matching a column named "filename" and the actual filename of the file without having to upload each file individually. We want to be able to dump the files from the database of the matching records, also. This way, once our catalog designer has found which designs they need to put into the new catalog, it will dump the psd's for us. The same for our staffer who does color separations.

    Any suggestions out there? If you need me to post further of what we're trying here, I will. This is for the bossman.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    store the location and file names and not the files themselves. you may be able to use text\image\blobs but I would reccomend against it. it will bloat your database, probably hamper performance and likely cause unneeded difficulty.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Thrasymachus
    store the location and file names and not the files themselves. you may be able to use text\image\blobs but I would reccomend against it. it will bloat your database, probably hamper performance and likely cause unneeded difficulty.
    As mentioned here (and in other posts, do a search), the general consensus among the community is to store a pointer (link) in the database and store the binary (image file) on a web server or somewhere else.

    However, I have run into situations where this was not permitted (courtesy of some rabid security folks) or impractical. I currently manage a pair of 200 GB image repositories and SQL does just fine storing the blobs. Do a google search for "pure asp upload" or similar variants and you will find some sample code for uploading binary streams to a database.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    at my last gig we measured our image data in the terabytes and there is no way I am bloating my database and my database server with that stuff and we got new stuff all of the time. One week managment came in with 2 terabytes from the Smithsonian. If I store that stuff in SQL Server not only would I have I needed a new machine, which I did, but I would have had to migrate all of my DBs onto a new machine as well.

    Besides logisitics, technically blob data is just a pain to handle. I was going back over some Ken Henderson last night and as he points out if you store data in blobs, the data is not really in the row. What you get is a sixteen bit pointer to other data pages and if you do not insert the blob at record creation you do not get a pointer. So it is reccomended to insert a default value. My two cents regarding this is now you are wasting space and inserting junk data. Ken also points out that BLOB data creates a transaction log conundrum. Do you log it and bloat the log or not and compromise your db's integrity.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Aug 2005
    Posts
    24

    Thanks!

    Thanks for these suggestions. This is a pretty new trail for us to blaze. Glad to know there are forums like this where we can get questions answered.

Posting Permissions

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