Database.ExecuteSQL

From Xojo Documentation

Method

Database.ExecuteSQL(SQLStatement as String [,ParamArray values() as Variant])

Supported for all project types and targets.


Method

Database.ExecuteSQL(SQLStatement as String [,values() as Variant])

Supported for all project types and targets.


Used to execute an SQL command. Use this for commands that do not return any data, such as CREATE TABLE or INSERT. SQLStatement contains the SQL statement.

Notes

If the SQL passed is invalid, a DatabaseException will occur.

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

To avoid this, use value identifiers in your SQL statement and then pass the values in as an array or parameter array. See the examples below.

Sample Code

In this example, the database is being updated without the use of value identifiers and thus leaves the database vulnerable to a SQL injection attack:

// Updates a table in a SQLite database (db)
Var sql As String
sql = "UPDATE Customer SET City='" + CityField.Value + "' WHERE PostalCode='" + PostalCodeField.Value + "'"
Try
db.ExecuteSQL(sql)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try

Here's the same example but using value identifiers which protects you against a SQL injection attack:

// Updates a table in a SQLite database (db)
Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Try
db.ExecuteSQL(sql, CityField.Value, PostalCode.Value)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try

The values can also be passed in as a variant array:

Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Var values(1) As Variant
values(0) = CityField.Value
values(1) = PostalCode.Value
Try
db.ExecuteSQL(sql, values)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try

This code creates the Team table:

// db is a SQLite database
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
Try
db.ExecuteSQL(sql)
MessageBox("Team table created successfully.")
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try