// Fill the Record
PhotoDR.Column("PNom") = Photo_RS.Field("PNom").StringValue
PhotoDR.Column("Nom") = Photo_RS.Field("Nom").StringValue
PhotoDR.Column("Photo") = Photo_RS.Field("ID_Scan").StringValue
// Insert the Record
Politics_DB.InsertRecord("Photo_Tbl", PhotoDR)[/code]
But the code below fails with an error:
[code] // Get the Fields from the sqlite db to copy data from
Name_First = Photo_RS.Field(“PNom”).StringValue
Name_Family = Photo_RS.Field(“Nom”).StringValue
Name_Photo = Photo_RS.Field(“ID_Scan”).StringValue
The goal of the code is to make a copy of a TABLE (ID_Scan) from a .sqlite file and populate another TABLE (Photo_Tbl) in another .sqlite file.
The Fields Photo and ID_Scan are png files (reduced Peoples Photo stored in a TABLE that I want to move from the master .sqlite file). The Photos are stored as String and displayed in a Canvas. This works fine. I used that trick when I was unable to use .PictureValue at store time (in a Blob); details forgotten.
BTW: I used three variables (three lines) in the second code for debugging purposes, but at that time, I already knew where the problem lies (in the last line: the png image).
The error-message will tell you why
Most likely the Name_Photo (String) contains characters such as an Apostrophe, and will therefore create an invalid SQL Execute Statement. It could be a Name_First/Family, too.
When creating your own SQL Statement(s), you’ll need to properly escape the values.
It might be easier to use prepared statements, or a framework-functionality (such as your first code snipplet) which handles this for you.
The line I set in the original post have Name_Photo between quotes: Name_Photo is set into the Photo field and report no error.
I think I also removed the surroundings '' and I get:
Error True: unrecognized token: ""
Reported error:
Error True: unrecognized token: "'?PNG
"
The Error Code is True !
The ised code to report the error is:
Jürg:
I understand your answer (escaping). But I only pass a “binary file contents as string” and I do not understand why I have to escape something in this case.
Jean-Yves:
I understand your answer too, but same as above: what must have to escape ?
Jürg, Dave:
Thanks for the +100: I do not noticed Jürg last line (boo to me).
I tried to use PreparedSQLStatement, but my reading of PreparedSQLStatement leaves me as cold as ice (did not ring a bell in my brain / I was just like in front of a white blackboard). In other words: I do not understand from where I can start.
All: usually, when I woke up from a nap, my brain is in a far better mood (better understanding), but not now.
You’ve given the answer yourself. You pass (something) as String. When writing an sql statement (like the one in your second example we’re talking about), you convert everything to a String, because the statement itself is a string.
The issue is that your ‘binary content (as string)’ contains the apostrophe’s (and most likely a whole lot of other “unsafe” characters) - and that apostrophe ends the value-part unexpectedly if it’s not escaped, leaving a lot of gibberish after it, which SQLite obviously can’t interpret.
If you Base64 encode it, there won’t be such “unwanted and to-be-encoded” characters (because they are encoded in a way that uses “sql statement safe” characters).
Click the link to SQLitePreparedStatement.
Have a look at the examples there - especially that one:
Xojo does not used BASE64 'till recently (in the LOCAL Language Reference DB file). When I created my application to report sqlite data base file contents, I tried it with sqlite file from everywhere I found some and this place was one I was searching (Firefoxs Application Support folder too).
Firefox still do not use BASE64. (I just checked).
Also: the readers set their focus in the code that do not works, while I store the image as string in both used code; the one that works and the one that do not. Same DB, same images.
Conclusion:
I will use BASE64 to encode / decode the images): the advice seems to be of good sense (store string stuiff in a string field , not Binary in String field).
Thank you folks.
About PreparedStatement:
I think that I will try to use it without understanding anything (why, how, etc.) because I already look (read, read and read again) at the page without seeing the light This happens sometimes (even reading advices here, and sometimes another advice gaves the light !). Write code, execute, does it works ? Yes, continue; No ? Change the code and start back from the beginning. Time wasting, but something that works will come at the end of the day (week, year )
Becareful when encoding data in a database (such as suggested above)… It is perfectly fine, if YOUR app creates the data and YOUR App (or another you write) CONSUMES that same data, because they can both know you encoded the data (Base64 or otherwise).
HOWEVER, if you encode a picture using Base64, and a 3rd party app attempts to access said data, they won’t know, and will present the data “as-is”… which is to them, just a long string of text.