locked
Blob or Memo (Binary)? RRS feed

  • Question

  • I need to save image or PDF files to the database and then view them whenever want. Which field type, Blob or Memo (Binary) is suitable for this purpose? TIA.

     
    Wednesday, February 5, 2014 1:00 AM

Answers

  • This is not question of 2 GB size limit but you have to decide between hundreds of single files somewhere in your directory structure and one (or a few) DBF+FPT files (large obviously) in your data folder.

    Either approach has its own advantages and disadvantages. I cannot say what is better or where is the limit which derives usage of one of above methods.

    Single files will became hardly manageable from certain number. DBF+FPT requires some programming to override the 2 GB limit and these files also require more stable network environment than single files.

    Both Blob and Memo binary is OK if you decide to store your files in DBF table.

    • Marked as answer by WWWilliam Monday, February 10, 2014 5:56 AM
    Wednesday, February 5, 2014 5:45 PM
  • Valid thoughts of Craig and Pavel,

    what are the reasons you want to store files into your database?

    If the user shouldn't be able to access files outside of your application, you'll have a hard time anyway. Eg if you output a word doc and open it in word via automation, users can save that anywhere else, too, and modify.

    No database can prevent that. You might add write protection in Word Docs, but that's breakable.

    Just a thought, I don't know the reason.

    Anyway SQL2012 offers filestream data types, which mean storage of files inside the SQL Server, even with versioning. It's the best solution you can use on Windows for storing files in a confined environment, if you ask me.

    If you only want to know the difference between Blob and Memo (binary), you can bind PictureVal property of an image control to Blob fields directly. Otherwise they don't differ much. Blob was mainly introduced to comply more to the ansi standard of field types and make mappings of other databases types easier to understand.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de


    • Edited by Olaf Doschke Thursday, February 6, 2014 7:34 AM
    • Marked as answer by WWWilliam Monday, February 10, 2014 5:57 AM
    Thursday, February 6, 2014 7:33 AM
  • Neither. You'll quickly hit the 2Gig file limit. Store the file on disk as normal and save the path/filename in the database.

    Craig Berntson
    MCSD, Visual C# MVP
    INETA Community Speaker
    www.craigberntson.com

    • Marked as answer by WWWilliam Monday, February 10, 2014 5:56 AM
    Wednesday, February 5, 2014 3:55 PM

All replies

  • Neither. You'll quickly hit the 2Gig file limit. Store the file on disk as normal and save the path/filename in the database.

    Craig Berntson
    MCSD, Visual C# MVP
    INETA Community Speaker
    www.craigberntson.com

    • Marked as answer by WWWilliam Monday, February 10, 2014 5:56 AM
    Wednesday, February 5, 2014 3:55 PM
  • This is not question of 2 GB size limit but you have to decide between hundreds of single files somewhere in your directory structure and one (or a few) DBF+FPT files (large obviously) in your data folder.

    Either approach has its own advantages and disadvantages. I cannot say what is better or where is the limit which derives usage of one of above methods.

    Single files will became hardly manageable from certain number. DBF+FPT requires some programming to override the 2 GB limit and these files also require more stable network environment than single files.

    Both Blob and Memo binary is OK if you decide to store your files in DBF table.

    • Marked as answer by WWWilliam Monday, February 10, 2014 5:56 AM
    Wednesday, February 5, 2014 5:45 PM
  • Valid thoughts of Craig and Pavel,

    what are the reasons you want to store files into your database?

    If the user shouldn't be able to access files outside of your application, you'll have a hard time anyway. Eg if you output a word doc and open it in word via automation, users can save that anywhere else, too, and modify.

    No database can prevent that. You might add write protection in Word Docs, but that's breakable.

    Just a thought, I don't know the reason.

    Anyway SQL2012 offers filestream data types, which mean storage of files inside the SQL Server, even with versioning. It's the best solution you can use on Windows for storing files in a confined environment, if you ask me.

    If you only want to know the difference between Blob and Memo (binary), you can bind PictureVal property of an image control to Blob fields directly. Otherwise they don't differ much. Blob was mainly introduced to comply more to the ansi standard of field types and make mappings of other databases types easier to understand.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de


    • Edited by Olaf Doschke Thursday, February 6, 2014 7:34 AM
    • Marked as answer by WWWilliam Monday, February 10, 2014 5:57 AM
    Thursday, February 6, 2014 7:33 AM
  • I disagree, Pavel. Even in server-based databases like SQL Server, best practice is to not store the file inside the database.

    Olaf talks about file stream, but didn't quite explain it correctly. With file stream, the file is stored outside the database as a disk file. However, SQL Server tracks that file. If it's deleted in the file system, SQL Server updates the reference in the database. If you delete the row in the table, the file is deleted.


    Craig Berntson
    MCSD, Visual C# MVP
    INETA Community Speaker
    www.craigberntson.com

    Thursday, February 6, 2014 2:40 PM
  • Files stored on the remote server are not visible to users so thousands of files in some folder is a nightmare for one administrator only... BUT imagine peer to peer network with shared files folder. Users have full access to everything and thousands of files in one folder (namely on FAT system) is not the best solution.  I know the strategy "Users have full access to everything" is not good but money are also important for customers...

    More important question to me is the shared files access support in Microsoft OS. It declines significantly and it seems Microsoft supports shared (DBF) files with higher and higher resistance...

    Thursday, February 6, 2014 3:03 PM
  • This is a requirement from one of my customers. They simply feel it's not easy to manage separated files
    somewhere else. I have decided to give them option to store files in the
    database and and then see how this thing is going. Many thanks to all of you.

    Thursday, February 6, 2014 10:02 PM
  • Hi WWWilliam

    could you please mark one or more posts as answer?

    TIA

    Pavel Celba - forum moderator

    Sunday, February 9, 2014 1:44 PM
  • Well, I'd still call that storing the files inside SQL Server. It's more than just a path to a file. Eg the filesystem used can be hidden from outside sql server, or remote access can be allowed.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    Monday, February 10, 2014 12:19 AM
  • This is a requirement from one of my customers. They simply feel it's not easy to manage separated files
    somewhere else.

    Well, you could do about the same as SQL Server does and introduce a directory for files at the database location. You can also limit access to that directory by impersonating a user you set up for read/write access to that folder. That keeps the files as is, but centralized by your application.

    You won't need to track what happens to the original files, they could even be deleted. Just ensure the user understands the files are persisted in your database and not just referenced, while indeed you do reference a file copy. They don't need to know the details, all that matters is, the files are stored at the database, they shouldn't care if this is inside fpt files or not and you won't need to use the fpt, which isn't only bad because of the 2gb limit. Think of corruption and how many files you would perhaps lose in case.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    Monday, February 10, 2014 7:14 AM