Database Class

From Xojo Documentation
Jump to: navigation, search

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 ErrorMessage ReadOnlyProperty.png UserName
Error ReadOnlyProperty.png Host
ErrorCode ReadOnlyProperty.png Password


Methods
Close IndexSchema SQLExecute
Commit InsertRecord SQLSelect
Connect Prepare TableSchema
FieldSchema Rollback

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.

Examples

Creating a SQLite Database

The following code creates a SQLite database and uses SQLExecute to create a table.

Dim dbFile As New FolderItem("MyDB.sqlite")

Dim db As SQLiteDatabase
db = New SQLiteDatabase
db.DatabaseFile = dbFile

If db.CreateDatabaseFile Then
Dim sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
db.SQLExecute(sql)
Else
MsgBox("Database error: " + db.ErrorMessage)
End If

The following example inserts a row in this table:

Dim row As New DatabaseRecord
// ID will be updated automatically
row.Column("Name") = "Penguins"
row.Column("Coach") = "Bob Roberts"
row.Column("City") = "Boston"

db.InsertRecord("Team", row)

If db.Error Then
MsgBox("DB Error: " + db.ErrorMessage)
End If

Fetching Data from a Table

This example gets the data from the Team table and displays it:

Dim sql As String
sql = "SELECT * FROM Team ORDER BY Name"

Dim data As RecordSet
data = db.SQLSelect(sql)

If data <> Nil Then
While Not data.EOF
MsgBox(data.Field("ID").StringValue + " " + data.Field("Name").StringValue + " " + _
data.Field("Coach").StringValue + " " + data.Field("City").StringValue)

data.MoveNext
Wend
data.Close
End If

See Also

DatabaseField, DatabaseRecord, MSSQLServerDatabase. MySQLCommunityServer, ODBCDatabase, OracleDatabase, PostgreSQLDatabase, PreparedSQLStatement, RecordSet, SQLiteDatabase classes.

Personal tools
Namespaces

Variants
Actions
Main
Content
Starting Out
Dig Deeper
More Help
Toolbox
About