B4J Question [SQL] question about blobs

[Cableguy]

Hi guys,

In my ongoing project I will be storing a lot of cell phone taken pictures... and I mean A LOT, like at times 1000 pics/ per work week.
I read some Devs prefer saving to the DB only the path of were the picture is saved rather than using BLOB and saving the actual image into the DB....

I would like some input about pros and cons, like privacy protection, encryption, dB migration when needed, etc.

Thanks in advance

 

[MarkusR]

moving pictures in filesystem is more simpler but can create problems. new date stamps, duplicate names, wrong or invalid image link.

haveing a binary field with other informations in database is nice. you can also put a second binary field as thumbnail into.
at sqlite i would fear that the database get broken or get unreadable.

cons of a phone, files & database can lost or stolen.
i would upload the data to a save place on a server with a backup system.

encryption is good but not if it blocks you to access the data. at worst case you have a lot of trash.

do you making a photo documentation app?

 

[Cableguy]

It will be an ABMaterial based project management system.
Upon each project completion, a photo journal needs to be made to "prove" the completion state. in the field each team leader will have a companion app in order to know wich photos need taking and upload them to the ABMserver (DB).
The database wil hold several types of files, being the majoroty photos, but also PDF and others.

 

[EnriqueGonzalez]

The approach to store files on db is really bad, databases become slow by simply having a tinyblob column.

It is better to save the path and properties of the file (like creation time)

Just try making a select Id, blobcolumn from table with 100 rows and see how slow it is.

 

[Harris]

The only time I store an image to a table is on the device (when VERY small) - and I have learned to avoid this. It is just bad news - blobs are (IMHO) a hack...
I use a folder(s) to store images (mp4 s well) for all projects. Hard learned trial and error from the experts on this forum.

You can see from your AMB source, most images want a relative path - so give it one - instead of extracting from a blob and then setting a path..
Dummies shows these approaches..

When you want to delete a record - delete any image(s) too. Structure your tables carefully (path / dir as fields in all tables) and use the same method to take care of clean up.

Remember, deleting blobs - or records won't free table (DB) space unless you vacuum the DB (SQLite).

Thanks

 

[MarkusR]

i started a b4a test app but for some reason it will not load the images from recordset.
https://drive.google.com/open?id=1mBl-8IEVtqIlDN-UQoMkmOZn0aznOi_-