I’m working with the a SQLIte recordset, and I can’t figure out how to update a simple recordset in the iOS version of Xojo. There seems to be no direct method in the SQLiteRecordSet class. Under regular Xojo all you need to do is write this code:
How can you make a simple update to an existing SQLite table. I looked at EEiOS and that just confuses me, and I can’t find any documentation on doing this simple operation.
In iOS you update an SQL database by issuing an update command and passing in the data as parameters, e.g.:
dim sqlCommand as text = "UPDATE myTable SET someValue = ?1, someOtherValue = ?2 WHERE myTableUniqueID = ?3;"
mySQLiteDatabase.SQLExecute(sqlCommand, someValueProperty, someOtherValueProperty, theTableUniqueIDProperty)
[quote=179235:@Jason Tait]In iOS you update an SQL database by issuing an update command and passing in the data as parameters, e.g.:
dim sqlCommand as text = "UPDATE myTable SET someValue = ?1, someOtherValue = ?2 WHERE myTableUniqueID = ?3;"
mySQLiteDatabase.SQLExecute(sqlCommand, someValueProperty, someOtherValueProperty, theTableUniqueIDProperty)
[/quote]
How do I make it so that text that is changed in a TextField is then saved to the correct field. What connect the TextField with the field in the database?
Those are column names. So let’s say my table consists of the columns Name, Email and CustomerID. So if I wanted to update CustomerID value 508 to Your Name, we would do:
myDB.SQLExecute("UPDATE CustInfo SET Name = 'James Redway', Email = 'james@redway.com' WHERE CustomerID = '508'")
Now you may want to do a prepared statement to allow for the odd characters, etc. but this is basically the syntax to end up with.
I find the website w3schools.com really good for a reference for SQL syntax.
myDB.SQLExecute("UPDATE CustInfo SET Name = ? , Email = ? WHERE CustomerID = ?", txtName.Text , txtEmail.text, txtID.Text)
each successive ? is “replaced” with the contents of a variable
the first ? by the contents of first variable (txtName.text in this case)
the second ? by the contents of the second
the third ? by the contents of the third
BUT it’s dont in a way you dont have to worry about quotes in the data
Yeah the parameterised method is definitely the best way, plus all the “kids” in the office think a little better of me now that I’m doing my SQLs that way…
Yeah dont do this on iOS
Manually concatenating a string together is a great way to avoid the safety we try to put in place and open yourself to sql injection attacks when you dont really need to
Using a prepared statements is basically the ONLY way to operate on iOS so what Jason wrote and what I wrote are slight variations on using them
Now I am really confused. Norman says not to use this with iOS. Thats what I am using… iOS. I don’t see how the ? are defined and how they match up with the information in the TestFields in the current recordset row.
So can I have a textfield can I write the code like this?
myDB.SQLExecute("UPDATE CustInfo SET Name = txtName.Text, Email = txtEmail.text WHERE CustomerID = 'txtID.Text'")
Anyhow, trying this again. ARGH.
So don’t do this directly. I don’t think Xojo for iOS has prepared statements yet. So the parameter method Jason advised is probably best.
But the direct syntax would look like
myDB.SQLExecute("UPDATE CustInfo SET Name = '"+txtName.Text+"', Email = '"+txtEmail.text+"' WHERE CustomerID = '"+txtID.Text+"'")
The problem with something like this directly is that it allows for SQL injection attacks and isn’t secure. Plus what if someone has a name like O’Malley. The single ’ would screw things up. So you want to use a statement, make sure that the entered text is all good and then do the SQL commands.
Now, most of the time, if I am executing a command entirely within code (i.e.: no user input), I do just write the entire statement and don’t mess with parameters, etc. But in any place where there’s user info you want to not do that…
What I just did is manually concatenating the string. That’s what you should not do. I just did it as an example of what the final command will look like to make it easier to understand.
[quote=179235:@Jason Tait]In iOS you update an SQL database by issuing an update command and passing in the data as parameters, e.g.:
dim sqlCommand as text = "UPDATE myTable SET someValue = ?1, someOtherValue = ?2 WHERE myTableUniqueID = ?3;"
mySQLiteDatabase.SQLExecute(sqlCommand, someValueProperty, someOtherValueProperty, theTableUniqueIDProperty)
[/quote]
So let’s take Jason’s statement.
Jason is building a SQL command. The table being operated on is myTable. someValue, someOtherValue and myTableUniqueID are all columns.
?1, ?2, ?3 are parameters as like in a method.
The execute command consists of the build command string, plus the values called out in the command string. It’s just like building a method and then calling the method.
So in my example we do:
dim sqlCommand as text = "UPDATE CustInfo Set Name = ?1, Email = ?2 WHERE CustomerID = ?3")
myDB.SQLExecute(sqlCommand, textfield1.text, textfield2.text, textfield3.text)
Now I didn’t do anything to clean up the textfield data. I don’t know if O’Malley would cause a problem when done like this. Maybe not. I’ve only used prepared statements a few times as most of my database work is all internal to my app.
My question Jon, is how do I assign the ?1, ?2, ?3 to specific TextFields that will be edited. In the EEiOS Xojo example it does not show how these values are assigned to specific TextFields. Sorry to be stupid about this. I just don’t get it.
Okay so that ?1 will be assigned to txtField1.Text, ?2 to txtField2.Text, etc… and this is assigned in the
myDB.SQLExecute(sqlCommand, textfield1.text, textfield2.text, textfield3.text) line?
Yes. ?1, ?2, etc are basically variables, parameters, etc. Whatever you want to call them.
The SQL command interpreter knows how to handle the parameter array you are passing. So it knows that the very first item is the command string. The next is parameter 1, the next is parameter 2, etc.
Okay, now I get it. … Finally Sorry to be so stupid about this. I just did not know where the ?1, ?2 came from and if they had to be declared somewhere else in the app. I will work on this and see what happens.
Thanks so much Jon, Norman and Jason. I really appreciate your help.
