Database.ExecuteSQL

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

A DatabaseException will be raised if the SQL passed is invalid or if the SQL could not be executed due to an error.

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

In this example, the database is being updated without the use of parameters 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 parameters 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 parameter 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