Difference between revisions of "Database.SelectSQL"
From Xojo Documentation
(→Notes) |
(→Sample Code) |
||
| Line 66: | Line 66: | ||
Var rowsFound As RowSet | Var rowsFound As RowSet | ||
Try | Try | ||
| − | rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode. | + | rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Text) |
For Each row As DatabaseRow In rowsFound | For Each row As DatabaseRow In rowsFound | ||
ListBox1.AddRow(row.Column("Name").StringValue) | ListBox1.AddRow(row.Column("Name").StringValue) | ||
| Line 81: | Line 81: | ||
Var rowsFound As RowSet | Var rowsFound As RowSet | ||
Try | Try | ||
| − | rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", PostalCode. | + | rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", PostalCode.Text) |
For Each row As DatabaseRow In rowsFound | For Each row As DatabaseRow In rowsFound | ||
ListBox1.AddRow(row.Column("Name").StringValue) | ListBox1.AddRow(row.Column("Name").StringValue) | ||
| Line 91: | Line 91: | ||
</rbcode> | </rbcode> | ||
| − | Multiple values can be passed to SelectSQL. In this example, Age and PostalCode are [[ | + | Multiple values can be passed to SelectSQL. In this example, Age and PostalCode are both [[DesktopTextField]] controls: |
<rbcode> | <rbcode> | ||
// db is a valid connection to a SQLite database | // db is a valid connection to a SQLite database | ||
Var rowsFound As RowSet | Var rowsFound As RowSet | ||
Try | Try | ||
| − | rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode. | + | rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode.Text) |
For Each row As DatabaseRow In rowsFound | For Each row As DatabaseRow In rowsFound | ||
ListBox1.AddRow(row.Column("Name").StringValue) | ListBox1.AddRow(row.Column("Name").StringValue) | ||
Latest revision as of 19:33, 18 February 2022
New in 2019r2
Supported for all project types and targets.
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
A DatabaseException will be raised if the SQL passed is invalid or if an error occurs.
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.
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:
Var rowsFound As RowSet
Try
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=" + PostalCode.Text)
For Each row As DatabaseRow In rowsFound
ListBox1.AddRow(row.Column("Name").StringValue)
Next
rowsFound.Close
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:
Var rowsFound As RowSet
Try
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE PostalCode=?", PostalCode.Text)
For Each row As DatabaseRow In rowsFound
ListBox1.AddRow(row.Column("Name").StringValue)
Next
rowsFound.Close
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
Multiple values can be passed to SelectSQL. In this example, Age and PostalCode are both DesktopTextField controls:
Var rowsFound As RowSet
Try
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode.Text)
For Each row As DatabaseRow In rowsFound
ListBox1.AddRow(row.Column("Name").StringValue)
Next
rowsFound.Close
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
See Also
Database.ExecuteSQL method