Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Red face Unanswered: Storing pictures from ODBC

    Hey there :

    1- How to store picture , wich is the best datatype ?

    2- How to inset pic while using ODBC ?
    - I mean the path conversion -

    Thanks !
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    pics

    I always store only the file-link in the database in a text field
    bbut If you work with pics in the databse use bytea
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Thumbs down

    I agree. If you can possibly avoid it, don't store pictures, or any kind of "binary large object," directly in an SQL database.

    They don't call 'em "blobs" for nothing!

    If possible, store the image data in a separate file and store its filename in the database. This works great if, for example, you'll be feeding those images to a web-page because you can put an img source= tag directly into the HTML output which references the file where it is. SQL servers, web servers, and web browsers all handle this scenario much more gracefully than the alternative.

    Blobs are also "big, awkward objects" and there's not a DBMS in the world, in my humble, that really handles them really well.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Success

    I have already done it ! In fact Postgresql Itself doesn't store Large objects but hosts the links to them !
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  5. #5
    Join Date
    Sep 2003
    Location
    Wisconsin, USA
    Posts
    34

    Exclamation Caution

    There's just one word of caution with the "store the filename" approach. The actual image is not backed up with pg_dump, only the filename ... so if you need to have reliable backup/restore, make sure to back up the actual files seperately.

    BTW, I suggest you store as little about the path to the file as you can in the database. If possible, store only the filename, and have your code use a constant for the file path. This is because filesystem layouts can change. For example, linux and freebsd lay out their files a little differently. (what if you want to switch someday?) New versions of either OS might change things further (that new version of RedHat15 just changed everything! oh no!). And don't forget, even if you're absolutely positively sure your software will never ever need to change, it probably will someday!

    If you have to have seperate directories, have an integer field that says what directory it's in. For example, instead of storing "/thumbnails/thumb01.jpg" store "thumb01.jpg" and an Int like 2, which means thumbnails. Then handle that in your code.

    It's always easier to change 1 line in a config file for your code than update thousands of records in a database

    Just a few helpful hints

  6. #6
    Join Date
    Sep 2003
    Location
    Wisconsin, USA
    Posts
    34

    Lightbulb

    Oh, one last thought. (I've mentioned this on the boards before, but I thought I'd mention it again.)

    I mainly interact with postgres via php, and it has functions for base64_encode and base64_decode. It can be very handy, depending on your application, to simply base64_encode your binary objects and store them in a text field. This will back up your images via pg_dump, which (again, depending on your situation) might ease the backup/restore process considerably. Then, when you pull your objects from the database, just base64_decode them & you have the full binary object, safe and sound.

Posting Permissions

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