Hi Folks.
I’m just full of strange questions…
I have two entries into my sqlite Database.
One is a textfield, the other a text Area.
I need to change ’ (apostrophes) as they stop the entry from writing.
I use this code to replace the characters. The textfield works, the textArea doesn’t.
replacednameTextField = nameTextField.text.ReplaceAll("’","")
replacednotesTextArea = notesTextArea.text.ReplaceAll ("’","")
Can anyone tell me why?
Regards
Neither should work as you are attempting to assign a string to a control. Did you write it like this in both instances?
replacednameTextField.Text = nameTextField.text.ReplaceAll("’","")
replacednotesTextArea.Text = notesTextArea.text.ReplaceAll ("’","")
But aside from that, you should not be doing it this way. You should use a PreparedStatement or a DatabaseRow to insert your data. If you are using the new ExecuteSQL, this is made easier.
Hi Kem.
Yes I did. All these need to do is replace the single quotes as when there are single quotes, the record doesn’t enter into the database.
Regards
A single quote (apostrophe) is a reserved character in SQL. So if you have a string of text that contains a single quote, in SQL you need to escape the single quote by using another single quote, like so:
UPDATE someTable
SET Name = 'Greg O''Lone'
WHERE ID = 1234
Which might mean, using a search-and-replace in your Xojo code that substitutes ' with '' (2x single quotes, not a double-quote).
I hope that helps.
I’ll give it a try and let you know.
Thanks for the tip scott
Regards
Hi Scott.
That seemed to work… yaaay.
I’ll do a little bit more testing… just to make sure!
Once again, thank you.
Hi Scott.
It is an ALMOST working.
On my mac, it works fine.
On a windows 10 machine, the record with an ’ isn’t written to the database.
Regards
Hmm,
The single quote issue is specifically about character Encodings.ASCII.Chr(39).
Maybe on Windows you’ve got a curved or curled single quote in your Xojo code? Something that is not Chr(39)?
Use a prepared statement.
You should not be using SQL Injection for data that is entered by users. If you use prepared statements you won’t have to worry about the single quotes either.
Var SQL As String = "UPDATE someTable SET Name = ? WHERE ID = 1234;"
db.Execute(SQL, "Greg O'Lone")thank you for all the responses.
I’ll look up prepared statements, as I have never used them before, and advise.
I know these questions are simple for you, but as I said, I am self taught. Not an excuse, just a statement… which will soon become a PREPARED statement… 
Regards
Once you learn about prepared statements, you’ll wonder how you got along without them. More than that, you’ll wonder why you got along without them.
The new ExecuteSQL and SelectSQL statements make them even easier to use. Let’s say you wanted to insert string data into your SQLite database. You’d do it like this:
var sql as string = _
"INSERT INTO my_table (col1, col2) VALUES (?, ?)"
db.ExecuteSQL( sql, myString1, myString2 )
The engine swaps out the question marks with your values.
Later, you want to query the table:
sql = "SELECT * FROM my_table WHERE col1 = ? AND col2 = ?"
var rs as RowSet = db.SelectSQL( sql, myString1, myString2 )
You do not need to worry about the contents of the variables, so apostrophes, or anything else, can remain as entered, and there is no chance of error or SQL injection caused by “bad” content.
Hi there, Kem.
I took all the good folks advice and started playing with prepared statements before modifying my main program.
I can read with no problem from my database with a select, but I can’t insert.
Here is the code where I have 2 textfields and 1 area that I can enter data in… for checking the apostrophe. The commented out items are for future testing. I’m just using textfield1 to work out the bugs…
// new test of preparedSQL statement for insert
dim db as SQLiteDatabase
dim dbfile as FolderItem
dim rows as integer
dim i as integer
dim workRS as Recordset
Var ps As SQLitePreparedStatement
dbfile = getopenFolderItem("")
if dbfile <> NIL then
db = new SQLiteDatabase
db.DatabaseFile = dbfile
if db.Connect then
MessageBox "Connected"
ps = SQLitePreparedStatement(db.Prepare("INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?"))
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(6, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(7, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(8, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(9, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(10, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, TextField1.Text) // for group, with an apostrophe
'ps.Bind(1, TextField2.Text) // for name with apostrophe
'ps.Bind(2, "2021-04-13 21:20:20") //start time
'ps.Bind(3, "2021-04-13 22:20:20")//end time
'ps.Bind(4, TextArea1.Text) //notes with apostrophe
'ps.Bind(5, "600") //seconds
'ps.Bind(6, "10") //minutes
'ps.Bind(7, "0.166666") //hours
'ps.Bind(8, "Fred Flintstone") //techname
'ps.Bind(9, "000-000-0001") //cell phone
'ps.Bind(10, "000-000-0002")// office phone
ps.SQLExecute ()
end if
end if
Can you see what boneheaded mistake I’m making?
Regards
I’m beginning to think I should be charging a fee to use my name in examples…
Or change your last name 
I just spent $145 on a new passport, so I’m not doing that…
On the “ps =“ line the last quote needs to go outside the second to last parentheses, not before it.
@Anthony_Mott saw one error. But also, you can’t create placeholder tokens without filling them. And ExecuteSQL makes the whole thing easier:
var sql as string = _
"INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?)"
db.ExecuteSQL( sql, _
TextField1.Text, _
TextField2.Text, _
"2021-04-13 21:20:20", _
"2021-04-13 22:20:20", _
TextArea1.Text, _
"600", _
"10", _
"0.166666", _
"Fred Flintstone", _
"000-000-0001", _
"000-000-0002" )
Using your name?
I made all this stuff up Greg.
And $145 for a passport? I paid roughly the same for mine. I’m in Canada.
What country you hang out it?
Regards
Hi All.
Thanks to the advice here, I found my problem.
Anthony and Kem:
This line works:
ps = (db.Prepare("INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?)"))
If we look at my ORIGINAL line
ps = SQLitePreparedStatement(db.Prepare("INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?"))
I have SQLitePreparedStatement… which I also have in my instatiation.
Now to test with apostrophes
Regards