SQLIte: working with Picture

Hi,

the following code works fine for me:

[code] Dim PhotoDR As New DatabaseRecord

// 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

Politics_DB.SQLExecute(“INSERT INTO Photo_Tbl (PNom, Nom, Photo) VALUES (’” + Name_First + “’, '” + Name_Family + “’, '” + “Name_Photo” + “’)”)[/code]

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 People’s 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).

Why ?

Well, which Error-Message/Number?

The error-message will tell you why :slight_smile:
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.

Thank you Jürg for your answer.

The error is in the ID_Scan / Photo field. The original offending code is:

Politics_DB.SQLExecute("INSERT INTO Photo_Tbl (PNom, Nom, Photo) VALUES ('" + Name_First + "', '" + Name_Family + "', '" + Name_Photo + "')")

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:

MsgBox("Error " + Str(Politics_DB.Error) + ": " + Politics_DB.ErrorMessage)

Well, exactly what I suspected :slight_smile:

this won’t work:

INSERT INTO MyTable (MyField) VALUES ('That's cool!')

and needs to be written escaped:

INSERT INTO MyTable (MyField) VALUES ('That''s cool!')

so you may need to do something like this before using it in your sql-statement:

Name_Photo  = ReplaceAll(Name_Photo, "'", "''")

and most likely you’ll run into other characters that need to be written properly for an sql-statement:

´`‘’

I’m not aware of a Xojo-Function that will properly escpape a String for using it in a sqlite-statement.
PHP does: SQLite3::escapeString

But even then it says:

So you are most likely better off using PreparedSQLStatement

it is a safe way to encode64 the pictures before storing them to avoid these problems.
https://documentation.xojo.com/index.php/EncodeBase64

+100

Thank you for your answers.

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 :frowning: (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.

Maybe a further read later in the evening ?

Edit: typo.

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:

This works for me, no idea if there is something else I should be doing. As mentioned earlier, encoding to Base64 avoids a lot of these problems.

// convert picture to string then save string to database record
strPic = EncodeBase64(pic.GetData(Picture.FormatPNG), -1)

// retrieve from database when needed
strPic = myRecordSet.Field(“MyPics”).StringValue

// show selected picture
Canvas1.Backdrop = picture.FromData(DecodeBase64(strPic))

Thanks for your advices.

For the record:

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 (Firefox’s 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 :frowning: 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…) :wink:

What about storing the path of the picture in the database and the pictures in a separate folder, like the web sites and many more projects.
jjc_Mtl

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.