REALSQLdatabase
From Real Software Documentation
The REALSQLdatabase class provides access to the Real SQL Database data source, a.k.a., database engine or database back-end. It is based on SQLite, which is described at http://www.SQLite.org.
Notes
The RealSQLdatabase class can be used on all editions of Real Studio on all supported platforms. Use this class to open or create Real SQL databases programmatically. A Real SQL Database can also be created or opened with the Project ↠ Add ↠ Database submenu and designed with a graphical user interface.
Real Studio uses SQLite version 3.7.3.
| Properties | |||||||||||||||
|
Notes
The REALSQLdatabase supports a subset of SQL/92 and SQL/99, including queries that involve self-joins, aggregate functions, and more. For the set of features the new REALSQLdatabase engine supports, its syntax is fully SQL compliant. It returns SQLite error codes. Please refer to REAL SQL Database Language Reference or www.sqlite.org for information on the SQL supported by the REALSQLdatabase engine.
A call to SQLSelect returns a dynamic RecordSet; you can move forward, backward, or jump to the beginning or end as much as you like.
The REALSQLdatabase engine supports transactions, both for schema changes and for data changes. A transaction is started automatically when you make any change to the database and is ended by calling either the Commit or Rollback methods of the database class.
The REALSQLdatabase engine supports all four RecordSet navigation methods: MoveFirst, MoveNext, MovePrevious, and MoveLast.
Encrypted Databases
The Real SQL Database supports encrypted databases. This is a feature of Real Studio Professional and Enterprise. To create an encrypted database, specify a value for the EncryptionKey property of the REALSQLdatabase connection object. The EncryptionKey property must be set before calling either Connect or CreateDatabaseFile. In other words, write something like this to create a new database:
db.DatabaseFile = GetFolderItem("db.rsd")
db.EncryptionKey = "howdy doody"
If Not db.CreateDatabaseFile then
//handle error here
End if
When you open an encrypted database file, you need to supply the key:
db.DatabaseFile = GetFolderItem("db.rsd")
db.EncryptionKey = "howdy doody"
If Not db.Connect then
//handle error here
End if
Boolean Values
Beginning with version 2006 Release 4, Boolean fields are more strict about what they expect when using the DatabaseField.BooleanValue function. Previously, "0" and "False" were treated as False and anything else was treated as True. Now, "0" and "False" are treated as False and "1" and "True" are treated as True. The behavior of any other values is undefined if retrieved using DatabaseField.BooleanValue.
DatabaseField.StringValue, on the other hand, should be able to retrieve the original data if it can't be identified as a Boolean. If the Real SQL Database can identify the value as a boolean, however, then "False" will always return False and "True" will always return True, regardless of how those values are stored in the database.
This is a side effect of the way in which database engines interact with the database API in the Real Studio framework. Note that the change to DatabaseField.BooleanValue should only be an issue if you are trying to store non-boolean data in a boolean column.
Result Codes
The REALSQLdatabase engine sets the database class's Error flag after each operation and returns values in the ErrorCode and ErrorMessage properties. When the Error flag is False, the ErrorCode is "0" and ErrorMessage is empty. If the Error flag is True, the following codes and messages are returned in ErrorMessage
REAL SQL Database returns SQLite error codes as follows.
| Error code | Error Message |
|---|---|
| 0 | Not an error |
| 1 | SQL logic error or missing database |
| 2 | Internal SQLite implementation flaw |
| 3 | Access permission denied |
| 4 | Callback requested query abort |
| 5 | Database is locked |
| 6 | Database table is locked |
| 7 | Out of memory |
| 8 | Attempt to write a read/only database |
| 9 | Interrupted |
| 10 | Disk I/O error |
| 11 | Database disk image is malformed |
| 12 | Table or record not found |
| 13 | Database is full |
| 14 | Unable to open database file |
| 15 | Database locking protocol failure |
| 16 | Table contains no data |
| 17 | Database schema has changed |
| 18 | Too much data for one table row |
| 19 | Constraint failed |
| 20 | Datatype mismatch |
| 21 | Library routine called out of sequence |
| 22 | Kernel lacks large file support |
| 23 | Authorization denied |
| 24 | Auxiliary database format error |
| 25 | Bind or column index out of range |
| 26 | File is encrypted or is not a database |
| 200 | Not connected |
Primary Keys
All Real SQL Database tables have an Integer Primary Key column. If you don't explicitly create such a column, one will be created for you with the name "Rowid". If you create your own INTEGER PRIMARY KEY column, then rowid will act as an alias to that column. This means that a query that includes rowID will instead return the column that is the primary key. For example, consider the following Employees table:
- EmployeeID (integer, primary key)
- FirstName (varchar)
- LastName (varchar)
Now consider the following queries:
In all of these cases, you will not get the rowid column because another column is the primary key. In fact, in this query:
You will get EmployeeID, FirstName and LastName back because rowID is acting as an alias to your primary key (EmployeeID in this case).
If however, you have your own primary key column but still wish to get the rowid, you can do so using the SQL AS keyword. The following example will return the rowid and all other columns as well:
If you don't explicitly define your own INTEGER PRIMARY KEY column, you won't get the 'rowid' column unless you ask for it by name in queries, such as in the statement:
Data Types
The following table contains information about the data types used by Real SQL Database.
| FieldType | Description |
|---|---|
| Binary | Stores code, images, and hexadecimal data. |
| Blob | 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. Blob data can be inserted into a Real SQL Database using the BlobColumn method of the DatabaseRecord class. |
| Boolean | Stores the values of TRUE or FALSE.
When retrieved by DatabaseField.BooleanValue, "0" and "False" are interpreted as False and "1" and "True" are interpreted as True. All other values are not defined. When retrieved by DatabaseField.StringValue, other values in a Boolean field may retrieved successfully. |
| Currency | A 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right. |
| Date | 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. |
| Double | Stores double-precision floating-point numbers. |
| Float | Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5). |
| Integer | A numeric data type with no fractional part.
The Real SQL Database supports 8-byte (64-bit) integers. Prior to REALbasic 2006 Release 4, the Real SQL Database supported only 32-bit integers. The DatabaseField.IntegerValue function will continue to work as it has but it can now return values up to 64 bits. However, the field type returned by Database.FieldSchema is now 19 rather than 3. If you are using this value, be sure to update your code. The Real SQL Database SmallInt field now stores 32-bit integers. |
| SmallInt | A numeric data type with no fractional part.
The maximum number of digits is implementation-specific, but is usually less than or equal to the Integer data type. The Real SQL database supports 4-byte(32-bit) smallints. Database.FieldSchema returns a value of 2 for SmallInts. |
| Text | Stores alphabetic data as a text string, using the database's encoding.
Real SQL Database converts text to the UTF-8 text encoding. SQLite expects text to be in that encoding. If you want to preserve the original encoding, use a Blob field. |
| Time | 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 | 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. |
| VarChar | 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.
Real SQL Database converts text to UTF-8 text encoding. If you want to preserve the encoding, use a Blob field. SQLite expects text to be in that encoding. |
Creating a Real SQL Database
You can create a Real SQL Database via the IDE by choosing Project ↠ Add ↠ Database ↠ New Real SQL Database. Real Studio will then present a standard save-file dialog box in which you can name the database and specify the directory in which it will be stored.
When you click OK, a Real SQL Database will be added to your project, with the name you entered in the dialog. You can double-click the item to add tables, fields, and indexes. When data have been entered, you can use a table viewer to view the data. For more information, see the chapter on Databases in the User's Guide.
Examples
See the example "Database Example" in the Examples folder that ships with Real Studio.
This example saves a product image using the DatabaseRecord.PictureColumn method to save an image that the user has dragged to an ImageWell. The column ha been declared a blob column.
rec = New DatabaseRecord
rec.Column("PartNumber") = ProductPartNumber.Text
rec.Column("Name") = ProductName.Text
rec.DoubleColumn("Price") = moneyToDouble( productPrice.text )
//write to the blob field using the PictureColumn method
rec.PictureColumn("Image") = productImageWell.Image
// Insert product record into the database
App.OrdersDB.InsertRecord "Products", rec
if App.OrdersDB.Error then
App.DisplayDatabaseError true
return
end if
The following example creates a new REALSQLdatabase:
Dim f as FolderItem
f=New FolderItem("mydb")
db=New REALSQLdatabase
db.databaseFile=f
If db.CreateDatabaseFile then
//proceed with database operations...
else
MsgBox "Database not created"
end if
The following example opens an existing REALSQLdatabase.
Dim db as REALSQLdatabase
db=New REALSQLdatabase
dbFile = GetFolderItem("Pubs")
db.DatabaseFile=dbFile
If db.Connect() then
//proceed with database operations here..
else
Beep
MsgBox "The database couldn't be opened."
end if
The following example adds a record to a table.
Dim db as REALSQLdatabase
db=New REALSQLdatabase
dbFile = GetFolderItem("Employees")
db.DatabaseFile=dbFile
If db.Connect() then
db.sqlexecute ("Insert into Employees (Name,Job,YearJoined) Values "_
+"('Dr.Strangelove','Advisor',1962)")
If db.error then
MsgBox db.errormessage
else
db.Commit
End if
else
Beep
MsgBox "The database couldn't be opened."
end if
See Also
Database Class, DatabaseRecord, PreparedSQLStatement, REALSQLPreparedStatement, RecordSet classes.
