Database

Class (inherits from Object)

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

DatabaseName	Password
Host	UserName

Methods

AddRow	Connect	SelectSQL
BeginTransaction	ExecuteSQL	TableColumns
Close	Prepare	TableIndexes
CommitTransaction	RollbackTransaction	Tables

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
MSSQLServerDatabase	Connects to Microsoft SQL Server. Use MSSQLServerPlugin.xojo_plugin (Windows-only).
MySQLCommunityServer	Supports the MySQL Community edition server. Use MySQLCommunityPlugin.xojo_plugin.
ODBCDatabase	Supports ODBC-based databases. Use ODBCPlugin.xojo_plugin.
OracleDatabase	Connects to Oracle 8i and above. Use OraclePlugin.xojo_plugin.
PostgreSQLDatabase	Supports PostgreSQL. Use PostgreSQLPlugin.xojo_plugin.
SQLiteDatabase	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
cubeSQL	Adds support for cubeSQL Server, a database server built on SQLite.
FrontBase	Adds support for the FrontBase database.
Valentina	Adds support for the Valentina stand-alone and server databases.
MBS SQL Plugin	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

Creating a SQLite Database

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

Fetching Data from a Table

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