DRAFT VERSION OF A PROPOSED SUPPLEMENT/REPLACEMENT FOR PORTIONS OF APPENDIX Q OF THE DOCUMENTATION FILE (RBBS-DOC.TXT) FOR THE RBBS BULLETIN BOARD SYSTEM. Last revised 5/2/94. Author: Corwin Moore 313/995-2100 (PRSG BBS [SYSOP]) 313/769-1616 (voice [evenings]) CompuServe: 73016,163 Comments and criticisms warmly welcomed! +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ APPENDIX Q -- Using RBBS to Access dBASE and ORACLE Remotely ------------------------------------------------------------ The Need for Database Services A feature that has been long missing from PC-based host communication systems is the ability for SysOps to install customized databases and let callers run true interactive database queries against them. Because database management is a major programming task, the most promising way to add database services is to use RBBS-PC's original and innovative "DOOR" mechanism to exit RBBS-PC and have the remote user enter an existing database management program. "DOORing" to a database management program, however, is not as easy as one might hope. The major problems stem from the fact that database management programs are never designed to work in this environment. This is because: 1) Most programs write to the hardware for speed rather than use BIOS calls, causing the "screen" output to appear on the host terminal rather than on the caller's terminal. 2) Database programs do not monitor for carrier. If carrier drops they simply sit forever waiting for input rather than terminating. 3) Most use "full screen" rather than "line at a time" editing, which usually does not work properly on a remote terminal. 4) Security. Most database programs have no way to limit what a user can do. For example, they do not have a read-only mode, or the ability to restrict a user to specific files or fields. Many let the user issue DOS commands inside them, which gives to call too much power. 5) Difficulty in learning to use. A caller can hardly be expected to know how to use a database. Hence it must be possible to simplify and control the user interface inside the database package. Progress has been made with two of the most popular PC databases -- dBASE and ORACLE. Using dBASE "DOORS" with RBBS-PC -------------------------------- The following discussion presumes that the reader is somewhat conversant with dBASE programming. (It is beyond the scope of the RBBS Manual to attempt a tutorial for dBASE!) Each of the difficulties cited above will be addressed in turn, with actual programing examples provided where appropriate. (The author of this section is Corwin Moore, SysOp of the PRSG BBS [313/995-2100] in Ann Arbor, Michigan.) Problem 1: BIOS Calls and Hardware Reads/Writes. ----------------------------------------------- We must distinguish here between INPUT and OUTPUT constraints. dBASE makes direct hardware writes, bypassing conventional BIOS calls. This prevents using the conventional DOS "redirection" commands for receiving input from the modem connected through a COM port. However, writing to a COM port has never been the problem. Even within dBASE II and III, screen output could be directed easily enough to another output device, in this case, the COM1 port. SET PRINTER TO COM1 SET PRINTER ON Indeed, turning the printer on and off from within a dBASE program allows control over what is actually passed to the remote user. (Not all screen activity on the local monitor, even with SET TALK OFF, is appropriate to send out to the remote user.) Instead, the problem has been capturing input FROM a COM port (i.e., from the remote user). Previously (with dBASE III), third-party software had been required to accomplish this feat. This task can now be addressed by programming options available entirely within dBASE IV. The following code fragment (which is specific to dBASE IV, and is NOT available in dBASE III or earlier versions) shows how to read a single incoming data bit from the COM1 port. (The applicability of this code fragment to other dBASE clones has not been researched by the author.) VN1=FOPEN("COM1","R") VC2=FREAD(V1,1) ? FCLOSE(VN1) The FOPEN command opens an existing file (in this case, the DOS handle COM1, which is actually the DOS-reserved name for the COM1 port), and returns a "file handle" number. The first line of this code fragment creates a memory variable (VN1) which is this file handle number. (In the examples here, memory variables for use within dBASE will be assigned a leading "V", then N/C/L for numeric/character/logic variable respectively, for ease of identification.) The FREAD command reads and returns a string of characters from a file (or in this case, from the COM1 port which has been substituted for a file). In the example above, only the FIRST character is stored to VC2. (The reason for reading only the first character will be explained later.) The FCLOSE command closes the low-level file (in this case, the COM1 port) designated by its file handle number, the one previously opened with the FOPEN command. If this file (i.e., the COM1 port) is successfully closed (as should always be the case when dealing merely with a COM port), the FCLOSE command returns .T. to the local screen (whether TALK is ON or OFF). (This is an example of a local screen output which is not needed or desired to be sent to the remote user. If the printer had previously been set ON in order to send characters out the COM port, you would want to set it OFF prior to the position of the code fragment above.) The problem with this method of capturing incoming data is that DOS (and/or dBASE?) will spend only a limited time attempting to "read" this "file" (namely, waiting for signal to come in from the COM1 port, which has been substituted for a file). Furthermore, DOS will not buffer or preserve this byte. The signal has to appear PRECISELY during that brief "window" when this "file" (for which the COM1 port has been substituted) is being "read." On a 12 MHz 286, this window exists only for about 1.5 to 2.0 seconds. (A similar or only slightly reduced time period seems to apply to faster and more powerful CPUs, but the author has attempted quantify the differences.) The duration of this "window for reading" increases as the number of bytes being "read" (only ONE in the example above) is increased. As soon as this incoming byte is "read," the procedure terminates (it's only looking for the first byte), and the FCLOSE is executed (and the .T. is shown on the local monitor). However, this limited window time turns out to be a VERY SUBSTANTIAL BENEFIT: It creates a clock against which time spent waiting for incoming data can be limited. This means that dBASE is capable of performing an INTERNAL measure of activity. A more comprehensive (and useful) example of how to incorporate this code fragment into a regular dBASE program will be discussed later. Problem 2: Monitoring for Carrier. --------------------------------- The can be a problem for most DOOR programs. The WATCHDOG programs provided with the RBBS utilities can help. For a door running some dBASE program(s), the better method is to design the menuing options to include time limits based on the internal timing mentioned above. Then, each menu in a "nested menu" environment (namely, where one menu calls another, which calls another, etc.) defaults to its predecessor if there is no valid response within a certain time. The top-most menu defaults to a exit routine which bails out of dBASE, returning system control back over to the *.BATch file which then re-invokes RBBS. This procedure works to bail out the inattentive remote user. The BBS is eventually returned to the re-entry request for the password, which (if ignored) itself times out. However, invocation of the protections in the WATCHDOG programs is still necessary. For instance, if the remote user (or his/her modem or software) has issued an XOFF command (an action which IS respected by the BBS modem, and is also imposed on and recognized by dBASE), the BBS would hang. In such a case, the protection of the WATCHDOG function would be necessary. Problem 3: Limitations on Full-Screen Editing. --------------------------------------------- The main benefits to full-screen editing are to permit REGRESSIVE cursor movement (from one field to another which is to its LEFT or ABOVE), or to permit viewing subsequent fields while editing prior ones. If the remote user is employing ANSI graphics, this problem can be overcome by addressing specific screen locations through commands imbedded within the dBASE program. This would be a laborious programming task, and requires that the remote user have ANSI graphics. To accommodate non-ANSI users, either we need to detect ANSI (which RBBS already does, but this information is not passed along to the door in the DORINFO*.DEF file created when the door is opened) and supply different screen displays for ANSI and non-ANSI users, or we need to limit ourselves to writing the one-line-at-a-time displays. This latter alternative requires some special attention if certain favorite dBASE commands (especially EDIT, BROWSE, DISPLAY, LIST, APPEND) are to be emulated in the remote-access operating environment. Some suggestions and examples of these emulation techniques will be presented later. Problem 4: Security. ------------------- With adequate attention to these command alternatives, the remote user need never be given access to the dBASE "dot prompt." In combination with other safeguards (such as providing an "evacuation" program which cleanly closes all files (and procedures and indexes, etc.) upon detection of some program error, and then QUITs dBASE (reinvoking the BAT file and then RBBS), sufficient security IS possible. Problem 5: Difficulty in Learning to Use. ---------------------------------------- The trick here is to anticipate all user access/entry/retrieval needs for a particular database (or series of databases, indexes, procedures, etc.). This need not be as intimidating a task as might initially seem. It does require some careful thought and planning about the nature of the interface which you want to provide for the remote user. For instance, a series of one-byte, nested (progressive) menu selections can perform nearly all of the most common dBASE functions. Even SQL interactions and "wildcard" and Boolean-logic searches can be implemented with relatively simple dBASE code. Examples will follow. The discussion below is organized into the following topic areas: GENERAL CONSIDERATIONS - Invoking dBASE - An Evacuation Plan - Administrative Checks - Controlling the Timing - Event Logging SPECIFIC COMMAND ALTERNATIVES - Code Fragment for Single-Character Entry - Code Fragment for Multiple Character Entry - A Sample Supervisory Shell - Alternatives for Data Display SPECIAL CONSIDERATIONS FOR FILE BUILDING - Data Collection and Building - Transactional Processing CLOSING THOUGHTS ============================================================================ GENERAL CONSIDERATIONS ---------------------- INVOKING dBASE -------------- The procedures for running a DOOR are explained elsewhere in this RBBS Manual. A sample which could be used for running the dBASE program BBSDATA.PRG, located in the default directory of dBASE (the default is established in the CONFIG.DB file in the C:\DBASE directory) is the following: @ECHO OFF C: CD \DBASE DBASE BBSDATA CD \RBBS RBBS This BATch file will get you into dBASE, and upon exiting, changes back to the directory from which RBBS may be launched. AN EVACUATION PLAN ------------------ The Conscientious Camper assembles the means to douse a campfire before ever igniting it, preferably before even building it. If the SysOp is going to be a "happy camper," precautions must be taken in designing any DOOR: - to prevent unauthorized access to system files and system-level commands; - to trap errors and to allow (indeed, to FORCE) a graceful exit, should an otherwise unintentional situation develop; and, - to disconnect from any "hung" system or externally-forced rebooting. The first precaution should be to design an evacuation plan, in order to take advantage of dBASE's internal error-trapping capability. To accomplish this, this highest level program (namely, the very top one which dBASE executes upon its initial loading, BBSDATA.PRG in the above example) should include the following command: ON ERROR DO XXX where XXX is a program (XXX.PRG) which terminates the dBASE program and returns to the initiating BATch file to reinvoke RBBS. A sample of such an evacuation program is: SET PRINTER TO COM1 && This first line may not be necessary. SET PRINTER ON && Sends what follows out the COM1 port. ? CHR(7) && Causes the remote computer to BEEP. ? "That command or response is improper. Returning to the BBS function." ? "Please standby while RBBS is reloaded." CLEAR ALL && Clears all existing files, etc. QUIT && Exits dBASE. (For the novice dBASE user: In this example, the text following the &&, or ANY text or command on a line whose first non-blank character is an asterisk [*], will be ignored by dBASE. In the samples provided in this Appendix, these two means (&& and *) will be used to provide commentary within a code fragment. In addition, an ellipsis [...] will indicate that there may be additional lines of program text which may be included.) The program XXX.PRG must reside in whatever drive and subdirectory is the default within dBASE. If the default drive or directory/subdirectory is changed within the dBASE program, XXX.PRG must be on that new default as well, or else reference to the local of that program must be provided. For instance, you might want instead to use: ON ERROR DO C:\DBASE\XXX to assure that upon some error, the evacuation program can be found with certainty. There are additional lines of code which might be incorporated into this evacuation program, to log the time and date of exit, the identity of the caller, the specific Error Code, etc., to an EVENT file (to be discussed later). You could even use the Error Code to construct a specific message to be sent to the remote user or to be entered into the EVENT file. ADMINISTRATIVE CHECKS --------------------- There are many problems within dBASE which can cause the program to halt. Finding and correcting code problems should be the goal of intensive pretesting before opening up the dBASE door to general use. Most programmers expect to spend more (sometimes, MUCH MORE) time debugging and correcting programs as they spent writing them in the first place. In addition to code-based problems, there are potential bombs in the various files to be used. Are the files present where they are supposed to be? Are the indexes current? Procedures to answer these and other questions can be built into a status-checking program run as a module from the initial supervisory program (BBSDATA.PRG in the examples above), for instance using the FILE() and FDATE(): VBOMB=.F. IF .NOT. FILE("MAIN.DBF") VBOMB=.T. ENDIF IF .NOT. FILE("SAMPLE.PRG") VBOMB=.T. ENDIF ... VDATE1=FDATE("MAIN.DBF") VDATE2=FDATE("MAIN.NDX") IF .NOT. VDATE2>=VDATE1 VBOMB=.T. ENDIF ... IF VBOMB && Alternatively, merely run one of the SET PRINTER ON && evacuation programs previously discussed. ? "A problem has been found." ? "This dBASE program will now terminate and return you to RBBS." CLEAR ALL QUIT && A customized evacuation program could log into the ENDIF && EVENT filed (discussed later) the reason for the && termination of this program. (Here and elsewhere in this discussion, there are references to program "modules," procedures, etc. For ease of checking, economy of compiling and memory management, subroutines run recurrently or even only once during the initiation of the master program are best put into these smaller programs, to be called up only when, if and as necessary.) This checking routine is best placed in its own program file, to be called up during the initial running of the supervisory program. Checking on file dates is especially important if users will be permitted (through the various supervisory programs) to add or to modify any data in the existing databases. Although indexes could be updated routinely by using dBASE IV's *.MDX resources (where all indexes for a particular database are maintained, even if not all are actually used for a particular procedure), there may be other reasons to call up only one index at a time. This risks leaving others out of date, which could cause dBASE to crash. CONTROLLING THE TIMING ---------------------- RBBS has a wealth of provisions for controlling timing (through per-session and per-day limits, and through banked timing) and access (through security levels and passwords.) However, once a caller leaves the supervision of RBBS, these controls are no longer available DIRECTLY. If the SysOp wishes to design a DOOR (in this case, a collection of dBASE programs and their associated files) which incorporates constraints which are otherwise located within RBBS, then some form of interface is needed. Fortunately, RBBS provides a limited interface, at least insofar as passing information TO the DOOR. RBBS creates a text file (DORINFO#.DEF, where # is the node number) each time a DOOR is opened. (See section 14.2.2 of the RBBS Manual.) From within dBASE, information from this text file can be queried. One way is to use a transactional database (for holding temporary data), such as one with the following, ultra-simple structure: Field Field Name Type Width Dec 1 TEXT_LINE Character 30 For purposes of this example, this database file will be named TRANSACT.DBF. The DORINFO#.DEF file contains 13 lines, each terminated with a . The TRANSACT database can be populated (after first being emptied of its prior contents!) by the following code: USE TRANSACT SET SAFETY OFF && Allows a *.DBF file to be ZAPped without query. ZAP && Erases the file's current contents. SET SAFETY ON && If you wish to reinvoke the SAFETY protection. APPEND FROM C:\RBBS\DORINFO#.DBF SDF && Where # is the node number. For particular information about the instant caller, you can choose the appropriate record number within this newly built file. For instance, record 7 has the caller's first name, record 8 has the caller's last name, record 11 has the caller's security level within RBBS, record 12 has the caller's maximum time permitted in the DOOR, etc. This information can be built into memory variables held within dBASE while the program remains active. This would permit periodically recalculating the elapsed time of the current dBASE invocation (for instance, as a user- transparent function in conjunction with some other recurrent event, such as menu selection), and terminating dBASE (and returning to RBBS) after the limit permissible for that user (or security level, etc.) had been reached. A SECOND ASPECT of controlling the timing aspects of a dBASE DOOR pertains to caller responses and responsiveness to menus. In examples to presented later, menus should be written to specify the defaults (for no correct or timely entry). An absence of a timely entry should force the program back to prior menu. With successive regressions, the properly designed programs will eventually return to the top, and then terminate. This substitutes for the "no response" protections within RBBS (which terminate the connection). (However, the protections of a WATCHDOG-type program should still be used.) EVENT LOGGING ------------- Some of the "temporary" information discussed above is worthy of being preserved even after the DOOR has closed and the instant connect session has terminated. For this, an EVENT file should be constructed. As an RBBS SysOp, you are probably familiar with the activity log in the CALLERS file. Extended logging is also available (parameter 91 in the RBBS configuration). However, once you are in a DOOR, nothing further is added to that log until the DOOR terminates and the RBBS program is reinvoked. An event logging file (an RBBS-independent database) is easy to set up and run within dBASE. The following file structure is suggested, with the file name EVENT.DBF: Field Field Name Type Width Dec 1 USER_ID Character 20 User's first and last name. 2 START_DATE Date 8 The date of program initiation. 3 START_TIME Character 8 The time of program initiation. 4 START_BALA Numeric 4 0 Beginning time balance. 5 EVENT_DESC Character 60 Text line to describe the event. 6 EVENT_TIME Character 8 Time of the event. 7 STOP_DATE Date 8 The date of program conclusion. 8 STOP_TIME Character 8 The time of program conclusion. 9 ELAPSED_T Numeric 5 0 dBASE-calculated elapsed time. 10 STOP_BALA Numeric 5 0 Balance of banked time. 11 TRIG1 Numeric 1 0 A processing field (for later use offline). This file can be initially set up (after the TRANSACT.DBF has been updated) with the following type of code. CLOSE ALL && Clears out any existing files. USE EVENT SELECT 2 USE TRANSACT 7 && Selects the record with caller's first name. VCFN=TRIM(TEXT_LINE) && Builds the VCFN memvar (memory variable) 8 && Selects the record with caller's last name. VCLN=TRIM(TEXT_LINE) && Builds the VCLN memvar. 12 && Selects the record with the remaining time. VCTB=TRIM(TEXT_LINE) && Builds the VCTB memvar. USE SELECT 1 APPEND BLANK && Sets up the database for incoming data. REPLACE USER_ID WITH VCFN+" "+VCLN && Enters the caller's full name. REPLACE START_BALA WITH VAL(VCTB) && Enters the time balance (minutes). && The above line assumes that the time is derived from the && DORINFO#.DEF file. If the time balance were to be derived && instead from within this EVENT database, you could backscroll && to find the remaining balance from the prior session for this && caller. Alternatively, you could maintain a separate ACCOUNTS && database from which to obtain this value. REPLACE START_DATE WITH DATE() && Enters the session starting date. VCTS=TIME() && Records the start time into memvar. REPLACE START_TIME WITH VCTS && Enters the session starting time. CLOSE ALL && Clears out any existing files. The program will later need to recall a memory variable giving the starting time of the session. This is why that variable (VCTS) is built now, rather than merely entering the starting time from the TIME() quest initially. For recording significant events (however you wish to define them!) which occur DURING the program run, the following code fragment could be used: SELECT 9 && Or some user area not likely to be used for other files. USE EVENT APPEND BLANK REPLACE EVENT_TIME WITH TIME() REPLACE EVENT_DESC WITH "A significant event (etc.) .." && To describe. USE SELECT 1 && To return to the prior program and file. Note that the caller's name is NOT built into this record. By building in the caller's name ONLY in the very first and last records pertaining to the particular connect session, it will be easier to BROWSE the EVENT file later, for instance to scan the logging functions, to calculate elapsed time, etc. RBBS is suspended during the operation of the DOOR, and cannot control the timing. This will have to be done within dBASE itself. At particular points DURING the connect session (perhaps as often as just prior to each menu presentation), the following code fragment could be used to notify the caller of the time remaining, or to terminate if the balance has dropped to or below zero: VCNOW=TIME() && Reads the current time into a memvar (HH:MM:SS). VNNOW=60*(VAL(SUBSTR(VCNOW,1,2)))+VAL(SUBSTR(VCNOW,4,2)) && Converts this into the MINUTE of the day. VNTB=VAL(VCTB) && Converts the remaining time balance from RBBS into && numeric variable, if this hasn't already been done. VNTS=VAL(VCTS) && Converts the session starting time into a numeric && variable, if this hasn't already been done. IF VNNOW5 SET PRINTER ON ? "You have " ?? LTRIM(STR(VNREM)) && This removes the leading blanks of a && numeric variable. ?? " minutes remaining in this session." CASE VNREM<1 ? "You have exhausted the time available for this DOOR." ? "Returning you now to the original BBS." && At this point of FORCED exit, you might want to && include the code fragment in the next example, && or run a program which constitutes this fragment, && possibly changing the EVENT_DESC to "Timed out." CASE VNREM<=5 ? "CAUTION: You have only " ?? LTRIM(STR(VNREM)) ?? " minutes left in this session." ? "Please plan to exit soon. Thanks." ENDCASE The code fragment above would be a candidate for including in its own program, since the very same code would be called up over and over again. NOTE that the second CASE statement (VNREM<1) also meets the qualifications of the third CASE statement (VNREM<=5). In the CASE statements in the numerous examples to follow, it is important to prioritize their order so that the more important are executed first. Upon a caller-requested termination of the DOOR, the following code might be used: CLOSE ALL && Clears out any existing files. USE EVENT APPEND BLANK && Sets up the database for incoming data. REPLACE USER_ID WITH VCFN+" "+VCLN && Enters the caller's full name. VCNOW=TIME() && Reads the current time into a memvar (HH:MM:SS). VNNOW=60*(VAL(SUBSTR(VCNOW,1,2)))+VAL(SUBSTR(VCNOW,4,2)) && Converts this into the MINUTE of the day. VNTB=VAL(VCTB) && Converts the remaining time balance from RBBS into && numeric variable, if this hasn't already been done. VNTS=VAL(VCTS) && Converts the session starting time into a numeric && variable, if this hasn't already been done. IF VNNOW] to signal an end-of-line or end-of-entry mark. ****************************************************************** ** ** ** It must be emphasized here that the goal of these code ** ** fragments is not to establish a remote dBASE operation. ** ** Instead, the author's goal has been to develop and provide ** ** a USER INTERFACE for accessing a series of databases, ** ** presenting summarized or comprehensive information but ** ** presuming no particular foreknowledge or caller expertise ** ** in running dBASE. ** ** ** ****************************************************************** CODE FRAGMENT FOR PAUSING OR SINGLE-CHARACTER ENTRY --------------------------------------------------- In displaying data (for instance, with dBASE's DISPLAY command), or in reviewing text materials (library lists, bulletins, messages, etc.) through RBBS, the caller is accustomed to viewing only one screen of information at a time. Within RBBS, continuous scrolling can be requested after the first screen display (using the C [CONTINUOUS] or N [NON-STOP] commands). Within dBASE, no such switch to continuous scrolling is available. Instead, the user would have to replace the DISPLAY command with the LIST command. (As much as SysOps might encourage callers to capture the screen displays to a local log file, for later reviewing or printout, many callers don't. Some don't even know how to toggle the capture function on and off within their local communications packages.) The discussion of programming code below will start with an example (in full context) of how a SINGLE-key entry program might look. Single-key entry within dBASE would be used to resume scrolling within the DISPLAY command, or for pausing or collecting but a single byte with the WAIT command. The primary problem is going to be how to collect user input coming in through the COM1 port. (NOTE: A would be a second byte to capture. The single-key code below does not require the remote caller to enter a following the single-letter command.) The sample below is actually typical of what the SysOp might want to provide for a caller to review the contents of an existing, indexed database. The original dBASE IV command, assuming entry from the local keyboard (at the "dot prompt") or from within a locally run program would have been: DISPLAY OFF FIELD1,FIELD2,FIELD3 WHILE FIELDX="&VCSTRING" where FIELD1, FIELD2, FIELD3, and FIELDX are field names in this database which is indexed on FIELDX. (FIELDX could be the same as one of the other fields.) The string being sought is contained in the memory variable VCSTRING. Under this command within dBASE IV, the scrolling would pause after 20 records (23 records if the STATUS line is OFF) for the first screen, and after 21 records (24 records if the STATUS line if OFF) for each subsequent screen. This assumes that the three fields from each record total not more than can be displayed on a single line (namely, that they total less than 78 characters plus the two intercolumn spaces). The data would be displayed in specific columns (a desirable format for visual scanning). (In a later section, "Alternatives for Data Display," we will discuss alternatives to the dBASE LIST and DISPLAY commands, for instance for presenting only a truncated field, but still retaining the columnar format.) The code described below was excerpted from a program which displays the same information. This code modestly enhances the dBASE command above by adding an option for continuous scrolling (such as is offered within RBBS). FIND &VCSTRING IF FOUND() SET TALK OFF && If this hadn't been done much earlier! VLCONT=.T. && Initializes this logic variable. *************** Beginning of the display loop. **************** DO WHILE VLCONT SET PRINTER ON && To send data to the COM1 port. DISPLAY FIELD1,FIELD2,FIELD3 OFF NEXT 15 WHILE FIELDX="DSTRING" SKIP VCSTRING2=FIELDX && Determines if the next record also fits. IF VCSTRING=VCSTRING2 ? "A)bandon, N)on-stop display, or to continue with " ?? "pauses [default]: " SET PRINTER OFF VCPAUSE="" VNREP=30 DO WHILE VNREP>0 && Begins the timing loop for a response. VNHANDLE=FOPEN("COM1","R") && Opens handle for incoming. VCPAUSE=FREAD(VNHANDLE,1) && Creates memvar for incoming. ? FCLOSE(VNHANDLE) && Closes the handle. VNREP=VNREP-1 && Counts down for timing. DO CASE CASE UPPER(VCPAUSE)="A" ?? VNREP && Shows countdown status on local. SET PRINTER ON && Echoes entry to remote, sends ?? "A" && message acknowledging. ? "Search sequence voluntarily abandoned." ? "==================================================" VLCONT=.F. && Terminates the timer looping. EXIT CASE VCPAUSE="$" VNREP=30 && Resets the count-down timer. ?? "$" && Echoes response to local screen. ??? "$" && Echoes response to remote screen. CASE UPPER(VCPAUSE)="N" && Request for non-stop display. SET PRINTER ON LIST FIELD1,FIELD2,FIELD3 OFF REST WHILE FIELDX="DSTRING" VLCONT=.F. && After non-stop display, quits loop. EXIT CASE LEN(VCPAUSE)>0 && Any alphanumeric response. SET PRINTER ON IF VCPAUSE>=" " .AND. VCPAUSE<="~" ?? VCPAUSE && Echoes response to local screen. ??? VCPAUSE && Echoes response to remote screen. ENDIF VLCONT=.T. EXIT CASE VNREP=5 && Warning of imminent timeout. SET PRINTER ON ? "Scrolling again in less than 10 seconds. " ?? "Enter `$' for more time: " SET PRINTER OFF OTHERWISE ?? VNREP && Shows count-down status on local. ENDCASE ENDDO SET PRINTER ON ELSE EXIT ENDIF ENDDO ****************** End of the display loop. ******************* ************************ Final pause. *********************** IF .NOT. UPPER(VCPAUSE)="A" ? "===========================================================" ? "Press any key to continue (or `$' for more time). " SET PRINTER OFF && Begins the timing loop for a response. VCPAUSE="" VNREP=30 DO WHILE VNREP>0 VNHANDLE=FOPEN("COM1","R") && Opens handle for incoming. VCPAUSE=FREAD(VNHANDLE,1) && Creates memvar for incoming. ? FCLOSE(VNHANDLE) && Closes the handle. VNREP=VNREP-1 && Counts down for timing. DO CASE CASE VCPAUSE="$" VNREP=30 && Resets the count-down timer. ?? "$" && Echoes response to local screen. ??? "$" && Echoes response to remote screen. CASE LEN(VCPAUSE)>0 && Any alphanumeric response. ?? VNREP SET PRINTER ON IF VCPAUSE>=" " .AND. VCPAUSE<="~" ?? VCPAUSE && Echoes response to local screen. ??? VCPAUSE && Echoes response to remote screen. ENDIF EXIT CASE VNREP=5 && Warning of imminent timeout. SET PRINTER ON ? "Continuing in less than 10 seconds. " ?? "Enter `$' for more time: " SET PRIN OFF OTHERWISE ?? VNREP && Shows count-down status on local. ENDCASE ENDDO SET PRINTER ON ENDIF ELSE ? "No qualifying records with " ?? VCSTRING ?? " were found." VNDELAY=0 && An alternative delay loop, needing no DO WHILE VNDELAY<10000 && caller response to recycle. The length VNDELAY=VNDELAY+1 && of time is determined by the 10000 count- ENDDO && down value, and should be adjusted for ENDIF && your own system's operating speed. In this code, the display on the local (BBS) screen will contain additional information not transmitted to the remote user. For instance, the value of VNREP, as it decreases in the countdown loop roughly every 2 seconds, is displayed to the BBS screen following the .T. verification of each successful closing [? FCLOSE(VNHANDLE)] of the file handle. This will result in the menu scrolling off the local screen if the caller's response is slow. In this single-character-command example, this off-scrolling could be fixed by replacing the single "?" with "??" [?? FCLOSE(VNHANDLE)]. The problem is not so easily addressed in a multiple-character-input setting, discussed later. The display presented on the local screen will also vary from that which the RBBS SysOp normally expects. For instance, there is no identification of the caller or the session starting time. Furthermore, there is no opportunity provided to the SysOp to intervene in the dBASE program, or to CHAT with the remote user. The SysOp could leave the program susceptible to a local ESCAPE command. (It is not necessary for the program to include the normal SET ESCAPE OFF command, because the remote user's input coming in through the COM1 port can't pass an ESCAPE command into the BBS.) However, this would not accomplish the conventional CHAT function. Namely, outside of the specifically implemented data-collection loops, dBASE won't display caller input (received through the COM1 port), nor will the SysOp's locally typed comments be transmitted out the COM1 port unless SET PRINTER ON is in effect. Therefore, implementation of a CHAT function within dBASE would not be easy, and has not been attempted in the examples shown here. To test this code in an actual application, you will need TWO computers (and their individual screens) linked through their respective COM ports. It would be preferable that they actually be linked through their own modems, so that you can view the screen display speed and check for other variances in the critical timing sequences for data input through the modems. (The author has not attempted to run both local and remote nodes simultaneously on the same computer (split screen) through MS Windows, DesqVIEW, or any other multiple-processing program.) CODE FRAGMENT FOR MULTIPLE-CHARACTER ENTRY ------------------------------------------ Multiple-character entry introduces several complexities not encountered with mere single-character entry: 1) how to echo back to the remote caller; 2) how to display to the local BBS screen; 3) how to provide a "destructive backspace" to the caller; and, 4) how to develop a substitute for dBASE's PICTURE provisions (to interactively filter the nature of the input, or to check for its correct format). The general rule is: EMPLOY SINGLE-CHARACTER ENTRY WHEREVER POSSIBLE, for instance in conjunction with menus and single-letter commands. Consider the multiple-character format for single-character entries ONLY if your callers want to delay executing the single-character command until a is sent, or if they need to be able to backspace/delete that single- character command. Multiple-character entry will be unavoidable for establishing search strings, and for data input (if your system allows user building or modification of existing databases). (The particular problems and opportunities for data-BUILDING will be discussed in a later section.) Within a conventional dBASE program (run from the local keyboard, displaying to the local screen), the command of creating a memory variable is: ACCEPT "Enter the search name: " TO VCSTRING Additional modifiers and PICTURE constraints can be added to limit the length and format of this memory variable. Unfortunately, such a simplified method of collecting input from the remote caller (when running dBASE within RBBS) is not possible. Instead, the character string to be built (VCSTRING) must be assembled byte by byte, and then terminated, before the dBASE program can be commanded to begin its search and data display. The following code demonstrates how such a memory variable could be built, for instance to be used later to display information to the caller's screen following the general program format demonstrated previously. This code was excerpted from a program which requests the name for which to search in a database indexed on a SINGLE field containing the personal name in the following format: LASTNAME, FIRSTNAME (A different procedure would be needed if your database has separate fields for the first and last names.) The index is built on the UPPER CASE of this single field. The actual searching and display could be controlled by the single-key example previously given. (Namely, the continuity of the display scrolling could be controlled by just a single-key routine.) This code fragment should be located in a program which is secondary to the supervisory program and menu. That way, if there is no appropriate caller response, control will revert to that prior program. (This prevents hanging if there is a loss of carrier, the caller has a heart attack, or some other contingency.) SET PRINTER ON ? "Enter the name of the person for whom to search, using the format:" ? ? " Lastname, Firstname" ? ? "The letters do not need to be capitalized. You must enter AT LEAST the" ? "first FOUR characters. If the last name has less than four characters," ? "then add the comma, and if necessary the space AND the beginning of the" ? "first name as well." ? ? "Name: " VCNAME2="" && Initializes the memvar. VNREP=30 DO WHILE VNREP>0 SET PRINTER OFF VNHANDLE=FOPEN("COM1","R") && The process of receiving VCNAME1=FREAD(VNHANDLE,1) && input from the remote ? FCLOSE(VNHANDLE) && caller. VNREP=VNREP-1 DO CASE CASE (VCNAME1=CHR(13) .OR. VCNAME1=" ") .AND. LEN(VCNAME2)=0 CLOSE ALL && Cancels the search because RETURN && of a leading space or . CASE VCNAME1=CHR(13) .AND. LEN(VCNAME2)>3 && Closes the build. EXIT CASE LEN(VCNAME2)>21 && Closes the build for max length. EXIT CASE VCNAME1="$" && Restart timer, entry. VNREP=30 VCNAME2="" SET PRINTER ON ?? "$" ? ? "OK, your request has been deleted." ? ? "Please start again: " SET PRINTER OFF CASE VCNAME1=CHR(08) .AND. LEN(VCNAME2)>0 && Destructive backspace. VNLEN=LEN(VCNAME2) VCNAME2=SUBSTR(VCNAME2,1,VNLEN-1) && Shortens the memvar. ??? CHR(08) && Backspaces caller screen. ?? VNREP ?? " " ?? VCNAME2 && Redisplays on local. CASE (VCNAME1=" " .OR. VCNAME1="." .OR. VCNAME1=","; .OR. VCNAME1="'") .AND. LEN(VCNAME2)>0 && Permits certain VCNAME2=VCNAME2+VCNAME1 && punctuation marks, but VNREP=VNREP+1 && only if not the first. ?? VNREP && Shows countdown status. ?? " " ?? VCNAME2 && Redisplays on local. ??? VCNAME1 && Echoes to remote. CASE UPPER(VCNAME1)>="A" .AND. upper(VCNAME1)<="Z" VCNAME2=VCNAME2+UPPER(VCNAME1) && Permits regular letters. VNREP=VNREP+1 ?? VNREP && Shows countdown status. ?? " " ?? VCNAME2 && Redisplays on local. ??? VCNAME1 && Echoes to remote. CASE VNREP=8 .AND. LEN(VCNAME2)>0 && Timeout warning. SET PRINTER ON ? CHR(7) ? "CAUTION: You have less than 15 seconds to complete your " ?? "command and enter" ? " , or $ to erase and restart the timing sequence." ? ? "Name: " ?? VCNAME2 SET PRINTER OFF CASE VNREP=5 .AND. LEN(VCNAME2)=0 && Non-response warning. SET PRINTER ON ? ? "CAUTION: In the absence of any entry, the program is about " ?? "to recycle back" ? " to the prior menu. To restart the timing clock, " ?? "enter $." ? ? "Name: " SET PRINTER OFF OTHERWISE ?? VNREP ENDCASE ENDDO VCNAME2=TRIM(VCNAME2) IF LEN(VCNAME2)=0 && Returns to the prior program if there no CLOSE ALL && appropriate caller input. RETURN ENDIF Again, there will be material displayed on the local (BBS) screen which is not sent (or is sent differently) to the remote caller. As the value of VNREP decreases in the countdown loop (once about every 2 seconds), the counts remaining will be displayed locally. In addition, the string stored within the memory variable VCNAME2 is also displayed on each line, in its entirety. At the remote terminal, only the most recent character (VCNAME1) is added after each countdown loop, but the prior characters are still on the same line. (This is the screen presentation which the user would expect within the conventional RBBS program, for instance.) The difference, from the user's standpoint, is that illegal characters (for instance, leading spaces or punctuation) are not presented at all, although they DO continue the countdown sequence. Also, letter case is preserved on the remote screen, even though built only in upper case on the local screen. Additional filtering can also be added by extending the DO CASE/CASE/ [OTHERWISE/] ENDCASE string, or by adding yet another such string after closure of the DO WHILE/ENDDO loop. Extravagant filtering of user input would be possible with such extensions. However, CASE-based evaluations must still be prioritized in your program code. A SAMPLE SUPERVISORY SHELL -------------------------- You should run a "supervisory shell" to call up each of the other dBASE programs, rather than to attempt to run everything in one monstrously big program. Departing a subordinate program can be much easier (RETURN) in the case of some impermissible response or terminating condition, than accurately getting precisely to the end of a series of nested DO WHILE/ENDDO loops. The supervisory shell would be that program called up initially when entering dBASE (BBSDATA.PRG in the example provided earlier). It might be as simple as the following: SET TALK OFF SET DEFAULT TO X: && If a different default directory is to be used. SET STATUS OFF SET SCOREBOARD OFF ON ERROR DO XXX && Your evacuation program. DO CHKBOMB && A subordinate program to check file existence, && integrity and date. (Discussed earlier) DO SETFIRST && You could put your "setup" commands here, for && instance to read DORINFO#.DEF, populate the && EVENT file, compute remaining time, etc. VLMENU=.T. SET PRINTER TO COM1 && Redirects specified text to the COM1 port. DO WHILE VLMENU SET PRINTER ON DO TIMELOOK && You could compute remaining session time here && and display it to the caller. CLEAR ? "You may select from the following options:" ? ? " A: (describe first option)" ? " B: (describe second option)" ... ? " Z: (describe last option)" ? ? " or @: To exit this database search and return to the BBS. ? ? "Your selection (you have 60 seconds to decide): " VCSELECT="" VNREP=30 DO WHILE VNREP>0 VNHANDLE=FOPEN("COM1","R") && Opens handle for incoming. VCSELECT=FREAD(VNHANDLE,1) && Creates memvar for incoming. ? FCLOSE(VNHANDLE) && Closes the handle. VNREP=VNREP-1 && Counts down for timing. DO CASE CASE VCSELECT="@" .OR. VCSELECT=CHR(13) CLEAR ALL VLMENU=.F. EXIT CASE UPPER(VCSELECT)="A" ?? VNREP && Shows countdown status on local. ?? VCSELECT ??? VCSELECT DO TASKA && The program (TASKA.PRG) to be executed. EXIT CASE UPPER(VCSELECT)="B" ?? VNREP && Shows countdown status on local. ?? VCSELECT ??? VCSELECT DO TASKB && The program (TASKB.PRG) to be executed. EXIT ... CASE UPPER(VCSELECT)="Z" ?? VNREP && Shows countdown status on local. ?? VCSELECT ??? VCSELECT DO TASKZ && The program (TASKZ.PRG) to be executed. EXIT CASE VCSELECT="$" VNREP=30 && Resets the count-down timer. ?? "$" && Echoes response to local screen. ??? "$" && Echoes response to remote screen. CASE VNREP=5 && Warning of imminent timeout. SET PRINTER ON ? "You have less than 10 seconds to request from this menu," ? "or to enter `$' for more time" ? "Your selection: " SET PRINTER OFF OTHERWISE ?? VNREP && Shows count-down status on local. ENDCASE ENDDO ENDDO DO CLEANUP && This would be your final "clean out" program (user logout && with time, annotation of "A normal exit", etc.), as && previously discussed. CLEAR ALL QUIT As discussed in the section "Code Fragment for Single-Character Entry", the display on the BBS local screen will contain information which is not sent out to the remote caller. ALTERNATIVES FOR DATA DISPLAY ----------------------------- In the sample code fragment for single-character entries shown above, the dBASE commands DISPLAY and LIST were used. These may not be satisfactory if the length of the fields (or the field names, whichever is greater) being transmitted to the remote caller are cumulatively more than 79 characters per record (for the specified fields). Some communications programs prefer to receive even NARROWER screen displays, for instance interjecting a left-most character to indicate that the logging or capture function has been activated, or displaying the incoming information only in a column-limited window on the remote caller's screen. Furthermore, the SysOp may prefer to tailor the field presentations, truncating some, substituting text (for record-imbedded codes) in others, even opting not to transmit certain records (or certain portions of certain records) at all, based on some uniquely identifiable contents. The displays transmitted to the remote caller can be individually tailored. ANSI commands to specify screen locations could be sent, but this would be confusing to non-ANSI-equipped callers. (It would also increase transfer time!) On the other hand, one-line-at-a-time (strictly column-delimited) transmissions would be universally perceived: all of the information would be neatly arranged. If the SysOp wishes to customize the displays, then the code fragments previously cited may need to be modified to add a record counter, to replace the record-counting capability internal to the dBASE commands (for example, LIST NEXT 15). Column headings (to replace dBASE field names, which are sometimes cryptic anyway!) could be introduced, and other "niceties" for the remote caller could be added (summation of all qualifying records found, suggestions for alternative text string searches, etc.) If the database(s) being accessed through the RBBS DOOR have numerous or lengthy fields, the displays limited to the 79- or 80-characters might best be used to show a summary of all qualifying records, along with some unique identifier for each. The caller can then request a customized display of just one particular record by that unique (indexed) identifier. ============================================================================ SPECIAL CONSIDERATIONS FOR FILE BUILDING ---------------------------------------- DATA COLLECTION AND BUILDING ---------------------------- The code fragments previously cited convey information in an existing database, but have no provisions for modifying existing records or appending new ones. The BROWSE and EDIT functions within dBASE cannot be fully emulated (to allow data modification or addition), because their cursor- and window-movement capabilities have no conventional ASCII equivalents, and thus cannot be conveyed through the code fragments and methods cited above. (Although dBASE can write directly to the screen and other hardware, the commands to do so cannot be conveyed directly by any ASCII code THROUGH THE MODEM.) A simpler (if less elegant) procedure could be employed, if the SysOp really wants to permit such data-modifying privileges to the remote caller. A one-field-at-a-time replacement by a character string sent by the remote caller could be implemented. For instance, the contents of particular record could be displayed, with each field assigned a single-character identifier. The caller could be queried as to which field (if any) (by identifier) was to be modified, and then accept the caller's subsequent character string. This one-field-at-a- time editing procedure would be much slower than conventional editing within dBASE, but works around the communications limitation cited above. (Remember that during the entirety of the dBASE run, the communications into and out from the computer are limited by dBASE's own capabilities, as well as by the limitations imposed by the modems themselves! The former are far more restrictive than the latter.) TRANSACTIONAL PROCESSING ------------------------ The SysOp may want to place some constraints on the kind of data modification or addition which can be done by a remote caller. This can be accomplished by collecting the new or modified data not in the original database, but in a subordinate one for later review by the SysOp before overwriting the original. The original record can be written to a single-record transactional database, edited as described above, and then appended to the collection of other records being held for SysOp consideration. This method of data collection and modification is highly recommended for any database system open for general public use. The SYSOP would be able to directly supervise any modifications to the original database. ============================================================================ CLOSING THOUGHTS ---------------- Some of the discussions above are rather complex. The program fragments cited are subject to further enhancements and improvements. If you want to see a dBASE door in actual operation, call the PRSG BBS in Ann Arbor, Michigan (313/995-2100) and request temporary user privileges. The SysOp can provide you with actual program code (which is somewhat more lengthy than just the examples cited above) to demonstrate how certain special features and enhancements can be added. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++