Seattle Magic Users Group Newsletter
July 7, 1996

Conjurings is printed and edited by Heidi Schuppenhauer of Trillium Custom Software, Inc.
Ads and other material are always welcomed.

INSIDE

Next Meeting: July 10, 1996

Next Meeting will be hosted by WORLDWIDE DISTRIBUTORS. See directions below, or call Dick Johnson at (206) 872-8746 for more details.

Magic With Style Class

By Heidi Schuppenhauer

The first Magic With Style class comes to an end this week, with 7 students graduating the intensive 8 week course.

The idea for the class grew out of a need some people have felt to do more in depth Magic training. As it turned out, most of the people who signed up for the class had little or no programming experience of any kind. The longer class time, with more time for homework, seemed to work out well: the students got a good grasp of Magic essentials.

MSE in California is working on getting Magic to be part of college curricula throughout the country. Magic will be offered as part of a database course at one of the UC campuses, and they are also talking to the UW.

Beyond Btrieve

One of Magic's strengths is that you can design programs that run on many platforms, using many different types of databases.

However, before you start designing a program to run on a different platform, you should keep some details in mind. While Magic tries to insulate you from the database details, not all databases are alike. Most of us are used to Btrieve, which for some reason doesn't get the respect it deserves: Btrieve is actually one of the more powerful and robust databases on the market. In this article I will talk about some of the limitations you can expect on other database systems.

I'm picking on RMS partially because I have good documentation of it. Also it is the standard DBMS for the VAX system, is considered one of the more robust DBMSs for building a major system, and comes with Magic for VAX. However, the limitations listed here are found in other DBMSs as well.

For those of you who intend to stick with Btrieve for awhile, this should help you appreciate what a nice tool it really is.

Note: we are not dealing with the SQL databases here at all. Btrieve is essentially a ISAM database where you handle the relational capabilities, and so is RMS, RDB, AS400. None of these translate well into SQL: you can run your Btrieve program on an SQL DBMS, but you probably won't get good performance.

Some of the differences you can expect in working with other databases:

Two-Way Keys
Btrieve can automatically do a descending or ascending search for each key. This is not true of some other systems. If the DBMS does not automatically do descending keys, then the following things are different:

1. You cannot specify Descending order in Task Control, Locate Order or Range order, or when you do a Link.

2. You can scroll backward only to your starting point or back through the previous 500 records.

3. You cannot go directly to the end of the file (End-End not supported).

4. After the Locate operation, the located record is placed at the top of the screen (not in the middle as with Two-Way keys).

5. If you define the key as Two-Way in the Key Properties dialog, Magic will create two indexes, Descending and Ascending, which will slow down adds and deletes.

Primary Key Modification
Some DBMSs do not allow the primary key to be modified once a record is written (RMS is one). Since the primary key is usually the one used as a Foreign key (your link field, or the one used in other records, such as Customer#), it probably isn't a good idea to allow the user to modify this key anyway. However, if you want your application to be portable across DBMSs, you will need to be careful that your first key is truly non-modifiable.

Mixed Ascending/Descending Keys
Some DBMSs do not support keys with mixed Ascending or Descending segments. For instance, in RMS you cannot have a key like:

CUST#     Ascending
Order Date    Descending
Status    Ascending

In this case, if you really needed Order Date in descending order, you would need to create an extra field called Order Date Inverted, where the value is 99999999 minus Order Date

Multi-Segment Keys
There are some DBMSs that do not support keys where there is space between the segments (RMS is NOT one of them). If you want to create a multi-segment key, you must re-create the field multiple times: for instance:

1. Cust# 1
2. Order Date 1
3. Cust# 2
4. Customer Name
5. Order Date 2

Where the keys are:

By Cust#
1. Cust# 1

By Order Date, Cust#
2. Order Date 1
3. Cust# 2

By Customer Name, Order Date
4. Customer Name
5. Order Date 2

Whenever you change Order Date, you must change it in both places. This kind of key isn't common, but I mention it because you will see it in, among others, Real World Accounting.   

Database Size
DBRECS and DBSIZE do not work on RMS, because it has no record count function.

Sorting
If you use RMS for your temporary Sort files, then the limitations mentioned above also apply to your Sorts: (i.e. you can't have mixed ascending and descending keys).

Alternate Collating Sequence
RMS uses the National Character Set, not the ASCII character set, so if you use an alternate collating sequence (to do non-case-sensitive searches, for instance, as mentioned in another newsletter), then you can't use it. There is a way of setting up an NCS collating sequence, but I've not used it.

Speed and Programming on a PC
Indirectly related to the issue of DBMSs on other platforms is the issue of which platform to program ON. You might assume that a VAX or AS400 is faster and more powerful than your humble PC. And you are probably right.

But my own experience in working on such platforms at various companies is that while they may be fast, they are also often servicing lots of users, so the actual response time may be worse than that 286 in your basement.

If you are programming in such an environment, sometimes it makes more sense to do your programming on your own PC (if there are no other programmers involved) or on a small LAN (if you are sharing a CTL). At one site the programmers were very impressed when I took their AS400 data, copied it to my C: drive using an export/import off the file dictionary, and kept on working while most of their computers were at a near standstill during the slow part of the day.

This is a very powerful feature of Magic: if you keep track of the differences in platforms (such as the differences between RMS and Btrieve) you can switch between programming on one platform and another as needed.

To SQL or Not to SQL

In some ways, the debate between SQL and ISAM (flat files like Btrieve, and RMS) reminds me of the debate between DOS and Windows a few years back. DOS was (and is) faster, easier to read, and sometimes easier to use: but GUI is, face it, prettier. I can't code as fast in Magic 6.0, but at a gut level, I like working with 3-dimensional widgets and fonts. And, human beings being the right-brained people they are, the sexy product usually wins.

Right now, much of the programming debate is centering on SQL design and programming business rules into the underlying databases. Most of us who have been using Magic for awhile find it pretty easy to keep relational integrity and design complex table structures, so we may or may not be interested in putting forth the time and money required to buy and learn an SQL system. Like GUI, SQL can buy you some nice features, but they come at a cost: of more money for the DBMS itself, of higher overhead for the programs, and of programming time.

Still, we do what the customer wants ... what are the tradeoffs, really, between an ISAM and SQL system? I came across a nice comparison in one of the Magic manuals I thought I would share with you.

Transactions
SQL always uses transactions. In ISAM, they are optional or not available (depending on the DBMS).

Journaling
SQL always has journaling available to achieve full recovery. In ISAM, it is sometimes available, but is more complicated.

Query Optimizers
SQL has Smart Algorithms to achieve good response to complex queries. With ISAM, it is up to the programmer. SQL has a lot of overhead for simple queries, whereas ISAM has very little: ISAM can be very efficient in accessing a single, indexed record.

Memory
SQL requires much more memory per user (2 MG for RDB). ISAM does not require much.

Multiple Record Updates
SQL is very efficient at manipulating many records at a time (Global updates, creating new indexes, sorting). ISAM manipulates one record at a time, and has no built-in sort capabilities.

Distributed Processing
SQL provides several viable options for distributed processing, including cross-platform distribution.

External tools
SQL is open to external tools like spreadsheets, graphical and statistical packages. ISAM data must be extracted into a flat file to be available to other tools.

Client Server
SQL is the de facto standard in open Client Server. With ISAM databases, Client Server processing can be achieved by using Magic Client/Server.

Cost
SQL databases can cost a LOT: Oracle can run into the tens of thousands of dollars. ISAM databases tend to be cheap or free.

Multi-User Locking
SQL uses a complex locking mechanism, that can be difficult to debug when there are problems. ISAM databases have simple locking mechanism.

Maintenance
SQL database systems require a qualified DBA at the customer site to install and maintain the databases. For ISAM databases, having a DBA is less important, although it still requires a knowledge of the tool to achieve good response in production.

In general, if you are creating a smaller application for a site with no DBA, ISAM is still a great way to go: you can create a robust application with minimal cost that will run on minimal equipment. If you are creating a larger application for a site where money is not a primary issue, good maintenance people are around, the hardware and network are fast, and there is a desire to use 3rd party tools, you might want to look into SQL.

Magic Engine Quiz

So you think you know how Magic works? Test your skill with the following quiz!

1. The files in the DB Files table are opened just after Task Prefix executes ( T F )

2. If you have a SORT defined, Magic will create an external index for every record in the file (T/F).

3. The Range expressions in Record Main are evaluated after those in Task Control. (T/F)

4. In Task Prefix, you can't access any of the data on any records. (T/F)

5. A SORT will create a temporary file, which Magic will automatically Delete.

6. If you are in the middle of a detail screen, and press ESCAPE, the rest of Record Main is not executed.

7. If you have a Batch Task in Query mode, you can't update the record.

8. Suppose you have a GUI slider called Temperature Fahrenheit. You have another field, Temperature Celsius, that uses an INIT to convert the data in Temperature Fahrenheit. When you move the slider, when will Temperature Celsius get updated?
(A) as soon as you leave Temperature Fahrenheit. (B) When the cursor moves to Temperature Celsius. C) As you move the slide.

9. Suppose you have a field, Hold Cust# which is used to LINK WRITE into your customer file. In Record Suffix you blank out Hold Cust# in preparation for the next go-around. What happens?

10. A LINK VALIDATE works just like a LINK QUERY, if you add a return code.

How to grade your answers

10 Correct: Congratulations! You are our new Grand Sorcerer.

7-9 Correct: You are a wizard. Come to the user's group meeting and share your knowledge!

3-6 Correct: You still can learn something from the rest of us mortals. Come join us on Wednesday and learn more!

0-2 Correct: Come on Wednesday anyway and share the pizza!

Quiz Answers

1. False. The files are opened BEFORE Task Prefix (which is why you can't delete any of those files in that task).

2. False. A sort file is created only for the records in that Data View (so if you have a good Range that selects only a few records, the sort will go fast).

3. False. The Range in Record Main happens first, and if it is a good enough range that it eliminates most of the records, then the sequential Range that happens in Task Control will happen pretty fast.

4. True. The only data you can access is that passed in via parameters: there is no record in the record buffer, and INITs haven't happened yet either.

5. Mostly True. A temporary file is created, and it will be deleted if the task ends normally. Files with names like _DFEWEER that appear in your directory are aborted SORT files, and you should clean them out occasionally.

6. False. The rest of Record Main is executed, in FAST mode.

7. Trick question! In Version 5.0, you could in fact update a record that was open in Query mode. Version 5.5 and after changed this.

8. (C). Sliders, like links and inits, happen non-procedurally.

9. Magic recomputes the LINK in record suffix, linking to a blank record. As it is a LINK WRITE, a blank customer record gets created. Since the LINKED record only gets written once, at the end of record suffix, the users changes to the linked record will be lost.

10. True. So why bother with a LINK VALIDATE, one asks? That question is beyond our resident Sorcerer ...

Map To WORLDWIDE DISTRIBUTORS

{Original map not included in WWW version}

For information about the Seattle Magic Users Group, please send snail mail requests to:
Conjurings C/O Trillium Custom Software, Inc.
PO BOX 609
Lake Stevens, WA 98258

Send Questions or Comments about this website to our webmaster
© 2000-04 Trillium Custom Software Incorporated. All Rights Reserved. Terms of Use.