I have hard times (in fact, I cannot) trying to UPDATE a Record WITH a Picture.
That is a PictureType Field. When I put it in my SET
string, I get at compile time a type error (basically: this is not a String
So, I tried to made a second UPDATE, but it too failed:
sql_Update = "UPDATE Accueillis SET Photo_ID='" + cPhoto.Backdrop + "' WHERE ID='" + TF_ID.Text + "';"
So, I converted the Picture (PNG) as string:
[code] Dim mb As MemoryBlock
If cPhoto.Backdrop = Nil Then
// Do nothing
// Get the image data
mb = cPhoto.Backdrop.GetData(Picture.FormatPNG, Picture.QualityMax)
sql_Update = “UPDATE Accueillis SET Photo_ID=’” + mb.StringValue(0,mb.Size) + “’ WHERE ID=’” + TF_ID.Text + “’;” // Special Case: PictureColumn
If mSQLite.gAccueillis_DB.Error Then
MsgBox “ Modification de la fiche (Photo) dans la base Accueillis.” + EndOfLine + EndOfLine +_
"Erreur " + Str(mSQLite.gAccueillis_DB.ErrorCode) + ": " + mSQLite.gAccueillis_DB.ErrorMessage
I got an error message with the first bytes of the image (the replacement character, PNG, etc.).
Do you use to UPDATE a Record with an image, and if so, how ?
you must encodebase64 the string of the picture before inserting it into an sql query.
Tried: it works fine, do what I wrote, but not what I want.
When I add a Record, I store the image (in this case a Photo) in a PictureColumn and I get it back as a PictureColumn.
String Picture(s) are not involved here.
I think that the last time I wanted to do that (according to code I saw but not really look at), I create a new Record that I save and delete the previous one. Strange idea.
I fired Feedback, but lost my time (not found a clue).
Beside the above strange idea, someone have a clue ?
Edit: used code:
EncodeBase64(mb.StringValue(0,mb.Size),0) // I do not display the whole line; read above
Looks like an entry for one liners !
use a string column and not a picture column, they are useless (to me at least)
Convert the file to a String and store the string as you would store any string. I use this to store any file regardless of its length.
[code]Function FileToString(f as FolderItem) As String
'reads a file as BinaryStream
'and saves as string to be used in string field type
'no need for Blobs and fast
dim b as BinaryStream
dim data as String
b = BinaryStream.Open(f,false)
data = b.Read(b.length)
if b.ReadError then
ShowMsg(“Error reading file " + f.Name”)
decode the file with
[code]Function StringToFile(data as String, fn as String) As FolderItem
dim bs as BinaryStream
'create the actual file
dim f as Folderitem = GetFolderItem(fn)
bs = BinaryStream.Create(f,true)
'write the file
this is why there is a BLOB datatype… to handle any “binary” type data
This is how Tadpole as well as most other SQLite Managers deal with Images as well as “document” and other file types to be stored IN the database as opposed to stored as external links
If you define the column as a BLOB you can put a binary string into it.
dim dbr as new DatabaseRecord()
dbr.IntegerColumn("id") = myID
dbr.BlobColumn("imageData") = stringData
rs = mDB.SQLSelect("SELECT imageData FROM tablename WHERE id = " + str(myID) ,false)
if rs <> nil AND not rs.EOF then
if mDB.Error then
Where rs is a record set
I use JPEGExporterMBS to export image to binary string
I certainly was not clear enough.
I created the Field as Picture. I open Picture and save it to the proper Field in the Record.
I open the SQLiteDatabase and display the Picture in a Canvas with PictureValue.
Get an eye here: DatabaseField.PictureValue .
In short: using PictureValue allow me to Store an image (from an image file or Paste int he Canvas) in a Record that I can save to disk. I also am able to read the RecordSet that have an image (as PictureValue) and assign it to my Canvas.
My only problem is to update a RecordSet with an image. I tried PictureValue but I failed.
Also: using code shared above, I can store an image converted to string in a MemoryBlock and Encoded 64 bits
But code like that is too error prone and I will have to change the Add a Record / Read a Record code and this is
pretty stupid IMHO.
My own SQLite data base reader is able to display PictureValue fields too without any trouble.
So, either there is a bug, a lack in the documentation, a bug or people use an Atomic Bomb to kill ants (convert an image to string, BASE64 in bot read and write from/to the Database
BTW, I do not want to store an URL or a Path or
whatever examples I found in a Google quest
FWIW: I even add an Export a Record that
Export a Record in a .txt file and export the image in an image file.
Nota: I do not have many Records (4 .sqlite files) and the Pictures are Photo IDs and the size near 300 x 400 pixels
I still have to add an Import a Record method for obvious reasons (import data into a different .sqlite file if needed).
@Graham Busch code above should work.
thanks for your answer.
But Graham code will force me to change my Read / Write code. I think that I will go back to Insert a new Record (with all the data) and delete the previous Record (the one I want to modify). Not so hard to write (I only have to store the Unique ID and few lines to delete it at the end of the current write Method).
BTW: yes, it is a solution.
Hi @Emile Schwarz …
I look on the code you have post but I don’t see the rs.edit ?
check this… Call Edit prior to performing modifications to the current record
How have you defined your Photo_ID column in your database?
Null, integer, real, text or blob? If you have used blob then you should be able to use my above code to update using the picturevalue property instead of setstring / string value (which pretty much does the same thing behind the scenes)
If you have it set to text then the data is going to be encoded utf8 etc and more than likely will corrupt your data. Unless you convert your image to a string and encode correctly.
Updating binary data with a sql command in xojo is hard and using database classes to do it for you is best option.
FWIW, I reviewed my code and for whatever reason that I do not recall now, we delete the previous record and insert a new one.
Look at DatabaseRecord . Last Method.
I never use it (and I do not know why). Maybe absent in the shared examples for SQLSelect or SQLExecute ?
Edit: and I read that conversation. So, I probably not understant what it was about
@Tim: you may end with that solution because you do not found a better one (as I do too) ?
That solution is in the mood of time, not specific to you and me.
When I was young (50 years ago or so), some people told me about existing pyramides in Aegypt and America (Central / South) letting the door open to suggest Aliens where behind them.
The time and photos have proved otherwise: the former is used once and as a thumb, the latter is used outside, often, and to kill people (for the “gods”.
Yes, the base idea was similar: a square building in stone, but not its use.
There are other examples of similar ideas, different implementations (78RPM --> 33RPM: SPs and LPs; and in that case the two survived until the Compact Disc).
Look at DatabaseRecord . Last Method.[/quote]
You miss understood. There is no concept of a “PictureColumn” in a sqlite database.
The column is defined as NULL, INTEGER, REAL, TEXT, BLOB.
PictureColumn is a Xojo property / method to allow easily read and write a xojo picture to a database.
Generally you do not update binary data in a sql command, you use the classes to update it. Binary data will contain control characters that will screw up your sql commands.
This is the code, or something very simular (not tested at all), to add or update into your tables.
dim sql as string = "SELECT * FROM Accueillis WHERE ID='" + TF_ID.Text + "'"
dim rs as recordset = mSQLite.gAccueillis_DB.SQLSelect(sql)
if rs = nil or rs.eof then
//No record found - add a new one
dim dbr as new DatabaseRecord()
dbr.Column("ID") = TF_ID.Text
dbr.PictureColumn("Photo_ID") = cPhoto.Backdrop //will default to Picture.FormatPNG, Picture.QualityDefault
if mSQLite.gAccueillis_DB.Error then
//found a record - update it
rs.Field("Photo_ID").pictureColumn = cPhoto.Backdrop //will default to Picture.FormatPNG, Picture.QualityDefault
rs = nil
if mSQLite.gAccueillis_DB.Error then
Note: This assumes that ID is the tables UID and there is only ever one row with that ID.