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 !
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.
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
decode the file with
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) bs = BinaryStream.Create(f,true) 'write the file bs.Write(data) bs.Close return f End Function
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 mDb.InsertRecord("tablename",dbr)
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
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).
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 S ..
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.
@Graham B How have you defined your Photo_ID column in your database?
Look at DatabaseRecord . Last Method.
@LoannisKolliageorgas I look on the code you have post but I don't see the rs.edit ?
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).
@Emile S PictureColumn
Look at DatabaseRecord . Last Method.
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 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.
Thanks Graham for your answer.
@Graham B You miss understood. There is no concept of a "PictureColumn" in a sqlite database.
University talk. (not a pun).
PictureColumn is a Type implementation you find in Xojo.
Also, RecordSet and DatabaseRecord are unknow @ sqlite.com
At last, I (try to) program in Xojo and this is not an easy task even with nearly 40 years in this business.
Before I forgot: I will (tomorrow 'cause it is too late for today 50 minutes to midnight) try your code. Thanks.