B4J Question Store image to SQLite (from HTMLeditor and Clipboard)

[ThRuST]

I want to store an image into a SQLite database using B4J and also load it back, let's say the image is in a HTMLeditor1 control. Store an image directly from the clipboard is also an option I would like to cover. Any advice on how it's done?

I zipped up my small source code example for you to play around with post your changes as a zip thanks. You will also get my B4JUtils library as a code module. It makes it very easy to create/access the application folder and subfolders.

btw the loaded byte array should show the image in ImageView2.

Solution in v1.1 Thanks to MarkusR for providing help for the solution. Erel contributed with help as well.

v1.2 contains the new improved code provided by Erel and MarkusR. Check it out

 

[Eme Fibonacci]

I would think about convert the image to base64

 

[MarkusR]

I would think about convert the image to base64

wahhh, why base64 ??? why not just binary data bytes

 

[ThRuST]

That means it can be stored as a string into the database? What about the loading a buffer way? someone? Browsers use this base64 method I think. More examples is warmly welcome.

 

[ThRuST]

A code example is highly appreciated (for everyone reading this post as well) thanks

 

[ThRuST]

what's wrong with the base64 way adviced by @Eme Fibonacci ?

 

[ThRuST]

I found a solution by @Erel that is for B4A. However I want it to be modified to grab an image from HTMLeditor1 control. I also need to load it back in memory.

Blobs
The last two methods write an image file to the database and then read it and set it as the activity background.
Code:
Sub InsertBlob
    'convert the image file to a bytes array
    Dim InputStream1 As InputStream
    InputStream1 = File.OpenInput(File.DirAssets, "smiley.gif")
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(1000)
    File.Copy2(InputStream1, OutputStream1)
    Dim Buffer() As Byte 'declares an empty array
    Buffer = OutputStream1.ToBytesArray
  
    'write the image to the database
    SQL1.ExecNonQuery2("INSERT INTO table2 VALUES('smiley', ?)", Array As Object(Buffer))
End Sub
Here we are using a special type of OutputStream which writes to a dynamic bytes array.
File.Copy2 copies all available data from the input stream into the output stream.
Then the bytes array is written to the database.

Code:
Sub ReadBlob
    Dim Cursor1 As Cursor
    'Using ExecQuery2 is safer as it escapes special characters automatically.
    'In this case it doesn't really matter.
    Cursor1 = SQL1.ExecQuery2("SELECT image FROM table2 WHERE name = ?", Array As String("smiley"))
    Cursor1.Position = 0
    Dim Buffer() As Byte 'declare an empty byte array
    Buffer = Cursor1.GetBlob("image")
    Dim InputStream1 As InputStream
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
  
    Dim Bitmap1 As Bitmap
    Bitmap1.Initialize2(InputStream1)
    InputStream1.Close
    Activity.SetBackgroundImage(Bitmap1)
End Sub

 

[ThRuST]

Is BLOB type neccessary for only storing images? I use TEXT type in SQLite for this. It works fine with storing formatted text and images so I want to shed some light between the usage of this as well.

 

[keirS]

Please read https://www.sqlite.org/intern-v-extern-blob.html before storing images in SQLite.

 

[MarkusR]

what's wrong with the base64 way adviced by @Eme Fibonacci ?

long ago i make a modul for base 64 in visual basic 6 because we send mails out with attachments, and base 64 is still part of a mail content.
i believe in the beginning of web it was used to put 8 bit data into a 7 bit mail. (downward compatible)
base 64 is a slow conversion.
https://en.wikipedia.org/wiki/Base64

the db browser for sqllite using the field type blob

 

[MarkusR]

Is BLOB type neccessary for only storing images? I use TEXT type in SQLite for this. It works fine with storing formatted text and images so I want to shed some light between the usage of this as well.

blob means Binary Large Object (every file or data). large objects will be transported in chunks/packets to database as i know.
using text fields for binary data is alienated.

 

[ThRuST]

what about base64? I should read the link @keirS recommended perhaps this answers my question. Thanks @MarkusR

 

[Eme Fibonacci]

Will these images be available for apps on internet?

 

[keirS]

blob means Binary Large Object (every file or data). large objects will be transported in chunks/packets to database as i know.
using text fields for binary data is alienated.

There is practically no difference between the text storage class and the BLOB storage class in SQLite. They both have the same maximum size limit. The only real difference is text storage is encoded and BLOB storage contains exactly what was written to it.

 

[ThRuST]

No I just want to handle this locally. Save an image into a SQLite database. From a HTMLeditor view and as I also asked directly from the clipboard to SQLite
It's growing into a programming challenge, it's good to learn this

 

[ThRuST]

@keirS And that TEXT can handle both formatted strings as well as combined with images, in that case they are combined. And BLOB only images I assume. Base64 for VARCHAR or TEXT and BLOB needs binary data, am I right? which means image should go into a buffer before storing when using a BLOB type.

 

[ThRuST]

@keirS Thanks, I sent the previous reply before reading your answer. The only difference should be BLOB can only hold image data.
I assume base64 is the old standard that can be used but prefferably not if it's slower. Base64 should probably be stored in VARCHAR or TEXT.

 

[ThRuST]

I found a post how to encode/decode images with the base64 format. You'll find it here

 

[keirS]

@keirS And that TEXT can handle both formatted strings as well as combined with images, in that case they are combined. And BLOB only images I assume. Base64 for VARCHAR or TEXT and BLOB needs binary data, am I right? which means image should go into a buffer before storing when using a BLOB type.

Text columns are encoded according to the encoding setting of the database which is either UTF-8 or UTF-16. So you would use Base64 to store binary data in a text column. That's pretty inefficient way of doing though as there is a 33% overhead in size for Base64 compared to binary data. You can pass a Byte array to execnonQuery2 to update or insert a blob field.

 

[ThRuST]

I found this solution by Erel (Bytes to File) that I understand is better to use than Base64. It can be found here