Class
Database
Description
The Database class is the base class for the Database subclasses that communicate with a variety of databases. Use one of the subclasses to connect to your Database.
Properties
Name |
Type |
Read-Only |
Shared |
---|---|---|---|
Methods
Name |
Parameters |
Returns |
Shared |
---|---|---|---|
tableName As String, row As DatabaseRow |
|||
sql As String |
|||
statement As String |
|||
sql As String, [Paramarray values() As Variant] |
|||
TableName As String |
|||
TableName As String |
|||
Property descriptions
Database.DatabaseName
DatabaseName As String
The name of the Database to open.
The DatabaseName is typically used with server databases (such as MySQL or PostgreSQL) to identify the specific Database to use on the server.
This code connects to a PostgreSQL Database called “BaseballLeague”:
Var db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
db.Connect
// Use the database
Catch error As DatabaseException
// DB Connection error
MessageBox(error.Message)
End Try
Database.Host
Host As String
The Database host name or IP address of the Database server.
This examples connects to a PostgreSQL Database called “BaseballLeague”:
Var db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
db.Connect
// Use the database
Catch error As DatabaseException
// DB Connection error
MessageBox(error.Message)
End Try
Database.Password
Password As String
The password that is required for access to the Database. Typically used in conjunction with UserName.
This examples connects to a PostgreSQL Database called “BaseballLeague”:
Var db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
db.Connect
// Use the database
Catch error As DatabaseException
// DB Connection error
MessageBox(error.Message)
End Try
Database.UserName
UserName As String
The username that is required for access to the Database.
This code connects to a PostgreSQL Database called “BaseballLeague”:
Var db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Try
db.Connect
// Use the database
Catch error As DatabaseException
// DB Connection error
MessageBox(error.Message)
End Try
Method descriptions
Database.AddRow
AddRow(tableName As String, row As DatabaseRow)
Inserts Data (a populated DatabaseRow) as a new row in TableName.
Always look for a DatabaseException to verify whether or not the data was added.
This code adds a row to an existing Team table with the columns “Name”, “Coach” and “City”. It’s assumed that the variable db contains an active Database connection:
Var row As New DatabaseRow
// ID will be updated automatically
row.Column("Name") = "Penguins"
row.Column("Coach") = "Bob Roberts"
row.Column("City") = "Boston"
Try
db.AddRow("Team", row)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try
Database.BeginTransaction
BeginTransaction
Creates a new transaction. Changes to the Database made after this call can be saved with CommitTransaction or undone with RollbackTransaction.
A DatabaseException will be raised if the transaction could not begin or another transaction is already in progress.
You typically want to Commit changes after ensuring there were no Database errors:
// Prior DB code has run
Try
DB.BeginTransaction
DB.ExecuteSQL("CREATE TABLE AddressBook name VARCHAR, email VARCHAR")
DB.CommitTransaction
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
DB.RollbackTransaction
End Try
Database.Close
Close
Closes or disconnects the Database.
Calling Close does not issue a Commit, but some databases will automatically Commit changes in a transaction when you Close the connection and some Database will automatically Rollback changes in a transaction when the connection is closed. Refer to the documentation for your Database to check what its behavior is.
For desktop apps, you will often Connect to the Database when the app starts and Close it when the app quits.
For web apps, you usually Connect to the Database when the Session starts and Close it when the Session quits.
This code in the App.Closing event handler closes a previously connected Database:
DB.Close // DB is a property on App
Database.CommitTransaction
CommitTransaction
Commits an open transaction. This permanently saves changes to the Database.
A DatabaseException will be raised if the transaction could not be committed.
You have to have an open transaction to be able to use CommitTransation. Use BeginTransaction to begin a transaction:
DB.BeginTransaction
You typically want to commit changes after ensuring there were no Database errors:
// Prior DB code has run
Try
DB.CommitTransaction
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
DB.RollbackTransaction
End Try
Database.Connect
Connect
Connects to the Database so that you can begin using it.
Warning
A DatabaseException will be raised if the connection could not be made.
This example connects to the Database object in the variable db:
Try
db.Connect
MessageBox("Connected!")
Catch error As DatabaseException
MessageBox("Error connecting to the database: " + error.Message)
End Try
Database.ExecuteSQL
ExecuteSQL(ExecuteString As String)
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.
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.Text + "' WHERE PostalCode='" + PostalCodeField.Text + "'"
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.Text, PostalCode.Text)
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.Text
values(1) = PostalCode.Text
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
Database.Prepare
Prepare(statement As String) As PreparedSQLStatement
Creates a PreparedSQLStatement using the SQL statement for use with the various Database prepared statement classes. A prepared statement is an SQL statement with parameters that has been pre-processed by the Database so that it can be executed more quickly if it is re-used with different parameters. Prepared statements also mitigate the risk of SQL injection in web apps.
To create a prepared statement, you assign the value of Prepare to appropriate class for the Database you are using.
Refer to the specific PreparedStatement class for the Database you are using to learn how to specify and bind parameters:
If the provided SQL statement has an error, a DatabaseException will occur when you call SelectSQL or ExecuteSQL.
SQL Injection is a way to attack Database-driven applications.
To create a prepared statement, you use the appropriate class for the Database you are using. For example, to create a prepared statement for PostgreSQL:
// db is a previously connected PostgreSQLDatabase object
Var ps As PostgreSQLPreparedStatement
ps = db.Prepare("SELECT * FROM Country WHERE code = $1")
Database.RollbackTransaction
RollbackTransaction
Cancels an open transaction restoring the Database to the state it was in before the transaction began.
A DatabaseException will be raised if the rollback could not be completed.
You will generally want to rollback Database changes if a DatabaseException occurs within the transaction.
You have to have an open transaction to be able to use Rollback. Call BeginTransaction to begin a transaction:
DB.BeginTransaction
This code uses rollback to revert Database changes in a transaction when an error occurs:
// Prior DB code has run
Try
db.CommitTransaction
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
db.RollbackTransaction
End If
Database.SelectSQL
SelectSQL(sql As String, [values() As Variant]) As RowSet
SelectSQL(sql As String, [Paramarray values() As Variant]) As RowSet
Executes a SQL SELECT statement and returns the results in a RowSet. sql contains the SQL statement.
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.
Warning
While you can pass an entire valid SQL statement to SelectSQL with all values included, doing so can leave your database open to a SQL injection attack.
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 |
---|---|
? |
|
? |
|
? |
|
:columnname |
|
$1, $2, etc. |
|
? (also ?NNN, :VVV, @VVV, $VVV - see docs) |
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.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:
// db is a valid connection to a SQLite database
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:
// 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.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
Database.TableColumns
TableColumns(TableName As String) As RowSet
Returns a RowSet with information about all the columns (fields) in the specified TableName.
The following code creates a table and then display each column name one at a time:
Var db As SQLiteDatabase
Var dbFile As FolderItem
dbFile = New FolderItem("mydb.sqlite")
If dbFile.Exists Then
dbFile.Remove
End If
db = New SQLiteDatabase
db.DatabaseFile = dbFile
Try
db.CreateDatabase
db.ExecuteSQL("CREATE TABLE Invoices (ID INTEGER, CustID INTEGER, Amount Double, Note TEXT)")
Var columns As RowSet = db.TableColumns("Invoices")
For Each c As DatabaseRow In columns
MessageBox("Column: " + c.Column("ColumnName").StringValue)
Next
Catch error As IOException
MessageBox("The database could not be created: " + error.Message)
Catch error As DatabaseException
MessageBox("Database error: " + error.Message)
End Try
Database.TableIndexes
TableIndexes(TableName As String) As RowSet
Returns a RowSet containing the list of indexes for the passed TableName. Returns Nil if the table has no indexes or the Database source does not support indexes.
A DatabaseException will be raised if an error occurs.
The RowSet returns one row for each index on the table and it has one field: IndexName As String.
This code displays the indexes for the “Invoices” table (if it exists) in the specified SQLite Database:
Var dbFile As FolderItem
dbFile = FolderItem.ShowOpenFileDialog("")
If dbFile <> Nil Then
Var db As New SQLiteDatabase
Try
db.Connect
Var indexRS As RowSet
indexRS = db.TableIndexes("Invoices") // A table with indexes in the SQLite DB
For Each row As DatabaseRow In indexRS
MessageBox("Index: " + row.ColumnAt(0).StringValue)
Next
indexRS.Close
Catch error As NilObjectException
MessageBox("This database has no indexes.")
Catch error As DatabaseException
MessageBox("Could not connect to database. Error: " + error.Message)
End Try
End If
Database.Tables
Tables As RowSet
Returns a RowSet with a list of all tables in the Database.
A DatabaseException will be raised if an error occurs.
Tables returns a RowSet with one field: TableName As String.
The following code gets and displays the table names for the connected Database:
// App.db is a connected database
Var tables As RowSet
tables = App.db.Tables
Try
For Each row As DatabaseRow In tables
MessageBox(row.ColumnAt(0).StringValue)
Next
tables.Close
Catch error As NilObjectException
MessageBox("This database has no tables.")
End Try
Notes
Database classes
The following subclasses of Database are included. Everything other than SQLiteDatabase requires that you copy the appropriate plugin to your Plugins folder.
Class |
Description |
---|---|
Connects to Microsoft SQL Server. Use MSSQLServerPlugin.xojo_plugin (Windows-only). |
|
Supports the MySQL Community edition server. Use MySQLCommunityPlugin.xojo_plugin. |
|
Supports ODBC-based databases. Use ODBCPlugin.xojo_plugin. |
|
Connects to Oracle 8i and above. Use OraclePlugin.xojo_plugin. |
|
Supports PostgreSQL. Use PostgreSQLPlugin.xojo_plugin. |
|
Supports the built-in SQLite data source. |
3rd party database plugins
The following Database plugins are also available. Contact the vendor for more information on how to use them.
Class |
Description |
---|---|
Adds support for cubeSQL Server, a database server built on SQLite. |
|
Adds support for the FrontBase database. |
|
Adds support for the Valentina stand-alone and server databases. |
|
Adds support for the various server databases including CubeSQL, Centura SQLBase, DB2, Firebird, Informix, InterBase, MariaDB, Microsoft Access, Microsoft SQL Server, MySQL, ODBC, Oracle Database Server, PostgreSQL, SQL Anywhere, SQLite, SQLCipher and Sybase. |
|
Sample code
The following code creates a SQLite Database and uses SQLExecute to create a table.
Var dbFile As New FolderItem("MyDB.sqlite")
Var db As SQLiteDatabase
db = New SQLiteDatabase
db.DatabaseFile = dbFile
Try
db.CreateDatabaseFile
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
db.ExecuteSQL(sql)
Catch error As DatabaseException
MessageBox("Database error: " + error.Message)
End Try
The following example inserts a row in this table:
Var row As New DatabaseRow
// ID will be updated automatically
row.Column("Name") = "Penguins"
row.Column("Coach") = "Bob Roberts"
row.Column("City") = "Boston"
Try
db.AddRow("Team", row)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try
This code gets the data from the Team table and displays it:
Var sql As String
sql = "SELECT * FROM Team ORDER BY Name"
Var rows As RowSet
rows = db.SelectSQL(sql)
If rows <> Nil Then
For each row As DatabaseRow In rows
MessageBox(row.Column("Name").StringValue + " " + row.Column("City").StringValue)
Next
rows.Close
End If
Compatibility
All projects types on all supported operating systems.
See also
Object parent class; DatabaseColumn, DatabaseException, DatabaseRow, MSSQLServerDatabase. MySQLCommunityServer, ODBCDatabase, OracleDatabase, PostgreSQLDatabase, PreparedSQLStatement, RowSet, SQLiteDatabase classes.