From Xojo Documentation
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.
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.
|MySQLCommunityServer||Supports the MySQL Community edition server.|
|ODBCDatabase||Supports ODBC-based databases.|
|OracleDatabase||Connects to Oracle 8i and above.|
|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:
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.
|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.
|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.|
|Unknown||255||Unrecognized data type.|
The values of IsPrimary and NotNull are set using the SQL Select statement that created the table.
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:
|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.
|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.|
Creating a REAL database
The following code creates an internal database and uses SQLExecute to create a table.
Dim f as FolderItem
Dim result as Boolean
If db.Connect() then
db.SQLExecute("create table invoices(id integer ,Cust_ID integer,Amount double, Date date)")
MsgBox "Database not created"
The following example inserts a row in this table:
Dim mybool as Boolean
Dim mydate as Date
r.DateColumn("Date") = mydate
The following example creates a RecordSet that contains all rows and columns for a particular customer sorted by amount:
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'.
This Select statement retrieves all rows and columns in which the customer's last name does not begin with the letter 'L'.
The following example returns a RecordSet that contains information on the fields in the customers table:
Dim db as REALSQLdatabase
Dim rs as RecordSet
dbFile = GetFolderItem("Pubs")
If db.Connect() then