1  UDFLIB

UDF Library

Creating Complex Indexes
James E. Pierce

One of the major functional advantages of dBASE IV is the ability to index
on complex expressions.  This capability is enhanced by the ability to use
UDFs in this expression, which means that the indexing expression can be of
virtually infinite complexity.  This article discusses a simple example of
indexing on a UDF, which involves indexing document section numbers that
are in technical outline format (for instance, 3.4.5, 10.2.3.1, and so
on).   

Before we talk about my specific example, we must note the inherent dangers
involved in indexing on a UDF.  First, if DBTRAP is set on, dBASE IV will
not let the user index on a UDF.  The reason for this protection is that if
the system tries to open an index file that uses a UDF and it is unable to
find the UDF, it will fail.  If the index file is the production .MDX file,
dBASE IV will attempt to open it as soon as the associated .DBF file is put
into use.  This means automatic problems.  The solution is to make sure
that the UDF is always available (a good approach is to put it in a .PRG
file with the same name as the UDF), SET DBTRAP OFF and go ahead and issue
the INDEX ON command.      

In this example, indexing on a field that contains document section numbers
is made difficult by the fact that the multiple periods in the section
number forces us to define them as character fields.  When we try to sort
on the section numbers, they get compared one character at a time, which
means that the 1 from section number "10.2.3.4" gets compared to the 3 from
section number "3.4.5" and section number "10.2.3.4" ends up coming first. 
This problem can be solved by stuffing spaces in front of each integer
substring.  In other words, "3.4.5" ends up looking like  " 3. 4. 5" and
"10.2.3.4" looks like "10. 2. 3. 4", that way the leading space comes
before the leading 1.  This examples assumes a maximum width of 2 for each
integer substring.  Using the UDF called 'expand', for which the listing is
included in this article, one can specify the desired width of the integer
substrings.  Indexing the database becomes a simple matter of issuing the
commands:

SET DBTRAP OFF
INDEX ON Expand((<Field Name>), <Width>) TAG <Tag Name>

where <Field Name> is the name of the section number field,
      <Width>      is the maximum width of the integer substrings.
      <Tag Name>   is the desired tag name for the index, (see
                   the INDEX command in the dBASE IV Language Reference.)

Working Days

For those of you who need to figure out the future date that falls a number
of working days past a given date, I have created WDIF(), the Working Days
In Future function.  Give it a date and tell it how many working days from
that date you want to go, and it returns the date that it would fall on. 

Create a very simple database and it will even figure in holidays.  The
structure of the necessary database is as follows: 

HOLIDAYS.DBF
Field name  Field type  MDX?
HOLIDATE      Date       Y

If you don't care about holidays, WDIF2() is much shorter and uses the same
basic algorithm.  The returned date for both functions always falls on a
working day.   

WDIF() assumes that your holidays database is open in some work area and is
in order by an index tag on the HOLIDATE field.  It also assumes that there
are no duplicate entries and that none of the holidays in the database fall
on a weekend date.  These two assumptions about your data are vital to the
accuracy of the function.   

A simple method for insuring there are no weekends in your holidays
database is: 

USE Holidays
DELETE FOR DOW( Holidate ) = 7 .OR. DOW( Holidate ) = 1
PACK

Example of usage (assuming you get the days from 12/24/91 through 1/2/92 as
holidays:

? WDIF({12/20/91}, 30)
02/12/92
? WDIF({12/20/91}, 3)
01/06/92

Function: Expand()
FUNCTION Expand
  * Author: Jim Pierce
  PARAMETER sect_num, width 
  PRIVATE result, dot_pos             &&  Local variables.
  result = ""                         &&  Resultant string.
  sect_num = LTRIM(RTRIM(sect_num))   &&  strip off leading and ending spaces
  dot_pos = AT(".", sect_num)         &&  position of next dot in string
  DO WHILE (dot_pos = 1)              &&  strip off leading "."
    sect_num = SUBSTR(sect_num, 2)
    sect_num = LTRIM(sect_num)
    dot_pos = AT(".", sect_num)
  ENDDO
       
  DO WHILE (dot_pos > 0)              &&  expand each section of the section number.
    result = result + TRANSFORM(SUBSTR(sect_num, 1, dot_pos - 1), ;
      "@j " + REPLICATE("x", width))
    sect_num = LTRIM(SUBSTR(sect_num, dot_pos + 1))
    dot_pos = AT(".", sect_num) &&  check for next section
    
    DO WHILE (dot_pos = 1)    &&  strip off leading "."
      sect_num = SUBSTR(sect_num, 2)
      sect_num = LTRIM(RTRIM(sect_num))
      dot_pos = AT(".", sect_num)
    ENDDO
    IF (LEN(sect_num) > 0)  &&  place period between sections
      result = result + "."
    ENDIF
  ENDDO
  IF (LEN(sect_num) > 0)    &&  expand last section
    result = result + TRANSFORM(sect_num, "@j " + REPLICATE("x", width))
  ENDIF
RETURN result
FUNCTION WDIF
  PARAMETER startdate, wdays
  PRIVATE weeks, x, xtradays, hdays, rtndate, nearwas, aliaswas 
  PRIVATE mdate
  STORE 0 TO weeks, x, hdays, xtradays
  STORE {} TO rtndate, mdate
  STORE "" TO nearwas, aliaswas

  IF wdays = 0
    RETURN startdate
  ENDIF

  * Rough guestimate of future date within a week
  weeks = INT(wdays / 5)
  rtndate = startdate + (weeks * 7)
  * Left over number of days from integer division above
  xtradays = MOD(wdays, 5)

  * Store current alias name and select Holidays database (this 
  * assumes it's already open and in Holidate order).
  aliaswas = ALIAS()
  SELECT Holidays
  * Count holidays that fall within date range
  nearwas = SET("NEAR")
  SET NEAR ON
  SEEK startdate
  SET NEAR OFF
  * Don't count starting day if it's in Holidays database.
  IF startdate = Holidate
    SKIP
  ENDIF
  SCAN WHILE rtndate >= Holidate 
    hdays = hdays + 1
  ENDSCAN

  * Add holidays to "left over" days from original guestimate
  xtradays = xtradays + hdays
  * Add extra days one day at a time to the original guestimate, 
  * skipping over holidays and weekends.
  DO WHILE xtradays > 0
    rtndate = rtndate + 1
    IF SEEK(rtndate) .OR. ;
      DOW(rtndate) = 7 .OR. DOW(rtndate) = 1
      LOOP
    ENDIF
    xtradays = xtradays - 1
  ENDDO

  * If return date falls on Saturday or Sunday, "re-wind" to Friday.
  rtndate = rtndate - ;
    IIF(DOW(rtndate) = 7, 1, IIF(DOW(rtndate) = 1, 2, 0))

  * If another database was origally in use, make it the active 
  * database again.
  IF "" # aliaswas
    SELECT (aliaswas)
  ENDIF
  * Set NEAR back to what it was orginally.
  SET NEAR &nearwas
RETURN rtndate
* End of WDIF()

FUNCTION WDIF2
  PARAMETER startdate, wdays
  * by Martin Leon
  PRIVATE weeks, xtradays, rtndate
  STORE 0 TO weeks, xtradays
  STORE {} TO rtndate

  IF wdays = 0
    RETURN startdate
  ENDIF

  * Rough guestimate of future date within a week
  weeks = INT(wdays / 5)
  rtndate = startdate + (weeks * 7)

  * Left over number of days
  xtradays = MOD(wdays, 5)

  * Add a extra days one day at a time to the original guestimate,
  * skipping over weekends.

  DO WHILE xtradays > 0
    rtndate = rtndate + 1
    IF DOW(rtndate) = 7 .OR. DOW(rtndate) = 1
      LOOP
    ENDIF
    xtradays = xtradays - 1
  ENDDO

  * If return date falls on Saturday or Sunday, "re-wind" to Friday.
  rtndate = rtndate - IIF(DOW(rtndate) = 7, 1, IIF(DOW(rtndate) = 1, 2, 0))

RETURN rtndate
* End of WDIF2()


2  Q&A

Q & A
CONFIRMation Class

Is there anyway for dBASE IV to force a READ to wait for the Return key to
be pressed before moving on to the next field?

There's a SET command for nearly everything.  The one you need here is SET
CONFIRM ON which you would issue before your READ command.  This tells
dBASE IV to wait for the Return key on all GETs before going to the next
GET.  This command is not new to dBASE IV and this question seems as
long-standing as the functionality itself (always worth mentioning
though).  Maybe they should've called it:

  SET PRESSRETURNTOGETTOTHENEXTFIELD ON

Nah, might be a little too lengthy.

How to Spot a Maintenance Release

I help install software at a large company.  We have various versions of
dBASE IV and maintenance releases all over the place.  Can you tell me how
to determine the maintenance release number of the copy of dBASE IV 1.1
that each person is using? 

The following command from the dot prompt:

? VERSION(0)

display the "build number" which is the number after the pair of x's.  
Key Trapping in the Memo Editor

I was wondering if it is possible to get an ON KEY LABEL trap to work from
within the memo editor.

No.  Once inside the memo editor, you lose all program control, including
ON KEY LABELs.  Program control is not regained until you exit the memo
editor.

Ton o' Files

We have been creating small database files like mad and now have about 400
files in the \DBASE directory.  We discovered just today that not all of
the .DBF files show up in the picklist when trying to add to an existing
catalog.  Any ideas why this occurs? 

Both the catalog and the popup that lists the files that you can add to the
catalog have a limit of about 200 files.  The only thing you can do is
re-organize, making sub-directories and separating the files into logical
groupings, and making separate catalogs for each sub-directory.

Picture This

I have a character field where I would like to limit the input to either a
"T" or an "X".  Any suggestions ?

If you want to limit the choices to "T"or "X", use a multiple choice
picture clause:

mVar = "X" 
@ 5,5 SAY "Enter Value:" GET mVar PICTURE "@M T,X" 
READ

Reconnecting an .MDX File

I accidentally copied my database to another directory without its
associated .MDX file.  I USEd the file and dBASE IV prompted with an error
message with three options, one of which was PROCEED.  When I later copied
the .MDX file to the directory where the .DBF file was, I discovered that
none of my .MDX tags existed anymore (and there were a lot of them).  Must
I rebuild the .MDX file again?

What will be necessary is to USE <filename> INDEX <.MDX filename>.  This
will cause dBASE IV to update the header of the .DBF to contain the
information that a PRODUCTION .MDX is associated with the .DBF file.  This
explicit way of opening a file is only necessary one time.  The next time,
just USE the file as you normally would and your .MDX tags will again be
available.

From Catalog to Directory

As I work with dBASE IV I find that I have files in several directories. 
Do you have an idea to help me copy all files from a catalog into a new
directory.

This was the subject of a TechNotes article way back in 1989 but you may
have missed it.  While there's no built-in function that does this type of
housekeeping operation a program can accomplish the feat.  You give it a
catalog name, a directory name, and a logical indicating whether to delete
files that were copied, and it will look in the specified catalog and copy
all files listed in the FILE NAME field to the specified destination
directory.  For instance:

DO CopyCAT WITH "UNTITLED.CAT", "C:\JUNK\", .F.

This would copy all files listed in the FILE_NAME field in UNTITLED.CAT to
the C:\JUNK\ subdirectory (make sure you put a backslash at the end of the
path specification) and will not delete the files that were copied.

PROCEDURE CopyCAT 
  PARAMETERS cCatName, cDestDir, lDelAfter 
  PRIVATE cOverwrite, cFilename 
  SET TALK OFF 
  SET SAFETY OFF 

  cOverwrite = "N" 
  cDestdir = TRIM(UPPER(cDestDir)) 
  USE (cCatName) 
  SCAN
    ?
    cFilename = TRIM(UPPER(File_name))
    IF FILE(File_name)
      cNewname = cDestDir + cFilename

      IF FILE(cNewName)
        ACCEPT cNewName + " already exists, overwrite? " ;
          TO cOverwrite
        IF LEFT(UPPER(cOverwrite), 1) = "Y"
          COPY FILE (File_name) TO (cNewname)
        ELSE
          LOOP
        ENDIF
      ELSE
        COPY FILE (File_name) TO (cNewname)
      ENDIF

      IF .NOT. FILE(cNewName)
        ? "Copy of " + cFilename + " to " + ;
          cNewName + "failed."
      ELSE
        ? cFilename + " copied to " + cNewName
        IF lDelAfter
          DELETE FILE (File_name)
          ? cFilename + " deleted."
        ENDIF
      ENDIF
    ELSE
      ? cFilename + " not found."
    ENDIF
  ENDSCAN

  SET TALK ON
  SET SAFETY ON
RETURN

Show Us Your Errorlevel

Many DOS commands and utilities set an ERRORLEVEL to indicate success or
failure of an operation.  Is there any way for dBASE IV to determine the
ERRORLEVEL of the most recently run DOS command?

There's not a way built into dBASE IV to do this.  One alternative could be
a .BIN file that calls the DOS program and returns the ERRORLEVEL to dBASE
IV.  An easier solution is to run your program through a batch file that
calls the DOS program and creates a file whose name indicates the
ERRORLEVEL the DOS program returned:

REM ERR2DB4.BAT 
@ECHO OFF 
REM First, delete any existing errorlevel indicator files 
FOR %%P IN (1 2 3 4 5) DO IF EXIST %%P.LVL DEL %%P.LVL
REM Create ERRORLEVEL indicator file. 
ECHO . > 0.LVL 
REM Call the DOS program with parameters if provided by dBASE 
REM (up to 5 parameters in this example) 
CALL %1 %2 %3 %4 %5 %6
REM Create an errorlevel indicator file 
IF ERRORLEVEL 5 
REN 0.LVL 5.LVL 
IF ERRORLEVEL 5 
GOTO DONE 
IF ERRORLEVEL 4 
REN 0.LVL 4.LVL 
IF ERRORLEVEL 4 
GOTO DONE 
IF ERRORLEVEL 3 
REN 0.LVL 3.LVL 
IF ERRORLEVEL 3 
GOTO DONE 
IF ERRORLEVEL 2 
REN 0.LVL 2.LVL 
IF ERRORLEVEL 2 
GOTO DONE 
IF ERRORLEVEL 1 
REN 0.LVL 1.LVL 
IF ERRORLEVEL 1 
GOTO DONE 
:DONE 
REM End of Err2DB4

Note that the maximum errorlevel this example program is set up to handle
right now is 5.  If you expect a higher errorlevel you'll need to make
appropriate modifications.  Also, it's set up to pass a maximum of 5
command line parameters to the program it calls. 

In dBASE IV, you'd use the following code:

RUN Err2DB3 <program> [param1][param2][param3][param4][param5] 
DO CASE
  CASE FILE ("0.LVL")
    *No errolevel
  CASE FILE ("1.LVL")
    *errorlevel 1
  CASE FILE ("2.LVL")
    *errorlevel 2
  CASE FILE ("3.LVL")
    *errorlevel 3
  CASE FILE ("4.LVL")
    *errorlevel 4
  CASE FILE ("5.LVL")
    *errorlevel 5 or greater 
ENDCASE 

Top-Down LISTing

I want to turn the LIST command on its side.  What I mean is that I have
several databases with large records and I want to see the fields listed
one on top of the other instead of next to each other but in a display that
is controlled like LIST.

You could create a report for each database, but the following dBASE IV
code will do this for the current record for whatever database is currently
in use:

SET TALK OFF 
x = 1 
DO WHILE "" # FIELD(x)
  mField = FIELD(x) + SPACE(11 - LEN(FIELD(x))
  ? mField
  ?? &mField
  x = x + 1 
ENDDO 
SET TALK ON

If you want to see your entire database listed out this way, add a SCAN
loop:

SET TALK OFF 
SCAN
  x = 1
  DO WHILE "" # FIELD(x)
    mField = FIELD(x) + SPACE(11 - LEN(FIELD(x)))
    ? mField
    ?? &mField
    x = x + 1
  ENDDO
  ?
ENDSCAN
SET TALK ON

Memo fields will be wrapped to whatever SET MEMOWIDTH is currently set.

Using READ SAVE

I've never seen any usage of READ SAVE.  I'm assuming this would be used in
a program loop where the program asks if all entries are correct and if
not, perform another READ SAVE.  Is this a correct application for READ
SAVE?

Exactly.  READ SAVE is just like READ, except that pending GETs aren't
automatically cleared after READ is terminated.  Look at the following code
segment:

mVar = "Hello everyone" 
@ 5,5 GET mVar 
DO WHILE .T.
  READ SAVE
  IF READKEY() = 270
    EXIT
  ENDIF 
ENDDO 
CLEAR GETS

This would force the user to press Ctrl-End to terminate this READ.  Any
other key would issue READ SAVE again, re-activating the GETs that were
defined before the loop was entered.  Make sure you CLEAR GETS at some
point after your READ SAVE or your GETs will be "carried over" to your next
READ.

Progressive Indexing

When doing a REINDEX, one of my indexes will go from 0 to 100% reindexed
without showing any intermediate amounts reindexed but another will show
progressive changes to the percentage reindexed until it reaches 100%.  Why
are they different?  Is there any way to affect the interval?

The answer to both your questions is that this is determined by an
algorithm that is internal to dBASE IV over which you are given no
control.  There is nothing that can be done to change the increment that
dBASE IV chooses to display. 

Windows Galore

How do I get past the limit of having only 20 windows defined in memory at
one time (as is stated in Language Reference, Page 2-116 under DEFINE
WINDOW)?  I have a database with 30 memo fields and why I try to create a
Format Screen with windows for the memos, it won't let me define more than
20.

You can't actually define more windows, but you can re-use them.  After you
define the first 20, leave the last ten memos defined as markers and save
the format.  Using MODIFY COMMAND, modify the .FMT file (which is a dBASE
command file) and use the GoTo menu to search for the names of the memo
fields that were left defined as markers.  Add the parameter WINDOW wndown
to the @.GET statement for each remaining memo.  The n for the window
assignment name wndown would be a number between 1 and 20.  You are
borrowing from the other windows.  If you wish the windows to automatically
be open, be sure to attach the parameter OPEN WINDOW instead of just WINDOW
to the @.GET statement.  

Exclusively Yours

When running dBASE IV on a network, I have problems when multiple users
enter the Control Center.  I'm able to get workstation #1 running dBASE IV
normally and performing the various activities from the Control Center. 
When I start dBASE IV from a second workstation however, I get the license
screen and then the error message "File in use by another".  I can't access
the Control Center until the first workstation exits the Control Center or
quits from dBASE IV.  What is going on? 

Chances are that the first workstation has EXCLUSIVE set to ON which means
that data files cannot be simultaneously accessed by multiple users.    

When accessing the Control Center, the catalog files (which are a special
kind of data file) must be read in order to display the file lists, file
descriptions, and so on.  If the first workstation has EXCLUSIVE set ON and
enters the Control Center, all other users are locked out of these catalog
files.  Therefore, until the first workstation exits from the Control
Center and releases the "file lock" on the catalog file, all other users
will get this error message and not be able to access the Control Center. 
Setting EXCLUSIVE to OFF will allow multiple users to simultaneously access
the Control Center. 

"How Do You Say That in Query-eze?"

I have a database from which I would like to pull records for a report
involving a particular date range.  I know how to pull all records after a
certain date but I'm not sure how to tell a query to pull all records
between a starting and stopping memory variable.  How is this done?    

Use the following filtering "phrase" with your command to limit your data
file:
   
<fieldname>  >= startdate .and. <datefield>  >= enddate
   
where "startdate" and "enddate" are appropriate date memory variables. 
This filtering phrase can be added to the command line where you call your
report form by issuing:    

REPORT FORM <report> FOR <date field>  >= SDATE .AND. <date field>  >=
EDATE
   
or to a SET FILTER command shown below, and then running the report.
   
SET FILTER TO <date field>  >= SDATE .AND. <date field>  >= EDATE
   
CARRY That Weight

When playing with the CARRY setting, I set a single field to carry at the
dot prompt and dBASE IV 1.1 did as expected.  However when I then issued
the more general: SET CARRY ON to get all my fields to carry, I didn't get
the desired result.  Only the single field specified earlier was "carried"
over and I had wanted to set all the fields of my file to carry.  Issuing a
SET CARRY OFF and then a SET CARRY ON still didn't help.

Issuing the command SET CARRY ON/OFF toggles whether fields previously set
to "carry" will be carried or not.  All fields will be carried by default,
unless particular fields have specifically set to carry.  SET CARRY TO
without any specified fields will clear the carry setting for the AREACODE
field and will allow all fields (the default) to carry when the SET CARRY
ON command is then issued.    

In addition however, there is a temperamental quirk where, in dBASE IV
version 1.1 only, you must SET CARRY TO and then follow immediately with a
SET CARRY OFF in order to clear the fields set to carry. s





 DOTINRT

 See Dot.  See Dot Run.  See
 Dot in RunTime
 Adam L. Menkes
 
 A dot prompt program for the RunTime environment
 
 Developers have often been faced with the problem of being at a clients
 site and needing to make some minor modifications to their program. 
 Unfortunately, the client does not have any version of dBASE IV and needs
 you to create a new index for them or save some memory variables to the
 .MEM file that was accidentally erased. You are hundreds of miles from your
 computer with dBASE IV Developer's Edition and are cursing about having to
 go back, make the changes, and send them back to your client who needs the
 changes yesterday! Even if you had remembered to bring all your dBASE IV
 disks (will the disk size be the same for each client?), do you want to
 install the product to their computer, make the changes, then uninstall it
 a few minutes later? 
 
 With this procedure (Dot.PRG  code on Page 4), you will now have much of
 the functionality of dBASE IV at your fingertips.  While you cannot COMPILE
 programs or CREATE / MODIFY anything (MODIFY STRUCTURE can still be
 performed by a two-step process using COPY STRUCTURE EXTENDED and CREATE
 FROM) or DEBUG your code, you can create INDEXes (and TAGS), VIEWs (FROM
 ENVIRONMENT), APPEND, BROWSE, EDIT, and so on.  You could even develop a
 menu program that allowed your users a choice to EXIT TO DOT PROMPT which
 would then run this program and allow them to exit back to the menu program
 (by pressing F2 or typing ASSIST  for an example of this, you can download
 the file ASSIST3.ZIP from the BBS which contains a dBASE III Plus-like
 interface for dBASE IV and RunTime). 
 
 Some errors that are trapped are not displayed. This is because the
 procedure MACROSUB which parses commands containing macro substitutions
 needs to parse them one at a time. If a command contains more than one
 ampersand (&), it will cause an error when it tries to execute. Therefore,
 the program traps the error, letting it continue parsing until either a)
 there are no more "&" or b) it has attempted 32 substitutions. 
 
 The following are acceptable commands (assuming the variables  adam = "X",
 bob = "Y", x = <any number>, and y = <any number>) : 
 
 . ? &adam/&bob * 5 / &adam* 100 +&bob /&adam + 7
 * Note that spacing between the + - * / and & is irrelevant.
 
 This is also acceptable :
 
 . test = "? MEMORY()"
 . &test
 
 Note that normally, in RunTime, you cannot macro expand an entire command
 line and in fact, this was the reason for this program.  Otherwise, in
 RunTime you could simply have a few lines of code: 
 
 command = SPACE(254)
 @ ROW, COL SAY "." GET command
 READ
 &command 
 
 Although this small amount of code will work in dBASE IV but not in
 RunTime, you will still encounter problems when trying to do macro
 substitution. You cannot "double" substitute:
 
 . command = "&test"
 . &command
 
 as dBASE IV will try to parse the variable &test instead of the contents of
 the variable test  ? MEMORY(). That is why, if you plan to have a
 simulated dot prompt in one of your applications, you should use the
 program code at the end of this article, whether or not the program will be
 run in dBASE IV or RunTime.  
 
 You can not use this program with a FORMAT screen active because the entire
 program revolves around GETting a command and READing it.  With a READ
 always active, it will constantly activate the format screen and try to
 READ the variables.  You should not ACTIVATE WINDOWs. The program can work
 inside a window but the command line may wrap. If the WINDOW is defined too
 small, you may hang the program.  You can however, move the dot prompt to
 another location on the screen (using the user-defined SHIFT command or
 redefining the variable Dot_Row). 
 
 I have written a procedure in order to simulate command line history. The
 default is 20 (regardless of your CONFIG.DB setting) but may be changed
 once in the program with the SET HISTORY TO command. The limit on SET
 HISTORY in dBASE IV is 16,000.  This program is limited by the size of an
 array 1,170 and by available memory. 
 
 Programs may be called from this program using the DO command (as you might
 have guessed).  To leave this program, you may type CANCEL or QUIT (to
 leave completely) or EXIT to return to the calling program (if any). 
 
 You can RELEASE ALL/CLEAR ALL without crashing the program (all necessary
 memory variables are preserved) but if you SAVE memory variables TO a .MEM
 file, you should include the option EXCEPT DOT_* (SAVE TO xxx ALL EXCEPT
 DOT_*). If you forget, Dot_Rest will be saved as well (all others are
 released). 
 
 Implied commands are also accepted. For instance, mvar = 5 does not have to
 be entered as STORE 5 to mvar (mvar is automatically declared PUBLIC); 7
 does not have to be input as GO/GOTO 7). ! (RUN), @, ?, ??, ??? are also
 supported. Most SET commands are honored and the RunTime manual denotes
 those SET commands which are prevented in RunTime. 
 
 You have the ability to add USER-DEFINED COMMANDS (UDCs) to this program
 and some examples have been included to show you how. These are in a file
 called Adam_Def. Basically, in the PROCEDURE User_Defined, add another CASE
 condition such that the KEYWORD is on the right of the equal sign in upper
 case letters (in quotes "") and the commands follow on the next line.
 
 CASE Dot_1st = <KEYWORD>
   <Commands>
 * such as:  DO PROGRAM / PROCEDURE [WITH PARAMETERS]
 
 For example:
 
 CASE Dot_1st = "CLS"
   CLEAR
 
 Also, you can add UDCs (User Defined Commands) outside of this program. 
 The program must be called USER_DEF (.DBO) and needs to be in the format
 specified above.  This hook has been added in order that you may have all
 your UDCs in a separate file so when changes are made the DOT.PRG, your
 programs remain unaffected.  I have done this with the file Adam_Def which
 contains some user-defined commands shown at the end of this article. 
 DOT.DBO (COMMAND.DBO and ADAM_DEF.DBO) are already available on the Borland
 BBS (BORBBS) as COMMAND.ZIP.  Periodically, I may make changes to this
 program.  If you have modem access, it is much more preferable to download
 the most recent version from the BBS.  Use the code as a learning tool, not
 as an exercise in typing.  The listing in TechNotes serves as a documented
 copy but, considering the extent of the code, I wouldn't want to be the
 data entry person who has to type that.  (What am I saying, I was that
 person!)  
 
 In addition to these UDCs, it will be fairly simple to add new commands
 when future versions of dBASE are released. Since the basic structure of
 PROCEDURE IsRtime is to evaluate the first word (up to the first space) and
 then macro expand the rest, it will be a simple matter to look at the
 change summary and add those KEYWORDS into more case conditions.  
 
 CASE "NEWCOMMAND" = Dot_1st
   NEWCOMMAND &Dot_Rest
 
 The reason the UDCs have the KEYWORDS on the right of the equal sign and
 the dBASE IV commands are on the left is because of the way strings
 compare. By having KEYWORD = Dot_1st, partial matches will execute,
 regardless of the EXACT setting, whereas Dot_1st = KEYWORD will honor SET
 EXACT and the entire command will need to be input. 
 
 Since the PROCEDURE DOT requires 3 parameters, you should have another
 program which calls DOT with the ROW, COLUMN, and PROMPT you want to start
 with. I wrote a little program called COMMAND which looks like : 
 
 SET PROCEDURE TO Dot
 SET STATUS ON
 DO DOT WITH 21, 0, "."
 RETURN
 
 When you issue the command from DOS , RUNTIME/T COMMAND you will come up
 with a dot prompt above the status bar which looks nearly identically to
 the one in dBASE (COMMAND shows instead of Command). If you try to run DOT
 without parameters, it will give you an error message that you may ignore
 and the default dot prompt will be set. You could use different screen
 coordinates and a different prompt (DO DOT WITH 24, 0, "This is my prompt
 ->"). In this program, the prompt can be below the status bar (or even on
 it, but that would look funny). 
 
 Dot.PRG
 PROCEDURE DOT
   PARAMETERS mDot_Row, mDot_Col, mDot_Prompt
   SET CONSOLE OFF
   Dot_Error = 0
   ON ERROR Dot_Error = ERROR()
   IF Dot_Error = 94
     * Set default dot row, based on status bar and display mode.
     * Some terminal emulators for non-DOS versions of dBASE use
     * the last line on the screen. This command adjusts for this.
     mDot_Row = IIF(SET("STATUS")="ON", 21, 24) + ;
       IIF("43" $ SET("DISPLAY"), 18, 0) - IIF("DOS" $ OS(), 0, 1)
     mDot_Col = 0
     mDot_Prompt = "."
   ENDIF
   RELEASE Dot_Error
   DO Dot_Public
   IF TYPE("m_Prog") = "U"
     PUBLIC m_Prog
     m_Prog = PROGRAM()
   ENDIF
   Dot_Talk = SET("TALK")
   SET TALK OFF
   CLEAR
   SET CONSOLE ON
   SET FORMAT TO
   SET ESCAPE ON
   ON ESCAPE Dot_Escape = 0
   ON ERROR DO DOT_ERR WITH ERROR(), MESSAGE()
   ON KEY LABEL CTRL-F9 CANCEL
   IF .NOT. "RUNTIME" $ UPPER(VERSION(0))
     ON KEY LABEL F1 HELP
   ELSE
     ON KEY LABEL F1 KEYBOARD "HELP" + CHR(13)
   ENDIF
   Dot_Row = mDot_Row
   Dot_Col = mDot_Col
   Dot_Prompt = mDot_Prompt
   RELEASE mDot_Row, mDot_Col, mDot_Prompt
   Dot_Pop = .F.
   Dot_Hist = 20
   DECLARE Dot_Array[Dot_Hist]
   Dot_Len = 80 - (Dot_Col + LEN(Dot_Prompt) + 1)
   Dot_Pic = '"@S' + LTRIM(STR(Dot_Len)) + '"'
   Dot_1st=""
   DO Set_Color
   DO WHILE .T.
     IF Dot_1st <> "@" .AND. Dot_1st <> "?" .AND. Dot_Talk = "ON"
       ?
     ENDIF
     Dot_Comm = SPACE(254)
     @ Dot_Row, Dot_Col SAY Dot_Prompt GET Dot_Comm ;
     PICTURE &Dot_Pic COLOR &Dot_Color
     READ
     Dot_Comm = RTRIM(LTRIM(Dot_Comm))
     IF MOD(READKEY(), 256) = 4
       DO Dot_Key
     ENDIF
     IF "" <> Dot_Comm .AND. Dot_Hist > 0
       DO Dot_Stack
     ENDIF
     IF UPPER(Dot_Comm) = "EXIT"
       EXIT
     ENDIF
     * Disable SET TALK
     IF UPPER(Dot_Comm) = "SET TALK"
       Dot_Talk = IIF(UPPER(Dot_Comm) = "SET TALK ON", "ON", "OFF")
     ENDIF
     IF "" <> Dot_Comm .AND. UPPER(Dot_Comm) <> "SET TALK"
       ON ERROR Dot_Error = ERROR()
       Dot_Cnt = 0          && Used for break-out of fatal error.
       DO WHILE "&" $ Dot_Comm
         DO MacroSub
         Dot_Cnt = Dot_Cnt + 1
         IF Dot_Cnt >= 32   && Attempted 32 macro substitutions.
           EXIT
         ENDIF
       ENDDO
       ON ERROR DO DOT_ERR WITH ERROR(), MESSAGE()
       DO IsRTime
     ENDIF
   ENDDO
   SET TALK &Dot_Talk
   ON ERROR
 RETURN
 
 
 PROCEDURE Dot_Key
   * Show commands in popup or on the command line.
   IF Dot_Pop
     DEFINE POPUP Dot_Pop FROM 0, 0
     counter = 1
     m_First = 1
     DO WHILE counter <= Dot_Hist
       IF TYPE("Dot_Array[counter]") = "L"
         m_First = m_First + 1
       ELSE
         DEFINE BAR 1 + (counter - m_First) OF Dot_Pop PROMPT ;
           Dot_Array[counter]
       ENDIF
       counter = counter + 1
     ENDDO
     ON SELECTION POPUP Dot_Pop DEACTIVATE POPUP
     IF counter - m_First > 0
       ACTIVATE POPUP Dot_Pop
       m_Bar = BAR()
       IF m_Bar <> 0
         @ Dot_Row, 0
         Dot_Comm = LTRIM(RTRIM(Dot_Array[m_Bar + m_First - 1]))
       ENDIF
     ENDIF
   ELSE
     Stack_Num = Dot_Hist
     DO WHILE .T.
       m_Temp = Dot_Array[Stack_Num]
       m_Temp = IIF(TYPE("m_Temp") = "L", SPACE(254), m_Temp + ;
         SPACE(254 - LEN(m_Temp)))
       @ Dot_Row, Dot_Col CLEAR TO Dot_Row, 79
       @ Dot_Row, Dot_Col SAY Dot_Prompt GET m_Temp ;
         PICTURE &Dot_Pic COLOR &Dot_Color
       KEYBOARD CHR(2)
       READ
       DO CASE
       CASE MOD(READKEY(), 256) = 4
         IF Stack_Num > 1
           Stack_Num = Stack_Num - 1
         ENDIF
       CASE MOD(READKEY(), 256) = 5
         IF Stack_Num < Dot_Hist
           Stack_Num = Stack_Num + 1
         ELSE
           Dot_1st = "?"
           EXIT
         ENDIF
       CASE LASTKEY() = 27
         EXIT
       CASE TYPE("m_Temp") <> "L"
         Dot_Comm = LTRIM(RTRIM(m_Temp))
         EXIT
       ENDCASE
     ENDDO
   ENDIF
 RETURN
 
 
 PROCEDURE Dot_Public
   * Sets up PUBLIC variables required by DOT.PRG
   PUBLIC Dot_1st, Dot_Rest, Dot_Color, Dot_Comm, Dot_Exact
   PUBLIC Dot_pic, Dot_len, Dot_talk, Dot_prompt, Dot_Row, Dot_Col
   PUBLIC Dot_Array, Dot_Hist, Dot_Pop, Dot_Error, Dot_Escape
 RETURN
 
 
 PROCEDURE Dot_Release
   * RELEASEs PUBLIC variables required by DOT.PRG
   RELEASE Dot_1st, Dot_Color, Dot_Comm, Dot_Exact
   RELEASE Dot_pic, Dot_len, Dot_talk, Dot_prompt, Dot_Row, Dot_Col
   RELEASE Dot_Array, Dot_Hist, Dot_Pop, Dot_Safety
   RELEASE Dot_Error, Dot_Escape
 RETURN
 
 
 PROCEDURE Dot_Save
   PARAMETERS mFirst
   * Preserves variables required by DOT.PRG when executing
   * commands that could RELEASE or CLEAR them.
   Dot_Safety = SET("SAFETY")
   SET SAFETY OFF
   SAVE ALL LIKE Dot_* TO Dot_1
   DO CASE
   CASE "CLEAR" = mFirst
     CLEAR &Dot_Rest
   CASE "LOGOUT" = mFirst
     SAVE TO Dot_2 ALL LIKE m_Prog*            
     SAVE TO Dot_3 ALL LIKE mDot_Row           
     LOGOUT
     SET TALK OFF
     PUBLIC m_Prog, mDot_Row
     RESTORE FROM Dot_2 ADDITIVE               
     RESTORE FROM Dot_3 ADDITIVE               
     ERASE Dot_2.MEM
     ERASE Dot_3.MEM
   CASE "RELEASE" = mFirst
     CLEAR MEMORY
   CASE "SAVE" = mFirst
     SAVE &Dot_Rest
   ENDCASE
   DO Dot_Public
   RESTORE FROM Dot_1 ADDITIVE
   ERASE Dot_1.MEM
   SET SAFETY &Dot_Safety
 RETURN
 
 
 PROCEDURE Dot_Stack
   * Stacks commands (history) in an array.
   IF TYPE("Dot_Array[Dot_Hist]") = "L" .OR. ;
       Dot_Array[Dot_Hist] <> Dot_Comm
     m_Temp = 1
     DO WHILE m_Temp < Dot_Hist
       Dot_Array[m_Temp] = Dot_Array[m_Temp + 1]
       m_Temp = m_Temp + 1
     ENDDO
     Dot_Array[m_Temp] = Dot_Comm
   ENDIF
 RETURN
 
 
 PROCEDURE IsRTime
   * Parses commands so they may be executed in RunTime.
   Dot_Exact = SET("EXACT")
   SET EXACT OFF
   IF AT(" ", Dot_Comm) = 0
     Dot_1st = UPPER(Dot_Comm)
     Dot_Rest = ""
   ELSE
     Dot_1st = UPPER(LEFT(Dot_Comm, AT(" ", Dot_Comm) - 1))
     Dot_Rest = RIGHT(Dot_Comm, LEN(Dot_Comm) - ;
       AT(" ", Dot_Comm))
   ENDIF
   IF TYPE("As3_Runtime") <> "U"
     IF Dot_1st <> "?" .AND. Dot_1st <> "SET"
       SET TALK ON
     ELSE
       SET TALK OFF
     ENDIF
   ENDIF
 
   * Evaluate first word or symbol in command.
   DO CASE
   CASE LEFT(Dot_Comm, 1) = "?"
     Dot_Rest = RIGHT(Dot_Comm, LEN(Dot_Comm) - AT("?", Dot_Comm))
     m_Temp = AT("SET(", UPPER(Dot_Rest))
     IF m_Temp > 0
       m_Text = UPPER(SUBSTR(Dot_Rest, AT("SET(", UPPER(Dot_Rest)) ;
         + 5, AT(")", UPPER(Dot_Rest)) - ;
         (AT("SET(", UPPER(Dot_Rest)) + 6)))
       IF m_Text $ "EXACT TALK"
         Dot_Rest = STUFF(Dot_Rest, AT("SET(", UPPER(Dot_Rest)), ;
           5, "Dot_")
         Dot_Rest = LEFT(Dot_Rest, AT(")", Dot_Rest) - 2) + ;
           IIF("EXAC" = m_Text, "T", "")
       ENDIF
     ENDIF
     ?&Dot_Rest                      && '&' must be next to '?'
     &&  ? &Dot_Rest won't work for ?? or ???
     ?
   CASE LEFT(Dot_Comm, 1) = "@"
     Dot_Rest = RIGHT(Dot_Comm, LEN(Dot_Comm) - AT("@", Dot_Comm))
     @ &Dot_Rest
   CASE LEFT(Dot_Comm, 1) = "!"
     Dot_Rest = RIGHT(Dot_Comm, LEN(Dot_Comm) - AT("!", Dot_Comm))
     RUN &Dot_Rest
   CASE "ACCEPT" = Dot_1st
     m_Temp = RIGHT(Dot_Rest, LEN(Dot_Rest) -  ;
       AT(" TO ", UPPER(Dot_Rest)) - 3)
     PUBLIC &m_Temp
     ACCEPT &Dot_Rest
   CASE "ACTIVATE" = Dot_1st
     ACTIVATE &Dot_Rest
   CASE "APPEND" = Dot_1st
     APPEND &Dot_Rest
   CASE "AVERAGE" = Dot_1st
     AVERAGE &Dot_Rest
   CASE "BROWSE" = Dot_1st
     BROWSE &Dot_Rest
   CASE "CALCULATE" = Dot_1st
     CALCULATE &Dot_Rest
   CASE "CALL" = Dot_1st
     CALL &Dot_Rest
   CASE "CANCEL" = Dot_1st
     CANCEL
   CASE "CHANGE" = Dot_1st
     CHANGE &Dot_Rest
   CASE "CLEAR" = Dot_1st
     IF "" = TRIM(Dot_Rest)
       CLEAR
     ELSE
       DO Dot_Save WITH Dot_1st
     ENDIF
   CASE "CLOSE" = Dot_1st
     CLOSE &Dot_Rest
   CASE "COMPILE" = Dot_1st
     IF .NOT. "RUNTIME" $ UPPER(VERSION(0))
       COMPILE &Dot_Rest
     ENDIF
   CASE "CONTINUE" = Dot_1st
     CONTINUE
   CASE "CONVERT" = Dot_1st
     CONVERT &Dot_Rest
   CASE "COPY" = Dot_1st
     COPY &Dot_Rest
   CASE "COUNT" = Dot_1st
     COUNT &Dot_Rest
   CASE "CREATE" = Dot_1st
     IF .NOT. "RUNTIME" $ UPPER(VERSION(0))
       CREATE &Dot_Rest
     ELSE
       IF LEFT(UPPER(TRIM(Dot_Rest)), 4) = "VIEW" ;
           .AND. " FROM ENVI" $ UPPER(Dot_Rest)
         m_Temp = SUBSTR(Dot_Rest, 6, AT(" FROM ENVI", ;
           UPPER(Dot_Rest)) - 6)
         CREATE VIEW &m_Temp FROM ENVIRONMENT
       ELSE
         IF FILE("User_Def.DBO")
           DO User_Def
           RETURN
         ENDIF
       ENDIF
     ENDIF
   CASE "DEACTIVATE" = Dot_1st
     DEACTIVATE &Dot_Rest
   CASE "DEBUG" = Dot_1st
     IF .NOT. "RUNTIME" $ UPPER(VERSION(0))
       DEBUG &Dot_Rest
     ENDIF
   CASE "DECLARE" = Dot_1st
     DECLARE &Dot_Rest
   CASE "DEFINE" = Dot_1st
     DEFINE &Dot_Rest
   CASE "DELETE" = Dot_1st
     DELETE &Dot_Rest
   CASE "DIR" = Dot_1st
     DIR &Dot_Rest
   CASE "DISPLAY" = Dot_1st
     DISPLAY &Dot_Rest
   CASE "DO" = Dot_1st
     DO &Dot_Rest
   CASE "EDIT" = Dot_1st
     EDIT &Dot_Rest
   CASE "EJECT" = Dot_1st
     EJECT &Dot_Rest
   CASE "ERASE" = Dot_1st
     ERASE &Dot_Rest
   CASE "EXPORT" = Dot_1st
     EXPORT &Dot_Rest
   CASE "FIND" = Dot_1st
     FIND &Dot_Rest
   CASE "GO" = Dot_1st
     GO &Dot_Rest
   CASE "IMPORT" = Dot_1st
     IMPORT &Dot_Rest
   CASE "INDEX" = Dot_1st
     INDEX &Dot_Rest
   CASE "INPUT" = Dot_1st
     m_Temp = RIGHT(Dot_Rest, LEN(Dot_Rest) -  ;
       AT(" TO ", UPPER(Dot_Rest)) - 3)
     PUBLIC &m_Temp
     INPUT &Dot_Rest
   CASE "INSERT" = Dot_1st
     INSERT &Dot_Rest
   CASE "JOIN" = Dot_1st
     JOIN &Dot_Rest
   CASE "KEYBOARD" = Dot_1st
     KEYBOARD &Dot_Rest
   CASE "LABEL" = Dot_1st
     LABEL &Dot_Rest
   CASE "LIST" = Dot_1st
     LIST &Dot_Rest
   CASE "LOAD" = Dot_1st
     LOAD &Dot_Rest
   CASE "LOCATE" = Dot_1st
     LOCATE &Dot_Rest
   CASE "LOGOUT" = Dot_1st
     DO Dot_Save WITH Dot_1st
   CASE "MODIFY" = Dot_1st
     IF .NOT. "RUNTIME" $ UPPER(VERSION(0))
       MODIFY &Dot_Rest
     ENDIF
   CASE "MOVE" = Dot_1st
     MOVE &Dot_Rest
   CASE "ON" = Dot_1st
     IF .NOT. ("ESCA" $ UPPER(Dot_Rest) .OR. "ERRO" $ UPPER(Dot_Rest))
       ON &Dot_Rest
     ENDIF
   CASE "PACK" = Dot_1st
     PACK
   CASE "PLAY" = Dot_1st
     PLAY &Dot_Rest
   CASE "PRIVATE" = Dot_1st
     PRIVATE &Dot_Rest
   CASE "PROTECT" = Dot_1st
     PROTECT
   CASE "PUBLIC" = Dot_1st
     PUBLIC &Dot_Rest
   CASE "QUIT" = Dot_1st
     QUIT &Dot_Rest
   CASE "READ" = Dot_1st
     READ &Dot_Rest
   CASE "RECALL" = Dot_1st
     RECALL &Dot_Rest
   CASE "REINDEX" = Dot_1st
     REINDEX &Dot_Rest
   CASE "RELEASE" = Dot_1st
     IF UPPER(TRIM(Dot_Rest)) = "ALL"
       DO Dot_Save WITH Dot_1st
     ELSE
       RELEASE &Dot_Rest
     ENDIF
   CASE "RENAME" = Dot_1st
     RENAME &Dot_Rest
   CASE "REPLACE" = Dot_1st
     REPLACE &Dot_Rest
   CASE "REPORT" = Dot_1st
     REPORT &Dot_Rest
   CASE "RESET" = Dot_1st
     RESET &Dot_Rest
   CASE "RESTORE" = Dot_1st
     Dot_Rest = Dot_Rest + IIF(" ADDI" $ ;
       UPPER(Dot_Rest), "", " ADDITIVE")
     RESTORE &Dot_Rest
   CASE "ROLLBACK" = Dot_1st
     ROLLBACK &Dot_Rest
   CASE "RUN" = Dot_1st
     RUN &Dot_Rest
   CASE "SAVE" = Dot_1st
     DO Dot_Save WITH Dot_1st
   CASE "SEEK" = Dot_1st
     SEEK &Dot_Rest
   CASE "SELECT" = Dot_1st
     SELECT &Dot_Rest
   CASE "SET" = Dot_1st
     Dot_Temp = UPPER(LEFT(LTRIM(Dot_Rest), 4))
     DO CASE
     CASE "HIST" = Dot_Temp
       IF " TO " $ UPPER(Dot_Rest)
           m_Temp = VAL(RIGHT(Dot_Rest, LEN(Dot_Rest) - ;
           AT(" TO ", UPPER(Dot_Rest)) - 3))
         DO CASE
         CASE m_Temp = 0 .AND. RIGHT(RTRIM(Dot_Rest), 1) = "0"
           RELEASE Dot_Array
           Dot_Hist = 0
         CASE m_Temp > 0
           IF m_Temp <> Dot_Hist
             Dot_Hist = m_Temp
             DECLARE Dot_Array[Dot_Hist]
           ENDIF
         ENDCASE
       ENDIF
     CASE "" <> Dot_Temp .AND. .NOT. Dot_Temp $ ;
         "FORMATDEBUGDOHISTORYECHOINSTRUCTSQL STEPTRAP"
       Dot_t_st = SET("STATUS")
       Dot_t_DI = SET("DISPLAY")
       SET &Dot_Rest
       DO Set_Color
       * Move prompt if STATUS or DISPLAY mode has changed.
       IF "DISPLAY" = Dot_Temp .OR. "STATUS" = Dot_Temp
         IF SET("STATUS") <> Dot_t_st
           Dot_Row = Dot_Row + IIF(SET("STATUS") = "ON", - 3, 3)
         ENDIF
         IF SET("DISPLAY") <> Dot_t_di
           Dot_Row = Dot_Row + ;
             IIF(RIGHT(SET("DISPLAY"), 2) = "43", 18, -18)
         ENDIF
         Dot_Row = MIN(IIF("43" $ SET("DISPLAY"), 42, 24), Dot_Row)
       ENDIF
       RELEASE Dot_t_st, Dot_t_di
     ENDCASE
     RELEASE Dot_temp
   CASE "SHOW" = Dot_1st
     SHOW &Dot_Rest
   CASE "SKIP" = Dot_1st
     SKIP &Dot_Rest
   CASE "SORT" = Dot_1st
     SORT &Dot_Rest
   CASE "STORE" = Dot_1st
     STORE &Dot_Rest
   CASE "SUM" = Dot_1st
     SUM &Dot_Rest
   CASE "TOTAL" = Dot_1st
     TOTAL &Dot_Rest
   CASE "TYPE" = Dot_1st
     TYPE &Dot_Rest
   CASE "UNLOCK" = Dot_1st
     UNLOCK &Dot_Rest
   CASE "UPDATE" = Dot_1st
     UPDATE &Dot_Rest
   CASE "USE" = Dot_1st
     USE &Dot_Rest
   CASE "WAIT" = Dot_1st
     IF "TO " $ UPPER(Dot_Rest)
       m_Temp = RIGHT(Dot_Rest, LEN(Dot_Rest) - ;
         AT(" TO ", UPPER(Dot_Rest)) - 3)
       PUBLIC &m_Temp
     ENDIF
     WAIT &Dot_Rest
   CASE "ZAP" = Dot_1st
     ZAP
   CASE "=" $ Dot_Comm
     mStoreVar = LEFT(Dot_Comm, AT("=", Dot_Comm) - 1)
     mstore = SUBSTR(Dot_Comm, AT("=", Dot_Comm) + 1, LEN(Dot_Comm))
     PUBLIC &mstorevar
     STORE &mstore TO &mStoreVar
   CASE VAL(Dot_Comm) > 0
     GO &Dot_Comm
   ENDCASE
   SET TALK OFF
   IF "" <> Dot_1st
     DO User_Defined
   ENDIF
   SET EXACT &Dot_Exact
 RETURN
 
 
 PROCEDURE MacroSub
   * Check for -, +, =, >=, =<, <>, #, <, >, = in COMMAND line
   DECLARE SYMBOL[11]
   SYMBOL[1] = "-"
   SYMBOL[2] = "+"
   SYMBOL[3] = ">="
   SYMBOL[4] = "<="
   SYMBOL[5] = "<>"
   SYMBOL[6] = "#"
   SYMBOL[7] = "*"
   SYMBOL[8] = "/"
   SYMBOL[9] = "="
   SYMBOL[10] = "<"
   SYMBOL[11] = ">"
   m_cnt = 1
   DO WHILE m_cnt <= 8
     DO WHILE AT(SYMBOL[m_cnt], Dot_Comm) > 0 .AND. ;
         (SUBSTR(Dot_Comm, AT(SYMBOL[m_cnt], Dot_Comm) + ;
         LEN(SYMBOL[m_cnt]), 1) <> CHR(32) .OR. ;
         SUBSTR(Dot_Comm, AT(SYMBOL[m_cnt], Dot_Comm) - 1, 1) ;
         <> CHR(32))
       Dot_Comm = STUFF(Dot_Comm, AT(SYMBOL[m_cnt], Dot_Comm), ;
         LEN(SYMBOL[m_cnt])," " + CHR(1) + " ")
     ENDDO
     DO WHILE AT(CHR(1), Dot_Comm) > 0
       Dot_Comm = STUFF(Dot_Comm, AT(CHR(1), Dot_Comm), 1, SYMBOL[m_cnt])
     ENDDO
     m_cnt = m_cnt + 1
   ENDDO
   DO WHILE m_cnt <= 11
     DO WHILE AT(SYMBOL[m_cnt], Dot_Comm) > 0 .AND. ;
         ((.NOT. SUBSTR(Dot_Comm, AT(SYMBOL[m_cnt], Dot_Comm) + ;
         LEN(SYMBOL[m_cnt]), 1) $ " <,>,=") .OR. ;
         (.NOT. SUBSTR(Dot_Comm, AT(SYMBOL[m_cnt], Dot_Comm) - ;
         1, 1) $ " ,<,>,="))
       Dot_Comm = STUFF(Dot_Comm, AT(SYMBOL[m_cnt], Dot_Comm), ;
         LEN(SYMBOL[m_cnt])," " + CHR(1) + " ")
     ENDDO
     DO WHILE AT(CHR(1), Dot_Comm) > 0
       Dot_Comm = STUFF(Dot_Comm, AT(CHR(1), Dot_Comm), 1, SYMBOL[m_cnt])
     ENDDO
     m_cnt = m_cnt + 1
   ENDDO
 
   Dot_Comm = LTRIM(RTRIM(Dot_Comm))
   part1 = SUBSTR(Dot_Comm, AT("&", Dot_Comm) + 1, LEN(Dot_Comm))
   IF AT(".", part1) = 0
     is_Dot = .F.
     m_Pos = IIF(AT(" ", part1) = 0, LEN(part1), AT(" ", part1) - 1)
   ELSE
     is_Dot = .T.
     IF AT(" ", part1) = 0
       m_Pos = AT(".", part1) - 1
     ELSE
       m_Pos = MIN(AT(".", part1), AT(" ", part1)) - 1
     ENDIF
   ENDIF
   part2 = LEFT(part1, m_Pos)
   part3 = RIGHT(Dot_Comm, LEN(Dot_Comm)  - AT("&", Dot_Comm) - ;
     m_Pos - IIF(is_Dot, 1, 0))
   Dot_Comm = LEFT(Dot_Comm, AT("&", Dot_Comm) - 1) + &part2 + part3
 RETURN
 
 
 PROCEDURE Set_Color
   Dot_Color = LEFT(SET("ATTRIBUTES"), AT(",", SET("ATTRIBUTES")) - 1)
   Dot_Color = Dot_Color + "," + Dot_Color
 RETURN
 
 
 PROCEDURE User_Defined
   * Allows UDCs (User-Defined Commands) to execute if a non-dBASE
   * command is entered.
   IF FILE("User_Def.DBO")
     DO User_Def
     * RETURN is optional. Add it here if you want to disable the
     * built-in UDCs (in Adam_Def). Only necessary if USER_DEF 
     * contains the same UDC.
   ENDIF
   IF FILE("Adam_Def.DBO")
     DO Adam_Def
   ENDIF
 RETURN
 
 
 PROCEDURE DOT_ERR
   PARAMETERS mDot_Err, mDot_Msg
   Dot_Error = mDot_Err
   IF .NOT. LTRIM(STR(Dot_Error)) $ "10"
     SAVE SCREEN TO Err_Screen
     @  8, 15 CLEAR TO 14, 60
     @  8, 15 TO 14, 60 DOUBLE
     @  9, 20 SAY "ERROR Number : " + LTRIM(STR(mDot_Err))
     @ 11, 20 SAY mDot_Msg
     @ 13, 20 SAY "Press any key to continue ...  "
     i=INKEY(0)
     RESTORE SCREEN FROM Err_Screen
     RELEASE SCREEN Err_Screen
 ENDIF
 RETURN
 *: EOF: DOT.PRG
 Here is a partial listing of the file Adam_Def.PRG which contains some UDCs
 that work with the above program :
 
 
 DO CASE
 CASE Dot_1st = "ASCII"
   * Displays an ASCII Chart. CHR(7) (BELL) does not show.
   m_DotStat = SET("STATUS")
   SAVE SCREEN TO Dot_Screen
   SET STATUS OFF
   CLEAR
   @ 0, 0 TO 24, 79 DOUBLE
   @ 1, 33 SAY "ASCII Chart"
   m_Ascii = 0
   DO WHILE m_Ascii <= 255
     @ 3 + MOD(m_Ascii, 20), 2 + (6 * INT(m_Ascii / 20)) SAY ;
       RIGHT("000" + LTRIM(STR(m_Ascii)), 3) + " " + ;
       IIF(m_Ascii = 7, "", CHR(m_Ascii))
     m_Ascii = m_Ascii + 1
   ENDDO
   @ 23, 20 SAY "Press Any Key to Continue ... "
   I=INKEY(0)
   SET STATUS &m_DotStat
   RESTORE SCREEN FROM Dot_Screen
   
 CASE Dot_1st = "ASSIST"
   * User-Defined menu program (ASSIST3) when using RunTime.
   IF "RUNTIME" $ UPPER(VERSION(0))
     IF FILE("Assist3.DBO")
       IF m_Prog = "ASSIST3"
         DO Dot_release
         Public m_Prog           && This variable gets released -
         m_Prog = "ASSIST3"      && needs to be reset.
         RETURN TO MASTER
       ELSE
         DO Assist3
       ENDIF
     ELSE
       ? "NOT AVAILABLE IN RUNTIME - Download Assist3.ZIP " + ;
         "from the BBS" AT 10
       ?
     ENDIF
   ELSE
     ASSIST
   ENDIF
 CASE Dot_1st = "CLS"
   CLEAR
   
 CASE Dot_1st = "COLOR"
   * Displays a color chart (Foreground and background colors).
   DO Colors
   
 CASE Dot_1st = "HELP"
   * User-defined HELP screen.
   SAVE SCREEN TO Dot_Screen
   CLEAR
   @  0,  0 TO 21, 79 DOUBLE
   @  1, 30 SAY "Added Commands"
   @  3,  3 SAY "ASCII - Display an ASCII chart"
   @  5,  3 SAY "ASSIST - Execute Assist3 program (if available)"
   @  7,  3 SAY "COLORS - Display a color chart"
   @  9,  3 SAY "HELP - This Screen "
   @ 11,  3 SAY "POP [on / OFF] - Command history is " + ;
     "displayed in a POPUP / on command line "
   @ 13,  3 SAY "SETCOLOR - Set colors for the program (if available)"
   @ 15,  3 SAY "SHIFT - Move Dot UP/DOWN n rows " + ;
     "(SHIFT 3 / SHIFT -1) "
   @ 20, 24 SAY " Press Any Key to Continue ... "
   I=INKEY(0)
   RESTORE SCREEN FROM Dot_Screen
   RELEASE SCREEN Dot_Screen
   
 CASE Dot_1st = "POP"
 * Display command line history in a POPUP or on the command line.
   Dot_Pop = (UPPER(Dot_Rest) = "ON")
   
 CASE Dot_1st = "SETCOLOR"
   * Allows user to set new colors (Foreground and background).
   IF FILE("setcolor.dbo")
     DO SETCOLOR
     DO SET_COLOR     && DOT.DBO procedure
   ENDIF
   
 CASE Dot_1st = "SHIFT"
   * Shift the prompt up / down n lines.
   IF VAL(Dot_Rest) <= 0
     Dot_Row = MAX(0, Dot_Row + VAL(Dot_Rest))
   ELSE
     Dot_Row = MIN(IIF("43" $ SET("DISPLAY"), 42, 24), ;
       Dot_Row + VAL(Dot_Rest))
   ENDIF
   
 ENDCASE
 RETURN
 **
 PROCEDURE Colors
 * By Steve Koterski - modified by Adam L. Menkes
 * Routine to display the full array of 16
 * Fore-ground and 8 back-ground colors
 mNorm = LEFT(SET("ATTRIBUTES"), AT(",", SET("ATTRIBUTES")) - 1)
 mStat = SET("STATUS")
 mClock = SET("CLOCK")
 SET STATUS OFF
 SET SCOREBOARD OFF
 SET COLOR OF NORMAL TO W+/N
 SET CURSOR OFF
 CLEAR
 STORE " COLOR " TO mvar
 STORE 1 TO ct1, ct2
 STORE 11 TO MCOL
 @  2, 2 SAY "FORE-"  COLOR R+
 @  3, 2 SAY "GROUND" COLOR R+
 @  4, 2 SAY "Black"
 @  5, 2 SAY "Blue"
 @  6, 2 SAY "Green"
 @  7, 2 SAY "Cyan"
 @  8, 2 SAY "Red"
 @  9, 2 SAY "Magenta"
 @ 10, 2 SAY "Brown"
 @ 11, 2 SAY "White"
 @ 12, 2 SAY "Gray"
 @ 13, 2 SAY "Blue   +"
 @ 14, 2 SAY "Green  +"
 @ 15, 2 SAY "Cyan   +"
 @ 16, 2 SAY "Red    +"
 @ 17, 2 SAY "Purple +"
 @ 18, 2 SAY "Yellow +"
 @ 19, 2 SAY "White  +"
 @ 20, 9 SAY "+ indicates BRIGHT" COLOR R+
 @ 1, 30 SAY "BACKGROUND" COLOR R+
 @ 3, 12 SAY "Black"
 @ 3, 19 SAY "Blue"
 @ 3, 26 SAY "Green"
 @ 3, 33 SAY "Cyan"
 @ 3, 41 SAY "Red"
 @ 3, 46 SAY "Magenta"
 @ 3, 54 SAY "Brown"
 @ 3, 61 SAY "Gray"
 DECLARE colors[8]
 colors[1] = "n"
 colors[2] = "b"
 colors[3] = "g"
 colors[4] = "bg"
 colors[5] = "r"
 colors[6] = "rb"
 colors[7] = "gr"
 colors[8] = "w"
 DO WHILE ct1 <= 8
   DO WHILE ct2 <= 16
     BR = IIF(ct2 > 8,"+", "")
     mColor = colors[IIF(ct2 > 8, ct2 - 8, ct2)] + ;
       BR + "/" + colors[ct1]
     @ ct2 + 3, MCOL SAY mVar COLOR &mColor
     ct2 = ct2 + 1
   ENDDO
   MCOL = MCOL + 7
   ct2 = 1
   ct1 = ct1 + 1
 ENDDO
 @ 20, 1 SAY ""
 SET COLOR OF NORMAL TO W+/N
 @ 21, 20 SAY " Press any key to continue ... "
 I = INKEY(0)
 SET STATUS &mStat
 SET COLOR &mClock
 SET COLOR OF NORMAL TO &mNorm
 SET CURSOR ON
 CLEAR
 RETURN
 
 


3  ANSISQL

dBASE SQL and 
ANSI SQL Primer
Jeffrey McCrimon

Although dBASE SQL is not 100% compatible with ANSI SQL, there are many
areas where dBASE SQL goes beyond what ANSI SQL requires

dBASE SQL, introduced with the release of dBASE IV, introduced users to the
ability of using the industry standard SQL database language with the
programming capabilities of the dBASE command language as well as the
design surfaces found in dBASE IV.  Originally dBASE SQL was only a
"localized" version which took the SQL commands and programs and converted
them to dBASE commands.  It also used special (but compatible) versions of
a dBASE database file.  With the introduction of the dBASE IV Server
Edition these same SQL commands and programs were given the capability to
be run against a remote database server such as the Microsoft SQL Server. 
This resulted in a great increase in performance due to the little
processing required by dBASE IV and the specific database server performing
the processing returning only the results to dBASE IV.

dBASE SQL can be used in two modes.  The first one is an interactive mode
which allows you to enter SQL commands from a prompt with the results of
the command given to you immediately.  The second mode is an embedded mode,
which allows for SQL commands to be embedded inside of a dBASE program. 
When this program is run, the SQL commands are properly parsed, and
executed with the results processed by the dBASE program.

The dBASE IV version of the SQL database language, dBASE SQL, is based on
the same implementation of SQL found in IBM's DATABASE 2 (DB2) and SQL/DS
mainframe computer database products.  Knowing this raises the question for
many users, "Is IBM's SQL in DB2 ANSI Level compatible, and if so, which
ANSI Level is it compatible with?"

The SQL commands in dBASE SQL programs written for the dBASE IV Server
Edition are automatically converted (with very little conversion if any) to
work against the Microsoft SQL Server.  SQL Server has its own version of
the SQL language called Transact-SQL which also goes beyond ANSI SQL
compliance in most areas but isn't 100% compatible with ANSI SQL. 
Transact-SQL has several features, such as user-defined datatypes, that are
not present in dBASE SQL.  So to take advantage of these features, dBASE IV
Server Edition includes the SENDSQL command, which allows a dBASE SQL
program to send a SQL Server specific Transact-SQL command to SQL Server
without conversion with the results sent immediately back to the dBASE SQL
program for processing.  The SENDSQL command is the basis for allowing
dBASE SQL programs to take advantage of any remote database server-specific
features should the dBASE IV Server Edition be enhanced to work against
other remote database servers in the future.  Please note that the more
your dBASE SQL program uses the SENDSQL command, the more likely it
requires modification when moving the program to a different remote
database server platform.

What is ANSI SQL?

ANSI, the American National Standards Institute, is a committee which
establishes standards for many of the products that we depend on every
day.  Such standards like the width of doors, the location of the steering
wheel in automobiles, and the conformity of plugs and outlets are all set
by ANSI.

With the proliferation of computers in society today, standards have become
increasingly important.  For example, SQL, which was developed by IBM in
the early 1970's, now has many different SQL dialects (SQL Server's
Transact-SQL for one) from many different vendors of SQL database
products.  

ANSI Levels 1 and 2

In the past, ANSI has established standards for many popular computer
languages, such as COBOL, FORTRAN, and lately C.  In 1986, ANSI completed
the first standard for SQL and published it in American National Standard
for Information Systems-Database Language-SQL, Document ANSI X3.135-1986. 
This standard (ANSI 1986) defined two areas of compliance, called Level 1
and Level 2.

In October 1989, ANSI approved data integrity enhancements, which define
the accuracy and reliability of information supplied through logging
transactions, running stored procedures, and activating triggers.  The
resulting document became American National Standard for Information
Systems-Database Language-SQL with Integrity Enhancement, Document ANSI
X3.135-1989.

The combination of the 1986 standard and the integrity enhancement feature
became ANSI 1989, the standard against which all current versions of SQL
are measured.
ANSI SQL Level 1 includes the following two categories of commands:

 Data Definition Language (DDL), including CREATE TABLE and CREATE VIEW. 
These commands define tables and views.  Column datatypes can be character,
decimal, integer, smallint, or float.  Identifiers (names of tables,
columns, views, and so on) must support names that contain at least 12
characters.
 Data Manipulation Language (DML), containing SELECT, INSERT, UPDATE, and
DELETE commands.
  In addition, the DML contains the transaction processing commands, which
COMMIT or ROLLBACK changes made to the data.  Level 1 also calls for a
cursor construct, which processes one record at a time when SQL is embedded
with a procedural language such as dBASE IV.

Including all of the ANSI SQL Level 1 constructs, ANSI SQL Level 2
introduces the GRANT command, which gives privileges to users on objects
such as tables and views.  The GRANT command is sometimes referred to as
Data Control Language or DCL.  DCL commands handle security and permissions
on objects in a database.

Along with the DCL, Level 2 also introduces the following enhancements to
SQL:
 UNION  A clause of the SELECT command which merges two or more tables
with the same columns and column attributes.
 Correlated subqueries  Returns more than one value, all of which
returned must be connected with a table column in an outer query.
 EXISTS  Operator of the WHERE clause which checks for the existence of
any rows in a subquery.
 INSERT with a SELECT subquery  Allows records to be selected from one
table and directly inserted into another.
 UPDATE WHERE CURRENT and DELETE WHERE CURRENT  Updates (or deletes) rows
within cursors that are part of the embedded SQL language.
 UNIQUE (on table column definition)  Rejects duplicate values when they
are inserted into a column or a combination of columns.
 NULL support  Represents missing or inapplicable information.
 CREATE SCHEMA  Groups similar information into individual collections
(databases) and contains all CREATE TABLE, CREATE VIEW, and GRANT
specifications.

ANSI Integrity Enhancement

The ANSI 1989 integrity enhancement calls for several constraint
definitions to maintain data integrity.  dBASE SQL does not comply with any
of the ANSI SQL integrity enhancements, however, if dBASE SQL is used under
the dBASE IV Server Edition for SQL Server, this compliance can be met,
although the compliance isn't 100% because SQL Server's approach is
different.

The ANSI SQL integrity enhancements include:

 Primary key  One or more non-null columns that uniquely identify a row
in a table and are the only guaranteed way of addressing that row.  Primary
and foreign keys establish relationships among tables and help create
joins.
 Foreign key  One or more columns in a table that match the values of a
primary key in another table.  Together, the primary and foreign key
constraints enforce referential integrity.
 Check  A condition to be satisfied by each row in a table.  For example,
    Check a range.
    Check that the values appear in a list of values.
    Check that a value equals a specific value.
 Reference  The relationship between the primary key and foreign key.
 Default  The option chosen by the system when no other option is
specified.

Limitations in ANSI SQL

There are several significant commands and features that are not present in
the current ANSI SQL standard.  They are:
 CREATE INDEX
 DROP TABLE, DROP VIEW, DROP INDEX, and DROP with any other object.
 ALTER TABLE
 REVOKE
 System catalogs

The ANSI SQL standard also specifically allows for enhancements and
extensions to the standard.  Therefore, an SQL implementation may be
considered compliant even if it contains extensions.  This is the norm with
many SQL vendors.

Compliance with ANSI SQL Level 1

To claim full compliance, a product must process DDL and DML at ANSI Level
1 or Level 2.  Support for the integrity enhancement is not required for a
vendor to claim compliance with Level 1 or Level 2, but support for
embedded SQL is required to claim full Level 1 or Level 2 compliance. 
According to Section 3.4 of the standard, an implementation claiming
SQL-DML conformance shall process, either at Level 1 or Level 2:
 Direct invocation of SQL DML statements ...
 Module language ...
and one of more of
 Embedded SQL COBOL 
 Embedded SQL FORTRAN
 Embedded SQL Pascal
 Embedded SQL PL/I

Although dBASE SQL doesn't comply with the ANSI 1989 integrity enhancement
and lack of module language support, it can still claim Level 1 compliance
through direct invocation and embedded SQL.

Users of the dBASE IV Server Edition for SQL Server lose the full Level 1
compliance because the decimal datatype is converted to the money datatype
when a dBASE SQL program is run against SQL Server.  But the ANSI 1989
integrity enhancement is gained to a degree as previously mentioned.  SQL
Server doesn't have module language support.

Compliance with ANSI SQL Level 2

dBASE SQL complies with most of ANSI Level 2.  The following is a list of
the areas where dBASE SQL differs and does not comply:
 CREATE SCHEMA  Instead dBASE SQL supplies the CREATE DATABASE command.
 LIKE  Although dBASE SQL does support the LIKE predicate, it doesn't
support an ESCAPE keyword that allows searching for the wildcard characters
"%" and "."
 UNIQUE  dBASE SQL allows the UNIQUE constraint to be specified on index
definitions.
 REAL and DOUBLE PRECISION datatypes  The datatype REAL is a synonym of
FLOAT.  dBASE SQL doesn't supply a DOUBLE PRECISION datatype.
 Detecting NULL values  dBASE SQL doesn't support NULL values.  

Users of dBASE IV Server Edition for SQL Server experience functionality
trade-offs when in their dBASE SQL programs, some of which aren't in
conjunction with Level 2 compliance.  They are:
 USER  SQL Server supplies a built-in function, user_name(), to return
the name of the current user.  A dBASE UDF can be written to retrieve the
value of this function.
 LIKE  SQL Server doesn't support an ESCAPE keyword to allow searching
for the wildcard characters "%" and " ", instead it uses UNIX-style regular
expressions to accomplish the same thing with even more power.  dBASE IV
Server Edition users would have to use SENDSQL to gain this feature.
 UNIQUE  Like dBASE SQL, SQL Server allows the UNIQUE constraint to be
specified on index definitions.
 DOUBLE PRECISION datatype  The SQL Server datatype Float, which is a
synonym of DOUBLE PRECISION and can be added through SQL Server's
user-defined datatype.  The user-defined datatype can be access through the
dBASE IV Server Edition SENDSQL command.
 Detecting NULL values  The SQL Server has this capacity by way of the
built-in function isnull().  The isnull() function fills NULL entries with
the specified value.  dBASE IV Server Edition users can access the isnull()
function through a dBASE UDF.

Conclusion

While the level of compliance isn't absolute, we found out that dBASE SQL
complies with most of the ANSI standard and where it doesn't, it usually
provides equivalent functionality.  Full compliance with the ANSI SQL
standard is planned for future versions of the Microsoft SQL Server.  dBASE
IV Server Edition for SQL Server automatically picks up these future
extensions in SQL Server through SENDSQL or with a subsequent update to the
product.

Suggested Reading

More information can be found on the topics covered in this article by
reading the following:

Borland:Using dBASE IV SQL (comes with dBASE IV Developer's Edition or
dBASE IV Server Edition)
Borland: Using dBASE IV Server Edition
Microsoft Technical Note "Discussion of the ANSI SQL Standard and Microsoft
SQL Server", Dec. 1990
American National Standard for Information Systems  Database Language 
SQL, Document ANSI X3.135-1986
American National Standard for Information Systems  Database Language 
SQL with Integrity Enhancement, Document ANSI X3.135-1989  s
