Class

SQLitePreparedStatement

Description

Used to create a PreparedSQLStatement for a SQLiteDatabase.

Methods

Name	Parameters	Returns
Bind	zeroBasedParam As Integer, value As Variant
BindType	zeroBasedIndex As Integer, type As Integer
ExecuteSQL	ParamArray bindValues() As Variant
SelectSQL	ParamArray bindValues() As Variant	RowSet

Method descriptions

SQLitePreparedStatement.Bind

Bind(zeroBasedParam As Integer, value As Variant)

Binds a value and its type for the prepared statement.

Use Database.Prepare to set up the bind.

This example creates a SQLite prepared statement to retrieve data from a Customers table. It then displays the data in a Listbox:

Var stmt As SQLitePreparedStatement

// note in a prepared statement you DO NOT put in the quotes
stmt = SQLitePreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))

// have to tell sqlite what types the items being bound are so it does the right thing
stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
stmt.Bind(0, TextField1.Text)

// perform the search
Var rs As RowSet = stmt.SQLSelect

ListBox1.RemoveAllRows
ListBox1.ColumnCount = rs.ColumnCount
ListBox1.HasHeader = True

Var hasHeadings As Boolean

While rs.AfterLastRow <> True
  ListBox1.AddRow("")

  For i As Integer = 0 To rs.ColumnCount-1
    If Not hasHeadings Then ListBox1.HeaderAt(i) = rs.ColumnAt(i+1).Name
    ListBox1.CellTextAt(ListBox1.LastAddedRowIndex, i) = rs.ColumnAt(i+1).StringValue
  Next

  rs.MoveToNextRow
  hasHeadings = True
Wend

SQLitePreparedStatement.BindType

BindType(zeroBasedIndex As Integer, type As Integer)

Specify types for multiple bind values. Each Database plug-in will have its own values.

SQLitePreparedStatement.ExecuteSQL

ExecuteSQL(ParamArray bindValues() As Variant)

Same as SelectSQL but does not return a result set. Executes and returns the result set of the prepared statement.

BindValues is optional and is intended for convenience only. If bindValues is not empty, ExecuteSQL will use the passed in values instead of the ones specified by calling Bind.

SQLitePreparedStatement.SelectSQL

SelectSQL(ParamArray bindValues() As Variant) As RowSet

Executes and returns the result set of the prepared statement.

The bindValues parameter is optional and is intended for convenience only. If bindValues is not empty SelectSQL will use the passed in values instead of the ones specified by calling Bind.

Interfaces

SQLitePreparedStatement implements the PreparedSQLStatement interface.

Notes

Note

The use of the prepared statement classes is rare because Database.SelectSQL and Database.ExecuteSQL utilize them automatically. See PreparedSQLStatement for information on cases where using prepared statement classes is appropriate.

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.

Note

When specifying parameters, do not wrap the value 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.

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 RowSet.

Var db As New SQLiteDatabase
Try
  db.Connect

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

  Var 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)

  Var rs As RowSet = ps.SelectSQL
  For Each row As DatabaseRow In rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
    " Age: " + row.Column("Age").StringValue)
  Next
  rs.Close

Catch error As DatabaseException
  MessageBox("Database Error: " + error.Message)
End Try

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

Var db As New SQLiteDatabase
Try
  db.Connect

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

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

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

  Var rs As RowSet = ps.SelectSQL("john", 20) // Values are passed here
  For Each Row As DatabaseRow In rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
    " Age: " + row.Column("Age").StringValue)
  Next
  rs.Close
Catch error As DatabaseException
  MessageBox("Database Error: " + error.Message)
End Try

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

Var db As New SQLiteDatabase
Try
  db.Connect

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

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

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

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

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

  Var rs As RowSet = ps.SelectSQL("john", 20)
  For Each row As DatabaseRow In rs
    MessageBox("Name: " + row.Column("Name").StringValue + _
    " Age: " + row.Column("Age").StringValue)
  Next
  rs.Close
Catch error As DatabaseException
  MessageBox("Database Error: " + error.Message)
End Try

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:

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

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, TextField1.Text)
Var rs As RowSet = ps.SelectSQL

Compatibility

All project types on all supported operating systems.