Thus, I am trying to learn SQLite and update Paul L.'s Baseball Team example to the current framework(s). This is a challenge. So far, I have been successful, but then we get to a method that involves SQL prepared statements and bindings. Using the code Paul provided results in a DBException.
I am not sure where to make changes, as I can’t figure out what bindings are and where faults may happen.
The code from the original example (with small updates) is:
//User finished updating the cell, so update
//related cell/column in database (db)
var sql As String
//Not sure I understand this
sql="UPDATE t_Team SET %column%=? WHERE ID = ?;"
var colName As String
Select Case column
Case 0
colName="Name"
Case 1
colName="Manager"
Case 2
colName="Location"
End Select
//create sql statement
sql=sql.Replace("%column%", colName)
//I don't get this
var ps As SQLitePreparedStatement
ps=db.Prepare(sql)
Call DBError
//This seems to break everything
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(1, me.CellTextAt(row, column))
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(1, me.RowTagAt(row).IntegerValue)
I would love to provide my code file, but I don’t think I can.
I apologize is this is a super simple thing to figure out. I have searched the forums, but I can’t find anything that helps.
If you use API-2 then for most cases, including this one, you can forget all about prepared statements. If you copy/paste your “method that involves SQL prepared statements and bindings” here we can probably help you simplify it. (And when posting code, don’t forget to use the </> button on it).
Relying to my own post, looks like you did provide at least most of that. So we can simplify it as follows:
//User finished updating the cell, so update related cell/column in database (db)
var sql, colName As String
Select Case column
Case 0
colName="Name"
Case 1
colName="Manager"
Case 2
colName="Location"
End Select
sql = "UPDATE t_Team SET " + colName + "=?1 WHERE ID = ?2"
db.ExecuteSQL (sql, colval, idval)
where colval is the value the user entered and idval is the id of the entry being changed. Where might these two items be available from?
You see I’ve dumped all the prepare/bind stuff. Not needed here.
Bind and BindType are the old way of doing things, along with the PreparedStatement classes. They were bulky and ungainly. They have been replaced with SelectSQL and ExecuteSQL, which do prepared statements for you behind the scenes. They’re much easier to use.
When you execute an SQL statement, you can “prepare it”, which acts to sort of compile it, in such a way that should you re-use that statement many times, you can just submit the semi-compiled version each time, which will speed up your app. In order to do the preparation, you have to bind parameters to it (if they are needed by your SQL), which is what the bind stuff is all about. As @Tim_Hare said, that is clumsy. The only way to avoid that, using the old SQLExecute and SQLSelect (API-1) statements, was to create your SQL statement entirely as a complete string, creating a fixed string by converting your variables to be parts of the string. So I used to do stuff like:
sql = "select mycol from mytable where name='" + username + "' and id=" + myid.ToString
.
You see that the property sql becomes a complete fixed string and so nothing then needs binding to it. The problem with this is SQL injection - a bad guy could replace the nice username string with bad SQL and erase your database (for example, as shown wittily at xkcd: Exploits of a Mom).
What the new (API-2) methods - SelectSQL and ExecuteSQL - do, as @Tim_Hare explained, is to do all that binding internally, so you don’ty have to bother, and so that you’re also protected against that sort of exploit.
Of course, now you will doing all those extra bind calls every time you approach the database, which is fine for me, for example, as there are essentially no occasions in my code where that would matter. But if you find you are submitting the same SQL statement to the database thousands of time, with the same variables, then there is scope for a speedup of your app by preparing and binding that particular statement and saving the prepared statement for re-use.
