Saving/loading canvas picture to DB?

I waited with this part of my app till the end because it looks very complicated. The Canvas is an impressive piece of object! I bought the “I wish I knew…” pdf about the canvas and it includes lots of stuff, except… how to save/load to a database.

I can drop an image (PNG) successfully on the canvas and it resizes very nicely. Now I need to get it saved to the column in the table and later read back into the canvas again when selecting a row on the Listbox. I do not get any crashes, so I assume the code is formulated correctly, however noting is being save to the DB yet. This it the UPDATE code:

app.db.ExecuteSQL("UPDATE Characters SET Character_Picture = ? WHERE ID = ?", CanvasDroppedCharacterImage.Backdrop.fromdata(picture.FormatPNG), CharacterID_Selected)

In the Change event of the Listbox, I have this code to read it back again:
CanvasDroppedCharacterImage.Backdrop = rs.Column("Character_Picture").PictureValue //Text field

Looking in the DBFile in Xojo, I see that the Character_Picture columns Type is Unknown, could that be the reason the image isn’t saved? The column in the DB is set to BLOB value. Where am I going wrong?
Screenshot 2022-09-25 at 07.46.06

I would encodebase64/decodebase64 the picture before storing it in the database
database accepts strings, not memoryblocks


Or use SQLitedatabase.CreateBlob.

Look up the doc for SQLiteDatabase, and then the CreateBlob method. You must have previously created the database to have a column of type Blob, then you can specify that in your call to CreateBlob. Having done that call, you use Write to write the picture data (a memoryblock) to the database. The doc has an example.

1 Like

Yes, I am using a sqlite db table with a blob column type. Trying to make it work since yesterday, but no luck still. Another question: would it be better to save the dropped canvas picture to a folder on the hard drive instead of to the DB? There will be only maximal 20 thumbnail sized pictures being used in a single project.

Thanks, I will look into this as well.

What does “no luck” mean?

It’s simply a matter of taste. You can do both, probably better stop wasting your time (now) and place the images in the Application and load them from there (or in your application support folder loa-cated in Application Support…

The pictures are not included by default. They are profile pictures from the persons in the DB dropped on the canvas by the user.
Screenshot 2022-09-26 at 13.40.04

I mean that I cannot get the code to work :slight_smile:

Create a Hot Images folder… At load time (Window Open time) the software scan that folder, add the image name into the list / store the FolderItem in that RowTag (or CellTag) and use it to display the image in the Canvas.

When you add an image, it will appears at next Window Open time (or from a Button click if such Update button exists).

I do not think a Data Base file can be mandatory here (more complex).

You can use a simple TEXT field in the Sqlite DB to store the picture data, when encode as Base64:

app.db.ExecuteSQL("UPDATE Characters SET Character_Picture = ? WHERE ID = ?", EncodeBase64(CanvasDroppedCharacterImage.Backdrop.GetData(Picture.FormatPNG), 0), CharacterID_Selected)

To read the picture from db, you can use this in you function loading the db data:

If rs.Column("Character_Picture").StringValue <> "" Then
  Var p As Picture
  p = picture.FromData( DecodeBase64( rs.Column("Character_Picture").StringValue ) )
  CanvasDroppedCharacterImage.Backdrop = p
  CanvasDroppedCharacterImage.Backdrop = Nil
End If

Do I conclude that the way in which you have no luck is a big secret that you are not going to share, such that we have to spend the next 1000 years guessing what the problem is?

FromData data As MemoryBlock Picture ← ???

what you want is ToData

data is the same as the contents in a file somehow a byte array.

field type in databases are usually binary or blob.
it make no sense to convert binary data to an encoded string.

Sorry Tim. I am trying all kind of ways to get this to work and feel frustrated after trying for 2 days, the suggestions I find in the forum and online searching feel like they are contradicting each other like use BLOB (or use string) in the database as type with/without Encoding/decoding to string/picture type. I tried many approaches but I only get errors. It feels as if I am the first person ever trying to save/load a picture to a database which can not be the case I am sure. I assume that this is standard practise but then why isn’t there a single way of doing this?

I save User Avatars into the SQLite database into a field with type BLOB and value:

EncodeBase64(tempAvatar.ToData(Picture.Formats.PNG, Picture.QualityDefault))

Seems to work OK. I use Base64 since I want to maintain the flexibility to change DB brand (eg PostgreSQL) without changing my code.

1 Like

OK here’s my method to write a blob from a memoryblock into a given database row.

Subroutine dbwriteBlob (Extends dbh As SQLiteDatabase, absid As Uint64, body As MemoryBlock) as Integer

Var  blobPtr as SQLiteBlob

blobPtr = dbh.CreateBlob ("imagetable", "imagedata", absid, body.Size)
if  (blobPtr=Nil)  then Return app.READ_DB

  if  (body.Size=0)  then       // Handle this separately to avoid exception
    blobPtr.Write ("")
    Return app.READ_OK
  end if
  blobPtr.Write (body)
  Return app.READ_OK
Catch error as IOException
  app.dbErrorsCount = app.dbErrorsCount + 1
  Return app.READ_DB
End Try

Call as follows:

result = db.dbwriteBlob (rownum, myMemBlk)
if  (result<>app.READ_OK)  then return               // return if error

The database table is imagetable, and the blob column is called imagedata.

1 Like

If you have the MBS plugins, I found the MBS plugin the easiest to use for saving pictures to a DB BLOB field. Also, compressing pictures into a jpeg uses less database space and resources. You also may want to resize your pictures so that they are never larger than a desired max height or max width.

// p is a picture, 100 is the jpeg quality
Var pstrng As String = PictureToJPEGStringMBS(p,100)
// Store the string into the DB
// convert the string back to a picture after retrieving it from the DB
Var p As Picture = JPEGStringToPictureMBS(dbstrng)
// Use the picture
1 Like

This should be very straightforward. I store images in a blob field and read/write them via StringValue (wrote the code before PictureValue was a thing).

You’re getting conflicting answers because there a several correct ways to do this.