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
Return
End If
// 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
mSQLite.gAccueillis_DB.SQLExecute(sql_Update)
If mSQLite.gAccueillis_DB.Error Then
MsgBox “[2] Modification de la fiche (Photo) dans la base Accueillis.” + EndOfLine + EndOfLine +_
"Erreur " + Str(mSQLite.gAccueillis_DB.ErrorCode) + ": " + mSQLite.gAccueillis_DB.ErrorMessage
Return
End If[/code]
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 ?
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 !
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)
app.DoEvents
data = b.Read(b.length)
if b.ReadError then
ShowMsg(“Error reading file " + f.Name”)
end if
b.close
return data
End Function
[/code]
decode the file with
[code]Function StringToFile(data as String, fn as String) As FolderItem
'initiate binarystream
'and folderitem
dim bs as BinaryStream
'create the actual file
dim f as Folderitem = GetFolderItem(fn)
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.
To add
dim dbr as new DatabaseRecord()
dbr.IntegerColumn("id") = myID
dbr.BlobColumn("imageData") = stringData
mDb.InsertRecord("tablename",dbr)
To Update
rs = mDB.SQLSelect("SELECT imageData FROM tablename WHERE id = " + str(myID) ,false)
if rs <> nil AND not rs.EOF then
rs.Edit()
rs.IdxField(1).setString(stringData)
rs.Update()
rs.Close()
if mDB.Error then
mDB.Rollback()
end if
end if
Where rs is a record set
I use JPEGExporterMBS to export image to binary string
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
Xojo ?
Edit:
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).
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).
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.
@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.
Digression:
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).
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
mSQLite.gAccueillis_DB.InsertRecord("Accueillis",dbr)
if mSQLite.gAccueillis_DB.Error then
//failed
else
//Worked
end if
else
//found a record - update it
rs.Edit()
rs.Field("Photo_ID").pictureColumn = cPhoto.Backdrop //will default to Picture.FormatPNG, Picture.QualityDefault
rs.Update()
rs.Close()
rs = nil
if mSQLite.gAccueillis_DB.Error then
//failed
mSQLite.gAccueillis_DB.Rollback()
else
//worked
end if
end if
Note: This assumes that ID is the tables UID and there is only ever one row with that ID.