Beginning REALbasic - From Novice To Professional (2006)
.pdf
286 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-9. Viewing the results of a simple database query, as seen on Windows
From here, you can sort the displayed database records by clicking the Sort button. This opens the Sort Data window where you can select one or more fields to sort by selecting an entry from the Column List field and clicking the Move button to add them to the Sort Order field. All records are sorted based on the first specified column. If a second column were added to the Sort Order field, it would be used to perform a secondary sort, as Figure 10-10 shows.
Figure 10-10. Sorting the results of a database query, as seen on Windows
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 |
287 |
Note the presence of the arrows, just to the left of the column name in the Sort Order field. They specify whether you want to sort a column based on ascending or descending order. You can toggle between ascending and descending sorts by clicking these arrows. The results of the sort operation are displayed in the Browse screen, as Figure 10-11 shows.
Figure 10-11. Viewing the results of a database sort operation, as seen on Windows
If necessary, you can create more complex queries by clicking the plus (+) button to the right of the third entry field in the criteria section to add another row. REALbasic lets you enter as many rows as you want. You then need to tell REALbasic how to match up data in the database against the different criteria you specified. This is done by selecting a value of either All or Any from the Results match drop-down list. Selecting All tells REALbasic to display only those records that match each of the stated criteria. Selecting Any tells REALbasic to display any record that matches at least one of the specified criteria.
Advanced SQL Query Statements
If you know enough about SQL to generate your own statements, you can do so by clicking the Advanced button in the lower-left hand corner of the Select from window. The Select from window changes and displays a single multiline EditField control into which you can type any SQL statement supported by the target database.
288 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
A number of good reasons exist for submitting your SQL queries using the advanced option. For starters, you can define a query using the simple version of the Select from windows, and then click the Advanced button to see the resulting statement REALbasic generates based on your specified criteria. You can use this approach as a self-learning technique for SQL. For example, Figure 10-12 shows the SQL statement generated based on selection criteria of overdue accounts.
Figure 10-12. Viewing the query that REALbasic generates on your behalf, as seen on Windows
Another reason for generating your own SQL statements is because the simple version of the Select from window only lets you generate a small set of SQL statements. To generate a more complex query, you must use the Advanced option. For example, by default, the simple version of the Select from window does not provide you with the capability to select which columns you want returned as part of the query. Instead, all columns are returned. By replacing the * character following the opening SELECT keyword with a comma-delimited list of column names, you can restrict the columns that are returned.
Changing Column Data
REALbasic’s Database Editor is not intended to be used as a data entry editor for databases, but you can use it to modify data stored in table columns. You cannot, however, use it to add or remove database records. To modify column data, you must generate a query, and then click the Update button when reviewing the query results. This opens the Update Database records window, as you see in Figure 10-13.
Figure 10-13. Modifying database data associated with specifc columns, as seen on Windows
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 |
289 |
In the example in Figure 10-13, all data stored in the selected table’s AcctStatus column would be changed to Paid in Full. If you are comfortable generating your own SQL statements, an advanced version of the Update Database records window is also available.
■Note You can add new records to a database by keying in an Insert statement using the Advanced option.
Developing Database Front-Ends
As you already learned, REALbasic provides everything you need to build effective database front-end applications, regardless of whether the back-end database is a REAL SQL database or a third-party database. REALbasic provides two database-specific controls designed to facilitate the creation of front-end database applications: the DatabaseQuery and the DataControl controls.
The DatabaseQuery Control
The DatabaseQuery control, like other REALbasic controls, can be found in REALbasic’s list of built-in controls. You add the DatabaseQuery control to an application like any other control, although, while you can see and interact with the control within the REALbasic IDE, it is invisible to the end user.
The DatabaseQuery control has only two properties: the Database property is used to associate the control with a specific database and the SQLQuery property contains a SQL statement automatically executed when the control is loaded. The purpose of this statement is to retrieve records from the database. The DatabaseQuery control has one method named RunQuery, which you can programmatically execute from within program code.
Using the DatabaseQuery control and a REALbasic programming technique, known as object binding, you can create a simple user interface to display database records. For example, using the REAL SQL database from previous examples, and a ListBox control and object binding, you can set up a simple user interface that provides a table level view of all the data stored in a particular database table.
■Note Object binding is the process of linking a control to a resource, such as a DatabaseQuery control to a ListBox control, to perform an action without program code.
To set this up, all you have to do is create a new project, add the MyTestDB to it, and then add a DatabaseQuery and a ListBox control to the window. Set the DatabaseQuery control’s Database property equal to the name of the database. This links the control to the database. Then, set the SQLQuery property equal to Select * From Customers. This SQL statement tells REALbasic to retrieve all the records from the Customers table. Next, select the ListBox control and set its ColumnCount property equal to 4 the number of columns in the database). This
290 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
enables the ListBox control to display all the columns in the database. All that remains is for you to link the DatabaseQuery control to the ListBox control using object binding. To do so, select the Database Query control, press Control, and then select the ListBox control (on Macintosh, press the Command key). Then, click Project Add Binding. REALbasic responds by displaying the Visual Bindings window with the following four options:
•Bind ListBox1 with list data from DatabaseQuery1
•Bind DatabaseQuery1 with string data from ListBox1
•DatabaseQuery1 when ListBox1 gains focus
•DatabaseQuery1 when ListBox1 loses focus
Select the Bind ListBox1 with list data from DatabaseQuery1 option and click OK. REALbasic responds by displaying a black line that visually links the two controls, as you see in Figure 10-14.
Figure 10-14. Examing the object link between two controls, as seen on Windows
Now, run the new application and you see the ListBox control display all the records stored in the Customers table, as you see in Figure 10-15.
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 |
291 |
Figure 10-15. Using object linking to build a simple database table-viewing application, as seen on Windows
The DataControl
Using REALbasic’s DataControl control, you can create a user interface for viewing, changing, and adding database records. To work with it, drag-and-drop an instance of the control on to a window. As you can see in Figure 10-16, the control has buttons for displaying the first, previous, next, and last records in a database.
Figure 10-16. Using object linking to build a simple database table-viewing application, as seen on Windows
To set up the control, you need to assign its Database property to the name of a database you added to the application. You also need to set its TableName property to the name of one of the tables in the database. Last, you need to assign a valid SQL query to the SQLQuery property, just as you did earlier with the DatabaseQuery control.
For the DataControl control to be useful, you need to associate it with other controls on the window, such as EditField, RadioButton, CheckBox, and PushButton controls. For example, assuming you just set the properties for the DataControl control, you could display data stored in the Name column of the Customer table in the MyTestDB database by adding an EditField to the window, and then set its DataSource property to DataControl and its DataField property to
292 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
Name. Setting the DataSource property links the EditField control to the DataControl, which, in turn, is linked to the database. Setting the DataField property links the EditField control to a specific database column. If you run this example, you’ll find you can use the DataControl to browse through all the names stored in the database. Figure 10-17 demonstrates how this example would look when run on Windows.
Figure 10-17. Using the DataControl control to browse information stored in a database
The RB Book Database
This chapter wraps up by showing you how to programmatically create a database that stores information about a user’s personal library, including book titles, author names, publishers, and ISBN numbers. In addition, a category column is defined, which can be used to group related books into subjects, such as programming, networking, biography, historical, and so on.
In following along with this exercise, you learn how to create and execute a new type of REALbasic program, known as a console application, which is a program that does not have a GUI. Although you can run console applications directly from the desktop, typically, they are executed from the command line, allowing the applications to display text output and to interact with the user when appropriate, using a text-based interface.
When run, the console application creates a new REAL SQL database called PersonalBookDB. In addition, the console application populates the application with three sample records. Once created, you can use this database as a back-end for other REALbasic applications. As an example, you learn how to create a small front-end utility that lets you add as many new records as you want— one at a time—to the database. Of course, you can also view and edit database records using the REALbasic Database Editor, as you saw earlier.
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 |
293 |
■Note Only the Professional version of REALbasic provides full support for console applications. However, the Standard version of REALbasic enables you to create demo applications. A demo application is simply an application that stops running after five minutes. The console application you develop here only runs for a moment as it creates the database. Therefore, the five-minute demo execution limitation imposed on REALbasic’s Standard version has no impact on the execution of the console application developed in this chapter.
In demonstrating how to create these two database applications, this chapter uses the Linux version of REALbasic, but you can just as easily create these applications using either the Macintosh or Windows versions of REALbasic. All the steps are the same.
Creating the PersonalBookDB Database
Let’s begin by creating the console application to be used to generate the PersonalBookDB. The first step is to create a new REALbasic project by clicking File New Project. When prompted to select the type of application you want to create, select Console Application. Because console applications do not have a GUI, REALbasic responds by opening the Project Editor. As you can see, console applications consist of a single App item. Double-click it and REALbasic responds by opening the Code Editor. Next, expand the Event Handlers entry in the browser area, as Figure 10-18 shows.
Figure 10-18. Console applications do not have a GUI and consist only of code statements, as seen on Linux.
294 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
As you can see in Figure 10-18, console applications have only two event handlers, one of which is the Run Event Handler. This event handler is executed whenever the console application is executed. The following lists the program code for the console application.
'Declare a database representing a REAL SQL Database Dim BookDB As REALSQLdatabase
Dim TargetFile As FolderItem 'Declare a FolderItem
Dim DBRecord As DatabaseRecord 'Declare a DatabaseRecord variable Dim blnResult As Boolean 'Declare Boolean variable
BookDB = New REALSQLdatabase 'Instantiate a REALSQLdatabase object
'Create a file to store the new database TargetFile = GetFolderItem("PersonalBookDB")
BookDB.DatabaseFile = TargetFile 'Associate database with the file
BlnResult = BookDB.CreateDatabaseFile 'Create the database
If BookDB.Connect() Then 'Proceed if the connection is successful
'Use the SQLExecute method to add a table in the new database BookDB.SQLExecute("Create Table BookCollection(Title varchar," _
+"Author varchar, ISBN varchar," _
+"Publisher varchar, Category varchar, Location varchar)")
'Create a index for the table BookDB.SQLExecute _
("Create Index TitleIndex On BookCollection (Title)")
DBRecord = New DatabaseRecord 'Instantiate a new record
'Assign elements to table columns DBRecord.Column("Title") = "Beginning REALbasic" DBRecord.Column("Author") = "Jerry Lee Ford, Jr." DBRecord.Column("ISBN") = "159059634X" DBRecord.Column("Publisher")="Apress" DBRecord.Column("Category") = "Programming" DBRecord.Column("Location") = "Shelf 4"
'Use the InsertRecord method to add the record to the table BookDB.InsertRecord("BookCollection", DBRecord)
DBRecord = New DatabaseRecord 'Instantiate a new record
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 |
295 |
'Assign elements to table columns DBRecord.Column("Title") = "Beginning SuSe Linux" DBRecord.Column("Author") = "Keir Thomas" DBRecord.Column("ISBN") = "1590594584" DBRecord.Column("Publisher") ="Apress" DBRecord.Column("Category") = "Operating Systems" DBRecord.Column("Location") = "Shelf 7"
'Use the InsertRecord method to add the record to the table BookDB.InsertRecord("BookCollection", DBRecord)
DBRecord = New DatabaseRecord 'Instantiate a new record
'Assign elements to table columns DBRecord.Column("Title") = "Beginning Ct" DBRecord.Column("Author") = "Ivor Horton" DBRecord.Column("ISBN") = "1590592530" DBRecord.Column("Publisher") ="Apress" DBRecord.Column("Category") = "Programming" DBRecord.Column("Location") = "Shelf 1"
'Use the InsertRecord method to add the record to the table BookDB.InsertRecord("BookCollection", DBRecord)
BookDB.Commit 'Complete the database transaction
BookDB.Close 'Terminate the database connection
'Display an message informational message
StdOut.WriteLine "DataBase creation and population: Completed!"
Else 'Proceed if an error occurred connectingto the database
'Display the error message that occurred
StdOut.WriteLine "DataBase Error: " + BookDB.ErrorMessage
End If
As you can see, the code statements listed here are well-documented with embedded comments that outline exactly what is going on, every step of the way. Once you add these statements, go ahead and save the application, and then assign it the name of DBCreator.
When you are ready, go ahead and run it. You can do so in either of two ways. First, you can run it like any other application by opening it from the desktop. Or, you can open and execute the application from the command prompt. When executed this way, you see a text message generated at the end of the application’s execution that either tells you the database was successfully created or displays an error message.