I have a remote SQL database with a column “picture”. It’s a TEXT column.
I create this string from a picture and load it into the “picture” column in the database
var fTemp as FolderItem = SpecialFolder.ApplicationSupport.Child("tempPicture.png")
var p as Picture = picture.Open(fTemp)
var mb as MemoryBlock = p.ToData(picture.Formats.PNG)
var s as string = mb
So far so good. When I want to retrieve it from the remote database with a URL connection, I’d like to send a JSON string with the picture’s string but the JSON string is UTF8 and when I convert the column contents into UTF8, it gives me this error message:
lexical error: invalid bytes in UTF8 string.
Is there any way I can convert a memory block into a valid UTF8 string?
Or is there an even better solution? Thank you!
Well, not really. The png will be in binary and that is what your string s will contain. So you have binary in your database. You could base64-encode s and I imagine JSON (about which I know nothing) will accept that.
Nothing wrong with that. But it will still be binary when you retrieve it, and you can’t turn that into UTF8. If you base64-encode it, that will turn the binary into ASCII, but make it 30% or so larger. ASCII is a subset of UTF8 so there’s nothing more to do after that.
Depending on the size of the pictures and the amount of pictures to be stored in the database, storing pictures directly in the DB is usually a bad practice.
It might be better to store the pictures on an Amazon S3 server / digitalocean spaces container and only have the ID or the direct URL to the picture in the DB.
That’s how I do it in an app I am working on. Users will be able to store up to 500MB of pictures each.
I have seen that debated on-line with regard to any binary data regardless of size… The majority seem to agree with you but not all. Why do you think it is a bad idea?
Certainly that is a more efficient use of resources but that potentially lends itself to synchronization issues.
I’m writing an app that deals with controlled documents as PDFs where where version etc have to be maintained but only the current vision has to be available for viewing. So everything need to be tightly controlled and maintained, preferably with as little effort as possible…
Most of the PDFs are in the 2MB range but a few get to 20-30MB. I’m using Postgres and It can sever data from local files if they are in certain locations… but from what I’ve read that usually is not practical if the DB ever gets hosted externally (initial deployment will be on an internal server)
I decided to store the PDFs in the DB in a table in bytea fields … is that a really bad idea this case?
There are many things to take into consideration in this case.
Size, quantity, storage, amount of users, bandwidth, costs, synchronisation…
For example if Christoph is storing PNG icons of each country flag (I know he works on a football manager app), even if each icon is 1MB, it isn’t an issue. The icons will only take 100-200MB in the DB which isn’t much.
In your case Karen, if your app is storing millions of PDFs, the monthly cost for a hosted database can be huge. If cost isn’t an issue and you favor synchronisation instead, it is easier to store the files directly in the DB.
The big advantage I see in using block storage for files and pictures is the ability to have a CDN to deliver them to the end user.
Every piece of software is different, your needs are certainly different than mine. I have some apps that have reached more than a million downloads, others that can have up to 10.000 users per hour, another that has 20GB of pictures…
Nowadays I always think about scale.
In my case it will likely never get more than 10,000 and that would take a 5-10 years. This is a stopgap to give us time to figure out what we need (2-3 years) as we grow. The plan is to eventually migrate to much more sophisticated (and costly) commercial software once that make sense.
Hi Jeremie, I think I’ll be going that way. I will store the pictures on some webspace and in the database I’ll have the URLs. Since the user has to have an internet connection when displaying the picture, I will not download it to the device but show it in an HTMLViewer.
Also thanks a lot to Kem & Tim since I did not know about Base64 Encoding & Decoding.