iOSSQLiteDatabase.SQLSelect

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 values	A list of values that will be bound to any parameters (usually specified using the "?" character) in the sqlstatement.

Method

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

New in 2019r2

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.
values	An array of values that will be bound to any parameters (usually specified using the "?" character) in the sqlstatement.

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 values you supply are used to bind to the prepared statement. Use the binding parameter (usually the ? character) to indicate where the values will get bound. Parameters are bound to the SQL left to right. The first supplied value binds to the first parameter in the SQL. These are the other bind parameters supported by SQLite (see docs):

Parameter Format	Example
?	?
?NNN	?1
:VVV	:City
@VVV	@City
$VVV	$City

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

When specifying the prepared SQL statement, do not wrap the parameter 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!

Passing NULL

To pass in NULL as a value you should create a variable and set it to Nil and pass it like this:

Dim null As Auto = Nil
rs = App.db.SQLSelect("SELECT * from Customers WHERE Country = ?;", null) // Valid!

Sample Code

Get the Name and City values for the Team table:

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

You can also supply parameters:

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

An example with a Text parameter:

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