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.

Events

None

Properties

DatabaseName

Error

ErrorCode

ErrorMessage

Host

Password

UserName


Methods

Close

InsertRecord

Commit

Rollback

Connect

SQLExecute

FieldSchema

SQLSelect

GetProperty

TableSchema

IndexSchema

 

More information available in parent classes: Object

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.


Notes

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.

ClassDescription
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.

FieldTypeValueDescription
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:

FunctionDefinition
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.

FunctionDefinition
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 RecordSet
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.