The DataBase engine is actually a group of functions built into the HP 100LX/200LX that drives the PhoneBook, Appointment Book /To-Do List, NoteTaker, and World Time applications. HP developed each of these different applications by modifying and extending the basic DataBase engine in different ways. HP even made it possible for users to develop their own DataBase application, and the thing that makes this possible is the clever use of a "Fourth Generation Programming Language."
Fourth Generation Languages (4GLs)
A 4GL lets you specify what you want the computer to do and then the 4GL figures out how to do it for you. A good example of a 4GL is Lotus 1-2-3 in which you tell the computer, for instance, "Here's a list of numbers. Add them up and show me the answer." You type in a function, @SUM( A1..B21), and the computer figures out how to find the numbers, add them, and show you the answer. In a third generation language, such as Pascal or C/C++, you'd have to write a lot of code to tell the computer how to find the numbers, how to add them, and how to display the answer.
Likewise, if you wanted to develop a DataBase program, you could use a third generation language such as TIPI, or C/C++ and eventually get the job done. With the 4GL language built into the DataBase engine, you merely fill out a form on the screen by picking options from a menu. You can add fields for Text, Numbers, Group Boxes and Option Buttons, a Category and a Note field merely by picking these field types from a dialog box. You can then move and re-size the fields on the screen with the arrow keys. When you're satisfied with the appearance of the "form", you press (F10) [Done] and the 4GL takes over and creates a custom DataBase program for you.
To get a formatted printout of the data, you can use another part of the 4GL called "Smart Clips" in which you "paint a picture" of the output on the screen. The DataBase 4GL then writes all the code to produce the printed report. It's all very slick and sophisticated.
Limitations of the Database engine
For all its sophistication, the DataBase engine does have its limitations. For one thing, it doesn't have any number crunching capability. Sure, you can use a combination of Smart Clips to write the information to a file, import the file into Lotus 1-2-3 and, using the parsing feature of the spreadsheet, isolate the numbers and add them up. The article on page 44 of the March/April 1995 issue of The HP Palmtop Paper shows how to do this.
A more pronounced limitation of the DataBase engine is its lack of any relational capabilities. The DataBase engine in the HP Palmtop uses a "flat file" approach to storing and retrieving data. Other DataBase engines such as Borland's Paradox and dBASE or Access, from Microsoft, use a relational approach to handling data. In a relational database, for instance, you can designate one or more fields as "hot-links" to records in another database. The greatest advantage to using a relational model for a database is that you can save disk space and reduce the time needed to edit or modify the data. The greatest disadvantage to using a relational database is the learning curve. Creating databases that incorporate several data files and getting them to work is a technique best left to professionals (and even they don't always get it right.) Trying to write a User's Guide for such a relational database would take a book at least as big as the current Palmtop's User's Guide and a support staff that would rival that of Borland, Inc.
Creating a custom Database for products and vendors
To illustrate the advantage of using relational database techniques, I'll take a real world example.
In the course of writing a forthcoming book on the HP Palmtops, I began to develop a database of all commercial, third party products for these machines. Preliminary research turned up almost 300 such products available from 160 different vendors. I started out using a database that had only one data file (a "flat-file" database). Each record in the PRODUCTS.GDB (ON DISK icon) database file contained fields that gave the name, address and telephone number of the vendor. However some vendors had 18 or more different products. I realized that I was duplicating a lot of information in 18 different places. That might be acceptable for a "one-time" use of the database, but what if the database was to be used for the next couple of years. And suppose a vendor moved or changed telephone numbers? Could I be sure that I had updated all 18 entries for the address and/or phone number?
Breaking up is hard to do
Instead, I chose to break the single file into two data files: one a DataBase file called PRODUCTS .GDB and the other a PhoneBook file called VENDORS.PDB (ON DISK icon). The latter file contained all the information about the vendor. The full name of the vendor went in the Company field while the Name field contained an abbreviation for the vendor.
In the PRODUCTS.GDB file (see example at bottom of page) I put the same abbreviation for the Vendor in the Cont(act) or Alt(Arnaud) fields.
This arrangement was fine, as far as it went. The net saving was about 14,000 bytes of disk space. However, it meant that each time I wanted to search for all the information about a product I had to open the PRODUCTS file and the PhoneBook file and perform two separate searches. A relational database program, such as dBASE IV would let me do this by creating a "Query." For example, with dBASE IV, I could set up a Query so that it would find all the products sold by ACE Technologies or show the FAX number of ACE given just one of the products from ACE. dBASE would open both files at once and "relate" one file to another before performing the search.
Emulating a relational database on the Palmtop using two macros
What I needed was a relational database program like dBASE III+ that would let me query one file using information in another file. I'd experimented with dBASE on the HP 100LX and found that it worked, but it soaked up a lot of disk space and had to be used in 80 X 25 mode. I wondered if dBASE's Query operation could be emulated using the HP DataBase. After a little experimentation, here's what I came up with.
The following two macros let me flip from my Products DataBase file to the Vendors PhoneBook file with a minimum of fuss and bother. I could pick a product, use it to find the information about the vendor and then hop back to the Products DataBase and find all the products offered by that Vendor.
MACRO 1 (Assigned to Fn+F5, to go from PRODUCTS to VENDORS)
{Esc}{Enter}{Alt+O}{Ctrl+C}{F10} {Phone}{Menu}fovendors.pdb{Enter} {Home}{F4}{Ctrl+V}{Alt+N}
What the macro is telling the Palmtop to do: After closing and opening the Data Item screen, hop to the Cont(act) field and Copy its contents to the clipboard. Activate the PhoneBook application and open VENDORS.PDB. Go to the first item in the list (Home) and activate the Find operation and paste the clipboard into the dialog box and search for the first instance of the field.
MACRO 2 (Assigned to Fn+F6, to go from VENDORS to PRODUCTS and find all the products from a given Vendor.)
{Esc}{Enter}{Alt+M}{Ctrl+C}{F10} {DataBase}{Menu}foproducts.gdb {Enter}{Home}{F6}{Alt+D}{F2}(Cont#"")| (Alt#""){Left}{Left}{Ctrl+V}{Home} {Right}{Right}{Right}{Right}{Right} {Right} {Right}{Ctrl+V}{F10}{F10}{F10}
What the macro is telling the Palmtop to do: After making sure the Data Item screen in the PhoneBook is active {Esc}{Enter}, hop to the Name field {Alt+M} and copy the field to the clipboard {Ctrl+C}. Close Phone Item screen and open the DataBase application {F10} {Database}. Open PRODUCTS.GDB {Menu}foproducts .gdb{Enter} and go to the first item in the list {Home}. Open the View Subset dialog box {F6}, Define a new subset {Alt+D}, and then specify that you want a General Subset Entry {F2}. The macro enters the string (Cont#"") |(Alt#"") and then uses the cursor movement commands {Left}{Left} {Ctrl+V} to paste the contents of the clipboard inside the two double quotes. The "#" sign is the SSL operator that means "Test if a field contains a string" In this case the SSL command tests if the string (e.g. "ACE") is contained in either the Cont or Alt fields. If the string is found, add this record to the subset {Right}{Right}{Right} {Right} {Ctrl+V}. The final repeated {F10} commands start the search, create an Unnamed subset and show the subset on the screen.
Individual item data screen for Products Database showing information about a product named "Real Search." The Vendor abbreviation "BIDS" is in the Contact field.
Further refinements
The trick of using the PhoneBook and custom DataBase in tandem will work with any two (or more) DataBases. In other words you don't need to make one of them a PhoneBook. Another trick: you don't need to open it in the PhoneBook application to get at the data. You can load any PhoneBook file into the DataBase application (or any custom DataBase file into the PhoneBook as long as you rename the file so that it has the .PDB file extension).
I chose to follow this approach so that I could keep my main PhoneBook open and to simplify matters. I renamed VENDORS.PDB to VENDORS.GDB just to make sure that I wouldn't accidentally open the VENDORS.PDB file in both the DataBase and PhoneBook at the same time. (Doing so could lead to loss of data.) Then I rewrote the macros above on the assumption that I'd have one or the other (PRODUCTS or VENDORS) loaded in DataBase. This let me eliminate the commands {Database} and {Phone} in the above macros. Everything else remained the same. At this point the biggest task was keeping the macros straight. As a memory aid, I put the following reminder in the note field of the first record in the PRODUCTS.GDB file: "To GO TO VENDORS, pick a product and press (Fn)-(F5). Likewise in the VENDORS.GDB file, I made the note field in the first record read: "To GO TO PRODUCTS, pick a Company and press (Fn)+- (F6)".
SSL -- the Subset Specification Language
The Subset feature is the strong-suit of the DataBase engine. It lets you select and view smaller portions of one of the DataBase-related applications. From a DataBase application press (F6) (F2) to define your Subset. From the Define Subset screen press (F2) again to access General Subset Entry screen, which lets you use the Subset Specification Language (SSL) to create subset criteria as command statements that are too complex for the Define Subset screen. To learn more about Subsets and SSL, refer to your HP 100/200LX User's Guide. Once you've read through the User's Guide and have done some experimenting, you should be able to get by with the on line help in the Palmtop.
In the above macro examples I used the "#" "relational" operator along with the "|" "Logical" OR operator. The DataBase SSL has a full range of such operators including NOT, AND, Greater Than, Less Than, Equal To, Not Equal. If you're not familiar with the use of relational and logical operators, you can stop short of SSL and fill in the details of a subset using the first Subset Entry screen. Once you've created and saved a Subset, simply press (F6) from the main DataBase screen, select the desired Subset and press (F10). The Subset feature will take over and perform the search for you.
I used the SSL rather than the simpler method to create the Subset because I wanted to do an "OR" search based on the information in the Cont and Alt fields of my Product DataBase. If, for example, I was looking for the vendor information for ACE, I'd be looking for ACE in either of these fields. If I had put ACE in both the Cont(act) and Alt(Arnaud) fields in the Define Subset Screen, I'd come up with an empty subset because the DataBase interprets this as an "AND" search (Cont#"")&(Alt#"") rather than an "OR" search (Cont#"")|(Alt#""). I needed the SSL to specify the "OR" search.
The Subset Specification Language is available not only in the DataBase Application, but also in PhoneBook, NoteTaker, and WorldTime.
Pushing the Envelope
For those of you who enjoy exploring all the nooks and crannies of the HP Palmtop, the DataBase engine contains several "undocumented features." Dave Smith is someone who has let curiosity get the better of him and has done a thorough job of tracking down what others have hinted at. You can view his discoveries in the file DBFEAT.ZIP (ON DISK icon). To paraphrase my standard instructional line: "It is left as an exercise for the reader to find a use for these undocumented features."
Until next time, Happy Programming.