iOSSQLiteDatabase.SQLSelect

From Xojo Documentation

Method

iOSSQLiteDatabase.SQLSelect(sqlstatement As Text, ParamArray values() As Auto) As iOSSQLiteRecordSet

Supported on Mobile(iOS).

Used to run an SQL statement that returns an iOSSQLiteRecordSet, such as a SELECT statement. You can optionally supply a list of values that will bind to parameters (specified with "?") in sqlstatement.

Parameters

Value Description
sqlstatement The SQL statement to execute.
ParamArray A list of values that will be bound to any parameters (usually specified using the "?" character) in the supplied SQL statement.

Notes

Returns an iOSSQLiteRecordSet that you can iterate through to get the data. If the record set is Nil, then no rows were found for the supplied SQL statement.

Any parameters you supply are used to bind to the prepared statement. Use the binding placeholders (usually the ? character) to indicate where the parameters will get bound. Parameters are bound to the SQL left to right. The first supplied parameter binds to the first placeholder in the SQL. These are the other bind placeholders supported by SQLite:

Bind Placeholder Example
 ?  ?
 ?NNN  ?1
 :VVV  :City
@VVV @City
$VVV $City

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

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:

rs = App.DB.SQLSelect("SELECT * FROM Customers WHERE Country = '?'", "USA";) // Invalid!

This is valid:

rs = app.db.SQLSelect("SELECT * from Customers WHERE Country = ?", "USA";) // Valid!

Sample Code

Get the Name and City values for the Team table:

Dim sql As Text = "SELECT Name, City FROM Team;"
Dim teams As iOSSQLiteRecordSet
Try
teams = DB.SQLSelect(sql) // DB is a previously created iOSSQLiteDatabase
Catch e As iOSSQLiteException
Dim err As Text = e.Reason
End Try

You can also supply parameters:

Dim sql As Text = "SELECT Name, City FROM Team WHERE ID = ?1;"
Dim teams As iOSSQLiteRecordSet
Try
Dim ID As Integer = 100
teams = DB.SQLSelect(sql, ID) // DB is a previously created iOSSQLiteDatabase
Catch e As iOSSQLiteException
Dim err As Text = e.Reason
End Try

An example with a Text parameter:

Dim sql As Text = "SELECT Name FROM Team WHERE City = ?1;"
Dim teams As iOSSQLiteRecordSet
Try
Dim city As Text = "Boston"
teams = DB.SQLSelect(sql, city) // DB is a previously created iOSSQLiteDatabase
Catch e As iOSSQLiteException
Dim err As Text = e.Reason
End Try