Database

From Xojo Documentation

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 Oracle, SQL Server, DB2, Sybase, Informix, InterBase/Firebird, SQLBase, MySQL, PostgreSQL, ODBC, SQLite and SQL Anywhere.

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 DatabaseExeception
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

See Also

DatabaseColumn, DatabaseRow, MSSQLServerDatabase. MySQLCommunityServer, ODBCDatabase, OracleDatabase, PostgreSQLDatabase, [[PreparedSQLStatement], RowSet, SQLiteDatabase classes.