Save image dropped into canvas to sqlite database

I am trying ( again ) to save image files dropped into a canvas into an SQLite database. I have the record created but the image has not been added/saved yet. So I essentially want to drag a photo into the canvas area, then click the update button and update the existing record to have the image saved in the database. I have the blob field created already.

What does your code look like? You have a reference to the picture in the canvas. Then you need to add the memoryblock to the database.

Right now my code is a disasterous mess that I am attempting to clean up.

So what? Embarrassing code is posted to the forum every day.

1 Like
Var rsData As RowSet
Var mBlock As MemoryBlock

' Function(s) / Logic...
If ( dbFishy.Connect )  = True Then
  Try
    rsData = dbFishy.SelectSQL( "SELECT * FROM tblFish WHERE tyoeFish=?", cboSelFishy.Text )
    If( rsData <> Nil ) Then
      mBlock = cvsDropPic.Backdrop.ToData( Picture.FormatJPEG )
      
      
      rsData.EditRow
      rsData.Column("photoFish").BlobValue = mBlock
      
      
      rsData.Close
    End If
  Catch error As DatabaseException
    MessageBox( "Error: " + error.Message )
  End Try
  
ElseIf dbFishy.Error Then
  // Testing for issues in the DB tissues!
  Beep
  MsgBox "Database Error: " + Str( dbFishy.ErrorCode ) + EndOfLine + EndOfLine + dbFishy.ErrorMessage
  Exit
End If

Not so much embarrassing but incoherent.

Well… I think it worked…

Var rsData As RowSet
Var mBlock As MemoryBlock

' Function(s) / Logic...
If ( dbFishy.Connect )  = True Then
  Try
    rsData = dbFishy.SelectSQL( "SELECT * FROM tblFish WHERE typeFish=?", cboSelFishy.Text )
    If( rsData <> Nil ) Then
      mBlock = cvsDropPic.Backdrop.FormatJPEG

      rsData.EditRow
      rsData.Column("photoFish").BlobValue = mBlock
      rsData.SaveRow

      rsData.Close
    End If
  Catch error As DatabaseException
    MessageBox( "Error: " + error.Message )
  End Try
  
ElseIf dbFishy.Error Then
  // Testing for issues in the DB tissues!
  Beep
  MsgBox "Database Error: " + Str( dbFishy.ErrorCode ) + EndOfLine + EndOfLine + dbFishy.ErrorMessage
  Exit
End If

Okay, I got something in the database field, there is data there. But now I am trying to query the database and populate the canvas with the image data from the sqlite database.

// Variable declarations...
Var rsData As RowSet
Var mBlock As MemoryBlock

// This is where magic is supposed to happen... but... yeah...
If ( dbFishy.Connect )  = True Then
  Try
    rsData = dbFishy.SelectSQL( "SELECT * FROM tblFish WHERE typeFish=?", Me.Text )
    If( rsData <> Nil ) Then
      'cvsDropPic.Backdrop.FromData = rsData.Column( "photoFish" ).BlobValue
      mBlock = rsData.Column( "photoFish" ).BlobValue
      cvsDropPic.Backdrop.FromData( mBlock )
      
      rsData.Close
    End If
  Catch error As DatabaseException
    MessageBox( "Error: " + error.Message )
  End Try
  
ElseIf dbFishy.Error Then
  // Testing for issues in the DB tissues!
  Beep
  MsgBox "Database Error: " + Str( dbFishy.ErrorCode ) + EndOfLine + EndOfLine + dbFishy.ErrorMessage
  Exit
End If

Is cvsDropPic.Backdrop assigned to a picture? By default it is nil. Pull the blob from the database and create a Picture from it. Assign that picture to cvsDropPic.Backdrop. Don’t try to go directly to the backdrop.

use System.DebugLog, Break Points and make sure typeFish is UniqueIndex.

i would add a datetime field in your row to see that the update was done. (.SQLDateTime)
in the select i would select only the needed fields: id, photoFish

try to use the .PictureValue
https://documentation.xojo.com/api/databases/sqlitedatabase.html#api-databases-sqlitedatabase-data-types

if your data are not found
use a “not found image” or create a red rect picture with white text in it.

free text:

    rsData = dbFishy.SelectSQL("SELECT * FROM tblFish WHERE typeFish=?", Me.Text)
    If rsData.RecordCount = 0 Then
       cvsDropPic.Backdrop = notFoundImage
    Else
      cvsDropPic.Backdrop = rsData.Column("photoFish").PictureValue      
    End If
    rsData.Close