SQLitePreparedStatement

From Xojo Documentation

Class (inherits from PreparedSQLStatement)

Used to create a PreparedSQLStatement for a SQLiteDatabase.

Methods
Bind SQLExecute
BindType SQLSelect

Notes

If you add a SQLiteDatabase directly to your project, then you need to upcast the result of the call to Database.Prepare to the SQLiteDatabase class. If you do not cast you will get a NilObjectException when you try to use it (refer to the example below).

These are the constants to use with the BindType method:

Constants
SQLITE_BLOB
SQLITE_BOOLEAN
SQLITE_DOUBLE
SQLITE_INT64
SQLITE_INTEGER
SQLITE_NULL
SQLITE_TEXT

SQLite can use 5 markers.

Markers
 ?
 ?NNN
 :VVV
@VVV
$VVV

In the markers above, NNN represents an integer literal, and VVV represents an alphanumeric identifier. For more information about these markers see SQLite binding.

Parameter binding does not do text substitution! Parameter binding substitutes each maker with your values at the database level for better security and performance.

fa-info-circle-32.png
When specifying parameters, do not wrap the placeholder in single quotes like you might do with text substitution. The value you are substituting should contain the entire value. For example, if using '%' in a LIKE clause, the '%' is part of the value you supply, not part of the prepared SQL statement.

For example, this is an invalid prepared statement because of the single quotes:

ps = App.DB.Prepare("SELECT * FROM Customers WHERE Country = '?'") // Invalid!

This is valid:

ps = App.DB.Prepare("SELECT * FROM Customers WHERE Country = ?") // Valid!

Multiple SQL Commands

If you prepare an SQL string that contains more than one SQL command then only the first command is executed .

For example a command such as this:

UPDATE foo SET bar = 1; UPDATE foo SET baz = 2;

will only execute "UPDATE foo SET bar = 1".

This is a specific security feature of SQLite.

Sample Code

The following sample creates an in-memory SQLiteDatabase, adds a table and populates it. It then uses a Prepared Statement to query the table and get a RecordSet.

Dim db As New SQLiteDatabase
If Not db.Connect Then Return

db.SQLExecute("CREATE TABLE Persons(Name, Age)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.SQLExecute("COMMIT")

Dim ps As SQLitePreparedStatement = _
db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, "john")
ps.Bind(1, 20)

Dim rs As RecordSet = ps.SQLSelect
If db.Error Then
// Always check for errors
MsgBox(db.ErrorMessage)
Return
Else
While Not rs.EOF
MsgBox("Name: " + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Wend
End If

This sample passes the values to bind using the SQLSelect method rather than calling the Bind method:

Dim db As New SQLiteDatabase
If Not db.Connect Then Return

db.SQLExecute("CREATE TABLE Persons(Name, Age)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 20)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 21)")
db.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 22)")
db.SQLExecute("COMMIT")

Dim ps As SQLitePreparedStatement = _
db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)

Dim rs As RecordSet = ps.SQLSelect("john", 20) // Values are passed here
If db.Error Then
MsgBox(db.ErrorMessage)
Return
Else
While Not rs.EOF
MsgBox("Name: " + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Wend
End If

This sample uses and reuses a Prepared Statement for the Insert:

Dim db As New SQLiteDatabase
If Not db.Connect Then Return

db.SQLExecute("CREATE TABLE Persons(Name, Age)")

Dim ps As SQLitePreparedStatement = _
db.Prepare("INSERT INTO Persons (Name, Age) VALUES (?, ?)")

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)

ps.SQLExecute("john", 20)
ps.SQLExecute("john", 21)
ps.SQLExecute("john", 22)
ps.SQLExecute("john", 20)
ps.SQLExecute("john", 21)
ps.SQLExecute("john", 22)
db.SQLExecute("COMMIT")

ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)

Dim rs As RecordSet = ps.SQLSelect("john", 20)
If db.Error Then
MsgBox(db.ErrorMessage)
Return
Else
While Not rs.EOF
MsgBox("Name: " + rs.Field("Name").StringValue + _
" Age: " + rs.Field("Age").StringValue)
rs.MoveNext
Wend
End If

This sample uses a SQLite Database that was added to the project, so it has to cast the result from db.Prepare. TextField1 is used to enter the search string:

Dim ps As SQLitePreparedStatement
ps = SQLitePreparedStatement(CustomerDB.Prepare("SELECT * FROM Customers WHERE FirstName LIKE ?"))

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, TextField1.Text)
Dim rs As RecordSet = ps.SQLSelect

See Also

Database, PreparedSQLStatement, SQLiteDatabase classes.