Database

From Xojo Documentation

Revision as of 17:20, 6 July 2010 by Npalardy (talk | contribs) (Created page with '{{ClassBox | super=Object | platform=all | scope=global }} A '''Database''' object represents an open database that can be accessed by REAL Studio's "front-end" database comm…')
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Class (inherits from Object)

A Database object represents an open database that can be accessed by REAL Studio's "front-end" database commands. You can create or open a REAL SQL Database using the REALSQLdatabase class. Use the ODBCDatabase, OracleDatabase, MySQLCommunityServer, or PostgreSQLDatabase classes to return Database objects.

Except for the REAL SQL Database, you need to install the appropriate database plug-in in the REAL Studio Plugins folder to use the other data sources. Database plug-ins may be updated more frequently than the REAL Studio 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. Contact FrontBase for more information.


Properties
DatabaseName ErrorMessage UserName
Error Host
ErrorCode Password


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


Notes

SQL for the REALSQLdatabase 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.


REAL Studio database plug-ins

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

Class Description
MySQLCommunityServer Supports the MySQL Community edition server.
ODBCDatabase Supports ODBC-based databases.
OracleDatabase Connects to Oracle 8i and above.
PostgreSQLDatabase Supports PostgreSQL.
REALSQLDatabase Supports the built-in REAL SQL Database data source and is built-into REAL Studio (no plug-in required).
REALSQLServerDatabase Supports the optional REAL Server database. The REAL Server is a multi-user client-server database that is available as a separate product for REAL Studio Professional owners. The REAL Server is included with the Enterprise version of REAL Studio.

Connecting to a data source

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 SQL database supports 4-byte smallints. 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 SQL Database supports 8-byte integer columns and the FieldType evaluates to 19 (64-bit integer). The REAL Server database also supports SmallInts, which are 4-byte integers and evaluate to FieldType 3. 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 This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.
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.

The REAL SQL Database does not support this data type. Use a Blob to store images.

String 18 Text up to about 2 billion bytes. The same as VarChar.
Int64 19 Stores a 64-bit integer.

Integer fields in the REALSQLdatabase and the REALSQLServerDatabase are 64 bits and FieldType returns 19.

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. It may not be supported by some 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 REALSQLdatabase 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)")
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

DatabaseField, DatabaseRecord, MySQLCommunityServer, [ ODBCDatabase, OracleDatabase, PostgreSQLDatabase, REALSQLdatabase, REALSQLServerDatabase, RecordSet classes.