Developing a Database Application:
The dB2K Tutorial
Ken Mayer, Senior SQA Engineer, dBASE Inc.
Michael Nuwer, dB2K user
Last Modified: October 13, 2001
Version 2.2.0
|
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 building the application's executable and deployment. 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 ... (as a developer myself, I tend to be less structured than what is shown here ...).
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, I 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 author -- 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. I 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 to help 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
dB2K. 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 issue but in greater detail.
Getting Started
You need to create a directory off the root of a hard drive on your
computer called "dB2KTutorial". (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 run the file named dB2KTutorial.exe. This is a self extracting archive that will copy a few support files to the dB2KTutorial folder (or whatever folder you choose to work from).
You will notice a number of subfolders are created by the archiver. While creating the Tutorial application we will be working with the following folders.
The first two subfolders will contain the custom classes and the tables used in the tutorial. The Deploy folder will be used for deploying the application when it it is finished.
In addition to these three folders, there is a folder named "Source" and one named "Final". The Source folder contains the 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. If you run these files or open them in the designers, you may have problems related to the Source Aliases. You should use the Source Editor to view these files.
The Folder named "Final" contains the compiled object files and other supporting files for the tutorial application. This is 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. If you install the tutorial project to a folder other than \db2ktutorial, you will need to modify the object paths in the tutorial.ini file accordingly.
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 dB2K 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 are commented in this file. 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 dB2K IDE Environment
This project assumes that dQuery/Web loads each time
you start dB2K. 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/Web window, and select the last item from the Pop 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 ... my 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 dB2K Desktop Properties dialog. If dQuery/Web 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, dB2K displays files in all the directories specified in both Look In boxes.
Create a Source Alias
The Custom Classes files that were copied to the Custom folder will
be used later in this project. You will also create, in a later phase,
additional custom classes in a later phase of the project. dB2K makes it easy to
use custom classes with the addition of Source Code Aliasing. This
feature lets you use an alias name rather than a path to call code located
in a Custom Class file (or any other source code file).
To setup a Source Alias for this project, you should bring up the dB2K Desktop Properties dialog again. Select the Source Alias tab. To add a new alias, enter "Tutorial" in the Alias entryfield and set the path to the tutorial's Custom folder (C:\dB2KTutorial\MyCustom). Then click the Add button. The alias pointing to the custom classes now exists and we will use it later in this project.
When you use a source code alias in your programming code it must be preceded and followed by a colon. For example:
set procedure to :MyAlias:MyProgram additive class testformForm of TESTCUSTCFORM from ":MyAlias:MycCust.cfm"
This will let dB2K know where you custom controls and classes are located. The source code alias is used only while you are developing an application with dB2K. After you compile and build your application, the .EXE file ignores all the source code aliases. We will be covering this topic in more detail towards the end of the tutorial project.
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 dB2K 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 dB2K 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 I would call a "complete" application, and I 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 dB2K.
I 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 I 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 \dB2KTutorial\Final. This version of the tutorial application is a "complete" runtime version 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.
Note: This application assumes that the program is installed in \db2ktutorial\final\. If you install the program to another folder, you must open tutorial.ini with a text editor and modify the Object Path. Look for the following settings and make whatever change that is needed to the paths.[ObjectPath] objpath0="\db2ktutorial\Final\Objects" objpath1="\db2ktutorial\Final\Support"Note: when this application runs it will create a new database to ensure that the correct alias is being used. The alias name is "dB2KTutFinal". When we actually build the application we will use a database alias named "dB2KTutorial". This is so that you can have both versions -- the "final" version and the one you build while walking through the tutorial project. You may want to delete the "dB2KTutFinal" alias when you have competed the tutorial project.
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.
You start the application by running Tutorial.exe. Open Windows Explorer, navigate to c:\dB2KTutorial\Final, and double click this file.
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).
Open the Customers Form
Select "Open"
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 dB2K.
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 dialog 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 data is 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 ...
Done With the Walk Through
Seriously ... 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.
I 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 dB2K. 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 dB2KTutorial project (C:\dB2KTutorial, or wherever you are working on it).
Also make sure you run the Configure.prg program before you do anything else -- it opens some custom control files that are necessary for the form and report design sections of the tutorial project.
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
I 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 dB2K 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 dB2K 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 \db2kTutorial\Source or \db2kTutorial\Final\Tables.
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 \db2kTurorial\final\tables into the \db2kTutorial\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:
I don't recommend doing it that way, but ...
The Legal Stuff: This document is part of the dB2K Tutorial created by Ken Mayer. This material is copyright © 2001, by Ken Mayer. dB2K 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 author, who retains all rights to the document.