Developing a Database Application:
The dBASE SE Tutorial
Ken Mayer, Senior SQA Engineer, dBASE Inc.
Michael Nuwer, dBASE user
|
Developing a Database Application:
|
|
Introduction |
The Tutorial Project that we will be creating is a step-by-step type project. The project will progress from designing the tables to the forms through to the reports, before getting into the application's startup program. Not all developers follow this path, so if it is not one you are used to, don't sweat it -- it's just one method of accomplishing the task.
Please do not skip a step. There will be a combination of hands-on work, and explanations of why you are doing these things. After you have performed some steps at least once the directions may not repeat the next time you need to do those same steps -- the idea is to get you used to doing them without hand-holding, and to also get you used to the idea that when all-else-fails, you can look things up.
When done with each section there is a link back to the menu, or you can continue to the next section directly (links to the next section are included at the bottom of the different html documents).
There are a lot of notes (indented text with lines on the top and bottom) -- you should read these. They are attempts to explain the whys and wherefores, or to explain bugs and bug workarounds (if it's a bug, we call it a bug in the tutorial -- there's no reason to "Beat around the bush" here). Many of these were added during the initial "beta" cycle of this tutorial, where a group of developers ran through the tutorial and pointed things out to the authors -- these notes expand on the rest of the tutorial. Don't skip them!
Keep in mind that this is just one way to create an application. We can pretty much guarantee that no two applications are going to be exactly the same -- each client you have will have different requirements, and the more you learn about the software, the more your own development techniques are likely to change.
Auxiliary and Supplemental Reading
There are many documents in the dBASE Knowledgebase that you should review as
supplemental reading for this tutorial. (These are the ones in existence
the last time the tutorial was updated.) Note that these documents are included
in the Knowledgebase that shipped with dBASE SE. It is strongly suggested that
you read these documents. The material in them would take too long to cover
in detail here, so it is a good idea that you read them along with this tutorial.
They cover many of the same issues but in greater detail.
Getting Started
You need to create a directory off the root of a hard drive on your
computer called "dBASESEtutorial". (Well, it does say what it means, doesn't
it?) The reason for this is so that everyone is working with as close
to the same setup as possible. This will hopefully make more sense once
we get into things like the database alias and such that we will be setting
up.
The simplest way to do this is with Windows Explore (File|New|Folder). A "Folder" is the same as a "Directory" and throughout this tutorial we will use the terms interchangeably.
Next unzip the file named dBASESEtutorial.zip. This archive will copy a few support files to the dBASESEtutorial folder (or whatever folder you choose to work from).
You will also notice a few subfolders are created by the archiver. While creating the Tutorial application we will be working in \dBASESEtutorial. The tables that makeup the database will be stored in a subfolder named "Tables."
In addition, there is a folder named "Source" and one named "Final". The Source folder contains the completed source code for all the files that you will create while working with this tutorial. These files are provided for reference purposes and are collected in one folder. You should use the Source Editor to view these files.
The Folder named "Final" contains a full "working version" of the application to be created. The reason for including these files is that it may help if you see what you will be creating in the tutorial project.
Configure.prg
In the main tutorial folder you will notice a file named "Configure.prg". This program file contains commands that you should use to configure the dBASE SE IDE Environment. To start with, there are three active commands.
_app.speedbar := true _app.statusBar := true set design on
While developing the tutorial application, your program will from time to time crash due to programming errors. At times our application will turn off the speedbar, statusbar and design mode. If the application crashes after this, we can simply run configure.prg to turn them back on.
Some additional lines in this file are commented. Later in the tutorial we will create some custom controls for use in our forms and reports. At that time we will uncomment these lines. Then, when configure.prg is run, our custom controls will be available to the form and report designer.
The dBASE SE IDE Environment
This project assumes that dQuery loads each time you start dBASE SE.
We will not, however, use the Easy Start menu. If you would like to deactivate
this menu, select Properties on the Easy Start menu and uncheck "Open Automatically".
If you want to bring up the Easy Start menu, you can right click the mouse any
place in the dQuery window, and select the last item from the pop-up menu that
appears.
Furthermore, we assume that the Navigator and the Command windows are both on screen most or all of the time. If they are not, you should go to the "View" menu, and select the missing windows, so that both appear on screen. Their locations are not important -- you can move them and/or resize them as you like. (You may even want to use the "Window" menu to "Tile" these windows so that they are next to each other. Our personal preference is to have the navigator on top and the command window on bottom.) It is, however, important to have both available at all times.
You should also bring up the dBASE SE Desktop Properties dialog. If dQuery is open you must first close it. Then with focus at the command or navigator windows, the menu "Properties" should be displayed -- select this, and then select "Desktop Properties". In this dialog, make sure that the following settings are done:
Now click the "Apply" button then the "OK" button.
The Navigator may have an entryfield titled "Also look in:" under the "Look in:" combobox. If this is the case, you should remove the combobox while working with the tutorial. This can be done from the Properties, Navigator Properties Menu. Un-check "Use Supplemental Search Path."
If you have not used a Supplemental Search Path they can, at times, be handy. The following is from Online Help: Use Supplemental Search Path - If checked, adds an additional search path text box (called "Also Look In:") to the Navigator. When a path name appears in both Look In boxes, dBASE SE displays files in all the directories specified in both Look In boxes.
Clearing Out Custom File References
This is important!! Before you start working on any new application, it is completely
possible that you may have used custom Forms or Reports in the past. To avoid
any complications in what we are doing for the tutorial, do the following (repeat
as shown):
Repeat the steps above for the Reports tab. This will clear out references that may cause some frustration or grief later in the tutorial. Note that following the steps given above before starting any new project in dBASE SE is a good idea.
This project is going to be a fairly simple Customer Order and Invoice system. This will require several tables, several forms, at least a couple of reports. The tables will include a couple of lookup tables, relational tables (Invoice -> Inventory, etc.), and more. There are aspects of a "real-world" Inventory system that will not be included in this application. This is a learning application; consider it to be one with training wheels. Once we take the training wheels off, you can add whatever flourishes you need to.
In order to make things easier, we will actually create the tables, and then populate the tables by copying data out of other tables. That way we will have some data that at least makes sense.
If you follow along in the order shown in the tutorial menu (above), by the time it is complete you will have created an application from scratch, tested it, and deployed it. This should give you a good grounding to go out there and start building your own applications, which is the idea of this tutorial.
Project Specification
This is a very brief specification of the details for this project.
(If you are a professional developer and do a lot of contract work, you should be familiar with the specification process -- this is not going to come close to what you would need from your customers for a paying gig, it is just here to give you, the student, an idea of what we will be doing for this project.)
The application will be a multiple-document interface (MDI) application, with a menu (but no toolbars). This will allow the user to open multiple forms at once, including the same form multiple times.
There must be:
This is not a very complex application, but we will take advantage of a few special features of dBASE SE as we go. In order to make customization easier, we will be using custom forms, custom reports, custom datamodules, and custom controls.
Caveat: In addition to all the other caveats scattered throughout this tutorial -- because this is a simple application, some processing that you might expect to be done is not being handled here, such as decrementing the quantity on hand for items in the inventory table when an invoice is saved. That kind of processing can get fairly involved, because of the fact that you have to decide when an Invoice has completed processing, and then make sure you don't decrement the QOH more than one time for that invoice. The amount of time necessary to create this kind of logic takes away from the purpose of the tutorial, which is to get you started. This is not what we would call a "complete" application, and we would never ship this to any customer "as is". It is, however, a jumping off place for those folk who need to get a start creating applications in dBASE SE.
We cannot emphasize enough that the techniques shown here are simply one way of coding an application, and that there are many more methods of doing the same things, and of course, no tutorial application can possibly cover every situation that every client you may have will need. This is a learning tool only, and as such cannot possibly cover every possible contingency. Have we put enough disclaimers into this project yet?
It is a good idea for you to see this application working before creating your version. You will find a fully functional version of the application in \dBASESEtutorial\Final. This version of the tutorial application is "complete" and all should be working as the tutorial is designed. The purpose of this is to show you how it works.
You should be able to use each option in each menu selection and the steps given below will walk you through it.
The following steps are here to give you a structured 'walk through' of the application. You will see it work, and hopefully this will help you understand what we are building, and why, in the tutorial itself. There are a lot of places where it maybe difficult to understand "why" we are doing something, and sometimes it is easier to just see it in action.
Start the application by running dBASE SE. Then, in the command window type the next two commands:
set directory to c:\dBASESEtutorial\Final do start.prg
Menu Options
We will go through each of these one at a time, briefly -- this is just an overview
of what is being created for the tutorial, so please bear with the terseness
of the following. Note that you can tinker all you want to -- try clicking the
various buttons and so on (add rows, navigate) ... it should all work just fine.
Click on the "File" menu, or press <Alt>+F (either should select the file menu).
To open the Customers Form select "Open," then select "Customer."
You should see the "Customer" form appear just below the menu. Try navigating (using the buttons with arrows). Click the "Find Record" tab at the bottom of the form. Select Sally Simpson from the names in the grid. Now switch back to the "Individual Record" page.
Try editing this record, and either save or cancel it. Try adding a new record. Enter your name and address information. Try adding or editing another record (row), enter something or change a value but don't save or abandon, and press the <PgUp> key or try to close the form.
Close the form with the "x" button or use File|Close.
Multiple Document Interface
Select "File", "Open" and then "Customer" (again)
Select "File", "Open" and then "Inventory"
Select "File", "Open" and then "Customer" (again)
Note that all three forms are open, and that you have two instances of the Customer form. You can actually navigate independently in these -- you can look at one customer in one form, and another in the other. You can look at the inventory in the other form.
Select the "Window" menu. You should see all three forms listed in this menu, and whatever the current one is will have a checkmark by it.
Try selecting a different form in this menu, and you should see that form get focus.
Select the "File" menu and "Close" -- this will close the form that has focus.
Select "Help" and "About Tutorial". Try to select one of the other open forms. Note that you can't. Try to select the "Window" menu, again you can't -- that's because this is a "modal dialog" form, which doesn't work the same as the MDI forms. You can close this with the OK button, and forms and the menu are available again.
Select "Window" and "Close All" to close all the forms.
Invoice Form
The Suppliers form and the two lookup forms should all work the same as the Customer and Inventory forms, and you should experiment. The Invoice form, however, works a bit differently than the others.
Select "File", "Open" and "Invoices". This form is effectively a read-only form. It gets data from various places and displays it based on your selections. You can add, edit or delete by using the appropriate pushbuttons for the Invoices and Line Items, but the changes are made (except for deletions) in special dialog forms (sub forms, child forms, whatever you want to call them).
Find the customer "Chen, Josie" (try typing "C" in the "seeker" at the top of the form).
Note that she has five invoices, and each invoice has at least one mug ordered.
Try editing one of the invoices by clicking on the Edit button. The Invoice dialog form opens in a modal state. Click Cancel.
Try adding a new invoice. Notice if you save it, that the "Add a Line Item" screen appears. Select a mug and click OK to save it.
Try printing an invoice. We will be creating this report, so take a good look at it. Note the preview form -- we won't create this for the application -- it's provided with the tutorial (and with this "final" version). Note that you can print from here. Close the report preview form if you didn't print the invoice (if you did, the preview automatically closes).
Does this form start to make sense?
Close the Invoices form using the 'x' button in the title bar.
The Reports
In addition to the Invoice Report there are four other reports in this application. In a real-world application of this nature there would be
quite a few more. Remember this tutorial is only getting you started with dBASE SE.
Select the "Reports" menu, and select "Supplier Information". This is a listing of information about each supplier. You could print this if you wanted, but go ahead and close the preview form.
Go back to "Reports" menu and select "Inventory Summary". This report is a bit more complex, as it has some totals and such.
The next item on the "Reports" menu is "Customer Statements". When you select this item you will see adialog form pops up. This forms is asking the user for some parameters that will be used for printing the report.
The sample data used for the tutorial contains invoices for 12-01-1997 through 12-31-1997. For convenience this date range is set as the defaults in the dialog form. Normally you would not use default data that are so long ago, but then normally you would not use data that is so old.
Enter 12/22/1997 for the Start Date and leave the End Date at 12/31/1997. Click OK. Use the Next Page buttons and skip forward to page 5. Notice that this report prints two of Josie Chan's five invoiced orders. Close the report viewer.
Run this report again. This time leave the Start Date at 12/01/1997 and change the End Date to 12/22/1997, then click OK. This time Josie Chan's Statement begins on page 8 and contains four orders that have been invoiced. Close the report viewer.
One more time, go back to "Reports" and this time select "Customer Labels". These are labels as they are generated by the label wizard.
That's all there really is to this application.
Select "File" and "Exit". Notice that if you left any forms open that they are closed for you.
This is a fairly lengthy project, and there is a lot of reading to do, especially if you follow the advice to read the KnowledgeBase documents as they are suggested.
In order to help out here are some hints that may make the whole thing a bit easier.
Printing the Tutorial
This thing is huge. You can, if you wish, print it out -- but be prepared
for the fact that doing so will use a lot of paper, approximately 170 pages!! There is no simple
way to "print it all in one shot" -- you will need to bring each document
into your browser and print them one at a time.
Stopping And Picking Up Where You Left Off
As this is such a long project, you will need to take breaks. We don't expect
that anyone is going to do the whole project in one sitting.
In addition, some folk may have projects that they are working on and need to switch over to those.
When you stop, note where you were in the tutorial.
Then,when you return to working on the tutorial project restart dBASE SE. If you had it up on screen, you may want to close it down and restart it, just to clear any resources that might have been in use. If you are using BDE Aliases in other projects, you may want to use the "CLOSE DATABASE" command to close any open BDE Aliases.
In the Navigator window, make sure you are pointing to the dBASESEtutorial folder (C:\dBASESEtutorial, or wherever you are working on it).
Adding Code
As you work through the tutorial there will be places where it will
be necessary to add code to your forms or reports.
The code is laid out in the tutorial for you, it has comments embedded in it, and there is some extra commentary (in the tutorial text) outside the code explaining it. You have several options for entering the code:
When working in the form or report designer the "Source Code" window will stay open until you either explicitly close it yourself, or close the form (or report). If you look at the "Windows" menu you should see the form (or report), and the "Source Editor" window listed. You can select it at any time while working on a form (or report) unless, as noted, you close it.
Save Your Work Often
We cannot emphasize enough -- in any design surface, use the <Ctrl>+S key
combination to save your work often. Any time you add code, save the form or
report. Any time you have completed adding a series of controls, save your work;
this can save you a lot of hassle later.
Wizard or Designer?
There are wizards built in to dBASE SE for the various designers. For
our purposes, except when it comes to the mailing labels (in the reports
part of this tutorial), we will only be using the designer.
When you double-click on an "Untitled" icon in the navigator (as instructed throughout the tutorial), in many cases you may be asked if you wish to use the Wizard or the Designer. You should select "Designer" unless instructed otherwise.
You could go into the properties menu of dBASE SE and turn this question off for each of these -- the Properties menu has at least two options normally, one for "Desktop Properties" and one for whatever other tool is open. Select "Desktop" properties, and then find the "application" notebook tab -- select this. On the left are checkboxes for the design surfaces -- uncheck them, and you will not get the query about using the Wizards or the Designers.
This is a preference issue, however -- if you would rather be prompted, leave things as they are and make sure unless told otherwise in the tutorial to select the Designer.
Ok, some of the folk examining this tutorial may feel like they already understand how to create tables, or some other aspect of what is being discussed in the tutorial. For you smarty-pants types, you can copy files that are complete from \dBASESEtutorial\Source or \dBASESEtutorial\Final.
For example, if you feel pretty comfortable with creating tables and indexes, you may want to simply copy all of the .DBF, .MDX and .DBT files from \dBASESETurorial\final into the \dBASESEtutorial\Tables directory and then move on to the datamodules section.
Here are the sections and the files by extension (or specific files) that should be copied:
We don't recommend doing it that way, but ...
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.