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?
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.
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.
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…
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
Else
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?
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?
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
Try
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.
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
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.
