Store picture in blob

As a real beginner in Xojo it is difficult to me to solve problems without examples.

Can pleas somebode write down the lines for me, how I store a picture in a blob field in database and how I get it back.

Because the hint, store it like always doesn’t work :slight_smile:

Please include the SQL-sentence and do I afterwards sqlexecute or something else?

If dpPic is a picture object then

sql="Update Anlage SET dbPic = " + dbPic

doesn’t work

How can I express this update or insert ?

and in the table dbPic is a BLOB-Column

You store the picture as binary in the BLOB I believe.

Might look at this post

Actually I want a previous dropped Picture to be stored - in any way it works:

Dim dbPic as Picture = Canvas1.BackDrop

an now how do I get this dbPic to the blob?

Dwane - I do not see any solution in this forum ???

Please use Prepared statement for the update. You can encode picture as JPEG for example with Picture.GetData function.
For query, you get back the jpeg data from recordset via stringValue of the field and than use Picture.FromData function.

Looking at the example project from the topic that was linked it looks surprisingly easy using a RecordSet.

Yes Tim this looks easy. But I want to update and do not know, where to put the “WHERE id =” + id
INSERT was quite easy.

And Christian:
Ich bin leider ein ziemlich blutiger Anfnger, das mit dem stringvalue htte ich gewusst, aber der memory-block und das getdata-zeugs … alles bahnhof. Und geht das auch fr canvas.backdrop?
Ich mcht einfach kleine Bilder in einer Liste abspeichern und spter am Bildschirm oder drucker ausgeben.
Scheint aber fr mich zu schwierig zu werden.
Noch ne Frage offtopic: Das treffen in Wien findet das statt und hat da ein blutiger Anfnger wie ich was verloren?

Not tested, but check if something like this works, change and adapt for UPDATE if it works:

  Dim db As New SQLiteDatabase
  If Not db.Connect Then
     MsgBox "Error conn"
  End If
  db.SQLExecute("CREATE TABLE Images (Id, TheImage)")
  Dim ps As SQLitePreparedStatement =  db.Prepare("INSERT INTO Images (Id, TheImage) VALUES (?, ?)")
  ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
  ps.SQLExecute(1, myPictureImage.GetData(Picture.FormatPNG,100)) // Inserts image as PNG High-quality

THKS Rick - This looks to me understandable. I will try it today, but now I have to leave.

You convert a picture to a string and then you can insert, select and update it as any other string in the database.

As Christian wrote, use picture.getdata to retrieve the picture as a memoryblock. You can convert the memoryblock to a string simply by assignment:

[code] Dim p As Picture = Canvas1.Backdrop
Dim mb As MemoryBlock = p.GetData(Picture.FormatJPEG, Picture.QualityMax)
Dim s As String = mb

Dim sql As String = “UPDATE myDB SET mypic = '” + s + "’ WHERE id = " + recordid[/code]

However, you may notice a degradation of picture quality over subsequent selects and updates of a picture.

I prefer to use MBS plugins to convert picture to a string:

Function GetPictureAsString(p As Picture) As String Dim s As String = PicturetoBinaryStringMBS(p) Return EncodeBase64(s,0) End Function

To convert the string back to a picture I use DecodeBase64(myRecordset.Field(“mypic”).StringValue) plus MBS plugin’s BinaryStringtoPictureMBS(photostring) .

Check documentation:

You must lookup the SQL syntax for crud (create/retrieve/update/delete) operations from the homepage of the specific database you are using.


Send insert, update and delete statements with sqlexecute to the database:

Or use sql prepared statements, and check examples :

To update an image

  Dim ps As SQLitePreparedStatement =  db.Prepare("UPDATE Images set TheImage = ? WHERE id = ?")
  ps.BindType(0, SQLitePreparedStatement.SQLITE_BLOB)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
  ps.SQLExecute(myPictureImage.GetData(Picture.FormatPNG,100), theID)

Recordset.Edit / RecordSet.Update would work as well.

PicturetoBinaryStringMBS? That’s my own algorithm to encode it.
I’d really suggest to better use a standard format like PNG or JPEG.
And Picture.GetData/Picture.FromData are okay for most people. If you need more speed or options, we have still a lot in our MBS Plugins.

And if done right with Blobs, the Encode/Decode Base64 is not needed. That’s only for storing image in TEXT fields.

Never insert binary content right into a SQL string. It’s almost like a random auto SQL injection. Prepared statements takes care of binary content for you. :slight_smile:

Don’t use JPEG to store updatable images, for write once, read many, that’s ok. JPEG is a lossy format. Every recoding will result in degradation of the content. PNG is optionally lossless and I hope that at 100% quality XOJO opts to use this format, so no one simple pixel should be changed over n interactions.

Actually for me it is write once and read many. The reason, why I have to do “UPDATE” instead if “INSERT” is, because the picture is added later, when the rest of data is already there.

Thanks Oliver, I’ve chosen your method and it worked perfectly from beginning (I had only to add the encoding, because otherwise there was an error “unrecognized token”)

Be careful. The presented Oliver’s way maybe is the easiest but have potential crashing problems depending on the content created (excepting when using his GetPictureAsString() that increases the data 33% and have some performance penalties)

1 Like

[quote=97821:@Robert Blazek]Actually for me it is write once and read many. The reason, why I have to do “UPDATE” instead if “INSERT” is, because the picture is added later, when the rest of data is already there.
Store the image in a separate table, keyed by the primary ID of the original table.