Save image into SQLlite DB

Databases
1

I’m digging around trying to figure out how to save images into an SQLlite Database. Seems a new requirement has arisen from the owner and I am rising to the challenge. I have managed to ascertain the field has to be a BLOB in the database but I am not sure how to read the image in and save it.

I also have ascertained that an imagewell is going to be needed as well. I am going to mess with imagewell after I post this and see how it works and try to understand it better. But the actual save an image thing… it’s eluding me.

Thank you!

2

An image can be converted to memoryblock (pictureIstance.ToData) and you can use it in a prepared statement (executeSQL) using a blob field

When you have an image in the blob field, you can obtain the picture using Picture.FromData and as argument the column nativeValue.

3

You can save an image in a blob field like this:

dbrec = new DatabaseRecord

dbRec.BlobColumn("MyImageBlob")            = MemoryBlockWithImage

You can save an image also like this:

dbRec.BlobColumn("MyFieldName") = MyVariableOfTypePicture.GetData(Picture.FormatPNG)

4

There’s an example project here that might help:

Examples/Database/SQLite/DatabasePictureTest

5

So I looked at the example and essentially copied what it was and tweaked for my needs. Annnnnd… kaboom!

' Primary Variable Declaration(s)
Var dbRow As New DatabaseRow

' Function(s) / Logic...
dbRow.Column( "barCode" ).IntegerValue = txtBarCode.Value.ToInteger
dbRow.Column( "plantImage" ).BlobValue = imgWellPlantPhoto.Image.ToData( Picture.FormatJPEG )

JHGCGCDB.AddRow( "tblPlantImage", dbRow )

And I get these errors:
frmImageAssign.btnSave.MouseDown, line 18
Parameter “format” expects enum Picture.Formats, but this is type String.
dbRow.Column( “plantImage” ).BlobValue = imgWellPlantPhoto.Image.ToData( Picture.FormatJPEG )

frmImageAssign.btnSave.MouseDown, line 18
Type mismatch error. Expected String, but got Int32
dbRow.Column( “plantImage” ).BlobValue = imgWellPlantPhoto.Image.ToData( Picture.FormatJPEG )

I used the formatting and itellicode suggestions… Hmmm

6

Hmm, nevermind, after poking at it some more I got the code to execute

dbRow.Column( "plantImage" ).BlobValue = imgWellPlantPhoto.Image.ToData( Picture.Formats.JPEG )
7

You might want to submit a feedback report against the example.

8

New issue… how do I get the image from the database to load into the ImageWell?

’ Primary Variable Declaration(s)
Var recSet As RowSet

' Function(s) / Logic...
Try
  recSet = JHGCGCDB.SelectSQL( "SELECT * FROM tblPlantImages WHERE barCode=?", txtBarCode.Value )
  If recSet <> Nil Then
    imgWellPlantPhoto.Image.FromData = recSet.Column( "plantImage" ).BlobValue
    recSet.Close
  End If
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

I get the following error:
frmImageAssign.btnGet.MouseDown, line 20
This method cannot accept an assigned value (it lacks an Assigns parameter).
imgWellPlantPhoto.Image.FromData = recSet.Column( “plantImage” ).BlobValue

9

Hmm, I get to submit my first feedback! Horky dorky!

10

Drop the equals sign and pass it as a parameter.

imgWellPlantPhoto.Image.FromData(recSet.Column( “plantImage” ).BlobValue)
11

Kewl! New error!

frmImageAssign.btnGet.MouseDown, line 20
You must use the value returned by this function
imgWellPlantPhoto.Image.FromData( recSet.Column( “plantImage” ).BlobValue )

My kids think I am nuts… I keep laughing at each new error!

12

I DID IT!!!

' Primary Variable Declaration(s)
Var recSet As RowSet
Var memBlock As MemoryBlock

' Function(s) / Logic...
Try
  recSet = JHGCGCDB.SelectSQL( "SELECT * FROM tblPlantImages WHERE barCode='" + txtBarCode.Value + "'" )
  If recSet <> Nil Then
    memBlock = recSet.Column( "plantImage" ).BlobValue
    imgWellPlantPhoto.Image = Picture.FromData( memBlock )
    
    recSet.Close
  End If
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

So I dug around some more and found that I need to convert the image ( thank you Michael Eckert for the tip that led me down THAT rabbit hole! ) to a memory block then assign it to the image well… and if freaking works!!!

Thank you ALL for you help yet again!

13

Oops, forum code. Sorry for that.

14

All good! You’ve been a great help over the years. I remember way back in RealBASIC days you were here to lend your knowledge!

1 Like
15

Okay… how do I clear the ImageWell? I can’t see any reference other than Refresh in the docs.

16

Set .Image = Nil?

17

Okay, so I have the upload, save and retrieve sorted out… now how do I write the image back to disk? I already know how to write data to disk but the image is eluding me… and it’s probably simple.

18

If you have a Picture object then the Save method will let you save it to disk.

19

Okay, I am reading the docs but not really seeing an example of what I want to do

fldrItem = New FolderItem( "/home/ian/Documents/The\ Nursery/Plant\ Image\ Files", FolderItem.PathModes.Native ) // Development Machine...
fldrItem = FolderItem.ShowOpenFileDialog(jpegType + pngType)

Above are the path and what files I want to look for specifically But I can’t seem to get it sorted out the way I want… I’ll keep poking at it and probably solve it , but then again…

20

Hi John,

I may offer another alternative that will keep the size of your SQLite db file size down. Instead of serializing your images and saving them as a BLOB in your table I recommend that you first save your image in the folder structure of your choosing and then just save the PATH to this image (path+filename) in your table. Then when you need to recall the image in code you can use the path to access the image.

HTH,
Mike

1 Like