BR-ODBC - Installation and Instr

BR ODBC DRIVER

Installation Instructions

As Of 4/20/02

If you have just installed BR ODBC for the first time and you did so without the sample application, we strongly suggest that you uninstall BR ODBC now and reinstall it with the sample application.

Each ODBC module is tied to a specific copy of Business Rules! When the ODBC driver starts up, the licensee information from the BR product is displayed for ten seconds.

Please note that a README file may be provided which may override information in this document. Also, it is wise to check the Release Notes associated with each release.

BR ODBC Documentation:

  • ODBC_Considerations.htm

  • ODBC_Installation_Requirements.htm

  • Dictionary_Considerations.htm

  • FILE_LOCATIONS_BR_INI.htm

  • Prompted_Substitutions.htm

  • Windows_32_bit_ODBC_Manager.htm

  • Manually_Uninstalling_BR_ODBC.htm

  • Date_Processing.htm

  • Security_Support.htm

Additional Documentation:

  • Installation_Directions.htm

  • ODBC_Data_Source_Configuration.htm

  • Using_The_Context_Manager.htm

Samples using ODBC:

  • Sample_Microsoft_Excel_Query.htm

  • Sample_Microsoft_Access_Query.htm

CONTEXT\CONTEXT.WB - Library for importing and using the Context Database:

  • Importing_File_Layouts_Into_the_Context_Database.htm

  • Context_Library Functions.htm


ODBC Installation Requirements

In summary, use the sample application to build your dictionary (note next paragraph), save it aside, and create a matching BR.INI file. (The program BR_INI.WB in the TOOLS directory can help you create your BR.INI file.) Then uninstall the BR ODBC driver and sample application. Put your BR.INI and BRSERIAL.DAT files in the BR ODBC temporarary installation directory (where you have unzipped the driver package) and double click setup.

It will be necessary to use the dictionary maintenance program provided with the sample application to create your own data dictionary. To clear the sample dictionary so you may begin adding entries that describe your files, DROP (don't remove) the following sample files:

· TOOLD\APP.*

· TOOLD\CONTEXT.*

· TOOLD\FILE_APP.*

· TOOLD\RECORD.*

If your sample has ELEMENT files *remove* them. The driver doesn't use them, but the CONTEXT manager will access them if it finds them in the dictionary directory.

A dictionary consists of these related files, all of which are maintained by the CONTEXT.WB program. File names in the dictionary may include leading subdirectory names.

When you have built your dictionary, place your version of the above files in the target directory you designate to hold your dictionary (e.g. TOOLD).

Next, specify your DictionaryPath, DataPath, and DataSources in a BR.INI file as described below. Place the BR.INI file on disk 1 of the install set, replacing the sample BR.INI file. If you have a purchased a permanent license, the BRLICENSE.DAT file should replace the temporary license file on disk 1.

You are now ready to uninstall BR ODBC and reinstall it without the sample application. Be sure to first preserve a copy of your dictionary, because uninstall will remove the sample application, including the dictionary. InstallShield will copy BR.INI, BRLICENSE.DAT, and INSTALL.TXT to their target locations. You must install your dictionary after installing the BR ODBC driver, and before using the driver to access data. The only dictionary files required for distribution are those in the TOOLD directory.


Dictionary Considerations

Column names have the following restrictions:

· A column name can not contain the '%' symbol.

· No single table can have more than one column with the same name.

· Security access codes are optional (see Security at the end of this discussion).

The files that comprise the dictionary (context) are:

· TOOLD\APP

The only fields used from Application records are

Application Code

Data Directory

While some of the other fields are required by the Context Manager the data is for documentation purposes only.

· TOOLD\FILE_APP

Application Data File records define what files are used by the application. All fields are relevant. The security codes are not required unless security is being invoked. Use of security is described below.

The File Path is regarded by the driver as a subdirectory to the Data Path directory that is defined in BR.INI and which is stored in the registry.

· TOOLD\RECORD

The Record file specifies which of multiple record types is being defined. This allows for the possibility of multiple record types existing in a single file.

· TOOLD\CONTEXT

This file describes each data field in the record. Significant fields are:

Field Name

Description

Form

Length

Decimal Positions

Relative Field Location

Date Format - see Date Processing below

Key Sequence - If this field is a key field, which key segment it pertains to

for each of the key files named in the relevant File_app record.


File Locations Described by BR

File Locations Described by BR.INI

During installation, BR ODBC will install BR.INI into the Windows directory. It expects to find the following statements in the BR.INI file (on the install set):

[BR]

BR LOCATION=C:\xxxxxxxx

If the above statement is not present, the system searches WB.INI for the location of the BR (WB) executable. If not found, the following statement is generated in BR.INI during installation:

BR LOCATION=C:\Program Files\ADS\Business Rules!

The installation process will check the specified location for an existing BRSERIAL.DAT. If it finds one, and the operator indicates that it should be replaced, it renames it to BRSERIAL_DAT.BAK. Then the BRSERIAL.DAT file from the install set is copied to the directory specified as BR LOCATION.

During installation, BR ODBC expects to find the following items in the BR.INI file:

[BR]

BR LOCATION=C:\BR Location

[ODBC] (required)

DictionaryPath C:\myapp\tood (this prefixes the dictionary names)

DataPath C:\myapp (this prefixes the data file names)

(myapp is symbolic for your own pathname)Optional Statements

Include C:\MYAPP\MAIN\SOURCES (applies only during installation)

Optional Statements

Include C:\MYAPP\MAIN\SOURCES

(applies only during installation)

Substitute string1, string2

· These substitutions apply to the ODBC Driver data file path names which are comprised of DataPath & ApplicationDir & FileName, where the second and third elements come from the dictionary.

· They do not apply to dictionary path names.

· Substitutions are cumulative allowing more than one substitution to be defined at a time.

· A comma separator is required between parameters.

Substitute string

· Clears the current substitution for string.

Substitute

· Clears all prior substitute specifications.


Prompted Substitutions

Specify:

Prompt arg-string, prompt-string, filename

Where filename is the pathname of a text file with pairs of replacement values and descriptions. When arg-string is encountered within a filename during an OPEN, the user is presented with a selection list of descriptions. When a description is selected from the list, the corresponding replacement value is substituted for arg-string. Commas are required for separating parameters in the prompt statement and the value list. From that point on, the Prompt statement works like a Substitute statement.

Prompt arg-string

· Clears the setting for the indicated arg-string.

Prompt

· Clears all prior Prompt specifications.

Required Statement(s)

DataSource DataSourceName, DictionaryFileName, Application [,READONLY ]

e.g. DataSource Automotive Shipping, Context, Shipping, ReadOnly

* * * * * One DataSource statement is required for each data source. * * * * *

At the present time, the dictionary must be named CONTEXT.

Additional DataPath, DictionaryPath, and DataSource commands will replace their previous definitions with the new definition. If READONLY is specified, write access will not be provided to the data source. The READONLY parameter is not case sensitive.

EXAMPLE


The Windows 32 Bit ODBC Manager

The Windows 32 Bit ODBC Manager Process

The respective data sources will be created by the ODBC installation. If any required data sources are not specified by the BR.INI file prior to driver installation, the installer must subsequently add them to the BR.INI file and then register each such data source (application name) via the 32 Bit ODBC Administrator (on the Windows Control Panel). During this process, the installer must specify full pathnames to all referenced files.

Once datasources have been specified in the WINDOWS\BR.INI file,

they can be installed through the Control Panel ODBC Administrator. All Business Rules! ODBC Datsources are registered as System DSNs.

To install a datasource, select the System DSN tab and click Add. Then select the Business Rules! ODBC Driver. You will be presented with a list of the datasources that you have specified in the BR.INI. Simply select the data sources you wish to install or reinstall. Reinstalling a datasource will replace any previous installation of the datasource.

Registry settings are setup by the installation process as follows:

HKEY_LOCAL_MACHINE

Software

ODBC

ODBC.INI

DataSetName

Application Name

Data Path

Dictionary Path

Dictionary File Name (full path)

Driver

Substitution Strings

ODBCINST.INI

Business Rules! ODBC Driver


Date Processing

Currently, date fields are interpreted as columns that have a name

containing "DATE" separated from other text by a digit or underscore.

e.g. valid date field names-

BIRTH_DATE, DATE_HIRED, DATE4

e.g. field names not of date type-

YEAR_TODATE, MANDATE_TYPE

In addition, using the Context Manager, you may specify the date storage format

for a field in the Date Format specification. You may specify DAYS, NODATE, or an actual date format in the Date Format Field. In the following table YYMMDD denotes a six character string or six digits. YY/MM/DD denotes an eight character string.

Example:

Field Name Date Format Field Result

============ ============== ======

BIRTH_DATE Empty String Date stored in days format.

BIRTH_DATE YMD Date in YYMMDD format.

BIRTH_DTE YMD Date in YYMMDD format.

BIRTH_DTE DAYS Date stored in days format.

BIRTH_DATE M/D/Y Date in MM/DD/YY format.

BIRTH_DTE Empty String No date processing.

VERIFY_DATE NODATE No date processing.


Security Support

Individual login names may be granted either read or read/write access to specific files. In the event no security codes are specified in the dictionary for a specific file, then all logins are granted full access to that file. However, READONLY in a DataSource statement will prevent write access to that application.

The easiest way to describe how this is accomplished is to provide a step by step example of adding security control to one of the sample files, as follows:

1. Install Business Rules! and specify it's location in WB.INI.

2. Install the BR ODBC Driver with the sample application in it's default location.

3. Copy the WBCONFIG.SYS file from the BR ODBC Driver directory to the Business Rules! directory.

4. Startup Business Rules!

5. Maximize the window.

6. RUN MENU

7. Enter the Security Maintenance selection.

8. Press F1 to add a record.

9. Press F3 to select an application.

10. Select PAYROLL.

11. Specify PR as the access code.

12. Press F10 to continue with the body of the record.

13. Press F7 to switch to read/write access.

14. Specify a Description of "general access".

15. Specify one or more login names to be granted access.

16. Press F10 to add the record.

17. Press F9 to exit Add mode.

18. Press F9 to exit the program.

19. Enter the Context Manager.

20. Select the PAYROLL application.

21. Press F3 to change the Deduction File record.

22. Specify PR as security group 1.

23. Press F10 to write the change to file.

24. Press F9 successively to exit the program.

Access the data in the deductions file via ODBC. The only access permitted will be that which has been authorized by the security file.


Installation Directions

}}ADS Drivers easily available on the web.

http://www.ads.net/brules/filebase/br-updates/odbc_4_01.zip

}

◦Download Drivers

  • ◦Extract to an Install Directory.
  • ◦Copy BRSERIAL.DAT to disk1 (Each user needs to do this!)
  • ◦EDIT BR.INI (If desired)
  • ◦EDIT WBCONFIG.SYS (If desired)

}Installing ODBC

  • ◦Copy the “DISK1” and “Cabs” to install Folder
  • ◦Run SETUP.EXE from the new “Install” Folder
  • ◦Select Driver & Sample (Or Just Driver for End Users)
}Configuration File
  • ◦C:\WINDOWS\BR.INI
    • �[BR] – Points to BRSERIAL.DAT folder
  • �[ODBC]
    • �DRIVE – Defines the “Drive Statement”
    • �Dictionary Path - Location of Context
    • �Database - Base Dir for Data
}Control Panel – Administrative Tools
  • ◦Data Sources (ODBC)
  • ◦System DSN
    • �Common Data Area - BR Sample Data Application
      �Payroll - BR Sample Data Application

BR Sample Application

  • }C:\Program Files\ADS\Business Rules!
  • }BRSERIAL.DAT
    • ◦(If you forgot to change the install folder)
  • }BR32.EXE
    • ODBC does not come with or need BR
  • }MENU.WBO
    • Menu to run Sample Application.
  • }


ODBC Data Source Configuration

Important: Carefully verify the Data Source configuration! Correct configuration is important for performance and proper behavior.
  1. Open the Control Panel and double-click on the Administrative Tools icon.

NOTE: If this icon is not present the Control Panel is in "Category View"; click "Switch to Classic View" on the left side of the window.

  2. Double-click the DataSources(ODBC) icon.

  3. Click on the Drivers tab at the top of the window.

  4. Verify the appropriate driver version and date as follows:

    • Left-click on "Business Rules! ODBC Driver" to highlight it.

    • Carefully review the information on the same line as "Business Rules! ODBC Driver". Use the horizontal scroll bar to navigate.

    • Review the information in the Version and Date columns to verify that Business Rules! ODBC Driver version: 3.00.19.40 or higher is installed on the workstation.

  5. Select the System DSN tab if the ODBC drivers are installed on the system for all users or select the User DSN tab if the ODBC drivers are installed for a specific user. (Refer to step 5 of the ODBC Driver Installation instructions.)

  6. Press the "Add..." button to Add the desired database or application.

    • The following configuration screen appears.

  7. Select the Business Rules! ODBC Driver

The following configuration screen appears.

8. Select Any Data Sources That you wish to add.

9. Select OK To close the configuration window.


Using the Business Rules

Using the Business Rules! Context Manager

The Business Rules! Context Manager is a utility that may be used to maintain a database describing the Applications, Files and Layouts for your application. The Context Manger is also the maintenance program for the file layouts used by the ODBC drivers.

** Note: Copy the Context Manager to a folder within your application. We call our folder ODBC.

  • Copy from \Progra~1\ADS\Brodbc~1\ to "...\ODBC\"

Change to the ODBC folder.

  • RUN CONTEXT/TOOLS

Application Selection

The first screen in the Context Manager is the application Selection. COMMON & PAYROLL are sample applications provided with the context manager.

** To clear the sample dictionary so you may begin adding entries that describe your files, DROP (don't remove) the following sample files:

  • TOOLD\APP.*

  • TOOLD\CONTEXT.*

  • TOOLD\FILE_APP.*

  • TOOLD\RECORD.*

In order to Create your First Application:

  • Press "F1: Add"

  • Enter an Application Code

  • Enter a Name or Description for the Database

  • Optionally enter additional information about your application.

  • Press "F10" to Finish

Once you have defined your application, you may "zoom" into the application by pressing enter or "F10" on the desired application.

You may also perform the following Functions:

  • F1 – Add/Create - Create a new application.
  • ◦F3 – Change Existing - Modify an existing Application.
  • ◦F4 – Delete - Delete An existing Application.
  • ◦F5 – Inquire/Details about - View the Application Information (Similar to Change, but read only).
  • ◦F6 – List - Create a Report or Printout.

Application Data File

This is where you identify the data files or tables to be used within your application. Keep in mind that all tables within an application must be referenced by a relative path.

In order to define your Fist Table:

  • Press "F1: Add"

  • Enter An Application File Code (Table Code)

  • Enter a Name or Description for the Table

  • Enter a Relative File Path (To the application) For Example "PRD".

  • Enter A Master File Name

  • Enter up to 9 Index Files or Keys.

  • Press F10 to Finish

** Note: Security Group Codes are supported by the context manager, Assign security groups to provide secure access to the ODBC drivers.

Once you have Defined your tables, you may press enter or "F10" on the desired table to further define the file layouts. Press "F9" to return to the prior screen.

You may also perform the following Functions:

  • F1 Add - Create a new Table,
  • F3 Change Existing - Modify an existing Table.
  • F4 Delete - Delete An existing Table.
  • F5 Inquire - View the Table Information (Similar to Change, but read only).
  • F6 List - Create a Report or Printout about a single Table.
  • F7 List All - Create a report for all Tables.
  • F8 List Range - Create a report for selected Tables.

Data File Record Selection

Tables will have one or more "Record or File Layouts" each of these will also need a name. Generally speaking, you only need one, and may call it the same name as the table.

As an example the Payroll application has a Table Named Employee, and a Record Description also named Employee

In order to define your Data File Record:

  • Press "F1: Add"

  • Enter a Record Name (Usually the Same as the Table Name)

  • Enter a Record Description.

  • You Must add either

    • Field Name Prefix or (A Code such as EM_)

    • Field Name Suffiix (A Code such as _EM)

  • Press F10 when Finished.

The prefix or suffix codes are not used by ODBC, but are intended to document the "Prefix" used when programming within BR. As en example there is a variable called "SEX", it would be referred to as "EM_SEX" within the application.

If you are going to have multiple records for a table, you must select a field that is present in all tables, name the field, and enter the value used as a filter for this particular record.

** Note: In legacy BR applications it was very appropriate to "Overload a record", using multiple file layouts within a single table. While there may still be good reasons to do this, it should be avoided. ODBC & SQL does not really work well with this concept.

Once you have Defined your Data File Records, you may press enter or "F10" on the desired entry to further define the file layouts. Press "F9" to return to the prior screen.

You may also perform the following Functions:

  • F1 Add - Create a new Table,
  • F3 Change - Modify an existing Table.
  • F4 Delete - Delete An existing Table.
  • F5 Inquire - View the Table Information (Similar to Change, but read only).
  • F6 List - Create a Report or Printout about a single Table.
  • F7 List All - Create a report for all Tables.
  • F8 List All+ - Create an Extended report for all Tables.

Context Definition

The ultimate goal of the context manager is to define the various fields within tables inside the application. This section allows you to define these fields.

In order to define a field:
  • Press "F1: Add"

  • Enter Field Name
  • Enter Filed Description
  • Enter Form Statement (C,PD,BH or any other Valid BR Type)
  • Enter field Length (Storage Length) For example a PD 6.2 would be 6
  • Enter # of Decimal Digits
  • Enter Field Starting Position
  • Field Ending Position will be calculated automatically (Make sure it is correct)
  • Date Format. (CCYYMMD, DAYS or similar information).
  • Press F10 to Save Record
  • (Repeat Process to add more fields)

** Special Field Name Considerations:

  • Date fields should contain the word "DATE_" or "_DATE". This will automatically treat the field as a date.
  • Special Characters and spaces should be avoided, stick to simply Alphanumeric characters. ("_" is fine).

** Sample Date Formats:

  • CCYYMMDD
  • MMDDCCYY
  • MM/DD/YY
  • CCYY/MM/DD
  • DAYS
  • NODATE

Once you have Defined your Data Field, you may Press "F9" to return to the prior screen.

You may also perform the following Functions:

  • F1 Add - Create a new Field.
  • F2 Insert - Insert a new field (Between other fields).
  • F3 Change - Change an Existing Field.
  • F4 Delete - Delete an existing Field.
  • F5 Inquire - A Bit redundant, but it is a read only screen displaying the field information.
  • F7 Field # ? - Changes the Default field when editing the file. Use when editing the same field in an existing table.


Microsoft Office Excel

NOTE: These instructions are based on Microsoft Office Excel 2003.

  1. Open Microsoft Office Excel.

  2. From the Menu bar, select Data > Import External Data > New Database Query....

  3. The Choose Data Source window appears. This example uses Payroll. After selecting the data source, click [OK].

    NOTE: The screen's appearance depends upon the BR.INI file.

  4. The Query Wizard window displays all of the available tables. (Select Any Desired Fields)

Access the Collection-Master Data through a PivotTable

  1. Open Microsoft Office Excel.

  2. From the Menu bar, open Data > PivotTable and PivotChart Report....

  3. Microsoft Excel's PivotTable and PivotChart Wizard appears. Select External data source under "Where is the data that you want to analyze?".
  4. Select PivotTable under "What kind of report do you want to create?". Click [ Next >].

  5. At the next window, click on the [Get Data...] button.

  6. A Choose Data Source window appears. (This example uses CLS Data.) After selecting the CLS Data Data Source, click [OK].

    NOTE: Screen appearance depends on the BR.INI file.

  7. A Query Wizard window appears that displays all of the available Collection-Master tables.


Microsoft Office Access Instruct

Microsoft Office Access Instructions

NOTE: These instructions are based on Microsoft Office Access 2003.

  1. Open Microsoft Access.

  2. To create a new database, locate the "Open" heading and click on "Create a new file...", or select File > New from the Menu bar.

  3. Click on "Blank database..."

  4. When prompted to enter a name and location for the new Microsoft Access database, it is advantageous to save the database on the network, so that other users may access it.

    NOTE: This example uses the name ODBC-101.MDB and saves the database to the user's user directory. Use an appropriate name.

  5. Upon creation of the database, the Database window appears:

  6. To access the Collection-Master data, select File > Get External Data > Link Tables... from the Menu bar. (Selecting Import... is an option; however, it is advisable to select Link Tables... to retain a Live link to the data.)

  7. Microsoft Access now prompts the user for the type of external data to link to. Click the down arrow under "Files of type:" and select the "ODBC Databases()" option.

  8. Click on the Machine Data Source tab. Select the desired data source under "Data Source Name" and click [OK]. This example uses CLS DATA as the Data Source Name.

    NOTE: The Data Source Name is dependent on the BR.INI file.

  9. The following window appears. Click [OK] to proceed.

  10. A new window appears with all of the available Collection-Master fields. Select the desired tables. When finished with the selection, click [OK].

  11. After selecting the desired tables, Microsoft Access prompts to select a "Unique Record Identifier" for each selected table. Click [Cancel] for each prompt.

    NOTE: Upon completion of the previous steps, the tables become linked:


Context\Context

Context\Context.wb - Importing File Layouts into the Context Database

Context\Context.wb is both an application and a set of libraries for use within BR. It's purpose is to import a tab delimited file "FILEAPP.TXT", in order to import application, table and record layout information. Context\Context.wb simplifies the work of making your application available for use within ODBC.

Context\Context.wb is not an official ADS product, rather a utility provided for the purpose of this seminar. Feel free to use the application and modify it as needed to meet your own individual needs.

Importing Layouts:

}FILEAPP.TXT – Tab Delimited File to define Tables:
}

Required Fields:

Field Name | Field Description
APP_ID | Application Code
APP_NAME | Application Name
APP_DATA | Data File Path
FILE_ID | Table Name
DESCRIPTION | Table Description
MASTER | Master File Name
KEY1-9 | Index File Names (Optional)
CONTEXT | Path to File Layouts to be imported.
  • ◦

The following is a sample that defines the ODBC Context Manager Files:

APP_ID | APP_NAME | APP_PROGRAM | APP_DATA | APP_SOURCE | FILE_ID | RECORD_ID | DESCRIPTION | MASTER | KEY1 | KEY2 | KEY3 | KEY4 | KEY5 | KEY6 | KEY7 | KEY8 | KEY9 | PREFIX | CONTEXT
ODBC | ODBC Context Manager | TOOLS | TOOLD | TOOLS | APP |  | Application | APP | APP.KEY | APP.KY2 |  |  |  |  |  |  |  |  | CONTEXT\_Context\APP.TXT
ODBC | ODBC Context Manager | TOOLS | TOOLD | TOOLS | CONTEXT |  | Context Definition File | CONTEXT | CONTEXT.KEY | CONTEXT.KY2 |  |  |  |  |  |  |  |  | CONTEXT\_Context\CONTEXT.TXT
ODBC | ODBC Context Manager | TOOLS | TOOLD | TOOLS | FILEAPP |  | Application File Name | FILE_APP | FILE_APP.KEY | FILE_APP.KY2 |  |  |  |  |  |  |  |  | CONTEXT\_Context\FILE_APP.TXT
ODBC | ODBC Context Manager | TOOLS | TOOLD | TOOLS | RECORD |  | Record File | RECORD | RECORD.KEY |  |  |  |  |  |  |  |  |  | CONTEXT\_Context\RECORD.TXT
ODBC | ODBC Context Manager | TOOLS | TOOLD | TOOLS | RECLINK |  | Record Link Layout | REC_LINK | REC_LINK.KEY |  |  |  |  |  |  |  |  |  | CONTEXT\_Context\REC_LINK.TXT

Individual Table or Record Layouts:

Notice that the "CONTEXT" field in the FILEAPP.TXT field references individual ".TXT" files. Each of these files contain the specific file layout for an individual TABLE or Record.

Each Table is a TAB delimited File:

Required Fields:

FIELD_NAME | 15 Character Code
DISK_STORAGE | Field Type (C,PD,BH,Etc)
DTE_FMT | Date Format (CCYYMMDD)
ORIG_CAPTION | English Description of Field
RECORD_POS | Field Starting Position
RECORD_LEN | Field Length
DECIMAL_POS | Number of Decimal Positions.

The following is a sample that defines the CONTEXT table:

FIELD_NAME | DISK_STORAGE | DTE_FMT | ORIG_CAPTION | RECORD_POS | RECORD_LEN | DECIMAL_POS
APP_ID | C |  | APPLICATION ID | 1 | 8 | 0
FILE_ID | C |  | FILE ID | 9 | 8 | 0
RECORD_ID | C |  | RECORD ID (OPTIONAL) | 17 | 8 | 0
FIELD_NAME | C |  | FIELD NAME (ON-Q & SUBSCRIPT) | 25 | 15 | 0
DISK_STORAGE | C |  | DISK STORAGE FORMAT | 40 | 20 | 0
DTE_FMT | C |  | DATE FORMAT (DEFAULT DAYS) | 60 | 8 | 0
ORIG_APP | C |  | ORIG WINDOW APPLICATION | 68 | 8 | 0
ORIG_NAME | C |  | ORIG WINDOW NAME | 76 | 8 | 0
ORIG_CAPTION | C |  | ORIG WINDOW CAPTION | 84 | 30 | 0
EDIT_NAME | C |  | EDIT ROUTINE NAME | 114 | 12 | 0
PACK_NAME | C |  | PACK ROUTINE NAME | 126 | 12 | 0
UNPACK_NAME | C |  | UNPACK ROUTINE NAME | 138 | 12 | 0
LIST_POINTER | BH |  | LIST POINTER | 150 | 4 | 0
RECORD_POS | BH |  | POSITION IN DISK RECORD | 154 | 2 | 0
RECORD_LEN | BH |  | LENGTH | 156 | 2 | 0
DECIMAL_POS | BH |  | DECIMAL POSITIONS | 158 | 2 | 0
KEY1 | BH |  | Key Sequance #1 | 160 | 1 | 0
KEY2 | BH |  | Key Sequance #2 | 161 | 1 | 0
KEY3 | BH |  | Key Sequance #3 | 162 | 1 | 0
KEY4 | BH |  | Key Sequance #4 | 163 | 1 | 0
KEY5 | BH |  | Key Sequance #5 | 164 | 1 | 0
KEY6 | BH |  | Key Sequance #6 | 165 | 1 | 0
KEY7 | BH |  | Key Sequance #7 | 166 | 1 | 0
KEY8 | BH |  | Key Sequance #8 | 167 | 1 | 0
KEY9 | BH |  | Key Sequance #9 | 168 | 1 | 0
DATE_CHANGED | BH |  | DATE RECORD LAST CHANGED | 169 | 3 | 0
REPORT_FORM | C |  | Report Form | 172 | 30 | 0
REPORT_CAPTION | C |  | Report Caption | 202 | 32 | 0


Context\Context

Context\Context.wb - Library Functions

Context\Context.wb is a set of library functions designed to simplify I/O for both BR internal files defined in the contexts manager as well as managing CSV files.

In order to Use Context\Context.wb, you must issue a library command within the calling application: Make sure that you have copied the entire contents of the "Context" folder provided into a folder called "Context".

Make sure you copy the following files from your TOOLD directory from the context manager. (These files contain your application and layouts).

  • Context
  • Context.key
  • Context.ky2
  • CONTEXT.WB
  • File_app
  • File_app.key
  • File_app.ky2

Sample library definition:

00010 LIBRARY "CONTEXT\CONTEXT.WB": FNOPEN_TABLE,FNSTATUS_FILE,FNCONTEXT,FNPACK_FORM$,FNINDEX_HANDLE,FNGET_VAR$,FNOPEN_CSV,FNPARSE_CSV

Functions to use tables defined in the Context Manager: (These functions are all used in the sample program EMPLOYEE.BRS)

FNOPEN_TABLE(APPLICATION$,FILENAME$,MAT TABLE_HANDLES)

This function will open a table defined within the context manager, along with all of it's related index files. The file handles will be passed to an array.

Parameters:

  • Application$ - Application Code
  • FILENAME$ - Table or File Code
  • MAT TABLE_HANDLES - An Array of File Handles assigned to each of the available index files.

Returns:

Table handle assigned to "Key1" or the "Primary Index".

Example:

100 LET EMPLOYEE_RESULT=FNOPEN_TABLE("PAYROLL","EMPLOYEE",MAT EMPLOYEE_HANDLES)

In the above example, EMPLOYEE_RESULT will be assigned the file handle to the "Primary Key" assigned to the "EMPLOYEE" Table.

FNSTATUS_FILE(MAT NAME_FILE$,MAT KFNAME_FILE$,MAT USE_FILE$)

This function will return 3 arrays. Containing information about the files that are currently open.

Parameters:

  • MAT NAME_FILE$ - An array containing the files that are currently open.
  • MAT KFNAME_FILE$ - An array containing the Key Names for the open files.
  • MAT USE_FILE$ - Contains a Shorthand List of both Name & KFNAME

** Note: USE_FILE$ is in the following format NAME=[DRIVE_LETTER][:][FILE_NAME],KFNAME=[DRIVE_LETTER][:][INDEX_NAME]
For example: "NAME=C:EMPLOYEE,KFNAME=C:EMPLOYEE.KEY"

Returns:

The highest file that is currently open.

Example:

00200 LET FNSTATUS_FILE(MAT NAME_FILE$,MAT KFNAME_FILE$,MAT USE_FILE$)

In the above example, the 3 arrays will be populated. Later, these can be used identify the files that are open.

FNCONTEXT(APPLICATION$,FILENAME$,ALIAS$,MAT DATA$,MAT DATA,MAT FIELDSC$,MAT FIELDSN$,&FORM_C$,&FORM_N$,&FORM_ALL$)

This function will assign values to the required fields that will be used to perform IO and define the "SQL Names" for the fields within a particular table.

Parameters:

  • APPLICATION$ - Context Application Code
  • FILENAME$ - File or TABLE to use
  • ALIAS$ - Specific Record Layout to Use
  • MAT DATA$ - An Array that will be used to contain Character variables in IO statements. (Re-Dimensioned by the function)
  • MAT DATA - An Array that will be used to contain Numeric variables in IO statements. (Re-Dimensioned by the function)
  • MAT FIELDSC$ - An Array that will be populated by the function, it contains the Character "SQL Field Names". (Re-Dimensioned by the function)
  • MAT FIELDSN$ - An Array that will be populated by the function, it contains the Numeric "SQL Field Names". (Re-Dimensioned by the function)
  • FORM_C$ - A string variable that contains the "PACKED " cform$" statement used to read Character variables.
  • FORM_N$ - A string variable that contains the "PACKED " cform$" statement used to read Numeric variables.
  • FORM_ALL$ - A String variable that contains the "PACKED" cform$ statment used to read both data types.

** Note: FORM_ALL$ may be too complex for BR, in those cases you will need to read the Character & Numeric variables separately.

Returns: N/A

Example:

00300 DIM EMPLOYEE_DATA$(1)*80,EMPLOYEE_DATA(1),EMPLOYEE_FIELDSC$(1)*20,EMPLOYEE_FIELDSN$(1)*20,EMPLOYEE_FORMC$*512,EMPLOYEE_FORMN$*512,EMPLOYEE_FORM_ALL$*512
00310 LET FNCONTEXT(APPLICATION$:="PAYROLL",FILENAME$:="EMPLOYEE",ALIAS$:="EMPLOYEE",MAT EMPLOYEE_DATA$,MAT EMPLOYEE_DATA,MAT EMPLOYEE_FIELDSC$,MAT EMPLOYEE_FIELDSN$,EMPLOYEE_FORMC$,EMPLOYEE_FORMN$,EMPLOYEE_FORM_ALL$)

In the above example, the Data & Form variables for EMPLOYEE from the PAYROLL application will be populated. (Note, no file is opened, but the arrays are re-dimensioned to match the appropriate information for EMPLOYEE.

FNPACK_FORM$*1024(F$*2048;PACK_TYPE)

This function is automatically used by FNCONTEXT, but may be useful to pack other form statements.

Parameters:

  • PACKED_FORM$ - Form Statment to Process
  • PACK_TYPE - Logical Value (0 Returns Unpacked,1 Returns variable as CFORM$)

Returns:

The form statement with duplicates converted to a compressed format ("3*C"). Based on PACK_TYPE, it will also return the CFORM$ or compiled value.

Example:

00400 DIM PACKED_FORM$*256
00410 LET PACKED_FORM$="FORM POS 1,C 8,C 1,C 1,N 6,PD 6.2,POS 50,C 80"
00420 PRINT PACKED_FORM$
00430 PRINT FNPACK_FORM$(PACKED_FORM$,PACK=0)

In the above example, the program will disply the form statement before & after the function. The packed form statement will look like this:

FORM POS 1,C 8,2*C 1,N 6,PD 6.2,POS 50,C 80

FNINDEX_HANDLE(INDEX_KEY$*80; ACCESS_RIGHT$, &RECORD_PRIOR)

This function is used to identify the file handle for existing index file. It is also capable of opening the data file if it is missing.

Parameters:

  • INDEX_KEY$ - The "Shorthand" for the index file we want to open.
  • ACCESS_RIGHT$ - INPUT,OUTPUT,OUTIN
  • RECORD_PRIOR - This will return the record pointer for the index file (-1 File was closed, 0 No record, >0 Record # assigned).

Returns:

The file handle for the requested index handle.

Example:

00100 LET EMPLOYEE_RESULT=FNOPEN_TABLE("PAYROLL","EMPLOYEE",MAT EMPLOYEE_HANDLES)
00500 LET EMPLOYEE_INDEX_HANDLE=FNINDEX_HANDLE("NAME=F:EMPLOYEE,KFNAME=F:EMPLOYEE.KEY")

The able example will find the file that has already been opened by FNOPEN_TABLE, and will assign EMPLOYEE_INDEX_HANDLE to the appropriate file handle. (In this example, there was only one index, so it was a bit redundant, but the example still works.)

FNGET_VAR$*128(UNQ$, MAT FIELDSC$, MAT FIELDSN$)

This function actually creates a "PROC FILE" to be executed by the calling program. Once you execute the proc, local variables will be created for use within your application. These variables are assigned automatically based on the "SQL Names" defined in the context database.

Parameters:

  • UNQ$ - Unique "Prefix" name to be used when creating the BR variables.
  • MAT FIELDSC$ - An Array Containing "Character" or String Variable Names.
  • MAT FIELDSN$ - An Array Containing "Numeric" Variable Names.

Returns:

The Name of a "temporary Proc" that will be executed by the calling program.

Example:

EXECUTE "PROC="&FNGET_VAR$(PREFIX$:="EMPLOYEE",MAT EMPLOYEE_FIELDSC$,MAT EMPLOYEE_FIELDSN$)

The above example will execute the procedure created by FNGET_VAR$. That procedure will assign variable such as the following.

  • EMPLOYEE_EMP_NAME
  • EMPLOYEE_EMP_ADDRESS
  • EMPLOYEE_EMP_ZIP

In order to use the "Name Field" within the application you would use the "DATA$" array as in the following example.

PRINT "Name=";employee_DATA$(EMPLOYEE_EMP_NAME)

FNOPEN_CSV(CSV_HANDLE,CSV$*80,&CSV_DELIMITER$,MAT CSV_FIELDS$,MAT CSV_DATA$)

This function will open a CSV or TAB delimited file, use the assigned a file handle, and populate the Delimiter, Field Names & Data arrays.

Parameters:

  • CSV_HANDLE - The file handle to use for this file.
  • CSV$ - The file name for the CSV file.
  • CSV_DELIMITER$ - The delimiter found in the "CSV$" file.
  • MAT CSV_FIELDS$ - An array containing a list of field names (Re-Dimensioned by the function)
  • MAT CSV_DATA$ - An array containing the data array (Re-Dimensioned by the function)

Returns:

The number of fields in the array.

FNPARSE_CSV(LIST$*3072,MAT WORK$;SEPARATOR$,NOTRIM)

This function will take a line read via "LINPUT$", and parse the results into the WORK$ array.

Parameters:

  • LIST$ - The string to be parsed.
  • MAT WORK$ - The destination string array (Make the array can support the appropriate # of characters in the fields)
  • SEPARATOR$ - Optional parameter that defines the separator. (Use CHR$(9) for Tabs).
  • NOTRIM - Optional parameter that instructs the function to keep the spaces from the source (NOTRIM:=1)

Note: This function has some special advanced parsing that supports certain "advanced" CSV "\" functions. These are documented somewhere in the CSV standard.

Returns:

The number of items parsed

FNLIST(LIST$*4096,MAT WORK$;DELIMITER$)

This function parses a list of comma separated values, similar to the FNPARSE_CSV, but the function expects a label (or at least a ":" at the beginning of the line).

Parameters:

  • LIST$ - The string to be parsed.
  • MAT WORK$ - The destination string array (Make the array can support the appropriate # of characters in the fields)
  • DELIMITER$ - The Separator to be used (Defaults to a ","), (Use CHR$(9) for Tabs).

Returns:

N/A

Example:

LINE$="COLORS:Red,Blue,Green,Purple"
LET FNLIST(LINE$,MAT WORK$)

The above example will populate WORK$ as follows.

  • WORK$(1)="Red"
  • WORK$(2)="Blue"
  • WORK$(3)="Green"
  • WORK$(4)="Purple"


EMPLOYEE

EMPLOYEE.BRS

00001 PRINT NEWPAGE
00005 DIM NAME_FILE$(1)*80,KFNAME_FILE$(1)*80,USE_FILE$(1)*80
00010 LIBRARY "CONTEXT\CONTEXT.WB": FNOPEN_TABLE,FNSTATUS_FILE,FNCONTEXT,FNPACK_FORM$,FNINDEX_HANDLE,FNGET_VAR$,FNOPEN_CSV,FNPARSE_CSV
00100 LET EMPLOYEE_RESULT=FNOPEN_TABLE("PAYROLL","EMPLOYEE",MAT EMPLOYEE_HANDLES)
00105 LET EMPLOYEE_DRIVE$=FILE$(EMPLOYEE_RESULT)(1:2)
00110 ! FNOPEN_TABLE opens all appropriate tables for a particular Application/Table !:
      ! EMPLOYEE_HANDLES contains an array of file handles !:
      ! EMPLOYEE_RESULT Returns the "Primary or KEY1 Handle"
00200 LET FNSTATUS_FILE(MAT NAME_FILE$,MAT KFNAME_FILE$,MAT USE_FILE$)
00210 ! MAT NAME_FILE$ contains a list of files!:
      ! Mat KFNAME_FILE$ contains a list of index_files$ !:
      ! MAT USE_FILE$ contains a "Shorthand List of Both"
00220 LET EMPLOYEE_HANDLE=SRCH(MAT USE_FILE$,"NAME="&EMPLOYEE_DRIVE$&"EMPLOYEE,KFNAME="&EMPLOYEE_DRIVE$&"EMPLOYEE.KEY") !:
      PRINT "Employee handle is:";EMPLOYEE_HANDLE;USE_FILE$(EMPLOYEE_HANDLE)
00300 DIM EMPLOYEE_DATA$(1)*80,EMPLOYEE_DATA(1),EMPLOYEE_FIELDSC$(1)*20,EMPLOYEE_FIELDSN$(1)*20,EMPLOYEE_FORMC$*512,EMPLOYEE_FORMN$*512,EMPLOYEE_FORM_ALL$*512
00310 LET FNCONTEXT(APPLICATION$:="PAYROLL",FILENAME$:="EMPLOYEE",ALIAS$:="EMPLOYEE",MAT EMPLOYEE_DATA$,MAT EMPLOYEE_DATA,MAT EMPLOYEE_FIELDSC$,MAT EMPLOYEE_FIELDSN$,EMPLOYEE_FORMC$,EMPLOYEE_FORMN$,EMPLOYEE_FORM_ALL$)
00320 ! APPLICATION$               = Context Application Code !:
      ! FILENAME$                  = Table Name to use !:
      ! ALIAS                      = File Layouts ALIAS Code!:
      ! MAT _DATA$,Mat DATA        = Data to be Used For I/O !:
      ! MAT _FIELDSC$,MAT FIELDSN$ = Field names!:
      ! _FORMC$,_FORMN$,_FORM_ALL$ = Form Statements for I/O
00400 DIM PACKED_FORM$*256
00410 LET PACKED_FORM$="FORM POS 1,C 8,C 1,C 1,N 6,PD 6.2,POS 50,C 80"
00420 PRINT PACKED_FORM$ !:
      PRINT FNPACK_FORM$(PACKED_FORM$,PACK=0)
00430 ! PACKED_FORM$ = Form Statement to process !:
      ! PACK=0 Return Unpacked !:
      ! PACK=1 Return as CFORM
00440 ! This function is just to demonstrate the Pack_form$, FNCONTEXT uses this to create optimal Form Statements !:
      ! Some tables are too complex for FORM_ALL$, in those cases you will need to use FIELDSC$ & FIELDN$
00500 LET EMPLOYEE_INDEX_HANDLE=FNINDEX_HANDLE("NAME="&EMPLOYEE_DRIVE$&"EMPLOYEE,KFNAME="&EMPLOYEE_DRIVE$&"EMPLOYEE.KEY")
00510 ! FNINDEX_HANDLE uses the "Shorthand" to find the Index Handle, within existing Open Files
00520 PRINT "Employee.KEY is opened as File #:";EMPLOYEE_INDEX_HANDLE
00600 EXECUTE "PROC="&FNGET_VAR$(PREFIX$:="EMPLOYEE",MAT EMPLOYEE_FIELDSC$,MAT EMPLOYEE_FIELDSN$)
00610 ! FNGET_VAR CREATES A TEXT FILE TO BE EXECUTED !:
      ! PREFIX is the unique code to be used !:
      ! Each Variable in _FIELDSC$ and _FIELDSN$ will be assigned a BR variable
00620 PRINT "Employee Name is field #:",EMPLOYEE_EMP_NAME
00621 PRINT "Employee Address is field #:",EMPLOYEE_EMP_ADDRESS
00622 PRINT "Employee Zip is field #:",EMPLOYEE_EMP_ZIP
00700 ! FNOPEN_CSV is used to open a CSV or TAB delimited file
00710 ! FNPARSE_CSV is used to Parse a Line from a CSV or TAB delimited table
01000 OPEN #1: "NAME="&ENV$("TEMP")&"\EMPLOYEE.XLS,REPLACE,RECL=32000",DISPLAY,OUTPUT
01010 PRINT #1: "<html>"
01020 PRINT #1: "<head>"
01030 PRINT #1: "<title>Employee File</title>"
01040 PRINT #1: "<!-- Generated using Data from BR Context Manager -->"
01050 PRINT #1: "</head>"
01060 PRINT #1: "<body>"
01070 PRINT #1: "<table>"
01080 PRINT #1: "<tr><th>Emp #</th>"
01081 PRINT #1: "<th>Employee</th>"
01082 PRINT #1: "<th>Address</th>"
01083 PRINT #1: "<th>City</th>"
01084 PRINT #1: "<th>State</th>"
01085 PRINT #1: "<th>Zip</th>"
01086 PRINT #1: "<th>Sex</th>"
01087 PRINT #1: "<th>Salary</th>"
01090 PRINT #1: "</tr>"
01100 MAIN_READ: READ #EMPLOYEE_HANDLE,USING EMPLOYEE_FORM_ALL$,RELEASE: MAT EMPLOYEE_DATA$,MAT EMPLOYEE_DATA EOF FINIS
01110 IF TRIM$(EMPLOYEE_DATA$(EMPLOYEE_CO_NUM))<>"1" THEN !:
      GOTO READ_NEXT !:
      ! Filter Company #1
01120 LET SALARY=EMPLOYEE_DATA(EMPLOYEE_STD_UNITS_PER)*EMPLOYEE_DATA(EMPLOYEE_STD_RATE_PER)
01200 PRINT #1: "<tr>"
01210 PRINT #1: "<td align=right>";EMPLOYEE_DATA$(EMPLOYEE_EMP_NUM);"</td>"
01220 PRINT #1: "<td>";EMPLOYEE_DATA$(EMPLOYEE_EMP_NAME);"</td>"
01230 PRINT #1: "<td>";EMPLOYEE_DATA$(EMPLOYEE_EMP_ADDRESS);"</td>"
01240 PRINT #1: "<td>";EMPLOYEE_DATA$(EMPLOYEE_EMP_CITY);"</td>"
01250 PRINT #1: "<td>";EMPLOYEE_DATA$(EMPLOYEE_EMP_ST);"</td>"
01260 PRINT #1: "<td>";EMPLOYEE_DATA$(EMPLOYEE_EMP_ZIP);"</td>"
01270 PRINT #1: "<td>";EMPLOYEE_DATA$(EMPLOYEE_SEX);"</td>"
01280 PRINT #1: '<td align="right" STYLE="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon][Red]($#,##0.00)">';SALARY;'</td>'
01290 PRINT #1: "</tr>"
01900 READ_NEXT: GOTO MAIN_READ
05000 FINIS: ! Close File & Display
05010 PRINT #1: "</table>"
05020 PRINT #1: "</body>"
05030 PRINT #1: "</html>"
05040 CLOSE #1:
05100 EXECUTE "sys -c -m start %TEMP%\Employee.xls"
05110 PRINT "DONE"


Manually Uninstalling BR ODBC

If it becomes necessary to manually uninstall the driver, take the following steps:

· Remove the BR ODBC Driver directory.

· Run REG_ODBC.EXE which will selectively remove the following references to the driver:

HKEY_LOCAL_MACHINE

Software

ADS

BR ODBC Driver <=

Microsoft

Windows

Current Version

Uninstall

BR ODBC Driver <=

ODBC

ODBC.INI <=

ODBCINST.INI

Business Rules! ODBC Driver <=

ODBC Drivers

(value -) Business Rules! ODBC Driver <=


ODBC Considerations

ODBC allows for the registration of Data Sources so users can pick from among various classes or groups of data. Data sources are implemented in BR ODBC as application names. The Business Rules! implementation of ODBC is dictionary oriented, and a dictionary may support one or more application names or data sources.

ODBC permits the registry of a path associated with each data source. In the BR implementation of ODBC, the data source path does NOT point to the data.

Instead, it points to a dictionary. The dictionary then points to the data. The data source path (dictionary path name) for each application name is specified in the BR.INI file by a DataSource statement (see below). Multiple data source statements may point to the same dictionary.

While the syntax of the BR.INI datasource statements imply that you may use various names for the data dictionary, this is not true. The driver looks only for the files named CONTEXT, FILEAPP, RECLINK and SECURE. However, the DATASOURCE statement can specify various subdirectory names which contain the dictionary files. In this way the driver supports more than one dictionary.

The BR.INI file specifies a DICTIONARYPATH, which is placed ahead of (prefixed to) dictionary path names in data source statements.

The BR.INI file also specifies a DATAPATH. This path prefixes file references in the dictionary.


Prompted Substitutions - Example

Suppose, your would like to setup two datasources that have the data, and data dictionary files in the following locations:

Datasource 1) My Payroll:

DataFile = "C:\MYCOMPANY\PAY\ABCPAY"

DataDictionary = "C:\MYCOMPANY\MAIN\CONTEXT"

Application = Payroll (Defined in the Data Dictionary)

Data Directory = PAY (Defined in the Data Dictionary)

However, the data dictionary specifies the data file as "XXXPAY".

Datasource 2) My Customers:

DataFile = "C:\MYCOMPANY\CUST\CUSTOMER"

DataDictionary = "C:\MYCOMPANY\MAIN\CONTEXT"

Application = Customer (Defined in the Data Dictionary)

Data Directory = CUST (Defined in the Data Dictionary)

The entries in WINDOWS\BR.INI would look like the following.

[ODBC]

COMMENT: Specify the first datasource

DataPath C:\MYCOMPANY

DictionaryPath C:\MYCOMPANY\MAIN

Substitute XXX, ABC

DataSource My Payroll, CONTEXT, Payroll,READONLY

COMMENT: Specify the second datasource

COMMENT: Clear any previous substitutions

Substitute

DataSource My Customers, CONTEXT, Customer, READONLY