Storing files in database - share your experience

greg · Post by **greg** » Sat Oct 03, 2009 10:09 am

Hi there,

I have recently run into problems with storing documents in various formats in AwareIM MySQL database. It was first reported to me that some queries of records with attachments returned System Error, while other searchers retrieved all records just fine.

Another problem with stored data files manifested itself during test run of the full database backup and restore. The sql backup file did not restore. The restore process halted on some, but not all, records containing stored data files. I attributed this problem to some binary characters confusing MySQL database.

Please share your thoughts and experience with data file storage, have you run into similar problems? Do you prefer storing data in the database or you only store the file path?

greg

pbrad · Post by **pbrad** » Sat Oct 03, 2009 1:48 pm

Hi,

If you really want some fun, try migrating from one server to another server when your database is gigabytes in size due to documents being stored in the database such as images and pdfs.

What I do is to import the document, then export it to a file system and maintain the path information to the document only in the database. Then you can either re-import it at runtime or even just point directly to the file and open it up in the browser. I used to import the documents at runtime but because I had thousands of documents spread against several business spaces I was started to get a weird error if two documents were imported at the same time by different business spaces and they would transpose themselves.

Now I just point to them within a secure folder and it works flawlessly without the hassles that occur with needlessly large database sizes.

Cheers,
Pete

tford · Post by **tford** » Sat Oct 03, 2009 1:55 pm

Ditto what Pete said.

The reason I began to do it this way was that earlier versions of AwareIM had too many User steps to display docs. Storing the path, I was able to control the display process.

thom · Post by **thom** » Sat Oct 03, 2009 2:16 pm

Greg,

I have more experience on SQLServer than MySQL, but up until a couple of years ago I believe the greater DBA community was of like mind on storing documents or files within a database column. It was generally recommend that if you were dealing with finite number of files (i.e. like Employee Images) that is was ok to store these files in a data object - but if you were going to store an unlimited number of files of varying sizes and types that you should store a pointer (a file path and name) within the database to a file server. The reason for this was mostly because the files were stored in BLOB or IMAGE data types (which are quirky at best and cause problems like you are experiencing)

With the advent of SQLServer2005/2008 we saw the support of new data types (new to MS, but not other db types) that allowed more effective storage of files within a column. The new data types are VARBINARY(MAX) and/or FILESTREAM. There was also some really cool stuff done here to compression/decompress file stored in a column using these types that had pretty impressive performance results.

Now, in AwareIM, the files are stored in an IMAGE data type (on SQLServer, but I am guessing its the same in MySQL). If this is true than I would recommend storing the files on the file server and saving the relative path or url on the database. I am not sure if or how Aware could do this but I am interested enough to try it out and I will post my comments here...

I apologize for the long winded response - just my gentle nudge to Aware that I would like to see some of these other data types (i.e. time, varchar(max), varbinary(max), and xml) supported ....eventually.

thom

ACDC · Post by **ACDC** » Sat Oct 03, 2009 2:34 pm

There is obviously pros and cons as to which way you do it. I understand the issue of backing up and restoring the database and the complexities, but are there any disadvantages of storing the file outside the database. ?

It seems to me if you have everything encapsulated in the database its more portable than having to worry about a secondary file store or file system. So maybe the answer is to find a solution to the back up and restore problem when there is large file sizes

The one issue that always concerns me is what happens if the file uploaded contains a virus, how do you handle this ? In some way if its isolated in the database I feel more protected

It would be good to hear further comments on this subject

ACDC · Post by **ACDC** » Sat Oct 03, 2009 3:40 pm

this article seems to shed more light on the topic

http://books.google.co.za/books?id=zzlh ... em&f=false

thom · Post by **thom** » Sat Oct 03, 2009 7:15 pm

ACDC - I agree with your first post, but it has been my experience that this is really bigger than just a backup/restore issue. The use of the BLOB/IMAGE data type is very problematic.....and over time you will see that some of your data that is stored in these columns is corrupted-all links will point you back to the inherent problems with data type rather than any specific code blocks that loads or retrieves the data.

Your second thread - referrences both the .NET framework as well as the use of the FILESTREAM data type - both of these are currently outside the AwareIM framework, although it does support encapsulation, but until these types are supported by Aware the best method is store them outside as Pete and Tom outlined.

If you have experience within Aware of doing it one way or the other I am keen on knowing your approach, benefits or pitfalls....

thom

rocketman · Post by **rocketman** » Sun Oct 04, 2009 4:34 pm

Hi all - just picked up on this thread because I've just started creating functionality to upload and store our committee minutes and flying manuals into our aware system.

So far Ive just used an attrib of the document type which I'm assuming puts the doc directly into the database, so if this is going to give me problems over time, I'd be interested to see some code snippets dealing with - how to allow a user to upload a doc and save it to disc without fist loading it into the database. Or is it a matter of uploading to database, exporting to disc, deleting the DB version and storing the path to the file into a plain e
text attrib

tford · Post by **tford** » Sun Oct 04, 2009 5:05 pm

Rocketman,

Or is it a matter of uploading to database, exporting to disc, deleting the DB version and storing the path to the file into a plain e
text attrib

Yes, that's how you do it. I use a Document attribute in LoggedInRegularUser to temporarily store the uploaded doc.

ACDC · Post by **ACDC** » Tue Oct 06, 2009 2:12 pm

If high levels of security are needed on the stored files, then one has to use the Database, I dont see any other way.

I have run some tests on backing up and restoring blobs in MySQL and the only problem that surfaces is when the files become larger than 1mb.

However if you set the "max_allowed_packet = 20M" (by default it is set to 1M) in the my.ini file of MySql server, this problem goes away and Restore and Backup can be run without a problem

tford · Post by **tford** » Tue Oct 06, 2009 2:17 pm

If high levels of security are needed on the stored files, then one has to use the Database, I dont see any other way.

Why do you say this?

ACDC · Post by **ACDC** » Tue Oct 06, 2009 2:37 pm

Well, sometimes you may not have full control over the the file system. If you are committed to security levels e.g.such patient records, imaging etc etc, I think the DB and application rules let you control the security far better .

[email protected] · Post by **[email protected]** » Tue Oct 06, 2009 5:28 pm

Tom,

Do you have a sample BSV that I could look at? I'm currently creating a CRM solution. I am currently storing the documents in the database, but from the sounds of it -- I should be storing them at the file level.

I put a rule at the BO level that does an export, but it exports all the attributes and not just the document. Not sure how to just export the document so that I can just embed a link onto the form for viewing the object.

tford · Post by **tford** » Tue Oct 06, 2009 7:00 pm

Sample BSV is at http://www.awareim.com/forum/viewtopic. ... 3269#13269

kklosson · Post by **kklosson** » Tue Oct 06, 2009 11:00 pm

This is an interesting thread. I have a BO that does nothing but store documents related to another BO. It's a key feature. I looked at the sample BSV (though not the code). It looked as though this was constructed to migrate the documents from the BLOB to the file system. My app is still in development and if it's a bad course to store documents in the Document attribute, then I should change my course now. However, I can't really imagine how to implement this in Aware IM. As stated, I have a Case File thing and I need to allow the attachment of many files. This would probably require a file stricture having a file folder for each case file or something.

Is this really a course of action I should consider? And if so, does anyone have a BSV that demonstrates this?

Thanks