Developing a Database Application:
The dBASE SE Tutorial
Ken Mayer, Senior SQA Engineer, dBASE Inc.
Michael Nuwer, dBASE user
|
Developing a Database Application:
|
|
Creating The DataModules |
The goals for Phase III of the tutorial project are:
Before Starting This Phase. It is very important that before you begin working on this phase you make sure that the "Look in:" combobox is pointing to the "dBASESEtutorial" folder, not to the "dBASESEtutorial\Tables" folder (a step that was included toward the end of the previous phase, but you may have missed it). Select the "Data Module" tab in the Navigator and make sure you are pointing there now.
dBASE, Inc. has taken the world-class object model that was created for Visual dBASE 5.x, and moved it over to the tables and the databases. What this means is that you can now use tables, records, and fields as objects, with each having it's own set of properties, events, and methods. This gives the developer a lot of control over the code, and allows the developer a lot of the object oriented power and reusability that you can get from other objects in dBASE SE.
So, what are all these objects, and how are they related? Before we get into the "in-depth" discussion, let's step back and take a look at the bigger view of the data objects in dBASE SE.
Most of these objects are containers. This means that they can hold other objects. An example which you will see a lot of, is the query object. This object holds a rowset object, so it is a container for the rowset object. Unlike some containers, there can be only one rowset object for each query object. The rowset object is a container for a fields array. The fields array holds pointers to each field object that is contained in the rowset. Hence, you might draw out a diagram something like the following:
|
The rowset object would have a parent, which is the query, the fields array would have a parent, which is the rowset object, the field objects have a parent, which is the fields array.
Note that you cannot define a rowset object independent of a query object, but you can define a field object independent of a fields array or rowset (but while you can use it outside of the rowset, it's kind of tricky unless you add it to the fields array of a rowset).
Now, to make things more interesting, a database object refers to a database or BDE Alias. A database, by its very nature, contains tables. In a way, the database object is a reference to an object -- the database itself. However, that is rather simplifying matters. You should normally use a BDE alias for reasons that will be discussed when we get to the database object.
A datamodule can be used to contain database objects and query objects. Rowsets are contained automatically by queries, so while a datamodule contains the query, it doesn't directly contain the rowset.
So, how confused are you now? Hopefully not very. All of this is leading to something.
Each of the data objects, as noted elsewhere in this document, has properties, events and methods.
Properties
Properties are the attributes of controls and include among
others identification, and other properties.
A property setting can be changed programmatically
as well as in the designer surfaces (form, report, datamodule).
The setting is normally a single value, for example, a character
string, number or reference to another object.
Events
Events are something that controls respond to, a mouse click, a control getting
focus, a change in the control's value, etc. Events are always "firing" provided
the event is occurring, however, in order to have something happen, you have
to "hook" code to the event. (Windows itself is really just a big "event" handler
-- it does not do anything until some event is fired.)
Events whose name begin with ON, such as onOpen, occur after the action to which they refer. Thus, the onOpen event occurs after the query is opened. The second thing is that events that begin with CAN, such as canClose, must return a logical value that will determine whether the indicated action may proceed. So, a query's canClose event must return true before the query may close. Returning a value of false will prevent the query from closing. Failure to return a Boolean value may yield unpredictable results.
Methods
Methods are code that performs an action. Objects have
a number of built in methods which are called through
the control, i.e., form.rowset.next ().
One thing you should remember is that, if you use the built-in methods as a programming hook to insert your own code (this is called over-riding the method), you will nearly always want to call the original built-in method before, during, or after your code. (This would be achieved by entering: SUPER::methodname() into the code.)
Database
The database object is used when you use BDE Aliases to handle
your databases. An alias is absolutely required when working with SQL Server
tables (such as Interbase, Oracle, etc.), and is optional
but useful when using local tables.
We are using a database alias in this project. There are a number of good reasons why we do this. Here are a few:
Creating a Database Object Reference
You can create a database object reference easily enough.
There are only a few things you must set:
// for local tables d = new Database() d.databaseName := "MyAlias" d.active := true
Query
The Query object is the most often seen data object. The query
object is used to reference the individual tables of your
database, and is required if you are going to do any
work with tables using the OODML.
As such, it is very important that you understand this one. It is the container of the rowset, which is where most of the data manipulation methods and events are, as well as the rowset being a container for the fields.
How Do I Use a Query?
We will keep coming back to this topic, but at it's most basic,
and assuming that you are starting with local tables, you
need to instantiate the query, assign the SQL property, and
then set the active property to true.
q = new query() q.sql = "SELECT * FROM mytable" q.active = true
One thing that sometimes worries new users of dBASE SE is that they "have to know SQL". No, you don't. The above command is the most SQL you absolutely have to know to do some very detailed applications in dBASE SE. Most of what you might want to do is covered by properties, events, and methods of the rowset object, which we will get to presently.
The "*" in the SELECT statement is a wildcard -- it means "all fields".
The above three statements are all fine and dandy if you are creating a program that needs to manipulate data, but what if you want to use a form or a report?
It couldn't be more simple. Bring up a form in the forms designer, click on the navigator "Tables" tab, and drag the table you wish to use onto the form (or report) surface. You will see an icon used to represent the query object. This has the letters "SQL" on it. The designer surfaces automatically fill in the required information, although the code looks different if you examine the source code:
this.CUSTOMER1 = new QUERY()
this.CUSTOMER1.parent = this
with (this.CUSTOMER1)
left = 52.5714
top = 2.5
sql = "select * from customer"
active = true
endwith
The designers stream out the left and top properties so that every time you open the form, the query icon is in the same location. When you run the form, the query icon will not appear on the form -- it is there only during design mode.
The first statement creates an instance of a query object, but note that rather than "CUSTOMER1" you see "this.CUSTOMER1" -- "this" refers to the form. Note also that the parent property is being assigned for you, and again "this" refers to the form.
You could write the same code that was originally shown in a similar fashion in your program:
q = new Query()
with( q )
sql = "select * from mytable"
active = true
endwith
This is just as valid as the previous way of writing the code.
SQL SELECT Statements
If you examine any book on SQL, you will find that there are
a lot of options that you can use with the SQL Select statement.
As noted above, you do not really need to know a lot about SQL Select statements to use these data objects. However, you should know a few things -- particularly if you are familiar with SQL:
WHERE clauses work fine, but there are some caveats.
query.sql = "select * from parts where part_id > 1"
query.sql = "select * from parts where ;
date_field = '01/01/1999'"
// or
query.sql = [select * from parts where ;
date_field = ']+date()+[']
// etc.
The reason dates must be enclosed in quotes is to
differentiate them from mathematical operations.
This allows the BDE to know that 04/15/1998 is a date
and not the literal value 4 divided by the literal
value 15 divided by the literal value 1998.
Read Only Queries
Local SQL (SQL statements used on local tables)
will often generate read only queries. The following
will generate read-only queries:
Rowset
Once you have the query set and it is active, what can you do with it? Well,
not all that much. To work with the data, you have to work with the rowset and
field objects.
The rowset object is the real work horse of the data objects in dBASE SE. A lot of the functionality found in the old XBase DML commands and functions is now in the rowset object's properties, events and methods. We will explore some of this functionality shortly.
Fields Array
This is a very simple topic. The fields array itself is
an array that is contained by the rowset, and holds pointers
to the field objects for the rowset.
What can you do with the fields array? Well, the fields array is how you address the fields. You can add or delete fields in the fields array (useful for calculated fields, a topic we'll get to later). You can find out how many fields are IN the fields array. There isn't much else.
As noted, this is fairly simple. The most important thing is that this is an associative array, which means that you can select a field either by a number (it's position in the list) or by it's name. You can do the following to find the value of a field:
? form.rowset.fields[ 1 ].value // or ? form.rowset.fields[ "fieldName" ].value
Rather than spend a lot of time here, let's look at the field object. There's a lot going on there.
Field
The field object is used to allow access to individual fields
in a table, and work with properties, events and methods to
modify the behavior and value of the field. Each element in the Fields Array
is an object of the Field class. And each field object corresponds
to one of the fields returned by the query.
Some of the Field object's properties provide useful information -- like fieldName, length, or type. The value property reflects the current value of a field for the current rowset; assigning a value to the value property assigns that value to the row buffer.
The following code, for example, will loop through each of the fields in the query "q" and print the fieldName of those that are empty.
for i=1 to q.rowset.fields.size
cFieldName = q.rowset.fields[i].fieldname
if empty( q.rowset.fields[cFieldName].value )
? cFieldName
endif
next
Now that we've examined the major data objects in dBASE SE let's take a look at using them a bit.
Most of the following is going to assume you are using forms (and/or reports, the ideas are the same for at least a lot of it), and so the syntax for the commands shown will assume forms, or in many cases the onClick event of pushbutton controls on forms. Keep in mind that the same types of things can be done programmatically.
Using a Table On a Form
After all that brouhaha, and detail, it sure sounds like this is going to be
complicated, doesn't it?
Actually, it couldn't be more simple. To use a table on a form, the simplest method is to open a new form in the designer, click on the navigator window in dBASE SE, click on the "Tables" tab of the navigator, and drag your table to the form designer surface.
What happens is that dBASE SE will place an icon on the form surface, which is used to represent a non-visual object (when the form is run, the user will never see this object). The icon will have the letters "SQL" on it -- this is a query object. In addition to placing the icon on the designer surface, dBASE SE will set the SQL property, and the active property will default to "true".
If you are using a BDE Alias, you will get, in addition to the query object, a database object as well. The form designer will automatically make the correct connections between the database object and the query object.
One thing that it is important to note is that the form itself has a property called "rowset". dBASE SE automatically stores a reference to the rowset of the first query object placed onto the form in this property of the form.
Navigating Through Tables
Once you have a table on a form, and presumably
some controls for fields, you will, at the very
least, need to allow your user to navigate through
the table.
If you have set the indexName property of the rowset object, the table will be navigated based on whatever sequence is set in the expression of the index.
Navigation is done via methods of the rowset, which were discussed way up in the rowset section of this document. There are some things you should note:
When you write your own navigation code, here are some things to consider:
Editing
You can set a rowset to be not editable until the user explicitly decides to
edit (we find this to be a good idea -- it's very easy to accidentally change
something) by setting the rowset's autoEdit property to false (it defaults to
true). This has the effect of disabling all controls that are dataLinked to
the fields of the rowset.
If you decide to use the autoEdit property set to false, then you will need to provide a way for the user to edit the current row. This can be done with:
form.rowset.beginEdit()
and you can place this code in the onClick event for a pushbutton.
You should probably provide a "save" option and a "cancel" option by calling the rowset's save() and abandon() methods.
Deleting Rows
The OODML does not support the XBase "soft delete"
(basically this is the ability to continue to display
a deleted row on the form) directly. It is possible
to do this via the BDE's API, and there is an
example of this in the dUFLP library.
The reason for this is that the .DBF is the only table format that allows this functionality -- all others basically assume that a deleted row is gone. Don't panic, however -- if your user decides to delete a row, it is actually still in the table, but you cannot show it to the user, and you cannot (using the OODML) allow the user to recall (undelete) that row.
Finding Rows in a Table
There are several ways to "find" rows in a table.
You can use "findKey()" and/or "findKeyNearest()", you
can use "beginLocate()" and "applyLocate()", and a lot
more.
Filtering Rows in a Table
As with "Finding" rows, there are quite a few ways
to filter rows in a table, including the "filter" property
of the rowset, the "beginFilter()" and "applyFilter()"
methods, the "setRange()" method, and the "canGetRow" event
of the rowset.
A datamodule is a container for data objects. The purpose of the datamodule is to set-up your data objects in whatever way that you might need in one place, so that if you need to re-use the same set-up (for example, you might have a set of tables set on a form, and need the exact same set-up for a report, or maybe for another form) you can, with no extra effort.
Another useful feature of datamodules is that you can have a custom datamodule which can be used for some basic setup, which then can have that setup inherited by the datamodules that are subclassed from it. We will be using a custom datamodule in the application we are creating which will have the database object on it. That is all that will be there, but this will be used for all of the subsequent datamodules that we need for this application.
In the tutorial project we will use datamodules for all our data access objects. At this point we just want to say that in any situation where you are working with more than one table, or you are setting up some fairly complex code (perhaps in canAppend events and such) you are better off using a datamodule and encapsulating all of that, so that if you need it in another form, you can save yourself a lot of work. Use datamodules a lot. They will make your life as a developer much easier.
dQuery is the center piece of dBASE SE. It offers visual tools for modeling, entering and filtering data. It also generates reports and desktop applications. Many of the dQuery tools are designed so that you can interact with your data. But there are also tools provided for the developer.
In this Tutorial, we will use dQuery to create our dataModules. We will not take advantage of the many filtering tools nor of the reporting tools. For those interested in these and other interactive tools you should read "The dB2K Guided Tour".
The dQuery interface is divided into four sections. The upper portion is the Design Surface. This is where we create and use database and query objects. The lower portion contains a notebook with three pages. The Live Data tab displays a grid reflecting the selected query object. When a parent-child relationship exists between two queries, the grid in the Live Data area will reflect the constraints of the child query.
The second and third tabs in the dQuery notebook can be used to create Custom Views of the data and to generate a report that reflects this data. The Tutorial Project will not need these two tabs.
There are may ways to add objects to the dQuery Design Surface. In this Tutorial we will normally instruct you to drag and drop an object from the Navigator onto the Design Surface. But there are other ways of accomplishing the same task.
As an alternative you can right-click on the Design Surface and select the appropriate object from the list in the pop-up menu. You can alternatively use the Main Menu and select New | Query from Table; or click the Query button in the toolbar; or if you are using Easy Start, click Option 2: Query.
These are all valid methods for adding objects to the dQuery Design Surface. The technique you choose to use is largely based on personal preference and the size and resolution of your monitor.
For our application we will be using a database alias, which means that we will need a database object. Rather than creating this database object on each datamodule we can put the definition in a custom datamodule and inherit the properties in the inherited datamodules.
To create a custom datamodule, the easiest method is to go to the Navigator and select the "Datamodules" tab. You should now see two icons, both of which are named "Untitled". The first is a 'standard' Datamodule (this is mostly blue), the one next to it (which is yellow) is a custom Datamodule. (Note: if you right click on the "Untitled" icons in the Navigator, you will see "Create a new ..." with the type of whatever object -- you can select this little menu option if you wish, but you can also use this to determine what the icon is for if you are not sure.)
Double click the "Untitled" custom Datamodule. This will bring up the dQuery design surface. We need to add a database object to the design surface. To do this you can select the database icon on the toolbar or you can right click the mouse and select Add Database Object from the popup menu.
The Select/Add Alias dialog form will appear.
Choose "dBASESEtutorial" from the list box and click "OK." Save the datamodule (File | Save), give it a name: BASE.CDM, and, if needed, change the folder location to C:\dBASESEtutorial.
If you open this custom datamodule in the source editor (press the F12 key), you will see code like:
class BaseCDATAMODULE of DATAMODULE
with (this)
left = -1
top = -1
endwith
this.dBASESEtutorial1 = new DATABASE()
this.dBASESEtutorial1.parent = this
with (this.dBASESEtutorial1)
left = 21
top = 16
width = 115
height = 112
databaseName = "dBASESEtutorial"
active = true
endwith
endclass
If you examined this in the source editor, close it (use the 'x' button in the titlebar, or press <Ctrl>+W).
We will come back to this code later in this document, but for now you can close the dQuery datamodule designer.
One of the many things we will need to do in our application is to create forms for interacting with the data. In order to do this, we need to set up some datamodules that handle a lot of the details (rather than re-creating them each time we need them).
One of the forms we will create is a form for interacting with the Customer table. This table will need to do some lookups on the State table and the Country table. Rather than creating the code to do this in the form, we will do it in a datamodule so that if we need to generate, say, a report that lists the customers we will not need to re-create the lookups.
In the Navigator, you should see two icons named "Untitled" in the the DataModule area. We want to create a "standard" datamodule, which we might do by double-clicking the first "Untitled" icon.
We want this dmd to inherit the objects and properties from the base datamodule. To do this select "File" from the menu. Then choose "Inherit New -> DataModule". The Select Custom Datamodule dialog form will open. Choose "Base.cdm," and click the Open button. The new datamodule is now subclassed from the base datamodule.
Note: "Subclassing" is the technique of using a class as a "base" class -- creating a new class that inherits all of the properties, methods, and events of the base class.An example of this is creating a form in dBASE SE (never mind "custom" forms) -- there is a "base" form class that is built in to dBASE SE. When you create a new form, it is a subclass of the base form. It has all the properties, events and methods of the standard form in dBASE SE.
Once you have subclassed an object you can modify its properties, events and even hook your own code into the methods of that class, without modifying the base class's definition.
Now you are designing a datamodule that is subclassed from the custom one we created earlier. You should see the database object (dBASESEtutorial1). This object is inherited from Base.cdm. We are not going to change any properties of this object, but we could (these changes would only affect this datamodule, not all datamodules in the application). If you wanted to affect all datamodules in an application you would want to make changes in the custom datamodule.
Next we need to add the Customer query to this datamodule, so that we can set it up for use in the data entry form we will be creating later.
Switch to the Navigator and click on the tables tab. Click on the "Look in:" combobox, and in the drop down list use the slider control to scroll up and down the entire list of items. You should notice that there are two sets of items which are represented by two types of icons. At the top of the list are file folders. One of them should be c:\dBASESEtutorial\Tables and another one should be c:\dBASESEtutorial. At the bottom of the list you will see database icons. These are the BDE Aliases that are registared on you sysetm. One of them should be "dBASESEtutorial." Notice that the database names are always displayed in upper case letters.
For the Tutorial project we want to access the tables via the database alias, so select the dBASESEtutorial database icon. Now you should see a list of tables.
Click on the "Customer" table and drag it to the design surface -- you will see a new object on the design form with the name Customer1 -- this is a query object
Save your work so far by pressing <Ctrl>+S (save), and name the file "customer.dmd".
The listbox that represent the Customer1 query object contains the fields that are included in this query object. In the bottom pane of the dQuery designer you see the query's rowset displayed in a grid. This query has selected all the field from the Customer table so the query's field list and the table's field list are the same. This is not, however, necessary when you create a query. In fact, a query can contain a subset of fields from one table or it can contain fields from multiple tables. This feature is part of what makes the query object such a powerful tool. However, in the current datamodule, the Customer1 query is a replica of the Customer table.
We want a specific index to be active, which will be the "Names" index. To use this click on the query object and right click the mouse. Select the Set Index option. In the Select Index dialog form, click "Names" from the list of index tags and then click the "Set Index" button. That is all that is necessary to set the index. Now Close the Set Index dialog form.
Next we need to set the datamodule's rowset property to point to the "customer1" rowset. Right click on the Design Surface and select "Inspect" from the popup menu (or press the F11 key). Be sure that the combobox at the top of the Inspector has only the word "Form". In the properties list, select "Rowset" and then select "customer1" from the dropdown list.
The rowset property of a datamodule defines it's controlling rowset. This will be useful when we create our forms. There are many coding statements that will reference the controlling rowset without needing to know the name of the query object.
Before we close this datamodule, let's review the code. With focus on the datamodule designer, press F12 to bring up the source code editor.
As you can see, creating datamodules isn't that difficult, except that it helps to have an idea what you need to do.
The Inventory Datamodule will be similar in design to the Customer datamodule. Switch to the datamodules tab in the Navigator and double click the untitled icon. This datamodule should be subclassed from base.cdm, so use the "Inherit New" menu item to do this now.
As we did with the Customer datamodule, once you have the new dQuery datamodule design surface on the screen click on the navigator, select the "Tables" tab, and select the "Inventory" table. Drag it to the design surface.
Set the datamodule's rowset property to point to the "inventory1" rowset.
Click on the Inventory1 query object and right click the mouse. Select the "Set Index (Key)" option.
Set the indexName to "Description";
Save the datamodule as "inventory.dmd" and exit.
The Supplier datamodule will be set up nearly exactly like the customer datamodule.
Open the new datamodule in the dQuery designer and subclass it from base.cdm (follow the steps given above). Drag the "Supplier" table to the design surface.
Set the datamodule's rowset property to point to the "supplier1" rowset.
Select the query object and Set the indexName to "Company"
Save the file as "supplier.dmd" and close the datamodule.
The Invoice datamodule is going to be a bit more complicated and will require that you follow along carefully. We're going to use three tables for this one. The Customer table will be the primary table, and we will be using it as the "parent" table for the Invoice table, which will then be the "parent" table for the LineItems table (these are the individual items ordered by the customer), creating a parent/child/grandchild type of relationship.
Lets create a new subclassed datamodule with dQuery.
Next, drag the Customer table to the design surface.
Click on the datamodule surface and the rowset property in the Inspector. In the combobox, select "customer1" (this sets the controlling rowset.)
Click on the customer query, right click the mouse and select "Set Index". Set the index to "Names".
Click the customer query again, right click the mouse and select "Inspect Rowset".
Set the autoEdit property to false.
Save the datamodule (<Ctrl>+S) with the name "invoice.dmd".
Next drag the Invoice table to the design surface, and set the Index to "Customer ID". It is very important that this be the index, because that is how a match will be found against the Customer table. Don't forget to open the Inspector and set autoEdit to false.
Next select the Customer1 query object and click the "Customer ID" field. Click the left mouse button and, while holding it down, drag the field onto the Invoice1 query object. Let up on the mouse button. You should see an arrow from the Customer1 query object and pointing to the Invoice1 query object. You have just used drag-and-drop to set a parent-child relationship between these two queries.
Next drag the LineItem table to the design surface. The LineItem table will be set such that we can have one or more line items per invoice. Some of that will have to be handled in code in the data forms we will use for adding data.
Set the Index to "Invoice ID" and remember to set autoEdit to false.
The Linking field between the Invoice1 query and the LineItem1 query is "Invoice ID". Select that field in the Invoice1 query object, than drag and drop it onto the LineItem1 query. Would you believe that this is all you have to do to set up a parent/child relationship?
When we use dQuery to set the parent/child relationship between two queries, there are two properties of the child rowset that are modified.
A child rowset object has a masterRowset property. The master rowset for Invoice1 is Customer1. The second property is the masterFields. This property identifies the key field in the parent rowset and is matched to controlling indexed of the child rowset. When you use dQuery's drag and drop, these properties are automatically set in your datamodule.
Note: If using tables that are not local (.DBF or .DB) you would need to use the query object's masterSource property instead of the masterRowset and masterFields properties of the rowset object. This is discussed in detail in various of the Knowledgebase documents.
Now that we've created the basic datamodules that will be used in our application, let's consider a few additional elements that can be added to a datamodule. The first thing we must do is ensure that the user of our applications enters a customer's last name and postal code when a new row is added to our system. It is not uncommon for a user to start appending new information, but then get called way by the phone (or any number of other office distractions). In other words, we want the last name and postal code to be required fields.
If either field is blank, we want to alert the user that the data is missing and that it must be entered before the row can be saved. If the user does not have a last name or a postal code to enter they should abandon the data entry until that information is known.
There are two main types of data validation that can be added to a query object. The first is field-level validation. Each field object in the rowset's field array has a canChange event. Each time the field value changes, this event fires and validation code will run if its been coded. The canChange event fires when the user tries to move out of a field to another field or to another object (like a lookup list).
This event should be used with great caution, because there are several drawbacks with field-level validation. The primary drawback is that if the user doesn't know what to enter it is difficult to escape from the entryfield and close the form.
The most appropriate place to validate data entry is at the row level. We can use the rowset's canSave event to check the values of any field before the data is saved. The row-level canSave event fires only when the user tries to save the record. This event offers the developer greater flexibility for guiding the user, or gracefully abandoning the modifications.
We are not going to use extensive validation in this application. We will choose a few examples that illustrate how this is typically coded into an application.
To make the customer's last name and postal code require fields, we will begin by re-opening the customer.dmd Datamodule. Find this file in the Navigator and double click it.
dQuery will open the datamodule in it's design surface and you should see a list box that represents the customer1 query object. Click your mouse inside that query object and click the right mouse button. In the popup menu, select "Inspect Rowset". This will call up the inspector.
Select the Event tab in the inspector and locate "canSave" in the list of events. Click this option and note that a wrench icon appears at the right. Click the wrench. The Source Editor comes up with the cursor positioned inside a function. The code that we will enter into this function executes each time the user attempts to save the customer1 rowset. This function returns a boolean value that controls whether dBASE SE will or will not save the row.
function rowset_canSave
local cErrors, bRetVal
cErrors = "" // String for errors
if empty( this.fields[ "Last name" ].value )
cErrors += "- LAST NAME cannot be blank" + chr(13)
endif
if empty( this.fields[ "postal" ].value )
cErrors += "- ZIP CODE cannot be blank" + chr(13)
endif
if "" # cErrors
msgbox( "Can't save current entry because:" + chr(13) + ;
cErrors, "Bad entry", 48 )
bRetVal = false
else
bRetVal = true
endif
return bRetVal
Save and close the source editor (Ctrl+"W"). After creating the function for canSave, we can test to see if the code works in the dQuery interface. On the toolbar click the "Add New Row" button. A blank row will appear in the dQuery Live Data window. Tab to the "First Name" field, enter your first name and click the "Save Changes to Row" button on the toolbar. The "Bad entry" message box should popup. (If it doesn't popup, you should check your code.)
If user mistakes are caught when the data is being entered, we will save a lot of time later when the data is being manipulated. If we do not validate data entry we might find that a customer's last name does not print on one of our reports. We must then go back and locate this customer's record, find the actual last name, enter it into the table, and reprint the report. Which can take a lot of time.
This is an often touted feature that really confuses people.
What exactly is "lookupSQL" and why should you even care? How many times have you tried to use proper "normalization" for your data, to the point that you have a lookup table with a code field and some values associated with the code in other fields, and had to write the program code to get your primary table to interact with the lookup table?
Isn't that a lot of work? Loading arrays to display data in comboboxes, scanning through the data to find the proper code and storing that value in the primary table ... all of that can be a lot of code.
Not any more! LookupSQL is a feature that will do this automatically (even if it's not as complete as some of us would like).
In the sample database that we are using for this project, the customer table has a field called STATE ID, which is a two character field. This stores, as you might imagine, the standard US two character state codes (CA = California, etc.).
For your users, however, you may wish to display the full state name. To do this using the OODML of dBASE SE, what you need to do is use the lookupSQL property (the steps are below) and enter a simple SQL Select statement to select the fields you need in that table. (The lookupSQL property is a property of the field object.)
For this to work properly, you must select the code field in the lookup table as the first field (the type must match that of the field you will store it into), and then the field you want to display. If your lookup table is designed that way in the first place, you could simply use:
select * from lookuptable
Let's add lookupSQL to our datamodules. First, open Customer.dmd into dQuery. In this datamodule we need to set lookups for the "State ID" and "Country ID" for the customer, which will allow us to have dBASE SE automatically display the actual States and Countries associated with the two character codes that are stored in the Customer table.
Click on the Customer1 query object and right click the mouse. Select the "Inspect Query" option. Make sure that the customer query ("customer1") is what is displayed in the inspector (use the combobox at the top of the inspector to select it). Look down the left hand column of the Properties tab and click on rowset. To the right you should see the word "Object" and an "I" button -- this means that if you click it, you want to inspect the object that is referenced here. Do so. The inspector will now be viewing the properties of the rowset object.
Note: Throughout the tutorial we use the term "drill down" when we discuss using the inspector. What this means is to click on an object reference in the inspector (such as above, we selected rowset) and then click on the "Inspect" button (the button with the letter "I" on it). This moves you through the object hierarchy to get to an object that is contained by another object. (The query contains the rowset.)When we use the term "drill down" we are referring to this process. So if we suggest you "drill down to the fields array", it means click on the fields object in the inspector, and click on the "Inspect" button.
Click on fields and the "Inspect" button. This will set you to inspecting the fields array.
Select the "State ID" field, and click on the "Inspect" button in the inspector. (You may need to click the "+" and expand the "Array Elements".) This will now bring the "State ID" field into the inspector and we are examining the properties of this field. Click on lookupSQL and type:
select * from state order by state
and make sure you press the <Enter> key. If you set this property correctly the value property of the "State ID" field will show something much larger than two characters on the form (this is called "data morphing" -- using online help you may want to look up the terms "morphing" and "lookupSQL"). In addition we are ensuring that the display is alphabetical by the "state" field with the "order by" clause. (Normally, we will not be using the "order by" clause -- in some cases with local tables this creates a "read only" query. In the case of a lookup, this is not a problem as the lookup is, by it's very nature, read-only.)
We need to do this again for the "Country ID" field. However, we have drilled down to the field object for "State ID". How do we move back up? Find the parent property, which says "object" -- click on the "Inspect" button, and we're back at the fields array. (You can also use the "Left Arrow" button at the top of the Inspector -- this takes you to the previous object inspected.)
Select the "Country ID" field, click the "Inspect" button, select lookupSQL, enter:
select * from country order by country
and press <Enter>.
If the data in the lower pane of the dQuery datamodule Designer disappears, click the query object again to refresh the grid. Now scroll the Customer1 data grid to the right and locate the "State" and "Country" fields. You will notice that the data contains the full names rather then the two digit codes.
Save this datamodule and close dQuery.
Next, open Supplier.dmd into dQuery. Set the lookupSQL property for both the "State ID" and "Country ID" fields the same as the customer datamodule. (Drill down to the fields array, click in the individual field and drill down to it, click on the lookupSQL property, and enter the appropriate SQL select statement.)
Save this datamodule and close dQuery.
The next lookupSQL we need is in the Inventory datamodule. So open Inventory.dmd into dQuery.
Click on the Inventory1 query object and right click the mouse. Select the "Inspect Query" option.
select supplier.'supplier id', company from supplier order by company
and press <Enter>.
This particular lookup is selecting specific fields to use because if we didn't we would end up displaying the wrong field. We need the company name to appear on the Inventory form, not the Contact in the company, which is what would appear if we were not specific (the lookupSQL property uses the fields in the sequence of the table if you do not specify the fields -- the first field in the supplier table is the "supplier id" field, but the next field is "contact" -- we want to display the "company" field). In addition, we are specifying "supplier.'supplier id'" because the fieldname has spaces in it -- it appears this is the only way to get the correct fieldname to work using a SQL select statement.
The final lookupSQL we need is in the Invoice datamodule. Open Invoice.dmd into dQuery.
In this datamodule we need to set a lookup on the "Item ID" field to look in the inventory table so that we display the description field. Go to the fields array, and select the Item ID field, and drill down. Select the lookupSQL property and enter:
select inventory.'item id', inventory.'description' from inventory
and press <Enter>.
Save the datamodule and exit dQuery.
Calculated fields are rather important in a lot of applications. These are simply fields that display a calculation or a value not in the table for the user (some folk think of a calculated field as specifically being aimed at math, but combining two fields into one is considered to be a calculated field). The user cannot directly interact with them, because they are, by their nature, read-only -- they are not directly datalinked to a table. (The user can actually edit these unless you set the readOnly property on the field, but it won't make a difference to the table itself.)
To create a calculated field, you must create an instance of a field object, set some properties, and then add the field to the fields array for the rowset. If set properly, as the user navigates through the table, this field will be calculated (using the beforeGetValue event), and the results will be displayed on the form (or report).
The following is a simple example of creating a calculated field to display a "full name" from first and last name fields in a table. Keep in mind that any valid dBASE SE expression will work. This example assumes that the field object is being added in a query's canOpen or onOpen event:
// instantiate the field object
f = new Field()
f.fieldName := "Full Name"
f.readOnly := true
// "this" in the following statement refers to the
// field object (important information)
f.beforeGetValue := {|| trim( this.parent["first name"].value ) ;
+ " " +this.parent["last name"].value }
// "this" in the following statement refers to the query:
this.rowset.fields.add( f )
It is very important that you use the beforeGetValue event to actually perform the calculation, or the value will not update as you navigate through your table. If you assign the "value" property instead, it will display the value assigned for the first row seen, and not update as you navigate.
You can have multiple calculated fields for a rowset, you would just want to make sure that each had a unique fieldName property.
At this point in the tutorial we will use a simple calculated field to display the customer's "full name" from first and last name fields in the Customer table. Later in this tutorial we will need to create some additional calculated fields, so let's write a single method for creating these field objects. We will put this method in the custom datamodule called Base.cdm. This way, all our standard datamodules will have access to the method.
So switch to the Navigator and open Base.cdm into dQuery. You will recall that this custom datamodule contains the database object. We are now going to add a method to this class.
Press F12 to bring up the source editor. Then move the cursor to the bottom of the class constructor code.
A Class is a definition of an object. As Alan Katz once pointed out: "Many beginners mistake a CLASS for a program or procedure. A CLASS is NOT executable code. It is a blueprint only. Just like a real blueprint, you can't live in the house represented by the blueprint until you build it." The Class Constructor Code is part of the "blueprint" for an object. It includes all the properties and their values. It does not include the methods (or functions) of the class. In the case of a container object, like the form or datamodule, the Constructor also includes the definition of all objects it contains -- like a database object or a query object. A method is the code that executes the behavior of an object. Methods are functions that belong to an object.
In the present case, there are no methods in our base datamodule class, so lines 2-16 are the class constructor code. You must move the cursor so that it is below the last "endwith" and above "endclass". This is where we will insert a new method.
Go to the "Method" menu, and select "New Method ...". We are going to add a method into the datamodule that will be "unattached" to any specific event. Change the statement "function Method" to "function calcField(cName,nLength)", and add the code below (the "function" statement and the "Return" statement should already be there, but you will want to modify them so that they look like what is shown below):
function calcField(cName,nLength)
local oField
oField = new field()
ofield.fieldname := cName
oField.length := nLength
this.rowset.fields.add(oField)
return
This method can be used to create a calculated field within any datamodule that is subclassed from Base.cdm.
We can now save and close the BASE datamodule. To be safe let's recompile this file before we move on. In the Navigator select BASE.CDM and right-click the mouse. In the pop-up menu select compile, then close the Status dialog form. Now we can return to the Customer datamodule and create the Full Name field.
Go to the Navigator and open Customer.dmd.
Select the Customer1 query object.
Right click the mouse and select Inspect Query.
The Inspector will come up. Click the Events tab (be sure "form.customer1" is the object being inspected).
Click the onOpen event and then click the wrench at the right.
Enter the following code into the customer1_onOpen function:
class::calcField("FullName",30)
Bug: Be sure there are no spaces in the field name. A space in the field name can cause problems later when the beforeGetValue method is created. If you create a beforeGetValue method for a field with a space the designer will write:function Full Name_beforeGetValue returnSince a function name can not contain a space, the above method will cause an error. To avoid the problem use field names without spaces for your calculated fields.
This line will call the calcField method and pass "FullName" as the field name and 30 Characters as the field length.
Save this datamodule. We now will exit dQuery and reopen the CUSTOMER.DMD datamodule in order to initialize the new field. You should do it now -- close dQuery and reopen CUSTOMER.DMD.
Next we will use the beforeGetValue event to actually perform the calculation.
Select the CUSTOMER1 query object.
Right click the mouse and select Inspect Rowset.
We need to "drill-down" in the inspector to the new field object. So find "Fields" in the properties list and click the "I" (Inspect) button. In the Fields Array find the "FullName" object (this is our calculated field) and click the "I" button.
Next click the Events tab and then select the beforeGetValue event.
Click the wrench button and enter the following code:
function FullName_beforeGetValue
local a
a = trim( this.parent["first name"].value ) + " " ;
+this.parent["last name"].value
return a
For those who like to economize on their code, the above method could be alternatively written as:function FullName_beforeGetValue return trim( this.parent["first name"].value ) + " " ; +this.parent["last name"].value
You can now look at the results of the calculation in dQuery's lower pane. Scroll to the far right of the data grid. The last column should be labeled "FullName" and the data should read as first and last name combined.
Now save the Customer datamodule. We are done with creating our first calculated field.
Ok, what you just did was set up re-usable modules that can work for both forms and reports, and have, in some cases, lookups defined (which in earlier versions of dBASE took a lot of code). In one case you have a set of three related tables set up ... not too shabby for a small amount of work. The best part is the "reusable" part. Most of these datamodules will work for forms and reports (and labels), and while this application will not need them for this, we could use the same datamodule on different forms.
The other feature that shows up through out the product (dBASE SE, that is) is the ability to create a datamodule (or form or report) and inherit from that, which gives you true object orientation. (You do not have to use custom datamodules, forms, or reports, that is just the main way of doing things. All datamodules, forms, reports and other objects can be subclassed, not just custom ones.)
Last Modified: October 25, 2002
The Legal Stuff: This document is part of the dBASE SE Tutorial created by Ken Mayer and Michael Nuwer. This material is copyright © 2002, by Ken Mayer and Michael Nuwer. dBASE SE is copyrighted, trademarked, etc., by dBASE, Inc., the BDE (Borland Database Engine) and BDE Administrator are copyrighted, trademarked and all that by Borland, International. This document may not be posted elsewhere without the explicit permission of the authors, who retains all rights to the document.