B4J Question [SQL] question about blobs

Cableguy

Expert
Licensed User
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

Well-Known Member
Licensed User
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

Expert
Licensed User
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.
 

Enrique Gonzalez R

Well-Known Member
Licensed User
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

Expert
Licensed User
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
 
Last edited:
Top