Database.SelectSQL

Method

Database.SelectSQL(SQLStatement as String [,ParamArray values() as Variant]) As RowSet

New in 2019r2

Supported for all project types and targets.

Method

Database.SelectSQL(SQLStatement as String [,values() as Variant]) As RowSet

New in 2019r2

Supported for all project types and targets.

Executes a SQL SELECT statement and returns the results in a RowSet. SQLcommand contains the SQL statement.

Notes

Typically only SQL SELECT statements return a RowSet, but some databases return a RowSet for SQL commands such as INSERT, UPDATE or stored procedures.

If the SQL does not return data then Nil is returned. Nil is also usually returned if there is an error in the SQL statement, but you should instead catch the DatabaseException to check if an error occurred.

You can pass an entire valid SQL statement to SelectSQL with all values included. However, doing so can leave your database open to an SQL injection attack.

Parameters

To avoid SQL injection attacks, use parameters in your SQL statement and then pass the values in as an array or parameter array. See the examples below.

Database	Parameter Format
MSSQLServerDatabase	?
MySQLCommunityServer	?
ODBCDatabase	?
OracleDatabase	:columnname
PostgreSQLDatabase	$1, $2, etc.
SQLiteDatabase	?, ?NNN, :VVV, @VVV, $VVV (see docs)

Sample Code

This sample adds the names of all customers in a particular postal code to a ListBox. It passes the entire SQL select as a single statement and appends the value from a TextField called PostalCode, leaving the database vulnerable to an SQL injection attack:

// db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Value)
If rowsFound <> Nil Then
For Each row As DatabaseRow In rowsFound
ListBox1.AddRow(row.Column("Name").StringValue)
Next
rowsFound.Close
End If
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

This is the same code as above but instead it uses a value identifier (?) and then passes the value in separately to avoid a SQL injection attack:

// db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", PostalCode.Value)
If rowsFound <> Nil Then
For Each row As DatabaseRow In rowsFound
ListBox1.AddRow(row.Column("Name").StringValue)
Next
rowsFound.Close
End If
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try

Multiple values can be passed to SelectSQL. In this example, Age and PostalCode are TextFields:

// db is a valid connection to a SQLite database
Var rowsFound As RowSet
Try
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode.Value)
If rowsFound <> Nil Then
For Each row As DatabaseRow In rowsFound
ListBox1.AddRow(row.Column("Name").StringValue)
Next
rowsFound.Close
End If
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try