Object

Database Class

A Database object represents an open database that can be accessed by REALbasic's "front-end" database commands. You can create or open a REAL SQL Database using the REALSQLdatabase class.

Properties
DatabaseName
Error
ErrorCode
ErrorMessage
Host
Password
UserName

Methods
Close | InsertRecord
Commit | Rollback
Connect | SQLExecute
FieldSchema | SQLSelect
GetProperty | TableSchema
IndexSchema |

# Notes

Use the Database4DServer, ODBCDatabase, OpenBaseDatabase, MySQLDatabase, OracleDatabase, or PostgreSQLDatabase classes to return Database objects.

Except for the REAL SQL Database, you need to install the appropriate database plug-in in the REALbasic Plugins folder to use the other data sources. Database plug-ins may be updated more frequently than the REALbasic application itself. You can obtain the most current versions of all database plug-ins at www.realsoftware.com.

FrontBase can also be accessed using a third-party plug-in. The FrontBase plug-in is free and is available on the REALbasic CD.

## SQL for the REALdatabase data source

The REALSQLdatabase data source supports a subset of SQL/92 and SQL/99. This subset supports selecting, inserting, and deleting records, creating, modifying, and deleting tables, and building indexes. For more information, see Appendix A, REALSQLdatabase SQL Language Reference in the pdf version of the Language Reference or the SQLite web site at http://www.sqlite.org.

## Connecting to a data source

Use the following subclasses of the Database class to connect to data sources.

Class | Description
--- | ---
Database4DServer | Supports 4D versions 6.8 and above, including 4D 2003 and above.
MySQLDatabase | Provides support for MySQL databases; this functionality was previously available only via third-party plug-ins.
ODBCDatabase | Supports ODBC-based databases.
OpenBaseDatabase | Supports OpenBaseDatabase.
OracleDatabase | Connects to Oracle 8i and above.
PostgreSQLDatabase | Supports PostgreSQL.
REALSQLdatabase | Supports the built-in REAL SQL Database data source and is built-into REALbasic (no plug-in required).

Third-parties may supply plug-ins that work with other data sources.

When establishing a connection to any data source, do a test like this before proceeding:

Dim db as Database
.
If db.Connect() then
//connection is successful
else
//connection failed
end if

In general, you will use the Username, Host, and Password properties of the Database class to establish the connection. Specific requirements are different for different data sources.

## FieldSchema and TableSchema

FieldSchema returns a RecordSet with five fields: ColumnName ( String), FieldType ( Integer), IsPrimary ( Boolean), NotNull ( Boolean), and Length in bytes ( Integer) for Text fields. FieldType uses the following values. Also, some field types have implementation-specific features, such as numeric precision and maximum length.

The following table contains information about SQL data storage types.

FieldType | Value | Description
--- | --- | ---
Null | 0 | Denotes the absence of any value, i.e., a missing value.
Byte | 1 | Stores the byte representation of a character string.
SmallInt | 2 | A numeric data type with no fractional part. The maximum number of digits is implementation-specific, but is usually less than or equal to INTEGER. The REAL database supports 2-byte smallints, which allow you to store values in the range of ±32,767. If you are using another data source, check the documentation of your data source.
Integer | 3 | A numeric data type with no fractional part. The maximum number of digits is implementation-specific. The REAL database supports 4-byte integers, which provide a range of ±2,000,000,000. If you are using another data source, check the documentation of your data source.
Char | 4 | Stores alphabetic data, in which you specify the maximum number of characters for the field, i.e., CHAR (20) for a 20 character field. If a record contains fewer than the maximum number of characters for the field, the remaining characters will be padded with blanks.
Text or VarChar | 5 | Stores alphabetic data, in which the number of characters vary from record to record, but you don't want to pad the unused characters with blanks. For example, "VARCHAR (20)" specifies a VARCHAR field with a maximum length of 20 characters.
Float | 6 | Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5).
Double | 7 | Stores double-precision floating-point numbers.
Date | 8 | Stores year, month, and day values of a date in the format YYYY-MM-DD. The year value is four digits; the month and day values are two digits.
Time | 9 | Stores hour, minute, and second values of a time in the format HH:MM:SS. The hours and minutes are two digits. The seconds values is also two digits, may include a optional fractional part, e.g., 09:55:25.248. The default length of the fractional part is zero.
TimeStamp | 10 | Stores both date and time information in the format YYYY-MM-DD HH:MM:SS. The lengths of the components of a TimeStamp are the same as for Time and Date, except that the default length of the fractional part of the time component is six digits rather than zero. If a TimeStamp values has no fractional component, then its length is 19 digits If it has a fractional component, its length is 20 digits, plus the length of the fractional component.
Currency | 11 | Stores a decimal amount with a Dollar sign.
Boolean | 12 | Stores the values of TRUE or FALSE.
Decimal | 13 | Stores a numeric value that can have both an integral and fractional part. You specify the total number of digits and the number of digits to the right of the decimal place, i.e., DECIMAL (5.2) specifies a decimal field that can contain values up to 999.99. DECIMAL (5) specifies a field that can contain values up to 99,999.
Binary | 14 | Stores code, images, and hexadecimal data. Consult the documentation of your data source for information on the maximum size of a Binary field.
Long Text (Blob) | 15 | Stores a text object. Consult the documentation of your data source for information on the maximum size of a Blob.
Long VarBinary (Blob) | 16 | Stores a binary object. The REAL SQL Database supports blobs of up to any size. Furthermore, a blob can be stored in a column of any declared data affinity. If you are using another data source, check the documentation of your data source.
MacPICT | 17 | Stores a Macintosh PICT image.
String | 18 | Text up to about 2 billionbytes. The same as VarChar.
Unknown | 255 | Unrecognized data type.

The values of IsPrimary and NotNull are set using the SQL Select statement that created the table.

TableSchema returns a RecordSet with one field, TableName ( String).

## The Property Method

The purpose of the Property method is to retrieve miscellaneous information from the data source. For example, if you are using 4th Dimension as the data source, you can pass "connectionString" to the Property method and it will return the connection string for the 4D Server database. It contains the name of the database. ConnectionString is supported by 4th Dimension but may not be supported by other data sources.

# SQLSelect and SQLExecute Methods

You use the SQLSelect and SQLExecute methods to communicate with your data source via SQL commands. You use SQLSelect to call the SELECT statement and the SQLExecute statement for all other statements. See Appendix A for detailed information on SQL supported by the REAL SQL Database or the SQLite web site at http://www.sqlite.org.

## SQL Aggregate Functions

The following aggregate functions are supported. Each calculates a value from a group of rows that is chosen by the WHERE clause in the SELECT statement in which the Aggregate function is used:

Function | Definition
--- | ---
AVG | Returns the arithmetic average of a numeric expression.
COUNT | Returns the total number of rows accessed by the expression.
COUNT (*) | Returns the total number of rows accessed by the expression. No list of fields is passed and Null and duplicate values are included in the count.
MAX | Returns the maximum value of the specified field or fields.
MIN | Returns the minimum value of the specified field or fields.
SUM | Returns the total value in the specified numeric fields.

## Date/Time and Misc. Functions

The following date and time functions are supported by the REALdatabase data source.

Function | Definition
--- | ---
CURRENT_DATE | Returns the current date in SQL Date format, YYYY-MM-DD.
CURRENT_TIME | Returns the current time in SQL Time, HH:MM:SS.
CURRENT_TIMESTAMP | Returns the current date-time in SQL TimeStamp format, YYYY-MM-DD HH:MM:SS, as a SQL TimeStamp data type.
LAST_ROWID | Returns the value of the last _rowID for the table passed. This value is needed for certain types of relational joins.

# Examples

## Creating a REAL database

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

Dim db as REALSQLdatabase
Dim f as FolderItem
Dim result as Boolean
f= New FolderItem("mydb")
db= New REALSQLdatabase
db.databaseFile=f
result=db.CreateDatabaseFile
If db.Connect() then
db.SQLExecute("create table invoices(id integer ,Cust_ID integer,Amount double, Date date)")
db.Commit
else
MsgBox "Database not created"
end if

The following example inserts a row in this table:

Dim r as DatabaseRecord
Dim mybool as Boolean
Dim mydate as Date
.
.
r= New DatabaseRecord
r.IntegerColumn("id")=4
r.IntegerColumn("Cust_ID")=2
r.DoubleColumn("Amount") =9.98
mybool= ParseDate("10/22/98",mydate)
r.DateColumn("Date") = mydate
db.InsertRecord("invoices",r)

## RecordSets

The following example creates a RecordSet that contains all rows and columns for a particular customer sorted by amount:

Dim rs as RecordSet
.
.
rs = db.SQLSelect("select * from invoices where cust_id=02 ORDER BY amount DESC")

The following Select statement retrieves all rows and columns from the Customers table in which the customer's last name begins with the letter 'L.

rs=db.SQLSelect("select * from Customers WHERE customer.name LIKE 'L%'")

## '

This Select statement retrieves all rows and columns in which the customer's last name does not begin with the letter 'L.'

rs=db.SQLSelect("select * from customer WHERE customer.name NOT LIKE 'L%'")

The following example returns a RecordSet that contains information on the fields in the customers table:

Dim dbFile as FolderItem
Dim db as REALSQLdatabase
Dim rs as RecordSet
rs= New RecordSet
db= New REALSQLdatabase
dbFile = GetFolderItem("Pubs")
db.DatabaseFile=dbFile
If db.Connect() then
rs=db.FieldSchema("Customers")
else
Beep
MsgBox db.ErrorMessage
end if

# See Also

Database4DServer, DatabaseField, DatabaseRecord, MySQLDatabase, ODBCDatabase, OpenBaseDatabase, OracleDatabase, PostgreSQLDatabase, REALSQLdatabase, RecordSet classes; OpenCSVCursor, OpenDBFCursor, OpenDTFDatabase functions.