Saving Files to SQL Database

[hrcap]

Evening All and Happy Easter

Is it possible to import a file such as a PDF file or a Microsoft Word file into the local LiveCode stack and then save it to the local SQLite database and a remote mySQL database?

Would this involve using base64encode?


Many Thanks

[Mikey]

If you're going to store it remotely, then you should probably base64 encode it. Is there a reason to put it into the database instead of storing it separately on the server and then retrieving it when necessary? You would then just store the path to the file in the database instead of the entire file. If for some reason you need to store the entire file, then look at the BLOB data type.

[hrcap]

Hi Mikey

Thanks for the reply. I was intending to store it inside the database using the BLOB type as you described, but I was unsure if this was possible so thanks for confirming that it is.

Is there any disadvantages over doing this as opposed to storing the file separately on the server?

[Mikey]

If you're storing an entire file, I don't see a benefit in a traditional SQL database. The client has to deconstruct the file, convert the contents for transport to the server, and transmit the contents and the properties (metadata) for the file. The server has to store the properties and the content separately in one or more tables, then reverse the process when a client is to receive the file (i.e. transmit the properties and the content, then rebuild the file).
If you want to store the file for retrieval, then I think it's much easier (not to mention more efficient) to have the client zip the file to prepare it for transport, then send it to the server. The server then deposits the zipped file somewhere, and the database only keeps the path to the zipped file and whatever metadata you care about (like the name and description). When transmitting the file back to a client, the file is already zipped and can be just sent back across, where the client can decompress it.
BLOB fields are useful for storing lots of things that we don't have a good way of storing any other way, but I think you will find that in this case you are making your life harder than it needs to be. In your case, I would use a BLOB to store a thumbnail, for example.
If this is a bit of a document management solution then have a look at scanhammer, too, to see if it might help (then turn around and contribute some code to the project...)

[MaxV]

There a PRO and cons.
The PRO are:

• it's more easy manage files, it's all on the DB
• backup, you just need to backup the DB
• it's very easy to delete files

Yes, the DB size will increase soon, but after a rapid increase, the size tends to stabilize.

[FourthWorld]

If you backup the site with tar it doesn't matter. If you backup with rsync it'll likely be faster because it won't have to re-download the image file just because some other dates in the table has changed (depending on unpredictable affects of writes across the btree over time; rsync is pretty clever).

If you do use the DB, best to store BLOBs in a separate table so performance on metadata queries is unimpeded by the large records in the btree.

Also, storing media in a DB rather than in files precludes the use of most commodity CDNs.

BLOB vs file path is an old debate no one has reached agreement on.

Tons to read on the subject:
https://duckduckgo.com/?q=blob+vs+file+mysql