Beginning REALbasic - From Novice To Professional (2006)

276 C H A P T E R 1 0 ■ W O R K I N G W I T H D A T A B A S E S

Creating a Database in Code

Programmatically, you can create a REAL SQL database using the REALSQLDatabase class’s DatabaseFile property. Once created, you can perform other commands, as the following example shows.

Dim TestDB As REALSQLdatabase

Dim TargetFile As FolderItem

Dim blnResult As Boolean

TestDB = New REALSQLdatabase

TargetFile = GetFolderItem("MyTestDB")

TestDB.DatabaseFile = TargetFile blnResult = TestDB.CreateDatabaseFile

If blnResult = True Then

'Add code statements here to perform database tasks TestDB.Close

MsgBox "Database created" Else

MsgBox "Database Error: " + TestDB.ErrorMessage End If

This example begins by declaring variables that represent the REALSQLDatabase and FolderItem objects. In addition, a Boolean variable named blnResult is declared. A REALSQLDatabase object is then instantiated as TestDB. Next, the GetFolderItem method of the FolderItem class is used to create a new file named MyTestDB in the same folder where REALbasic resides. The next two code statements use the REALSQLDatabase class’s DatabaseFile property to associate the new database with the just-created file and the

CreateDatabaseFile method to set up the database file. Finally, an If…Then…Else block is set up that checks to ensure the database was successfully created. Embedded within the If…Then…Else block are statements that close the database connection or display error text, when appropriate.

Connecting to an Existing Database from the IDE

If you already have a database you want your REALbasic application to work with, you can connect to it by clicking Project Add Database Select REAL SQL Database. REALbasic responds by displaying a standard Open dialog, enabling you to specify the name and location of the database. Once added, a new item representing the database is displayed in the Projects screen.

Connecting to an Existing Database in Code

You can also programmatically connect to a REAL SQL database using the REALSQLDatabase class’s Connect method. Once connected, you can perform other commands, as the following example shows.

Dim TestDB As REALSQLdatabase

Dim TargetFile As FolderItem

Dim blnResult As Boolean

TestDB = New REALSQLdatabase

TargetFile = GetFolderItem("MyTestDB")

TestDB.DatabaseFile = TargetFile blnResult = TestDB.Connect()

If blnResult = True Then

'Add code statements here to perform database tasks TestDB.Close

MsgBox "Database connection established"

Else

MsgBox "Database Error: " + TestDB.ErrorMessage

End If

In this example, the FolderItem class’s GetFolderItem method is used to retrieve a reference to the specified database. Next, the REALSQLDatabase class’s Database property is used to tell REALbasic the specified file is a database. The REALSQLDatabase class’s Connect method is then used to establish a connection to the database.

Defining Database Schema

Once you create a new REAL SQL database, you need to specify its schema. If you have connected to an existing database instead of creating a new one, you can view and modify that database’s schema. Schema is a fancy word for referring to a database’s structure, which is made up of one or more tables. Each database table consists of one or more columns and each column is used to store data.

You can view a database’s schema from the REALbasic IDE by opening the Project Editor screen and double-clicking the database. Doing so opens the window, as you see in Figure 10-2.

278 C H A P T E R 1 0 ■ W O R K I N G W I T H D A T A B A S E S

Figure 10-2. As shown on Mac OS X, a new database does not have any tables defined.

As Figure 10-2 shows, REALbasic lays out a database schema in three panes. The first pane contains a listing on any defined tables. The second pane displays a list of any columns added to the selected table. The third pane display the properties associated with the selected table or column. Above the three panes is a toolbar. Here, you see buttons that let you add new tables and columns, as well as save any changes or delete a selected table or column.

Adding Tables, Columns, and Indexes from the IDE

Every database must contain at least one table to be useful (although you can create empty databases if you want.) You can add and define a new table using the following procedure.

1.Double-click a database to open it.

2.Click the Add Table button. REALbasic responds by adding a new table with a name of

Untitled.

3.Select the new table and change its name to something more descriptive by modifying its Name property.

4.Click the Add Column button to add a column to the table.

5.Select the new column and change its name to something more descriptive by modifying its Name property.

6.In the Properties pane, select the appropriate data type for the column by clicking the Type property. This displays a pop-up window showing all the data types supported by the database.

7.Add and configure as many columns as necessary.

8.Click the Save button.

9.Add as many additional tables as required by repeating Steps 2–8.

Figure 10-3 demonstrates how a small customer database created using the previous procedures might look.

Figure 10-3. A customer database made up of one table, as seen on Windows

■Note Any property not supported by a particular type of database is grayed out in the Properties pane. A Primary key is a column used to link two or more tables together. For example, in a billing application that consists of a table containing customer addresses and a second table containing custom account balances, a customer-ID column might be marked as a primary key in both tables. Primary keys allow database queries to tie both tables together and pull out all data related to a particular customer. A Mandatory key marks a column as required (that is, one that cannot be set equal to null).

280 C H A P T E R 1 0 ■ W O R K I N G W I T H D A T A B A S E S

Once you finish laying out the schema for the database, you may want to add an index. An index is a column that will be searched often or used when sorting data. Indexing speeds data retrieval. Consider indexing columns that will be searched on or sorted often. Avoid indexing every column, which makes things run slower by forcing the database to make too many index updates when adding and removing data to and from the database. Do not index columns that will rarely be searched or that contain only a small subset of values.

To add an index to a database, its schema already needs to be defined. You can then define one or more database indexes using the following procedure.

1.Double-click a database to open it.

2.Select the table for which the index is being added and click the Indexes button. REALbasic responds by opening an Indexes sheet, as Figure 10-4 shows.

Figure 10-4. A customer database consisting of two tables, as seen on Windows

3.Click the Add Index button. REALbasic responds by adding a new index with a name of Untitled, as Figure 10-5 shows.

Figure 10-5. Adding an index to a REAL SQL database table, as seen on Windows

4.Select the new index and change its name to something more descriptive by modifying its Name property.

5.Click the Add Column button. The Select Column window appears and displays a listing of columns from the database.

6.Select the name of the column to be indexed.

7.If desired, create additional indexes by repeating steps 2–6.

8.Click Save.

Adding Tables, Columns, Indexes, and Records in Code

If you prefer, you can also programmatically add tables, columns, and indexes to a REAL SQL database. To do so, you need to programmatically create a new database or connect to an existing database, as previously shown. Regardless of whether to build or connect to a database, you need to add code to your database set-up application that defines its schema, creates indexes, and adds data.

DBRecord.IntegerColumn("Account") = 23456

DBRecord.Column("Name") = "Donal W. Lazyfeather"

DBRecord.IntegerColumn("Phone") = 5555555

DBRecord.Column("AcctStatus") = "Overdue"

TestDB.InsertRecord("Customers", DBRecord)

TestDB.Commit

The first four statements add data to the database using the DatabaseRecord class’s IntegerColumn and Column methods. Each of these methods takes an argument that must match up against a table’s column. Once data is assigned to each column, it is inserted into the database as a record using the Database class’s InsertRecord method, which takes as arguments the name of the table and the name of the DatabaseRecord object. Once the record is inserted, the Database class’s Commit method is used to save the change (addition) to the database. As you can see, a second record is then added to the database using the same set of methods.

■Note As you have just seen, you can create a new database and define its schema and indexes either from the REALbasic IDE or via code. You can also populate a new database with records via code. However, while you can modify existing database records, as the next section shows, you cannot add records to a database directly from the REALbasic IDE as you can via code. You can develop a GUI database front-end that supports database data entry, however, as you see in the section “The RB Book Database.”

Querying Database Data

The REALbasic Database Editor provides you with the capability to view, search, sort, and update data already stored in a database. Doing so requires you to submit a SQL statement to retrieve and modify database records. The Database Editor provides you with two ways of submitting SQL statements. For those with little SQL background, you can fill out a GUI window that translates selections into SQL statements. For those with an understanding of SQL, an advanced option enables you to submit your own SQL statements.

Letting REALbasic Generate Your SQL Statements

To view existing database data, create a new REALbasic project and connect to a database. Double-click the database on the Project Editor screen to open the database using the Database Editor. From here, you can select and view the tables and columns that make up the database’s schema. To view the data currently stored in the database, click the Browse button located in the Database Editor’s toolbar. REALbasic responds by adding a new screen to the Database Editor, as Figure 10-6 shows.

To submit a simply query, all you have to do is fill in the fields shown on the Select from window. To do so, select a table in the database from the Search in drop-down list. You then select a column from the table by selecting it from the first drop-down list in the Criteria section. Next, you must specify the type of operation you want to perform. Your choices are as follows:

•Is

•Is not

•Starts with

•Ends with

•Contains

•Greater than

•Less than

Then, you must key in a value in the third field in the Criteria section. REALbasic takes this information, generates a SQL statement for you, and submits it when you click the Select button. For example, using the database created in the previous example, you can view a list of all customers with overdue accounts by filling out the Select from window, as Figure 10-8 shows.

Figure 10-8. Providing REALbasic with the information needed to build a simple database query, as seen on Windows

After clicking the Select button, the results you see in Figure 10-9 are displayed in the Database Editor’s Browse screen.