Saving Files to SQL Database

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
hrcap
Posts: 86
Joined: Mon Jan 14, 2019 5:20 pm

Saving Files to SQL Database

Post by hrcap » Fri Apr 19, 2019 10:41 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 727
Joined: Fri Jun 27, 2008 9:00 pm

Re: Saving Files to SQL Database

Post by Mikey » Sat Apr 20, 2019 6:34 pm

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
Posts: 86
Joined: Mon Jan 14, 2019 5:20 pm

Re: Saving Files to SQL Database

Post by hrcap » Sat Apr 20, 2019 11:40 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 727
Joined: Fri Jun 27, 2008 9:00 pm

Re: Saving Files to SQL Database

Post by Mikey » Sun Apr 21, 2019 2:30 am

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
Posts: 1577
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Saving Files to SQL Database

Post by MaxV » Mon Apr 22, 2019 11:08 pm

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.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 8008
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Saving Files to SQL Database

Post by FourthWorld » Tue Apr 23, 2019 1:22 am

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
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”