Android Question .jpg pictures to blob in .db database

Enthousiastic

Active Member
Licensed User
Hello , I have a rbooks.db database from my books and there is a field for cover book in BLOB format that is null so far.The database has the tblrbook table with the following structure : Bid as integer,ISBN,Title,Authors,Publisher as Char fields and Cover as BLOB field. In C:\covers folder I have the corresponding images for the cover books in .jpg format. How can I write an update query to fill the table tblrbbok converting the jpg pictures to BLOB ? Your answer will be considerably important for me to continue . Many , many thanks in advanced to all of you !!!!
 

MarkusR

Well-Known Member
Licensed User
Example
B4X:
Type Photo(InspectionId As Int, PhotoId As Int, Photo() As Byte, Thumbnail() As Byte, Note As String, AppId As String, Barcode As String)

Dim sql1 As SQL = SQL1.Initialize(YourPath, "Inspections.db", False)

Dim arg As List
arg = Array As Object(Item.InspectionId, Item.Note, Item.Barcode, Item.Photo, Item.Thumbnail, Item.AppId)
    
sql1.ExecNonQuery2("UPDATE Photo SET InspectionId=?,Note=?,Barcode=?,Photo=?,Thumbnail=?,AppId=? WHERE PhotoId=" & Item.PhotoId, arg)
some helpers
B4X:
'convert a file content to a bitmap
Sub BytesToBitmap(Data() As Byte) As Bitmap
    
    Dim InputStream As InputStream
    InputStream.InitializeFromBytesArray(Data,0,Data.Length)
    
    Dim NewBitmap As Bitmap
    NewBitmap.Initialize2(InputStream)
    
    InputStream.Close
    
    Return NewBitmap
    
End Sub

'make a jpg as bytes
Public Sub BitmapToBytes(Bmp As Bitmap) As Byte()
    Dim Out As OutputStream
    Out.InitializeToBytesArray(0)
    Bmp.WriteToStream(Out, 100, "JPEG")
    Out.Close
    Return Out.ToBytesArray
End Sub

Sub ThumbnailFromBytes(Data() As Byte) As Byte()

    Log("Thumbnail FromBytes " & DateTime.Date(DateTime.Now))
    
    Dim Bitmap1 As Bitmap
    Bitmap1 = BytesToBitmap(Data)
    
    Log("Bitmap Size " & Bitmap1.Width & " x " & Bitmap1.Height )
    
    Dim Out As OutputStream
    Out = File.OpenOutput(File.DirInternalCache, "Thumbnail.jpg", False)
    Bitmap1.WriteToStream(Out, 70, "JPEG")
    Out.Close
    
    Dim BitmapThumbnail As Bitmap
    BitmapThumbnail = LoadBitmapResize(File.DirInternalCache, "Thumbnail.jpg",128dip,128dip,True)
    
    Dim NewData() As Byte
    NewData =  BitmapToBytes(BitmapThumbnail)

    Log("Thumbnail FromBytes Ready " & DateTime.Date(DateTime.Now) )

    Return NewData
            
End Sub
 

Xicu

Active Member
Licensed User
I suppose you have previously evaluated the disadvantages versus the advantages of saving the images in a database.
If not, I recommend a search information on the internet.
It all depends on the size of the photos, type of database to use (relational or not), number of records that you think you will have in the future (affects the backup time, access time), later need to access the images with editing programs, etc ...
 
Last edited:

Enthousiastic

Active Member
Licensed User
Up to now, the number of records in the above database are about 1300 with two image fields , cover’s picture and author’s picture. There is an increase rate of 6-10 records per month. Based on these facts, what do you suggest BLOB field or image name as text and the image be stored in a folder in hard disk , since you mentioned advantages and disadvantages. Is there any free web sql hosting who can hold and save such amount of data? Thanks again .
 

Xicu

Active Member
Licensed User
Up to now, the number of records in the above database are about 1300 with two image fields , cover’s picture and author’s picture. There is an increase rate of 6-10 records per month. Based on these facts, what do you suggest BLOB field or image name as text and the image be stored in a folder in hard disk
Personally, I suggest saving the images in a folder and the image name as text in the database:

These 1300 records (without blob field) must occupy a few megabytes in the database.
If we estimate that we have:
1300 records
2 images record (two blob fields)
and we estimate for example that the image weighs about 500 Kb.
Then we will have 1300x2x500kb = 1.300.000kb = 1.24GB.

If we include the images in a blob field, we would go from having a database with few megabytes to a big database(with respect to the few records it contains).
This size would surely affect performance, backups, etc ...

Is there any free web sql hosting who can hold and save such amount of data? Thanks again .
I dont know:(
 

Enthousiastic

Active Member
Licensed User
Α great thank you all for the time you had available to me for your instructions and the immediate answer !!!!!!!!!!!!!!!!!!!!!!!!!!!
 
Top