(Base One logo) .NET database and distributed computing tools

Base One Command Processor
 
Run complex scripts that include SQL and DOS commands
 

Sample Command File Document Screen - Script Editing
Sample Command File Document Screen  (Script Editing)

The Command Processor is an invaluable tool for programmers, operators, database administrators, and sophisticated end users. It runs command files (scripts) that can include SQL, DOS, and Windows commands, plus special BFC database commands, for example, for viewing, loading and unloading large databases. 

The Command Processor is especially useful for creating, maintaining, upgrading, and extracting data from complex databases. You can also enter and execute commands, one at a time, directly in the Command Processor Dialog. This provides a fast way to do simple operations and to try out the wide variety of database commands.


Sample Command Processor Dialog
Sample Command Processor Dialog

Command files can be run interactively, using the Command Processor Dialog, and can also be run (without change) in unattended "batch" mode, using the Distributed Batch Processing Services. (Any Windows computer can be turned into a Batch Job Server that can run command files as part of job streams handled by the distributed computing infrastructure.) The databases being manipulated can be local (2-tier) or remote (3-tier), through the Base One Internet Server (BIS).

Most database actions can be performed through command files, so they are especially useful for storing sequences of commands to be executed repeatedly. A command file is a reusable, editable text file that is easy to read and understand. Nested command files with comments are supported. By using the "DOS" command, for example, you can include complete DOS command lines and run utilities and existing application programs (and .BAT files) as part of the script. Having a command file means the sequence of steps doesn't have to be recreated each time it needs to be run.


The Command Processor can be used across the Internet - without the inefficiency and security limitations of trying to do serious administration and data processing through "screen scraping" remote control and database vendor supplied utilities. With BIS, programmers and operators can create, launch and monitor database scripts remotely, over the Internet - without the a browser. In combination with Base One's Batch Job Server (Distributed Batch Processing Services), it's practical to do secure, automated handling of information exchange and data mining for sensitive, large-scale production systems.

The extraordinary power of the Command Processor comes from its integration with the core Data Dictionary, security, and distributed computing features.

For example, this allows BFC to support simple commands for automated database creation including building tables, indexes, referential constraints, and granting of access rights to individual users and groups. The bottom line is less programming, easier maintenance, and fewer errors.

Tracing Command Processor Execution

To see exactly what's being passed to the database engine, use the Trace facility. You can Trace execution by the Command Processor, and this allows display of all database function calls, the values of passed parameters, SQL statements, and processing start and completion times. 


Open Command File

Open Command File

  • Command files can be viewed in any text editor (File > New > Command File)
  • Used for editing, saving and running the command file

BFC supplies command files used for both installing a release and for upgrading from one BFC release to the next. BFC uses the Command Processor to build the application database, load the built-in record types, and load the sample data. The Command Processor provides facilities for automatically creating a database from scratch, that is, from the back-end independent data dictionary. The Command Processor supports commands which can be used for database creation instead of using hard-coded CREATE TABLE, etc. This eliminates the need for extensive command files for creating tables. 

Starting from a back-end independent data dictionary layout the Command Processor can create the back-end dependent SQL needed to build a new database (CREATE TABLE, CREATE INDEX, ALTER TABLE, GRANT, etc.). The Command Processor facilities include commands for granting rights to particular record types or regions to individual users and groups of users.

One immediate benefit of the Command Processor is its automatic handling of errors. If an error occurs, for example, a complaint such as "Duplicate Data", a useful, detailed message is produced. This error message displays, for example, the SQL being executed, the names of the database fields in the unique constraint causing the trouble, and the script file and line number where the problem occurred.

The Command Processor allows for the creation of an output command file listing all commands that will be executed when the command file is executed. Macros and nested command files are expanded for a total picture of what will happen at run time. All statements are listed, but are not executed, providing a tool for understanding, testing, and maintaining command files.

The low-level programmer's (as opposed to operator's) interface to the command processor is the Command Processor class (clsCmdProc) (as opposed to dialog). The Command Processor class is derived directly from MFC CObject and is used to execute commands directly or to run files containing commands. (Many of the commands used in command files are available directly as C++ functions, with equivalent parameters.) Besides the direct MFC C++ interface, the Command Processor class can be used through COM and .NET interfaces, and there are C++, C#, Visual Basic, VB.NET, ASP and ASP.NET examples.

Studying the Command Processor is a great way to familiarize yourself with the Database Library because so many of BFC's database programming capabilities are available easily through the Command Processor.


Types of Commands Accepted by the Command Processor

The Command Processor supports a variety of commands. The processing done for a command line depends on the type of command line.

1. Common SQL commands

If the first token is one of the recognized SQL commands, the command line is passed to the SQL engine, possibly after some modest processing to simplify entry. For example, prefixing the qualifier (schema) of the table name is done automatically in the FROM clause of insert, update, and delete statements.

The recognized SQL commands are:
    INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REVOKE, and COMMIT

SELECT is not one of the directly supported SQL commands. Instead, you formulate queries and return results through Database Buffer commands (see below).

Also introduced below are some friendlier alternative Database Library commands, which can be used instead of the standard SQL commands. For example, you can use AddRec instead of INSERT, ChgRec instead of UPDATE, and CreatDbRecTyp instead of CREATE.

2. Built-in basic commands

If the first token is one of the recognized basic built-in commands, then the appropriate Command File is executed or Command Processor function is invoked. "Built-in" basic commands are an integral part of the Command Processor and are available exclusively through its command line interface.

The Command Processor's built-in basic commands include:

  • Run, GoToCmdFil, DefMacro, UndefMacro
  • OpenOutputCmdFil, ClosOutputCmdFil

The Command Processor can create an Output Command File listing all commands that will be executed when the command file is executed. Macros and nested command files are expanded for a total picture of what will happen at run time. All statements are listed, but are not executed, providing a tool for understanding, testing, and maintaining command files.

3. Built-in commands that use the Data Dictionary

If the first token is one of the recognized "built-in" commands that take advantage of the presence of the Data Dictionary that is part of the Rich Client architecture, then the appropriate Command Processor function is invoked in combination with access to the current database's data dictionary.

The Command Processor's built-in commands that use the Data Dictionary include:

  • LoadRecs, UnloadRecs, DefMacroFromDb
  • CreatDbRecTyp, DropDbRecTyp, CreatDbIdx, DropDbIdx
  • CreatDbRefIntegrity, DropDbRefIntegrity
  • GrantDbRights, RevokeDbRights

4. DOS and WIN command lines

If the first token is "DOS" or "WIN", then the rest of the command line is passed for DOS or Windows command line execution. (For DOS paths, because backslash, \, is considered the general "escape" character, remember to double any backslashes, \\, and the Command Processor will automatically convert them back into a single backslash, \, before passing the command line on for execution).

5. Error suppression commands

If the first token is SuppressDbErrsBegin or SuppressDbErrsEnd, then this command line marks the start or the end of a sequence of commands in which all but fatal errors are ignored, and only the most severe errors cause processing to stop.

6. Database buffer commands

A database buffer represents an area of main memory used for retrieving data from and transmitting data to a database. For each type of database buffer, there is a separate Create command (e.g. CreatRecBuf, CreatRecSetScrlCacheBuf), CreatProcBuf), which appears first on the command line, and the second token is the name to be given to the new database buffer. Thereafter, if the first token of a command line is a user-created Database Buffer Name, then this must be followed by "dot", i.e. ".", followed by a function name that is valid for the type of Database Buffer. The rest of the command line is treated as parameters to that function. Most operations on a database buffer require using the "BufferName - dot - command" syntax (e.g. "recStudent.GetNextRec").

The Database Buffer commands are described in the Database Library manual. The ones that can be used in a Command processor command line include:

  • CreatRecBuf, CreatRecSetBaseBuf, CreatRecSetScrlCacheBuf
  • CreatProcBuf, CreatRecSetFilBuf
  • InitRec, InitRecSet, InitProc, InitRecSetFil
  • AddRec, ChgRec, DelRec, SetData, SetParamData, SetPrepareDbCmdFlag
  • ExecProc, BuildRecSetFil, WriteRec
  • GetRecSet, GetRec, GetFirstRec, GetLastRec, GetNextRec, GetPrevRec
  • SetSelItmLst, SetSelFromLst, SetSelCriteria, SetSelDistinctFlag
  • SetSelFromLstAndJoinExpr, SetGrpByColLst, SetGrpHavingCondition
  • SetSortOrder, SetSortKey, SetSearchVal, FindRec
  • GetData, GetAttObj, SetAttObj, SetDbBufNam

7. Database Manager commands

The Database Manager provides the Command Processor with most important functions for operating on databases as a whole. Whatever follows the command name in the command line is passed as parameters to the Database Manager function. There are commands for opening and closing additional databases or switching between databases. There are other useful commands, like CopyData, which copies data from one database buffer to another, DelResultSet, which is used for deleting large number of database records from a record type based on a criteria and DelRecSetFil for deleting a record set file.

The Database Manager commands are described in the Database Library manual. The ones that can be used in a Command processor command line include:

  • OpenDb, ClosDb, SwitchDb, SetDbTyp, SetLogonUsr
  • Commit, SuppressDbErrsBegin, SuppressDbErrsEnd
  • CopyData, DelResultSet, DelRecSetFil, InitDbLog, LogStr
  • StartDbTrace, StopDbTrace, SetDbTraceLvl, SetDbTraceOutputOpt

8. Custom commands

If the first token in a command line is on the list of "custom" commands in that application's "global definitions", then the associated, application-supplied, Custom Command Function is invoked, with the rest of the command line passed as a parameter. ShowStr and ShowDbBuf are sample, useful, custom display commands, and their C++ source code can be changed to suit the application. Such global definitions are not available through the BFCCOM interface, and Custom Commands are not available through BFCCOM.

Besides adding new commands, the Custom Command facility can also be used by C++ programmers to deny access to any of the known commands that can appear as the first token in a command line. The search to determine if the first token is a Custom Command precedes all other lookups. This permits the programmer to prevent the use of any command simply by making an entry that launches a custom function that returns an access-denied message.

9. Macro assignment command lines

If the first token of a command line is not recognized, and the second token is the equal sign, then the first token is taken as the name of a macro to be set with the return value of the command specified by the rest of the command line (that is, starting with the third token).

10. Any other command line

If the first token is not recognized by the Command Processor and the second token is not the equal sign, =, then the entire command line is passed without change for execution to the SQL processor of the current database.


Local Resolution of Command File Location for Security

Command Files can be run interactively, using the Command Processor Dialog, and can also be run (without change) in unattended "batch" mode, using the Distributed Batch Processing Services. The databases being manipulated can be local (2-tier) or remote (3-tier), through the Internet Server (BIS).

For security reasons, both the location of Command Files and line-by-line processing are always local to the program running that Command File. In other words, if a 2-tier, client server application is running the Command Processor Dialog, the command files are found relative to that user's machine. Similarly, executing a DOS or Win command is done on that user's computer. Although the Command Processor can be used, for example, to execute a stored procedure on a database server, there is no Command Processor mechanism for directly executing an arbitrary command line on some other machine.

The situation is the same even if the application is 3-tier. That is, Command Files are still found on the end user's machine, and DOS and Win commands still run on the end user's machine. In 3-tier mode, the Internet Server does all of a client's database access, but the Internet Server never runs a Command File supplied by the client. To allow otherwise would create a serious security exposure.

If a user submits a Batch Job that runs a Command File, then Command Files are found relative to the Batch Job Server and run there. This allows for a secure mechanism for users to run very powerful DOS command lines remotely.

Because a Batch Job Server will only run those Batch Job Functions that have been linked in programmatically, it's easy to build distributed computing systems that only permit the running of specific Command Files, which, for example, could be required to reside on specific secure servers. Since the execution of Command Files through the Distributed Batch Processing Services is asynchronous, it can support long running tasks, with human or automated review, if needed.

If it were otherwise, and remote end-users or applications could create Command Files on their machines and easily cause them to be executed remotely (for example, on a Batch Job Server or Internet Server), the entire internal network would be wide open to security violations. It also would be unreasonably difficult to administer a system that permits end users to upload arbitrary scripts (such as Command Files) to be run on machines that have independent security settings. The Command Processor is not intended as a direct substitute for remote control programs used by programmers and operations staff. That would be too much uncontrolled, general purpose power to give out freely to end users in any environment that requires secure production systems.

The local resolution of Command Files is designed to support administrators who must allow substantial remote access while strictly controlling what's being run on privileged servers. Regardless of where end-users are located, running Command Files on Batch Job Servers (using a cluster supercomputing model) provides the security and efficiency required for large-scale, high power, database applications.


Command Processor Dialog Introduction

The entire command language supported by the Command Processor class (clsCmdProc) can be used in the Command Processor dialog (dlgCmdProc). The source code for this handy dialog is a good illustration of sophisticated usage of clsCmdProc. There are three basic ways of using the Command Processor dialog:

  • the programmer's version, which allows direct editing and pausing

  • the operator's version, with a predefined title and SuccessMessage

  • the batch version, designed for remote running without intervention

Command Processor (Programmer Version)

Command Processor (Programmer Version)

  • Runs Command Files (scripts) that can include SQL, DOS, and Windows commands, plus special commands for loading and unloading large databases.
  • A standard Command File can be maintained for loading and reloading sample data.
  • Used as part of loading data, for creating the database built-in record types (tables) and also can be used for upgrading a production system between releases.
  • Commands can be typed in the command box and press <Go> to execute the command. Command Files can be made to run step-by-step by stopping after executing each command or run continuously until an error occurs (the default).

Users running the programmer's version of the Command Processor dialog have diagnostic options such as stopping on every line of a command file, ignoring errors, or issuing commands by typing them directly without using a command file at all. The operator's version of the Command Processor dialog is the same as the programmer's version except that operators cannot edit command files and the operator's version always stops on errors. It is simple for a programmer to create a custom dialog which operators use to run a particular command file. The programmer merely supplies a dialog box title and a message to display on successful completion.

Command Processor (operator version) - Rebuild BFC Sample Data

Command Processor (operator version) - Rebuild BFC Sample Data

  • Command Processor dialog can be used in three modes, the Programmer’s version for direct editing and pausing, the Operator’s version with predefined command scripts and the Batch version for running commands remotely.
  • The Operator version of the Command Processor uses a predefined title and success message and runs a predefined set of commands that implement some particular function (for example, Rebuild BFC Sample Data).

The programmer's version is launched by a menu item under the Foundation App's System menu. The operator's version is used extensively for activities found under the Administration/System Utilities, such as "Build Application Database", and "Unload Sample Data" (one of the Test Utilities). The batch version is used in the sample batch function for running a command file as a batch job.


Sample Command File Showing LoadRecs Usage

/**************************************************************************
Date last modified: 2000-05-16
File Name: IWDLSam.cmd
Module Name: InterWatch - LOAD SAMPLE Data - Command File
********************** */

// Delete all sample data if present
DELETE FROM RptSpecItm WHERE UsrCod >= 51 AND UsrCod <= 100;
DELETE FROM RptSpecMast WHERE UsrCod >= 51 AND UsrCod <= 100;
DELETE FROM UsrMast WHERE UsrCod >= 51 AND UsrCod <= 100;
DELETE FROM UsrGrp WHERE UsrGrpCod >= 51 AND UsrGrpCod <= 100;
DELETE FROM SecGrp WHERE SecGrpCod >= 51 AND SecGrpCod <= 100;

// Sample Security Group
LoadRecs SECGRP
SECGRPCOD,SECGRPNAM,
SECGRPREM;
{
51,InterWatch Guest User Privileges,
No Administrative or System Utility functions
}

// Sample User Group
LoadRecs UsrGrp
UsrGrpCod, UsrGrpNam, UsrGrpRem;
{
51,Base One/Allegiance, Programmers and Testers
}
{
52,Young and Rubicam, Sample Client
}
{
53,InterWatch Trainee Access, CMR and IAS trainees
}

// Sample Users
LoadRecs USRMAST
USRCOD,USRID,
USRSORTNAM,NAMPREFIX,
FIRSTNAM,MIDDLENAM,LASTNAM,
NAMSUFFIX,
USRPASSW,
USRGRPCOD,SECGRPCOD,USRTYPCOD,
LOGFILPATH,
(USRNOTE,AttObjTypCod);
{
51,JSMITH,
"smith, john",,
John,,Smith,
,
JSMITH,
52,51,4,
,
}
{
52,MJONES,
"jones, mary",,
Mary,,Jones,
,
MJONES,
51,1,4,
,
}


Sample Command File Document Screen

(screen shot - command file editor)


See for yourself - Order BFC (programmer's toolkit)
- includes fully functional Command Processor

BFC | Visual Studio | Database Technology | Distributed Computing


Home Products Consulting Case Studies Order Contents Contact About Us

Copyright 2012, Base One International Corporation