The Programmer's Guide. Databases With Isam

THE PROGRAMMER'S GUIDE. BASIC 7.1
CHAPTER 10: DATABASES WITH ISAM

ISAM STATEMENTS AND PROCEDURES

ISAM VS. OTHER TYPES OF FILE ACCESS

THE ISAM PROGRAMMING MODEL

ISAM CONCEPTS AND TERMS

ISAM COMPONENTS

THE ISAM ENGINE

THE PARTS OF THE ISAM FILE

ISAM FILE ALLOCATION AND GROWTH

WHEN TO USE ISAM

THE TABLE/INDEX MODEL

A SAMPLE DATABASE

DESIGNING THE BOOKSTOCK TABLE

CREATING, OPENING, AND CLOSING A TABLE

NAMING THE COLUMNS OF THE TABLE

SPECIFYING THE DATA TYPES OF THE COLUMNS

DATA TYPE COERCION

OPENING THE BOOKSTOCK TABLE

USING OPEN AND CLOSE WITH ISAM

OPENING A TABLE

CLOSING A TABLE

THE ATTRIBUTES OF FILENUMBER%

DEFINING A RECORD VARIABLE

CREATING AND SPECIFYING INDEXES ON TABLE COLUMNS

INDEXES ON BOOKSTOCK'S COLUMNS

CREATING A UNIQUE INDEX

SUBORDERING OF RECORDS WITHIN AN INDEXED COLUMN

CREATING A COMBINED INDEX

PRACTICAL CONSIDERATIONS WITH INDEXES

RESTRICTIONS ON INDEXING

DETERMINING THE CURRENT INDEX

TRANSFERRING AND DELETING RECORD DATA

THE CURRENT POSITION

CHANGING THE CURRENT INDEX

MAKING A DIFFERENT RECORD CURRENT

SETTING THE CURRENT RECORD BY POSITION

DISPLAYING THE BOOKSTOCK TABLE

A TYPICAL ISAM PROGRAM

SETTING THE CURRENT RECORD BY CONDITION

SEEKING ON STRINGS AND ISAM STRING COMPARISON

A MULTI-TABLE DATABASE

DELETING INDEXES AND TABLES

ISAM NAMING CONVENTION

STARTING ISAM FOR USE IN QBX

ESTIMATING MINIMUM ISAM BUFFER VALUES

ISAM AND EXPANDED MEMORY (EMS)

USING ISAM WITH COMPILED PROGRAMS

PRACTICAL CONSIDERATIONS WHEN USING EMS

TSRS AND INSTALLATION/DEINSTALLATION ORDER

BLOCK PROCESSING USING TRANSACTIONS

SPECIFYING A TRANSACTION BLOCK

THE TRANSACTION LOG

USING SAVE POINTS

MAINTAINING PHYSICAL AND LOGICAL DATA INTEGRITY

RECORD VARIABLES AS SUBSETS OF A TABLE'S COLUMNS

USING MULTIPLE FILES: "RELATIONAL" DATABASES

THE REPAIR UTILITY

THE ISAMPACK UTILITY

CONVERTING BTRIEVE CODE

RUN-TIME ERROR MESSAGES AND CODES

MICROSOFT BASIC GIVES YOU THE POWER AND FLEXIBILITY OF INDEXED SEQUENTIAL ACCESS METHOD (ISAM) THROUGH A GROUP OF STRAIGHTFORWARD STATEMENTS AND FUNCTIONS THAT ARE PART OF THE BASIC LANGUAGE. ISAM STATEMENTS AND FUNCTIONS PROVIDE AN EFFICIENT AND SIMPLE METHOD FOR QUICKLY ACCESSING SPECIFIC RECORDS IN LARGE AND COMPLEX DATA FILES.

THIS CHAPTER DESCRIBES ISAM, ITS STATEMENTS AND FUNCTIONS, AND HOW TO USE THEM IN PROGRAMS THAT ACCESS AND MANIPULATE THE RECORDS IN ISAM DATABASE FILES. THESE STATEMENTS AND FUNCTIONS MAKE IT EASY FOR YOUR PROGRAMS TO MANAGE DATABASE FILES AS LARGE AS 128 MEGABYTES.

WHEN YOU FINISH THIS CHAPTER, YOU'LL UNDERSTAND:
O.~ WHAT ISAM IS, AND WHEN AND WHY IT IS USEFUL.
O.~ THE NEW AND MODIFIED BASIC STATEMENTS USED FOR ISAM FILE ACCESS AND MANIPULATION.
O.~ A GENERAL APPROACH TO CREATING, ACCESSING, AND MANIPULATING RECORDS IN ISAM DATABASES.

O.~ THE STRUCTURE OF AN ISAM FILE.
O.~ USING INDEXES TO WORK WITH DATA RECORDS AS THOUGH THEY WERE SORTED IN MANY WAYS.
O.~ USING EMS (EXPANDED MEMORY) WITH ISAM PROGRAMS.
O.~ USING TRANSACTION STATEMENTS IN APPLICATIONS WITH COMPLEX BLOCK PROCESSING REQUIREMENTS.

O.~ CONVERTING EXISTING DATABASE CODE TO ISAM CODE.
O.~ USING ISAM UTILITIES TO CONVERT YOUR SEQUENTIAL AND DATABASE FILES TO ISAM FORMAT, TO COMPACT ISAM DATABASES, REPAIR DAMAGED DATABASES, AND EXCHANGE TABLES BETWEEN DATABASE FILES AND SEQUENTIAL FILES.

NOTE.~ ISAM IS SUPPORTED ONLY IN MS-DOS. YOU CANNOT USE IT IN OS/2 PROGRAMS.

WHEN A PROGRAM USES OR MODIFIES RECORDS STORED IN A FILE, IT OFTEN HAS TO SORT AND RE-SORT THE RECORDS IN VARIOUS WAYS. WHEN A FILE CONTAINS MANY COMPLEX RECORDS, SORTING CAN REQUIRE SUBSTANTIAL PROGRAM CODE AND A GREAT DEAL OF PROCESSING TIME.

ISAM IS AN APPROACH TO CREATING AND MAINTAINING A SPECIAL DATA FILE, IN WHICH THE WAY RECORDS TYPICALLY NEED TO BE SORTED CAN BE EASILY DEFINED AND EFFICIENTLY STORED ALONG WITH THE RECORDS THEMSELVES. THIS MEANS YOUR PROGRAM DOESN'T HAVE TO RE-SORT THE RECORDS EACH TIME THE FILE IS USED OR EACH TIME YOU WANT A DIFFERENT PERSPECTIVE ON THE RECORDS.

IN ADDITION TO YOUR DATA RECORDS, AN ISAM FILE CONTAINS INFORMATION THAT DESCRIBES AND FACILITATES ACCESS TO EACH DATA RECORD. MUCH OF THIS INFORMATION IS MAINTAINED IN "TABLES" AND "INDEXES". TABLES SERVE MANY PURPOSES, INCLUDING ALLOWING QUICK ACCESS TO ANY OF THE VALUES OF A SPECIFIC DATA RECORD.

INDEXES REPRESENT VARIOUS WAYS OF ORDERING THE PRESENTATION OF RECORDS IN A TABLE, AND THEY PERMIT YOU TO EASILY ACCESS A WHOLE RECORD BY THE VALUE OF A FIELD IN THE RECORD. ISAM STATEMENTS AND FUNCTIONS MANIPULATE, PRESENT, AND MANAGE THE RECORDS IN ISAM DATA FILES.

ISAM'S RECORD-SEARCHING AND ORDERING ALGORITHMS ARE FASTER AND MORE EFFICIENT THAN ROUTINES THAT YOU MIGHT CREATE IN BASIC TO PERFORM THESE TASKS, SO IT NOT ONLY SAVES YOU SIGNIFICANT PROGRAMMING EFFORT, BUT IMPROVES THE SPEED AND CAPACITY OF MANY DATABASE PROGRAMS AS WELL.

FOR DATABASE APPLICATIONS, ISAM FILES ARE MORE CONVENIENT AND EFFICIENT THAN RANDOM-ACCESS FILES BECAUSE THEY ALLOW YOU TO ACCESS THE FILE AS THOUGH THE RECORDS WERE ORDERED IN A VARIETY OF DIFFERENT WAYS.

THE NEXT SEVERAL SECTIONS COMPARE ISAM TO OTHER TYPES OF FILES AND INTRODUCE SOME CONCEPTS AND TERMS THAT ARE HELPFUL IN UNDERSTANDING ISAM. (TRADITIONAL SEQUENTIAL AND RANDOM-ACCESS FILES ARE DISCUSSED IN CHAPTER 3, "FILE AND DEVICE I/O").

ISAM STATEMENTS AND PROCEDURES

THE STATEMENTS FOR PERFORMING ISAM FILE TASKS ARE INTEGRATED INTO THE BASIC LANGUAGE. IN MOST CASES, NEW STATEMENTS HAVE BEEN ADDED FOR ISAM. IN A FEW CASES, EXISTING STATEMENTS HAVE SIMPLY BEEN EXPANDED. THE FOLLOWING LIST CATEGORIZES THE ISAM STATEMENTS BY THE TYPE OF TASK FOR WHICH YOU USE THEM:

TASK                                  STATEMENTS
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
FILE AND TABLE CREATION/ACCESS       OPEN, CLOSE, DELETETABLE,
                                     TYPE...END TYPE
CONTROLLING PRESENTATION ORDER OF    CREATEINDEX, GETINDEX$,
DATA (INDEXING)                      SETINDEX, DELETEINDEX
POSITION CHANGE RELATIVE TO THE      MOVEFIRST, MOVELAST,
CURRENT RECORD                       MOVENEXT, MOVEPREVIOUS,
                                     TEXTCOMP
POSITION CHANGE BY FIELD VALUE       SEEKGT,  SEEKGE,  SEEKEQ
TABLE INFORMATION                    BOF,  EOF, LOF, FILEATTR
DATA EXCHANGE                        INSERT, RETRIEVE, UPDATE,
                                     DELETE
TRANSACTION PROCESSING               BEGINTRANS, COMMITTRANS,
                                     CHECKPOINT, ROLLBACK,
                                     SAVEPOINT

SOME ISAM STATEMENT USAGE RULES PARALLEL BASIC RULES, WHILE OTHERS ARE MORE SPECIFIC DUE TO THE CHARACTERISTICS OF THE ISAM FILE. FOR EXAMPLE, THE BASIC LOF FUNCTION, WHICH RETURNS THE LENGTH OF A SEQUENTIAL FILE OR THE NUMBER OF RECORDS IN A RANDOM-ACCESS FILE, RETURNS THE NUMBER OF RECORDS IN THE SPECIFIED TABLE WHEN USED ON AN ISAM FILE.

THE TYPE...END TYPE STATEMENT IS USED TO DEFINE THE STRUCTURE OF THE RECORD VARIABLES THAT WILL BE USED TO EXCHANGE DATA BETWEEN YOUR PROGRAM AND THE ISAM FILE. THE ELEMENTS IN A TYPE...END TYPE STATEMENT CAN HAVE ANY USER-DEFINED TYPE OR BASIC DATA TYPE EXCEPT VARIABLE-LENGTH STRINGS AND DYNAMIC ARRAYS.

HOWEVER, THE TYPE...END TYPE STATEMENT USED FOR ISAM ACCESS CANNOT CONTAIN BASIC'S SINGLE TYPE. FLOATING-POINT NUMERIC ELEMENTS IN A TYPE...END TYPE STATEMENT USED FOR ISAM ACCESS MUST HAVE DOUBLE TYPE. FIXED-POINT DECIMAL NUMERIC ELEMENTS CAN HAVE BASIC'S NEW CURRENCY TYPE.

SIMILARLY, WHEN YOU NAME THE ELEMENTS OF THE USER-DEFINED TYPE, YOU MUST NAME THEM ACCORDING TO THE ISAM NAMING CONVENTION (WHICH IS A SUBSET OF THE BASIC IDENTIFIER-NAMING CONVENTION).

THE NAME OF THE USER-DEFINED TYPE ITSELF, HOWEVER, IS A BASIC IDENTIFIER AND FOLLOWS THE BASIC NAMING CONVENTION. SIMILARLY, SOME ARGUMENTS TO ISAM STATEMENTS FOLLOW BASIC NAMING CONVENTIONS, WHILE OTHERS FOLLOW THE ISAM SUBSET (DESCRIBED IN THE SECTION "ISAM NAMING CONVENTION" LATER IN THIS CHAPTER).

NOTE.~ THE ISAM STATEMENTS AND FUNCTIONS ARE INTEGRATED INTO THE BASIC LANGUAGE. HOWEVER, WITHIN THE QBX ENVIRONMENT THE ISAM STATEMENTS ARE RECOGNIZED BUT CANNOT BE EXECUTED UNLESS YOU INVOKE A TERMINATE-AND-STAY-RESIDENT (TSR) PROGRAM BEFORE STARTING QBX.

USING A TSR ALLOWS THE QBX TO PROVIDE A FULL ISAM SUPPORT, BUT ONLY WHEN YOUR PROGRAMS ARE NEEDING IT. FOR PROGRAMS THAT DO NOT USE THE ISAM, NOT WHEN YOU ARE LOADING (OR UNLOADING), THE TSR SAVES A SUBSTANTIAL MEMORY.

ISAM VS. OTHER TYPES OF FILE ACCESS

ISAM FILES ARE OFTEN USED IN PLACE OF RANDOM-ACCESS FILES BECAUSE ISAM PROVIDES MORE FLEXIBLE ACCESS TO ANY ARBITRARY RECORD WITHIN THE DATABASE. ALTHOUGH IT IS NOT AN ASCII TEXT FILE, AN ISAM FILE IS A SEQUENTIAL-ACCESS FILE. WHEN AN ISAM FILE IS OPENED, BASIC USES ISAM ROUTINES THAT HANDLE ALL INTERACTION BETWEEN THE OPERATING SYSTEM AND THE ACTUAL FILE.

ISAM ORGANIZES YOUR DATA RECORDS INTO A STRUCTURE CALLED A TABLE. YOU CAN THINK OF THIS TABLE AS A SERIES OF HORIZONTAL "ROWS", EACH ROW CORRESPONDING TO A FULL DATA RECORD. THE TABLE IS ALSO DIVIDED INTO VERTICAL "COLUMNS", EACH COLUMN CORRESPONDING TO ONE OF THE FIELDS IN YOUR DATA RECORDS.

WITH RANDOM-ACCESS FILES YOU USE THE GET STATEMENT TO ACCESS A RECORD BY ITS RECORD NUMBER (WHICH REPRESENTS THE ORDER IN WHICH THE RECORD WAS INSERTED INTO THE FILE). RANDOM ACCESS DOES NOT PROVIDE ACCESS TO RECORDS BASED ON THE VALUES IN SPECIFIC FIELDS WITHIN THE RECORD.

WHEN YOU ACCESS AN ISAM FILE, ISAM'S INDEXING AND SEEK OPERAND STATEMENTS ALLOW YOU TO TEST THE VALUES IN A SPECIFIED GROUP OF "VERTICAL" FIELDS (COLUMNS) AGAINST A STATED CONDITION. PUT ANOTHER WAY, A RANDOM-ACCESS FILE IS ACCESSIBLE BY ROW NUMBER ONLY, LIKE A LIST.

WITH AN ISAM FILE YOU CAN ACCESS RECORDS EITHER BY RELATIVE POSITION (ROW) OR BY THE CONTENTS OF ANY FIELD IN A SPECIFIED COLUMN. THE ISAM STATEMENTS GIVE YOU THE ABILITY TO ACCESS SPECIFIC RECORDS IN THE FILE WITH THE SPEED OF A RANDOM-ACCESS FILE, BUT WITH A GREAT DEAL MORE FLEXIBILITY.

THE FOLLOWING LIST CONTRASTS THE UNIT OF ACCESS USED BY BASIC FILE TYPES:

FILE TYPE      ACCESS UNIT
- - - - - - - - - - - - - - - - - - - - - - - -
BINARY         BY BYTE
SEQUENTIAL     BY LINE OR BY BYTE
RANDOM         BY RECORD NUMBER (I.E., ROW ONLY)
ISAM           BY POSITION, OR BY THE VALUE OF ANY FIELD
               (OR GROUP OF FIELDS) WITHIN A TABLE

ISAM ALSO DIFFERS FROM OTHER RECORD-INDEXING APPROACHES BECAUSE ALL THE INFORMATION RELATING TO THE RECORDS IS CONTAINED IN A SINGLE FILE THAT ALSO CONTAINS THE DATA RECORDS THEMSELVES. THIS CAN GREATLY FACILITATE USER MANAGEMENT OF COMPLICATED DATABASES WITHOUT SACRIFICING SPEED AND CONVENIENCE.

THE ISAM PROGRAMMING MODEL

THE FOLLOWING CHART DESCRIBES THE GENERAL SEQUENCE OF STEPS USED IN ISAM DATABASE PROGRAMMING. IT COMPARES THE ISAM MODEL AND STATEMENTS TO THE CORRESPONDING TASKS AND STATEMENTS USED WITH RANDOM-FILE ACCESS.

TASK TO BE PERFORMED     ISAM APPROACH    RANDOM-FILE APPROACH
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ASSOCIATE PROGRAM      USE DIM AND        USE DIM AND
VARIABLES WITH         TYPE...END TYPE.   TYPE...END TYPE.
DATABASE RECORDS.

ACCESS RECORDS.        USE OPEN TO ACCESS  USE OPEN TO ACCESS
A TABLE.               A FILE..

CHANGE PRESENTATION   USE CREATEINDEX   NO SUPPORT PROVIDED.
ORDER OF RECORDS      AND/OR SETINDEX.  REQUIRES SORTING CODE
ACCORDING TO VALUE                     UNLESS RECORD-INSERTION
IN A SPECIFIED FIELD.                  ORDER IS ADEQUATE.

SPECIFY RECORD TO WORK  USE MOVE DEST TO MOVE   USE GET TO
WITH.                   BY ROW OR  SEEK OPERAND RETRIEVE A
                        TO MOVE TO A RECORD     RECORD BY
                        CONTAINING A SPECIFIED  RECORD NUMBER,
                        FIELD VALUE.            OR IF THAT IS
                                                NOT ADEQUATE,
                                                REQUIRES
                                                SEARCHING CODE
                                                TO DETERMINE
                                                WHICH RECORD
                                                TO GET.

DATA EXCHANGE. USE RETRIEVE TO ASSIGN  USE GET AND PUT FOR
               A RECORD FROM A TABLE   SIMPLE FETCHING AND
               TO PROGRAM VARIABLES.   OVERWRITING OF EXISTING
               USE UPDATE TO ASSIGN    RECORDS. TO DELETE A
               PROGRAM VARIABLES TO A  RECORD, YOU TYPICALLY
               RECORD IN A TABLE. USE  CODE TO MARK IT FOR
               INSERT TO INSERT A      DELETION; WRITE A
               RECORD IN A TABLE. USE  TEMPORARY FILE THAT
               DELETE TO DELETE A      OMITS IT; THEN DELETE
               RECORD FROM A TABLE.    THE ORIGINAL FILE; AND
               WHEN YOU OVERWRITE,     FINALLY, RENAME THE
               INSERT, OR DELETE       TEMPORARY FILE WITH THE
               RECORDS, ISAM HANDLES   ORIGINAL FILENAME.
               ALL TABLE AND INDEX     TO DO A SIMPLE INSERT
               MAINTENANCE             OF A RECORD, YOU MUST
               TRANSPARENTLY.          CODE TO KEEP TRACK OF
               THE NUMBER OF RECORDS,
               THEN INSERT EACH NEW
               RECORD AS NUMBER N+1.
               INSERTING AT A SPECIFIC
               POSITION REQUIRES CODE
               TO SWAP RECORDS.

CHANGE PRESENTATION      USE CREATEINDEX   REQUIRES SORTING
ORDER OF RECORDS TO GET  OR SETINDEX.      CODE.
A DIFFERENT PERSPECTIVE
ON DATA.

CLOSE THE FILE(S).       USE CLOSE FOR TABLES.   USE CLOSE.

ISAM CONCEPTS AND TERMS

MANY TERMS USED IN DESCRIBING ISAM ARE FAMILIAR, HOWEVER WHEN USED WITH ISAM MANY TERMS HAVE SPECIALIZED CONNOTATIONS. FOR EXAMPLE, WHEN USING RANDOM FILE ACCESS, ONE TYPICALLY THINKS OF A FILE AS A COLLECTION OF LOGICALLY RELATED RECORDS.

IN ISAM SUCH A COLLECTION OF RECORDS IS CALLED A "TABLE", SINCE AN ISAM DISK FILE (CALLED A "DATABASE") CAN CONTAIN MULTIPLE AND DISTINCT COLLECTIONS OF RECORDS. THIS SECTION EXPLAINS SOME FUNDAMENTAL ISAM CONCEPTS AND TERMS.

FIELD.~ A SINGLE DATA ITEM CONSTITUENT OF A RECORD.

RECORD.~ A COLLECTION OF LOGICALLY RELATED DATA-ITEM FIELDS. THE ASSOCIATION OF THE FIELDS IS DEFINED BY A TYPE... END TYPE STATEMENT IN YOUR PROGRAM.

ROW.~ SYNONYM FOR RECORD. WHEN PLACED IN AN ISAM TABLE, THE COLLECTION OF FIELDS IN A SPECIFIC RECORD IS REFERRED TO AS A ROW. THUS, A ROW IN A TABLE CORRESPONDS TO A SINGLE DATA RECORD. SEE FIGURE 10.1.

TABLE.~ AN ORDERED COLLECTION OF RECORDS (ROWS), EACH OF WHICH CONTAINS A SINGLE DATA RECORD. THE RECORDS IN A TABLE HAVE SOME LOGICAL RELATIONSHIP TO ONE ANOTHER. THE DEFAULT ORDER OF RECORDS IN THE TABLE CORRESPONDS TO THE ORDER IN WHICH RECORDS WERE ADDED.

COLUMN.~ EACH COLUMN IN A TABLE HAS A NAME. A COLUMN IN A TABLE IS THE COLLECTION OF ALL FIELDS HAVING THE SAME COLUMN NAME. THUS, A COLUMN IS A VERTICAL COLLECTION OF FIELDS IN THE SAME WAY A ROW IS A HORIZONTAL COLLECTION OF FIELDS.

DATABASE.~ A COLLECTION OF TABLES AND INDEXES CONTAINED IN A DISK FILE.

INDEX.~ AN INDEPENDENT STRUCTURE WITHIN THE ISAM FILE CREATED WHEN A CREATEINDEX STATEMENT IS EXECUTED. EACH INDEX REPRESENTS AN ALTERNATIVE ORDER FOR PRESENTATION OF THE RECORDS IN THE TABLE. THE ORDER IS BASED ON THE RELATIVE VALUES OF EACH DATA ITEM IN THE COLUMN (OR COLUMNS) SPECIFIED IN THE CREATEINDEX STATEMENT.

YOU MIGHT WANT TO THINK OF AN INDEX AS A "VIRTUAL TABLE", THAT IS, A VIRTUAL ORDERING OF THE TABLE'S DATA RECORDS. AN INDEX MUST HAVE A NAME, AND MAY HAVE OTHER ATTRIBUTES. ANY INDEX YOU CREATE IS SAVED AND MAINTAINED AS PART OF THE DATABASE UNTIL IT IS EXPLICITLY DELETED. FIGURE 10.2 ILLUSTRATES A TABLE.

BESIDE THE TABLE, A LIST OF POSITIONS INDICATES WHERE EACH RECORD ACTUALLY RESIDES IN THE TABLE. BENEATH THE TABLE, A DIAGRAM ILLUSTRATES HOW AN INDEX ON ONE OF THE COLUMNS (THE INVOICE COLUMN) CHANGES THE PRESENTATION ORDER OF THE DATA WHEN THE INDEX IS SPECIFIED.

SPECIFYING AN INDEX IS A SEPARATE STEP IN WHICH THE ORDER OF THE INDEXED COLUMN (OR COLUMNS) IS IMPOSED ON ANY PRESENTATION OF THE TABLE'S RECORDS. TO PRESENT A TABLE'S RECORDS IN THE INDEX'S ORDER, YOU FIRST SPECIFY THAT INDEX IN A SETINDEX STATEMENT.

IF YOU WERE TO CREATE, AND THEN SPECIFY THE INDEX ON THE INVOICE COLUMN IN THE PRECEDING FIGURE, THE RECORDS WOULD BE PRESENTED IN THE ORDER SHOWN IN THE FINAL PART OF FIGURE 10.2, WITH THE RECORD AT TABLE POSITION 3 FIRST, FOLLOWED BY THE RECORD AT TABLE POSITION 6, FOLLOWED BY THE RECORD AT TABLE POSITION 2, AND SO ON.

NULL INDEX.~ THE DEFAULT INDEX FOR A TABLE; THAT IS, THE PRESENTATION ORDER OF THE RECORDS WHEN NO USER-CREATED INDEX IS SPECIFIED. WHEN THE NULL INDEX IS IN EFFECT (FOR INSTANCE, WHEN THE TABLE IS FIRST OPENED), THE ORDER OF THE RECORDS IS THE ORDER IN WHICH THEY WERE INSERTED INTO THE TABLE. IN FIGURE 10.2, THIS ORDER IS ILLUSTRATED BY THE TABLE ITSELF.

RECORD ORDER.~ THE ACTUAL PHYSICAL ORDER OF RECORDS ON DISK IS ARBITRARY BECAUSE WHENEVER RECORDS ARE DELETED FROM A TABLE, THEIR PHYSICAL DISK POSITIONS ARE FILLED BY THE NEXT RECORDS INSERTED IN THE TABLE. THIS OPTIMIZES ACCESS SPEED AND DISK-SPACE USAGE.

FOR EXAMPLE, IF YOU DELETE THE THIRD RECORD ADDED TO THE TABLE, THEN ADD THE SIXTH RECORD, THE SIXTH RECORD WOULD BE PLACED IN THE PHYSICAL DISK POSITION PREVIOUSLY OCCUPIED BY THE THIRD RECORD AS SHOWN IN FIGURE 10.3.

INSERTION ORDER.~ THE ORDER IN WHICH THE RECORDS ARE INSERTED INTO THE TABLE. THIS ORDER CORRESPONDS TO THE ORDER IMPOSED BY THE NULL INDEX (THE DEFAULT INDEX).

PRESENTATION ORDER.~ THE APPARENT ORDER IMPOSED ON THE TABLE BY THE CURRENT INDEX. NOTE THAT FILE-SPACE OPTIMIZATION HAS THE SIDE EFFECT THAT SUBORDERING OF RECORDS WHEN AN INDEX IS APPLIED CORRESPONDS TO THE ACTUAL PHYSICAL ORDER OF RECORDS ON THE DISK. IF YOU WANT PRESENTATION ORDER TO INCLUDE SPECIFIC SUBORDERING, USE A COMBINED INDEX.

COMBINED INDEX.~ AN INDEX THAT IS BASED ON MORE THAN A SINGLE COLUMN. SPECIFYING A COMBINED INDEX ENFORCES A SPECIFIC SUBORDERING ON THE PRESENTATION ORDER OF THE RECORDS.

INDEXED VALUE.~ THE VALUE (OR COMBINATION OF VALUES) THAT DETERMINES A RECORD'S POSITION ON A PARTICULAR INDEX. WHILE AN INDEX IS A COLLECTION OF INDEXED VALUES, THERE IS ONE INDEXED VALUE FOR EACH RECORD (ROW). THEREFORE, WITH A COMBINED INDEX, THE INDEXED VALUE IS THE COMBINATION OF THE CONSTITUENT FIELDS OF THE INDEX.

KEY VALUE.~ A VALUE AGAINST WHICH INDEXED VALUES ARE TESTED WHEN USING A SEEK OPERAND STATEMENT TO SEEK A RECORD THAT MEETS THE SPECIFIED CONDITION.

UNIQUE.~ INDEX AN INDEX REQUIRING THAT EACH INDEXED VALUE (I.E., EACH FIELD IN THE COLUMN ON WHICH THE INDEX IS DEFINED) BE DIFFERENT FROM ALL OTHERS. WHEN YOU USE THE CREATEINDEX STATEMENT TO CREATE AN INDEX, YOU CAN SPECIFY IT AS "UNIQUE".

FOR EXAMPLE, IF YOU SPECIFIED A UNIQUE INDEX ON CLIENTCODE COLUMN IN THE TABLE IN FIGURE 10.2, ISAM WOULD GENERATE A TRAPPABLE ERROR IF THE VALUE OF ANY CLIENTCODE FIELD EVER DUPLICATED THE VALUE OF ANY OTHER CLIENTCODE FIELD IN THE TABLE. YOU COULD USE A UNIQUE INDEX TO PREVENT A USER FROM ASSIGNING THE SAME CLIENTCODE TO TWO DIFFERENT CLIENTS.

CURRENT POSITION.~ THE FOCUS OF ACTIVITY IN A TABLE. UNDERSTANDING "POSITION" IN THE TABLE IS IMPORTANT BECAUSE, IN ALL CASES, POSITION IS RELATIVE TO SOME STRUCTURE WITHIN THE TABLE THAT DOESN'T EXIST IN OTHER BASIC FILES. IN AN ISAM TABLE, "CURRENCY" (MEANING THE CURRENT POSITION, NOT THE NEW CURRENCY DATA TYPE) IS DETERMINED BY SEVERAL FACTORS.

SINCE MULTIPLE INDEXES MAY BE CREATED ON ANY TABLE, THE CURRENT POSITION DEPENDS ON WHICH INDEX HAS BEEN SPECIFIED. THERE IS NO CONCEPT OF A "CURRENT TABLE" IN ISAM, BUT THERE IS ALWAYS A "CURRENT INDEX" FOR EACH OPEN ISAM TABLE. IF A TABLE CONTAINS ANY RECORDS, ONE RECORD IS THE "CURRENT RECORD", EXCEPT IN TWO CASES:

O.~ THERE IS NO CURRENT RECORD AT THE BEGINNING OR END OF THE TABLE (WHEN BOF OR EOF RETURN TRUE).
O.~ THERE IS NO CURRENT RECORD AFTER THE UNSUCCESSFUL EXECUTION OF A SEEK OPERAND STATEMENT (BECAUSE EOF THEN RETURNS TRUE).

OTHERWISE, EVERY OPEN TABLE HAS A CURRENT INDEX AND A CURRENT RECORD (IF IT CONTAINS ANY RECORDS).

CURRENT INDEX.~ THE INDEX WHOSE SORTING ORDER DETERMINES THE ORDER OF APPEARANCE OF A TABLE'S RECORDS. WHEN A TABLE IS FIRST OPENED, THE NULL INDEX IS THE CURRENT INDEX. ONCE AN INDEX IS CREATED ON A COLUMN OR COMBINATION OF COLUMNS, SPECIFYING IT AS THE CURRENT INDEX IMPOSES THE SORTING ORDER OF THAT COLUMN OR COMBINATION OF COLUMNS ON THE PRESENTATION ORDER OF ALL RECORDS IN THE TABLE. THAT INDEX REMAINS THE CURRENT INDEX ON THAT TABLE UNTIL IT IS DELETED, A DIFFERENT INDEX IS SPECIFIED FOR THAT TABLE, OR THE TABLE IS CLOSED. EVERY OPEN TABLE HAS A CURRENT INDEX.

CURRENT RECORD.~ THE FOCUS OF ACTIVITY BETWEEN YOUR PROGRAM AND AN ISAM TABLE. DIRECTLY FOLLOWING A SETINDEX STATEMENT, THE CURRENT RECORD IS THE RECORD WITH THE SMALLEST INDEXED VALUE, ACCORDING TO THE INDEX SPECIFIED.

ISAM PROVIDES MANY STATEMENTS FOR MAKING DIFFERENT RECORDS CURRENT, AND FOR ALTERING THE CURRENT RECORD. AT ANY TIME, ONE AND ONLY ONE RECORD CAN BE THE CURRENT RECORD IN EACH OPEN TABLE. WHEN THE NULL INDEX IS SPECIFIED WITH SETINDEX, THE CURRENT RECORD IS THE RECORD THAT WAS INSERTED INTO THE TABLE FIRST.

FOCUS.~ THE FOCUS OF DATA EXCHANGE (THAT IS, THE RECORD AFFECTED BY AN ISAM STATEMENT THAT FETCHES, OVERWRITES, INSERTS, OR DELETES DATA IN A TABLE). THE FOCUS IS ALWAYS THE CURRENT RECORD, AS DETERMINED BY THE CURRENT INDEX.

ISAM ENGINE.~ ROUTINES USED BY ISAM TO ACCESS AND MAINTAIN THE DATABASE.
ISAM FILE.~ A DATABASE CREATED AND MAINTAINED USING ISAM.
DATA DICTIONARY.~ TABLES AND INDEXES USED BY THE ISAM ENGINE IN MAINTAINING THE DATABASE.

ISAM COMPONENTS

ISAM WORKS BY APPLYING ROUTINES CONTAINED IN THE ISAM ENGINE TO A PHYSICAL DISK STRUCTURE CALLED AN ISAM FILE. THE FOLLOWING SECTIONS DESCRIBE THESE COMPONENTS AND THE TABLE/INDEX MODEL FOR RECORD ACCESS.

THE ISAM ENGINE

THE ISAM ENGINE CREATES A TABLE REPRESENTING YOUR DATA RECORDS TO ENHANCE THE STORAGE OF AND RAPID ACCESS TO EACH RECORD. THE RELATIONSHIP BETWEEN THE INDIVIDUAL RECORDS IN THE TABLE AND WHAT IS ACTUALLY IN MEMORY AS YOUR PROGRAM RUNS IS DETERMINED BY THE ISAM ENGINE.

THIS LETS YOU EASILY MANIPULATE THE RECORDS OF VERY LARGE FILES AS THOUGH THEY ALL FIT IN MEMORY AT ONCE. THE ACTUAL ORDER OF PHYSICAL STORAGE OF RECORDS IN THE FILE IS UNIMPORTANT BECAUSE ISAM ALLOWS YOU TO DEAL WITH THE RECORDS AS THOUGH THEY WERE STORED IN A VARIETY OF CONVENIENT WAYS.

YOUR PROGRAM CAN PRESENT THE RECORDS IN THE FILE ACCORDING TO THE SORTING ORDER OF ANY COLUMN IN THE TABLE SIMPLY BY CREATING, AND THEN SPECIFYING AN INDEX. USING THE SETINDEX STATEMENT IS FUNCTIONALLY EQUIVALENT TO SORTING THE RECORDS ACCORDING TO THE VALUES OF THE CONSTITUENT FIELDS OF THE INDEXED COLUMN (OR COMBINATION OF COLUMNS).

WHEN INDEXES ARE CREATED, THEY BECOME PART OF THE DATABASE. THEY CAN THEN BE SAVED AS PART OF THE DATABASE, OR DELETED. WHEN SAVED, THE ISAM FILE CONTAINS THE INDEXES YOU'VE CREATED, IN ADDITION TO THE TABLES CONTAINING THE DATA RECORDS THEMSELVES. INDEXES ARE DESCRIBED IN DETAIL IN THE SECTION "CREATING AND SPECIFYING INDEXES ON TABLE COLUMNS" LATER IN THIS CHAPTER.

THE PARTS OF THE ISAM FILE

ISAM PLACES YOUR DATA RECORDS IN THE TABLE (OR TABLES) YOU SPECIFY. EACH TABLE REPRESENTS A GROUP OF LOGICALLY RELATED RECORDS, BUT THE LOGIC OF THE GROUPINGS IS COMPLETELY UP TO YOU. FOR EXAMPLE, IT MIGHT BE USEFUL TO KEEP ONE TABLE IN THE DATABASE FOR YOUR INVENTORY AND ANOTHER FOR CLIENTS.

INFORMATION DESCRIBING AN ISAM FILE IS MAINTAINED WITHIN THE FILE IN A SET OF SYSTEM TABLES CALLED THE DATA DICTIONARY. THE DATA DICTIONARY ITSELF IS INVISIBLE TO YOUR PROGRAM, AND YOU NEVER HAVE TO DEAL WITH IT IF YOU DON'T WANT TO. IN FACT, IT IS SAFEST TO SIMPLY LET THE ISAM ENGINE HANDLE ALL INTERACTION WITH THE DATA DICTIONARY, SINCE CORRUPTING IT COULD DESTROY YOUR DATABASE.

INFORMATION IN THE DATA DICTIONARY INCLUDES TABLE NAMES, INDEXES AND INDEX NAMES, COLUMN NAMES, AND ALL THE OTHER INFORMATION USED BY ISAM TO ACCESS AND MANIPULATE THE RECORDS IN RESPONSE TO THE ISAM STATEMENTS.

YOU CAN CREATE ANY NUMBER OF TABLES WITHIN A DATABASE FILE, ALTHOUGH THE NUMBER OF TABLES YOU CAN OPEN SIMULTANEOUSLY HAS AN UPPER LIMIT OF 13 AND DECREASES EACH TIME AN ADDITIONAL DATABASE IS OPENED. A PRACTICAL MAXIMUM OF FOUR DATABASES CAN BE OPENED AT ONCE. THE SECTION "USING MULTIPLE FILES" DESCRIBES HOW MANY TABLES CAN BE OPENED, RELATIVE TO THE NUMBER OF OPEN DATABASES.

ISAM FILE ALLOCATION AND GROWTH

BECAUSE AN ISAM FILE CONTAINS DESCRIPTIVE INFORMATION, IT HAS SOME FILE-SIZE OVERHEAD. ADDITIONALLY, TO OPTIMIZE ACCESS SPEED AND FLEXIBILITY, ISAM FILES GROW PERIODICALLY IN LARGE CHUNKS (32 KBS. PER CHUNK), RATHER THAN IN RECORD-SIZED INCREMENTS AS SINGLE RECORDS ARE ADDED. A DATABASE CONTAINS A HEADER OF ABOUT 3 KBS.

EACH TABLE HAS 4 KBS. OF OVERHEAD BEYOND ITS ACTUAL DATA RECORDS; EACH INDEX REQUIRES AT LEAST 2 KBS. THE DATA DICTIONARY CONSISTS OF FIVE SYSTEM TABLES PLUS EIGHT SYSTEM INDEXES, RESULTING IN A TOTAL INITIAL OVERHEAD OF ABOUT 39 KBS. THEREFORE, THE SMALLEST ISAM FILE IS 64 KBS.

THOUGH AN ISAM FILE WITH A SINGLE RECORD IS 64 KBS., THERE IS CONSIDERABLE ROOM FOR ADDING DATA RECORDS WITHIN THAT 64 KBS. FILE BEFORE THE NEXT 32 KBS. CHUNK IS ADDED. THE INITIAL COMBINATION OF SYSTEM TABLES AND SYSTEM INDEXES IS ABOUT 39 KBS.; THE REMAINING 25 KBS. ARE USED FOR YOUR DATA RECORDS AND THE NEW INDEXES AND TABLES YOU CREATE.

WHEN TO USE ISAM

FOR DATA FILES TOO LARGE TO COMPLETELY LOAD INTO MEMORY, ISAM VASTLY SIMPLIFIES FILE MANIPULATION BECAUSE ISAM SUPPORT REPLACES THE KIND OF SORTING THAT CAN ONLY OTHERWISE BE ACCOMPLISHED EFFICIENTLY BY LOADING ALL DATA RECORDS IN MEMORY SIMULTANEOUSLY.

THIS MAKES ISAM AN EXCELLENT METHOD FOR DEALING WITH LARGE AMOUNTS OF DATA WHICH REQUIRE SORTED ACCESS. AN ISAM FILE CAN BE AS LARGE AS 128 MEGABYTES. ISAM HANDLES ALL THE WORK OF MOVING PORTIONS OF SUCH A HUGE FILE IN AND OUT OF MEMORY DURING RECORD MANIPULATION.

WHENEVER DATA RECORDS CONTAIN MANY FIELDS THAT NEED TO BE EXAMINED IN A VARIETY OF WAYS, USING ISAM SIMPLIFIES THE PROGRAMMING. ALTHOUGH YOU CAN WRITE CODE TO SORT OR INDEX RANDOM-ACCESS FILES, ISAM INTEGRATES THESE TASKS FOR YOU WITH HIGH-LEVEL STATEMENTS THAT MANIPULATE SOPHISTICATED FILE STRUCTURES.

THIS LETS YOU EASILY MANIPULATE RECORDS BY THE VALUES IN SPECIFIC FIELDS, AND IS FAR MORE FLEXIBLE THAN A RANDOM FILE'S ONE-DIMENSIONAL ORDERING BY RECORD NUMBER. (FOR AN EXAMPLE OF HOW MUCH BASIC CODE JUST ONE INDEX FOR A RANDOM FILE REQUIRES, SEE THE PROGRAM INDEX.BAS LISTED IN CHAPTER 3, "FILE AND DEVICE I/O").

HOWEVER, IF DISK SPACE IS AT A PREMIUM, DON'T AUTOMATICALLY CHOOSE ISAM FOR SHORT, EASILY-SORTED FILES OF RELATIVELY CONSTANT SIZE. THESE MAY BE BETTER HANDLED USING OTHER METHODS (FOR EXAMPLE, BY CREATING AND USING HASH TABLES). HOWEVER, IF YOU NEED TO SORT ON DIFFERENT FIELDS AT DIFFERENT TIMES, OR IF YOU NEED VERY FAST ACCESS TO RECORDS ACCORDING TO COMPLEX SUBSORTING ORDERS.

THE BENEFITS OF THE ISAM FILE QUICKLY MAKE UP FOR ITS OVERHEAD. ALSO, CONSIDER THAT FOR VERY LARGE FILES, THE AMOUNT OF DESCRIPTIVE INFORMATION RELATIVE TO ACTUAL RECORDS REMAINS RELATIVELY CONSTANT, SO THE PERCENTAGE OF THE FILE DEVOTED TO OVERHEAD DECREASES PROGRESSIVELY.

THE TABLE/INDEX MODEL

IN ISAM, TABLES AND INDEXES REPRESENT VARIOUS FUNDAMENTAL ARRANGEMENTS OF THE DATA RECORDS. WHEN YOU INSERT A RECORD IN AN ISAM TABLE, ITS PLACE IN THE TABLE IS THE RESULT OF A PROCESS THAT OPTIMIZES FILE SIZE AND SPEED OF ACCESS. REFERENCES TO THE RECORD ARE IMMEDIATELY PLACED IN ALL OF THAT TABLE'S EXISTING INDEXES, INCLUDING THE NULL INDEX.

SO THE PRESENTATION ORDER OF ALL RECORDS IS ALWAYS INTERNALLY CONSISTENT. THE DEFAULT ORDER FOR A TABLE IS THE CHRONOLOGICAL ORDER OF INSERTION. SPECIFYING AN INDEX OTHER THAN THE NULL INDEX ORDERS THE RECORDS OF THE TABLE BY THE SORTING ORDER OF EACH FIELD IN THE INDEXED COLUMN (OR COMBINATION OF COLUMNS).

FOR EXAMPLE, IF EACH ROW IN A TABLE CONTAINS FIVE COLUMNS, YOU CAN CREATE INDEXES ON ANY, OR ALL, OR ANY COMBINATION OF THE FIVE COLUMNS. WHEN YOU SPECIFY ONE OF THESE INDEXES (WITH SETINDEX), YOU IMPOSE THE SORT ORDER OF THE INDEX ON THE PRESENTATION ORDER OF THE RECORDS.

(THE SORT ORDER IS EITHER NUMERIC OR ALPHABETIC, DEPENDING ON THE DATA TYPE OF THE COLUMN OR COLUMNS). SPECIFYING A DIFFERENT INDEX CHANGES THE PRESENTATION ORDER OF THE RECORDS. AS YOU ADD AND REMOVE RECORDS FROM THE TABLE, ISAM MAINTAINS ALL RELEVANT INFORMATION ABOUT THE TABLE.

FIGURE 10.4 ILLUSTRATES A SIMPLE TABLE IN WHICH EACH RECORD IS A COLLECTION OF SIX USER-DEFINED FIELDS. THE TABLE CAN BE REPRESENTED AS FOUR ROWS, EACH HAVING SIX COLUMNS. THE VALUES IN EACH FIELD IN THE NUMBER COLUMN REPRESENT THE ORDER IN WHICH EACH RECORD WAS ADDED TO THE TABLE.

IN PRACTICE, YOU WOULD PROBABLY NEVER DEFINE SUCH A COLUMN, SINCE INSERTION ORDER, THE NULL INDEX, IS THE DEFAULT ORDERING OF THE RECORDS IN AN ISAM TABLE, BUT IT IS INCLUDED HERE FOR ILLUSTRATIVE PURPOSES.

IN A RANDOM-ACCESS FILE, THE NUMBER COLUMN WOULD CORRESPOND TO THE RECORD NUMBER, AND WOULD BE THE ONLY WAY YOU COULD REFERENCE A RECORD WITHOUT WRITING SPECIAL CODE TO SORT THE FILE. HOWEVER, BECAUSE THIS IS AN ISAM TABLE, YOU CAN USE AN INDEX TO SPECIFY A PRESENTATION ORDER THAT CORRESPONDS TO THE SORT ORDER OF ANY OF THE COLUMNS.

SUPPOSE YOU WANTED TO ORGANIZE A CELEBRATION, AND YOU WANTED TO COMPILE AN INVITATION LIST THAT INCLUDED ONLY WOMEN. WITH JUST THE OPEN, CREATEINDEX, AND SETINDEX STATEMENTS YOU COULD CREATE AND SPECIFY AN INDEX ON THE SEX COLUMN.

SINCE F SORTS BEFORE M, ALL THE WOMEN IN THE TABLE WOULD BE PRESENTED BEFORE ANY OF THE MEN, AS SHOWN IN FIGURE 10.5. WITH THIS ORDER THE PROGRAM COULD EASILY START FROM THE FIRST RECORD, DISPLAY ITS DATA, THEN USE THE MOVENEXT STATEMENT TO MAKE EACH SUCCESSIVE RECORD THE CURRENT RECORD.

CONVERSELY, IF YOU WANTED TO INVITE ONLY MEN, THE PROGRAM COULD START FROM THE LAST RECORD (USING THE MOVELAST STATEMENT), AND THEN USE MOVEPREVIOUS TO TRAVERSE THE RECORDS IN REVERSE ORDER. AS THE PROGRAM DISPLAYED EACH PREVIOUS RECORD, YOU COULD CHOOSE AMONG THE MEN.

AN INDEX CAN BE SPECIFIED ON EACH COLUMN OF A TABLE. FIGURE 10.6 PRESENTS THE TABLE INFORMATION INDEXED ON THE SPORT COLUMN.

YOU CAN ALSO CREATE COMBINED INDEXES BY "COMBINING" THE VALUES IN SEVERAL FIELDS SO RECORDS APPEAR SORTED, FIRST BY ONE FIELD, THEN SORTED BY ANOTHER, AND SO ON. FOR EXAMPLE, YOU COULD CREATE AND SPECIFY A COMBINED INDEX THAT PRESENTED THE RECORDS SORTED FIRST BY THE PHONE COLUMN, THEN BY THE BIRTHDAY COLUMN.

THIS WOULD PRESENT THE RECORDS SORTED FIRST BY HOUSEHOLD, THEN WITHIN EACH HOUSEHOLD, BY THE ORDER OF THE BIRTHDAYS OF EACH PERSON WITH THE SAME PHONE NUMBER, AS SHOWN IN FIGURE 10.7.

THE PRECEDING EXAMPLES ARE FOR ILLUSTRATIVE PURPOSES. NORMALLY, WHEN DESIGNING A PROGRAM YOU WOULD PROVIDE THE USER WITH SEVERAL USEFUL INDEXES ON THE RECORDS, RATHER THAN DESIGNING THE PROGRAM TO LET THE USER CREATE INDEXES AS NEEDED. HOWEVER, IF YOU WANT TO LET USERS CREATE THEIR OWN INDEXES, YOU CAN DO SO USING THE ISAM STATEMENTS AND FUNCTIONS.

A SAMPLE DATABASE

THE FOLLOWING SECTIONS DESCRIBE A TABLE WITHIN AN ISAM DATABASE FILE THAT COULD BE USED BY A LIBRARY TO KEEP TRACK OF ITS INVENTORY OF BOOKS. EXAMPLES DEMONSTRATE HOW TO CREATE OR OPEN THE DATABASE AND VIEW THE RECORDS FROM SEVERAL DIFFERENT PERSPECTIVES.

(THIS PROGRAM, BOOKLOOK.BAS, AS WELL AS ITS ASSOCIATED .MAK FILE (BOOKLOOK.MAK), SECONDARY MODULES (BOOKMOD1.BAS, BOOKMOD2.BAS, BOOKMOD3.BAS), DATABASE FILE (BOOKS.MDB), AND INCLUDE FILE (BOOKLOOK.BI) ARE INCLUDED ON THE DISKS SUPPLIED WITH MICROSOFT BASIC. WHEN YOU RAN THE SETUP PROGRAM, THEY WERE PLACED IN THE DIRECTORY YOU SPECIFIED FOR BASIC SOURCE AND INCLUDE FILES.

DESIGNING THE BOOKSTOCK TABLE

INVENTORY MAINTENANCE OF A BOOK-LENDING LIBRARY CAN BE USED TO ILLUSTRATE THE ISAM APPROACH. ASSUME THAT THE PATRONS OF THE LIBRARY ARE CONCERNED ONLY WITH BOOKS DEALING WITH THE BASIC PROGRAMMING LANGUAGE. FOR EXAMPLE, YOU CAN CREATE A DATABASE CONTAINING A SINGLE TABLE THAT INCLUDES PERTINENT INFORMATION ABOUT ALL THE LIBRARY'S BOOKS ABOUT BASIC.

CREATING, OPENING, AND CLOSING A TABLE

THE STATEMENTS USED FOR CREATING, OPENING, AND CLOSING DATABASES AND TABLES ARE THE FAMILIAR BASIC TYPE... END TYPE, OPEN, AND CLOSE STATEMENTS. HOWEVER, THEY ARE USED DIFFERENTLY WITH ISAM FILES THAN WITH OTHER TYPES OF FILES.

NAMING THE COLUMNS OF THE TABLE

THE FIRST STEP IN CREATING A TABLE IS TO INCLUDE AN APPROPRIATE TYPE...END TYPE STATEMENT IN THE DECLARATIONS PART OF YOUR PROGRAM. THE NAME YOU USE FOR THIS USER-DEFINED TYPE IS AN ARGUMENT TO THE OPEN STATEMENT THAT CREATES THE DATABASE FILE AND THE TABLE, AND MAY BE USED SUBSEQUENTLY WHENEVER THE TABLE IS OPENED. WHEN THE TABLE IS FIRST CREATED, THE NAMES USED FOR THE ELEMENTS IN THE TYPE...END TYPE STATEMENT BECOME THE NAMES OF THE CORRESPONDING COLUMNS IN THE TABLE (SEE FIGURE 10.8).

SPECIFYING THE DATA TYPES OF THE COLUMNS

WHAT CAN APPEAR IN A COLUMN OF A TABLE IS DETERMINED BY THE COLUMN'S DATA TYPE. FOR INSTANCE, A COLUMN HAVING INTEGER TYPE CAN ACCEPT WHOLE NUMBERS IN THE NORMAL INTEGER RANGE. SIMILARLY, A COLUMN HAVING STRING TYPE CAN CONTAIN A STRING AS LARGE AS 32 KBS. ASSIGNING DATA TYPES TO THE COLUMNS MAKES IT POSSIBLE FOR ISAM TO CREATE THE INDEXES THAT CAN BE USED TO CHANGE THE PRESENTATION ORDER OF THE TABLE'S RECORDS.

NOTE.~ ALTHOUGH YOU CAN FETCH AND WRITE DATA THAT HAS THE FOLLOWING CHARACTERISTICS TO AN ISAM TABLE, YOU CANNOT CREATE INDEXES ON THEM:

O.~ STRING COLUMNS LONGER THAN 255 BYTES.
O.~ COLUMNS WITH AGGREGATE (I.E., ARRAY) TYPE.
O.~ COLUMNS WITH STRUCTURE (I.E ., USER-DEFINED) TYPE.

EACH COLUMN IN A TABLE HAS THE DATA TYPE SPECIFIED IN THE TYPE...END TYPE STATEMENT USED AS THE TABLETYPE IN THE OPEN STATEMENT THAT CREATED THE TABLE. DATA TYPES YOU SPECIFY IN AN ISAM TYPE...END TYPE STATEMENT MUST BE ONE OF THOSE SHOWN IN TABLE 10.1.

NOTE THAT BASIC'S SINGLE DATA TYPE IS NOT LEGAL IN ISAM; USE DOUBLE OR CURRENCY INSTEAD. THE FOLLOWING DECLARATION CAN BE USED IN A PROGRAM THAT CREATES OR ACCESSES THE SAMPLE BOOKSTOCK TABLE.

TYPE BOOKS
IDNUM AS DOUBLE    'ID NUMBER FOR THIS COPY
PRICE AS CURRENCY    'ORIGINAL COST OF BOOK
EDITION AS INTEGER    'EDITION NUMBER OF BOOK
TITLE AS STRING * 50    'THE TITLE OF THE BOOK
PUBLISHER AS STRING * 50    'THE PUBLISHER'S NAME
AUTHOR AS STRING * 36    'THE AUTHOR'S NAME
END TYPE

ALTHOUGH BASIC WOULD ACCEPT ELEMENT IDENTIFIERS UP TO 40 CHARACTERS LONG, THE ELEMENTS IN THIS STATEMENT MUST FOLLOW THE ISAM NAMING CONVENTION (SEE THE SECTION "ISAM NAMING CONVENTION" LATER IN THIS CHAPTER), SINCE THEY WILL BECOME THE NAMES OF COLUMNS WITHIN THE ISAM DATABASE FILE. THE ACTUAL NAME OF THE USER-DEFINED TYPE CAN BE ANY VALID BASIC IDENTIFIER HOWEVER, BECAUSE IT IS NEVER ACTUALLY USED WITHIN THE ISAM FILE.

DATA TYPE COERCION

ALTHOUGH BASIC PERFORMS CONSIDERABLE DATA TYPE COERCION IN OTHER SITUATIONS, THE ONLY COERCION PERFORMED BETWEEN YOUR BASIC PROGRAM AND ISAM IS IN RELATION TO SEEK OPERAND STATEMENTS, AND EVEN THEN ONLY BETWEEN INTEGER AND LONG VALUES.

THEREFORE, IF A LONG VALUE IS EXPECTED BY ISAM, AND YOU PASS AN INTEGER, THE INTEGER WILL BE COERCED TO A LONG, AND NO TYPE-MISMATCH ERROR IS GENERATED. HOWEVER, IF YOU TRY TO PASS A LONG WHEN ISAM EXPECTS AN INTEGER, COERCION MAY RESULT IN AN OVERFLOW ERROR.

IN OTHER SITUATIONS, SUCH AS PASSING A CURRENCY VALUE WHEN A DOUBLE IS EXPECTED, A TYPE MISMATCH ERROR IS GENERATED. SINCE BASIC'S DEFAULT DATA TYPE IS SINGLE PRECISION NUMERIC, PASSING A LITERAL (EVEN 0) TO ISAM CAN CAUSE A TYPE MISMATCH (SINCE SINGLE IS NOT A VALID ISAM DATA TYPE).

IN SUCH A CASE, YOU SHOULD APPEND THE TYPE-DECLARATION CHARACTER FOR THE TYPE EXPECTED BY ISAM TO THE NUMBER. EVEN IF YOU RESET THE DEFAULT DATA TYPE WITH A DEF TYPE STATEMENT, IT IS A GOOD IDEA TO SCREEN THE TYPES OF ALL NUMBERS PASSED TO ISAM TO MAKE SURE THEY ARE PROPERLY TYPED AND THAT THEY WILL FIT WITHIN THE RANGE OF THE EXPECTED TYPE.

OPENING THE BOOKSTOCK TABLE

THE DECLARATION OF THE USER-DEFINED TYPE IS ALL THE PREPARATION A SIMPLE PROGRAM NEEDS TO PREPARE FOR OPENING AN ISAM DATABASE AND TABLE. THE FOLLOWING CODE CAN NOW BE USED TO CREATE OR OPEN THE TABLE WITHIN THE ISAM FILE:

'YOU COULD WRITE CODE HERE TO CHECK TO SEE IF THE FILE EXISTS,
'THEN OPEN THE FILE IF IT DOES OR DISPLAY A MESSAGE IF IT DOESN'T.
OPEN "BOOKS.MDB" FOR ISAM BOOKS "BOOKSTOCK" AS #1

USING OPEN AND CLOSE WITH ISAM

TO OPEN A TABLE, YOU USE THE TRADITIONAL BASIC OPEN STATEMENT WITH ARGUMENTS AND CLAUSES SPECIFIC TO ISAM. WHENEVER YOU OPEN A TABLE, YOU MUST SPECIFY THE DATABASE FILE THAT CONTAINS THE TABLE. THE SYNTAX FOR AN ISAM OPEN IS AS FOLLOWS:

OPEN DATABASE$ FOR ISAM TABLETYPE TABLENAME$ AS #FILENUMBER%

ARGUMENTS:

DATABASE$.~ A STRING EXPRESSION REPRESENTING A DOS FILENAME, SO IT FOLLOWS OPERATING-SYSTEM FILE-NAMING RESTRICTIONS. THIS ARGUMENT CAN INCLUDE A DRIVE LETTER AND A PATH.

TABLETYPE.~ A BASIC IDENTIFIER THAT SPECIFIES A USER-DEFINED TYPE ALREADY DECLARED IN THE PROGRAM. NOTE THAT, UNLIKE THE OTHER ARGUMENTS, IT CANNOT BE A STRING EXPRESSION.

TABLENAME$.~ A STRING EXPRESSION THAT FOLLOWS THE ISAM NAMING CONVENTION.

FILENUMBER%
AN INTEGER WITHIN THE RANGE 1-255, THE SAME AS IN THE TRADITIONAL BASIC OPEN STATEMENT. NOTE THAT FILENUMBER% IS ASSOCIATED WITH BOTH THE TABLENAME OF THE TABLE BEING OPENED AND THE DATABASE FILE (DATABASE$) ITSELF CONTAINING THE TABLE.

THEREFORE, THE SAME DATABASE$ CAN APPEAR IN ANY NUMBER OF OPEN STATEMENTS, EACH OF WHICH OPENS A DIFFERENT TABLE (WITH A UNIQUE FILENUMBER%) IN THE SAME DATABASE FILE. YOU CAN USE FREEFILE TO GET AVAILABLE VALUES FOR FILENUMBER%.

THE STRING ARGUMENTS ARE NOT CASE SENSITIVE, SO YOU CAN USE INCONSISTENT CAPITALIZATION IN ANY OF THESE REFERENCES. THE CLOSE STATEMENT IS THE SAME FOR AN ISAM DATABASE AS FOR ANY OTHER FILE:

CLOSE #FILENUMBER%, #FILENUMBER% ...

OPENING A TABLE

THE FOR ISAM CLAUSE SIMPLY REPLACES THE FOR OUTPUT (OR APPEND, OR INPUT) CLAUSE USED FOR OTHER SEQUENTIAL-FILE ACCESS. THE ISAM ENGINE THEN HANDLES ALL FILE INTERACTION. THE BEHAVIOR OF AN OPEN...FOR ISAM STATEMENT IS SIMILAR TO OPEN...FOR OUTPUT OR OPEN...FOR APPEND WITH OTHER TYPES OF SEQUENTIAL FILES.

FOR EXAMPLE, IF DATABASE$ DOES NOT YET EXIST AS A DISK FILE, IT IS CREATED BY THE OPEN STATEMENT. SIMILARLY, IF TABLENAME DOES NOT EXIST WITHIN THE DATABASE, THE OPEN STATEMENT CREATES A TABLE OF THAT NAME WITHIN THE DATABASE, AND OPENS IT.

THE TABLETYPE ARGUMENT MUST IDENTIFY A USER-DEFINED TYPE PREVIOUSLY DECLARED IN THE PROGRAM WITH A TYPE...END TYPE STATEMENT. THIS PRECLUDES WRITING PROGRAMS THAT PERMIT THE END USER TO DESIGN CUSTOM TABLES AT RUN TIME.

IF AN ISAM OPEN STATEMENT FAILS, ALL ISAM BUFFERS ARE WRITTEN TO DISK AND ANY PENDING TRANSACTIONS ARE COMMITTED (SEE THE SECTION "BLOCK PROCESSING WITH TRANSACTIONS" LATER IN THIS CHAPTER FOR INFORMATION ON TRANSACTIONS).

NOTE.~ YOU CANNOT LOCK AN ISAM DATABASE USING OPEN...FOR ISAM. HOWEVER, YOU CAN OPEN A DATABASE THAT HAS BEEN DESIGNATED READ-ONLY BY SOME OTHER PROCESS. IF YOUR PROGRAM OPENS SUCH A FILE, CERTAIN ISAM STATEMENTS WILL GENERATE ERRORS, INCLUDING: DELETE, DELETEINDEX, DELETETABLE, CREATEINDEX, INSERT, AND UPDATE. THESE STATEMENTS CAUSE PERMISSION DENIED ERROR MESSAGES.

CLOSING A TABLE

A CLOSE STATEMENT WITH FILENUMBER% AS AN ARGUMENT CLOSES THE TABLENAME$ ASSOCIATED WITH FILENUMBER%. CLOSE WITH NO ARGUMENTS CLOSES ALL OPEN TABLES (AND ANY OTHER FILES, ISAM OR OTHERWISE). ANY ISAM CLOSE STATEMENT CAUSES ALL PENDING TRANSACTIONS TO BE COMMITTED. (SEE THE SECTION "BLOCK PROCESSING WITH TRANSACTIONS" LATER IN THIS CHAPTER FOR INFORMATION ON TRANSACTIONS).

THE ATTRIBUTES OF FILENUMBER%

A PROGRAM THAT OPENS AN ISAM TABLE CAN OPEN OTHER FILES FOR OTHER TYPES OF ACCESS. IN SUCH CASES, YOU MAY NEED TO DETERMINE AT SOME POINT WHICH FILES (OR TABLES), ASSOCIATED WITH WHICH FILE NUMBERS, ARE OPEN FOR WHICH TYPES OF ACCESS. FILEATTR HAS THE FOLLOWING SYNTAX:

FILEATTR(FILENUMBER%, ATTRIBUTE%)

WHEN YOU USE THIS FUNCTION, YOU PASS THE NUMBER OF THE FILE OR TABLE YOU WANT TO KNOW ABOUT AS THE FIRST ARGUMENT, AND EITHER 1 OR 2 AS ATTRIBUTE%. IF YOU PASS A 1, THE VALUE RETURNED IN FILEATTR IS 64 IF FILENUMBER% WAS OPENED AS AN ISAM TABLE.

OTHER RETURN VALUES INDICATE THE FILE WAS OPENED FOR ANOTHER MODE, AS FOLLOWS:

1    INPUT
2    OUTPUT
4    RANDOM
16    APPEND
32    BINARY
64    ISAM

WITH AN ISAM FILE, IF YOU PASS A 2 AS ATTRIBUTE%, THE FILEATTR RETURNS ZERO.

DEFINING A RECORD VARIABLE

ALTHOUGH IT ISN'T NECESSARY TO DO IT AT THE SAME TIME YOU OPEN THE DATABASE, YOU EVENTUALLY NEED TO DEFINE A RECORD VARIABLE HAVING THE PROPER USER-DEFINED TYPE FOR THE TABLE. THIS VARIABLE IS USED IN TRANSFERRING DATA BETWEEN YOUR PROGRAM AND THE ISAM FILE. IN THE CASE OF THE BOOKS TYPE, IT COULD LOOK LIKE THIS:

DIM INVENTORY AS BOOKS

CREATING AND SPECIFYING INDEXES ON TABLE COLUMNS

MUCH OF THE POWER OF ISAM DERIVES FROM THE EASE WITH WHICH THE APPARENT ORDER OF DATA RECORDS CAN BE CHANGED. THIS IS ACCOMPLISHED BY SPECIFYING A PREVIOUSLY CREATED "INDEX" ON A COLUMN (OR COLUMNS) IN A SETINDEX STATEMENT. IF YOU DON'T SPECIFY AN INDEX ON A TABLE, THE DEFAULT INDEX (THE NULL INDEX) IS USED.

AND THE APPARENT ORDER OF THE RECORDS IS THE ORDER IN WHICH THE RECORDS WERE ADDED TO THE FILE. THEREFORE, WHEN YOU INITIALLY OPEN A TABLE, THE CURRENT INDEX IS THE NULL INDEX UNTIL (AND UNLESS) YOU SPECIFY A DIFFERENT INDEX. YOU CREATE YOUR OWN INDEXES WITH THE CREATEINDEX STATEMENT, USING THE FOLLOWING SYNTAX:

CREATEINDEX #FILENUMBER%, INDEXNAME$, UNIQUE%, COLUMNNAME$, COLUMNNAME$...

ARGUMENTS:

FILENUMBER%.~ THE INTEGER USED TO OPEN THE TABLE ON WHICH THE INDEX IS TO BE CREATED.

INDEXNAME$.~ A STRING EXPRESSION THAT FOLLOWS THE ISAM NAMING CONVENTIONS.THE INDEX IS KNOWN BY INDEXNAME$ UNTIL EXPLICITLY DELETED.

UNIQUE%
A NUMERIC EXPRESSION. A NON-ZERO VALUE SPECIFIES A UNIQUE INDEX ON THE COLUMN, MEANING THAT NO VALUES IN ANY OF THAT COLUMN'S FIELDS CAN DUPLICATE ANY OF THE OTHERS. A VALUE OF ZERO FOR THIS ARGUMENT MEANS THE INDEXED VALUES NEED NOT BE UNIQUE.

COLUMNNAME$
A STRING EXPRESSION FOLLOWING THE ISAM NAMING CONVENTION THAT SPECIFIES THE COLUMN TO BE INDEXED. NOTE THAT MULTIPLE COLUMNNAME$ ENTRIES DO NOT CREATE MULTIPLE INDEPENDENT INDEXES, BUT RATHER CREATE A SINGLE COMBINED INDEX. EACH SUCCEEDING COLUMNNAME$ IDENTIFIES A SUBORDINATE SORTING ORDER FOR THE RECORDS (WHEN THAT INDEX IS SPECIFIED).

THE CREATEINDEX STATEMENT IS USED ONLY ONCE FOR EACH INDEX. IF YOU TRY TO CREATE AN INDEX THAT ALREADY EXISTS FOR THE TABLE A TRAPPABLE ERROR IS GENERATED.

ONCE AN INDEX EXISTS, YOU USE THE SETINDEX STATEMENT TO MAKE IT THE CURRENT INDEX (THEREBY IMPOSING ITS ORDER ON THE PRESENTATION ORDER OF THE RECORDS). SETINDEX HAS THE FOLLOWING SYNTAX:

SETINDEX #FILENUMBER%, INDEXNAME$

AN INDEXNAME$ ARGUMENT IS MANDATORY IN THE CREATEINDEX STATEMENT, BUT OPTIONAL WITH SETINDEX. IF YOU DO NOT SPECIFY AN INDEX NAME WITH SETINDEX, THE NULL INDEX BECOMES THE CURRENT INDEX. IMMEDIATELY AFTER EXECUTION OF SETINDEX, THE SPECIFIED INDEX IS THE CURRENT INDEX, AND THE CURRENT RECORD BECOMES THE RECORD HAVING THE LOWEST SORTING VALUE IN THAT COLUMN.

NOTE.~ COMPARISONS MADE BY ISAM WHEN SORTING STRINGS DIFFER SOMEWHAT FROM THOSE PERFORMED BY BASIC. WHEN COLLATING, THE CASE OF CHARACTERS IS NOT SIGNIFICANT, AND TRAILING BLANK SPACES ARE STRIPPED FROM A STRING BEFORE COMPARISON IS MADE. IN STRINGS THAT ARE OTHERWISE IDENTICAL, ACCENTS ARE SIGNIFICANT, AND COLLATING IS PERFORMED BASED ON THE CHOICE YOU MADE WHEN RUNNING THE SETUP PROGRAM.

ENGLISH, FRENCH, GERMAN, PORTUGUESE, AND ITALIAN COMPRISE THE DEFAULT GROUP. DUTCH AND SPANISH EACH HAVE THEIR OWN COLLATING ORDERS, AND THE SCANDINAVIAN LANGUAGES (DANISH, NORWEGIAN, FINNISH, ICELANDIC, AND SWEDISH) COMPRISE THE FOURTH GROUP. SEE APPENDIX E, "INTERNATIONAL CHARACTER SORT ORDER TABLES", IN THE BASIC LANGUAGE REFERENCE FOR SPECIFICS OF EACH GROUP.

INDEXES ON BOOKSTOCK'S COLUMNS

FOR EXAMPLE, ASSUMING THE BOOKSTOCK TABLE ILLUSTRATED IN FIGURE 10.8 WAS OPENED AS #1, YOU COULD USE THE CREATEINDEX STATEMENT TO CREATE THE INDEX TITLEINDEXBS (ON THE TABLE'S TITLE COLUMN) AS FOLLOWS:

CREATEINDEX 1, "TITLEINDEXBS", 0, "TITLE"

AFTER THIS DEFINITION, YOU CAN USE SETINDEX TO MAKE THIS INDEX THE CURRENT INDEX AS FOLLOWS:

SETINDEX 1, "TITLEINDEXBS"

ONCE SPECIFIED AS CURRENT, THE INDEX REPRESENTS A VIRTUAL TABLE IN WHICH THE DATA RECORDS ARE ORDERED ACCORDING TO THE VALUES IN THE TITLE COLUMN. THIS INDEX IMPOSES AN ALPHABETIC PRESENTATION ORDER ON THE TABLE. THEREFORE, ALL COPIES OF BOOKS ENTITLED QUICKBASIC MADE EASY WOULD APPEAR IN SEQUENCE, AND PRECEDE COPIES OF QUICKBASIC TOOLBOX, AND SO FORTH.

CREATING A UNIQUE INDEX

IN THE BOOKSTOCK TABLE, THE IDNUM COLUMN CONTAINS NUMBERS FOR EACH COPY OF EACH BOOK IN THE LIBRARY. WHEN NEW COPIES OF A BOOK ARE ACQUIRED, EACH IS GIVEN A UNIQUE NUMBER. IN THIS EXAMPLE ALL COPIES OF QUICKBASIC TOOLBOX HAVE A WHOLE NUMBER PART OF 15561, BUT EACH HAS A DIFFERENT FRACTIONAL PART.

THE FOLLOWING STATEMENT CREATES AN INDEX ON THIS COLUMN AND PASSES A NON-ZERO VALUE AS THE UNIQUE% ARGUMENT, ENSURING THAT NO DUPLICATE IDNUM VALUES CAN BE ENTERED FOR ANY COPIES OF ANY BOOKS:

CREATEINDEX 1, "IDINDEX", 1, "IDNUM"

IF THERE ARE ALREADY DUPLICATES IN THE COLUMN, A TRAPPABLE ERROR IS GENERATED WHEN YOUR PROGRAM ATTEMPTS TO EXECUTE THE CREATEINDEX STATEMENT. IF YOUR PROGRAM EVER ATTEMPTS TO UPDATE THE TABLE WITH A DUPLICATE VALUE IN A FIELD IN A UNIQUE INDEX, A TRAPPABLE ERROR IS GENERATED.

IN DETERMINING WHETHER STRING VALUES ARE DUPLICATES, COMPARISONS ARE CASE-INSENSITIVE AND TRAILING BLANKS ARE IGNORED. ACCENTED LETTERS ARE NOT DUPLICATES OF THEIR UNACCENTED COUNTERPARTS.

SUBORDERING OF RECORDS WITHIN AN INDEXED COLUMN

THERE ARE MANY CASES IN WHICH A USER MIGHT NEED OR EXPECT A SPECIFIC SUBORDERING. FOR EXAMPLE, WHEN BROWSING A GROUP OF CUSTOMER ORDERS, IF YOU ARE TRAVERSING AN INDEX BASED ON ACCOUNT NUMBERS, YOU MIGHT EXPECT THE ACTUAL ORDERS ASSOCIATED WITH A SPECIFIC CUSTOMER NAME TO BE IN THE ORDER IN WHICH THE RECORDS WERE ADDED.

TO ENSURE THAT RECORDS ARE PRESENTED IN THE WAY YOUR USER EXPECTS, YOU CAN CREATE A COMBINED INDEX. IN THE BOOKSTOCK TABLE EXAMPLE, THE IDEA OF MULTIPLE RECORDS REPRESENTING MULTIPLE COPIES OF A SPECIFIC BOOK IN THE LIBRARY MEANS THAT THE TITLEINDEXBS INDEX CREATED PREVIOUSLY WOULD BE SUITABLE FOR A LIBRARIAN WHO WANTED TO SEE QUICKLY HOW MANY COPIES OF A SPECIFIC BOOK THE LIBRARY OWNED.

THE LIBRARIAN ALSO MIGHT WANT TO HAVE THE BOOKS PRESENTED IN THE ORDER IN WHICH THEY WERE PURCHASED. CREATING, THEN SPECIFYING A COMBINED INDEX ON THE TITLE AND AUTHOR COLUMNS WOULD PRESENT ALL OF THE BOOKS WITH A SPECIFIC TITLE/AUTHOR COMBINATION GROUPED TOGETHER.

WHEN YOU INDEX ON A COLUMN THAT CONTAINS THE SAME VALUE IN MORE THAN ONE RECORD, THE SUBORDERING OF RECORDS WITH THE SAME VALUE FOR THE COLUMN (IN THIS CASE, THE COMBINATION OF TITLE AND AUTHOR COLUMNS) IS UNPREDICTABLE BECAUSE MANY TABLE ENTRIES MAY HAVE BEEN INSERTED AND DELETED AT DIFFERENT TIMES.

THE BOOKS WOULD APPEAR IN THE ORDER IN WHICH THEIR RECORDS ACTUALLY APPEAR ON THE DISK. ISAM OPTIMIZES FOR SPACE BY ALLOWING NEW RECORDS TO BE PLACED ON THE DISK IN SPACE PREVIOUSLY OCCUPIED BY DELETED RECORDS.

THEREFORE, ALTHOUGH THE TITLE/AUTHOR COMBINED INDEX WOULD GROUP THE PRESENTATION OF ALL COPIES OF BOOKS TITLED QUICKBASIC TOOLBOX AND WRITTEN BY D. HERGERT, IT WOULD PRESENT THEM IN THE ORDER IN WHICH THEY APPEAR ON THE DISK.

HOWEVER, THE IDNUM FOR EACH COPY WOULD CORRESPOND TO THE DATES WHEN EACH WAS ADDED TO THE COLLECTION (ASSUMING THE LIBRARY DID NOT REUSE AN OLD IDNUM ONCE AN OLD COPY OF A BOOK WAS REPLACED). A COMBINED INDEX THAT INCLUDED THE TITLE, AUTHOR, AND IDNUM COLUMNS WOULD PRESENT THE RECORDS WITH THE OLDEST COPY APPEARING FIRST AMONG THAT GROUP OF TITLES BY THAT AUTHOR, AND SO ON.

SIMILARLY, IF THE LIBRARY HAD PURCHASED THREE HARD-BOUND AND FIVE PAPERBACK COPIES, THE DIFFERENCE WOULD SHOW UP AS A SIGNIFICANT DIFFERENTIAL IN PRICE. IN PRESENTING THESE BOOKS IN THE DATABASE, A LIBRARIAN MIGHT WANT TO HAVE ALL THE HARD-BOUND COPIES APPEAR IN SEQUENCE, SEPARATED FROM THE PAPERBACK COPIES.

A COMBINED INDEX ON THE TITLE, AUTHOR, AND PRICE COLUMNS WOULD CREATE THAT PRESENTATION ORDER (ASSUMING PAPERBACKS OF A SPECIFIC TITLE ARE ALWAYS CHEAPER THAN THEIR HARDBOUND COUNTERPARTS). IF THE IDNUM COLUMN WERE ADDED TO CREATE A TITLE, AUTHOR, PRICE, IDNUM INDEX, THEN ALL THE PAPERBACKS WOULD APPEAR IN THE ORDER IN WHICH THEY WERE ADDED TO THE COLLECTION BEFORE ANY OF THE HARD-BOUND COPIES.

CREATING A COMBINED INDEX

A COMBINED INDEX CAN BE CREATED USING AS MANY AS 10 COLUMNS IN A TABLE BY LISTING MULTIPLE COLUMNNAME$ ARGUMENTS IN THE CREATEINDEX STATEMENT. WHEN SUCH A MULTI-COLUMN INDEX IS THE CURRENT INDEX, THE RECORDS APPEAR AS THOUGH FIRST SORTED BY THE FIRST COLUMNNAME$.

THEN THOSE RECORDS WHOSE FIRST INDEXED VALUES ARE IDENTICAL APPEAR AS THOUGH SORTED BY THE NEXT COLUMNNAME$, AND SO ON. THE FOLLOWING EXAMPLE CREATES A COMBINED INDEX:

CREATEINDEX 1, "BIGINDEX", 0, "TITLE", 'AUTHOR', "IDNUM"

WHEN SETINDEX IS USED TO SPECIFY BIGINDEX AS THE CURRENT INDEX, THE RECORDS APPEAR SORTED FIRST BY TITLE. THE SAME TITLE MIGHT APPEAR IN THE DATABASE FOR SEVERAL BOOKS BY DIFFERENT AUTHORS, BUT MAKING THE AUTHOR THE SECOND PART OF THE COMBINED INDEX WOULD KEEP ALL THOSE BY A PARTICULAR AUTHOR GROUPED.

FINALLY, GIVING THE IDNUM AS THE LAST PART OF THE INDEX WOULD CAUSE THE OLDEST COPY OF THE DESIRED BOOK (BY THE GIVEN AUTHOR) TO BE PRESENTED FIRST IN ITS GROUP.

YOU CAN DESIGNATE A COMBINED INDEX AS UNIQUE. IF YOU DO SO, ONLY THE COMBINATION MUST BE UNIQUE. FOR EXAMPLE, A UNIQUE INDEX ON THE AUTHOR AND TITLE COLUMNS WOULD PERMIT ANY NUMBER OF OCCURRENCES OF THE SAME AUTHOR OR THE SAME TITLE, BUT ONLY FOR ONE INSTANCE OF THE SAME AUTHOR AND THE SAME TITLE.

NULL CHARACTERS WITHIN INDEXED STRINGS IN A COMBINED INDEX

IF YOU PLACE NULL CHARACTERS WITHIN STRINGS IN COLUMNS THAT ARE COMPONENTS OF A COMBINED INDEX, THERE ARE SITUATIONS IN WHICH THE ORDER OF THE INDEX MAY DEVIATE FROM WHAT YOU EXPECT. THIS IS RARE, BUT RESULTS FROM THE FACT THAT ISAM USES THE NULL CHARACTER AS A SEPARATOR IN COMBINED INDEXES.

FOR INSTANCE, IF THE LAST CHARACTER IN A STRING FIELD IS A NULL, AND ITS INDEX IS COMBINED WITH ONE WHOSE FIRST CHARACTER IS A NULL, AND IN ALL OTHER WAYS THEY ARE THE SAME, THE TWO FIELDS WILL COMPARE EQUAL.

THIS APPLIES ONLY TO COMBINED INDEXES. THERE IS NO RESTRICTION ON NULL CHARACTERS IN AN ISAM STRING, BUT YOU SHOULD BE AWARE OF THIS SITUATION IF YOU PLAN TO USE NULL CHARACTERS IN STRINGS OF INDEXED COLUMNS.

PRACTICAL CONSIDERATIONS WITH INDEXES

REMEMBER THAT EACH TIME AN INSERT, DELETE, OR UPDATE STATEMENT IS EXECUTED, EVERY INDEX IN THE AFFECTED TABLE IS ADJUSTED TO REFLECT THE CHANGED STATE OF THE RECORDS. IN THE NORMAL COURSE OF MOVING THROUGH A DATABASE AND MAKING CHANGES TO RECORDS, THE TIME NEEDED TO ADJUST INDEXES WOULD NOT BE NOTICEABLE TO A USER.

HOWEVER, THE TIME REQUIRED BY AN AUTOMATED PROCESS THAT MAKES THESE TYPES OF CHANGES MAY BE SIGNIFICANTLY AFFECTED BY THE NUMBER OF INDEXES IN THE TABLE. IN SOME CASES IT MAY MAKE SENSE TO DELETE UNNECESSARY INDEXES BEFORE SUCH A PROCESS BEGINS, THEN RECREATE THEM WHEN IT IS FINISHED.

RESTRICTIONS ON INDEXING

PART OF THE INFORMATION ISAM MAINTAINS IS THE DATA TYPE OF EACH COLUMN IN EACH TABLE. THESE DATA TYPES ARE STORED IN THE DATA DICTIONARY SO THE ISAM ENGINE CAN MAKE VALID COMPARISONS WHEN SORTING RECORDS IN AN INDEX. ISAM CAN INDEX COLUMNS HAVING UP TO 255 BYTES IN COMBINED LENGTH.

THEREFORE, YOU CAN CREATE AN INDEX ON A STRING COLUMN HAVING A LENGTH OF UP TO 255 BYTES, OR A COMBINED INDEX WHOSE CONSTITUENT COLUMNS TOTAL A LITTLE LESS THAN 255 BYTES OR LESS (THERE IS A LITTLE OVERHEAD ASSOCIATED WITH EACH CONSTITUENT INDEX).

COLUMNS HAVING ARRAY OR STRUCTURE (THAT IS, USER-DEFINED) TYPE CANNOT BE INDEXED. ATTEMPTING TO CREATE AN INDEX ON A COLUMN WITH ARRAY OR STRUCTURE TYPE, OR ON A STRING COLUMN LONGER THAN 255 CHARACTERS, OR DEFINING A COMBINED INDEX WHOSE TOTAL LENGTH IS GREATER THAN 255 BYTES, CAUSES A TRAPPABLE ERROR.

DETERMINING THE CURRENT INDEX

THE GETINDEX$ FUNCTION LETS YOU FIND OUT WHAT THE CURRENT INDEX IS. GETINDEX$ HAS THE FOLLOWING SYNTAX:

GETINDEX$ (FILENUMBER%)

THE FILENUMBER% ARGUMENT IS AN INTEGER IDENTIFYING ANY OPEN TABLE. GETINDEX$ RETURNS A STRING REPRESENTING THE NAME OF THE CURRENT INDEX. IF THE VALUE RETURNED IN GETINDEX$ IS A NULL STRING (REPRESENTED BY ""), THEN THE CURRENT INDEX IS THE NULL INDEX.

IN A COMPLEX PROGRAM, IT MAY BECOME DIFFICULT TO PREDICT WHICH INDEX IS THE CURRENT INDEX ON A SPECIFIC TABLE. ALTHOUGH SETINDEX IS A SINGLE CALL, IT IS USUALLY MORE EFFICIENT TO TEST THE CURRENT INDEX WITH GETINDEX$ FIRST, THEN ONLY USE SETINDEX IF YOU ACTUALLY WANT A DIFFERENT INDEX. THE FOLLOWING FRAGMENT ILLUSTRATES THIS:

IF GETINDEX$(TABLENUM%) <> "MYINDEX" THEN
SETINDEX TABLENUM%, "MYINDEX"
END IF

EVEN THOUGH THE PRECEDING IS MORE CODE THAN SIMPLY USING SETINDEX, IT IS USUALLY MORE EFFICIENT. NOTE ALSO THAT THE EFFECT ON THE CURRENT RECORD MAY BE DIFFERENT DEPENDING ON WHETHER THE SETINDEX STATEMENT IS EXECUTED. IF THE CURRENT INDEX IS ALREADY MYINDEX, THE CURRENT RECORD WILL BE THE SAME (ON THAT INDEX) AS IT WAS PREVIOUSLY. IF THE SETINDEX STATEMENT IS EXECUTED, THE CURRENT RECORD WILL BE THE ONE THAT SORTS LOWEST IN THE INDEX.

TRANSFERRING AND DELETING RECORD DATA

THE SYNTAX FOR THE DATA-MANIPULATION STATEMENTS IS SIMILAR TO THAT FOR SETINDEX:

DELETE #FILENUMBER%
RETRIEVE #FILENUMBER%, RECORDVARIABLE
UPDATE #FILENUMBER%, RECORDVARIABLE
INSERT #FILENUMBER%, RECORDVARIABLE

ARGUMENTS:

FILENUMBER%.~ THE INTEGER USED TO OPEN THE TABLE WHOSE CURRENT RECORD YOU WANT TO REMOVE, FETCH, OR OVERWRITE. IN THE CASE OF AN INSERTION, IT IS THE TABLE IN WHICH YOU WANT THE RECORD INSERTED.

RECORDVARIABLE
A VARIABLE OF THE USER-DEFINED TYPE CORRESPONDING TO THE TABLE INTO WHICH THE CURRENT RECORD VALUES ARE PLACED, OR WITH WHICH THE CURRENT RECORD IS TO BE OVERWRITTEN. IN THE CASE OF AN INSERTION, RECORDVARIABLE IS THE RECORD YOU WISH TO INSERT.

ITS ELEMENTS (IN ITS TYPE...END TYPE DECLARATION) MAY BE EXACTLY THE SAME AS THOSE OF THE TABLE, OR A SUBSET OF THEM. SUBSETS OF RECORDVARIABLE ARE DISCUSSED IN THE SECTION "RECORD VARIABLES AS SUBSETS OF A TABLE'S COLUMNS" LATER IN THIS CHAPTER.

RETRIEVE, UPDATE, AND DELETE ALL REFER TO THE CURRENT RECORD. THE DATA TRANSFER STATEMENTS ALL TAKE THE DATA IN RECORDVARIABLE AND EITHER PLACE IT IN THE TABLE (UPDATE AND INSERT) OR FETCH THE CURRENT RECORD (RETRIEVE) AND PLACE ITS DATA INTO RECORDVARIABLE.

DELETE REMOVES THE CURRENT RECORD FROM THE SPECIFIED TABLE, AND ALL AFFECTED INDEXES ARE ADJUSTED APPROPRIATELY. FOLLOWING A DELETION, IF THE CURRENT RECORD WAS NOT THE LAST RECORD IN THE CURRENT INDEX, THE NEW CURRENT RECORD IS THE RECORD THAT IMMEDIATELY SUCCEEDED THE DELETED RECORD. IF THE DELETED RECORD WAS THE LAST RECORD, NO RECORD IS CURRENT, AND EOF RETURNS TRUE.

WHEN YOU USE RETRIEVE, THE CONTENTS OF THE CURRENT RECORD ARE ASSIGNED TO RECORDVARIABLE. WHEN YOU USE UPDATE, THE CONTENTS OF RECORDVARIABLE OVERWRITE THE CURRENT RECORD, AND ALL AFFECTED INDEXES ARE ADJUSTED APPROPRIATELY.

A TRAPPABLE ERROR OCCURS IF NO RECORD IS CURRENT WHEN A DELETE, RETRIEVE, OR UPDATE STATEMENT IS EXECUTED. INSERT PLACES THE CONTENTS OF RECORDVARIABLE IN THE TABLE, THEN ADJUSTS ALL AFFECTED INDEXES APPROPRIATELY.

A NEWLY INSERTED RECORD ASSUMES ITS APPROPRIATE POSITION IN THE CURRENT INDEX. THEREFORE, IF YOU DISPLAY THE CURRENT RECORD IMMEDIATELY AFTER AN INSERTION, THE RECORD DISPLAYED IS THE SAME RECORD THAT WAS DISPLAYED PRIOR TO THE INSERTION, NOT THE NEWLY INSERTED RECORD.

TO SEE THE NEW RECORD, EXECUTE A SETINDEX STATEMENT TO MAKE THE NULL INDEX CURRENT, THEN EXECUTE A MOVELAST STATEMENT, THEN DISPLAY THE CURRENT RECORD. THE INSERT STATEMENT ITSELF DOES NOT AFFECT POSITIONING. A TRAPPABLE ERROR OCCURS IF YOU TRY TO INSERT A RECORD CONTAINING A DUPLICATE VALUE IN A COLUMN ON WHICH A UNIQUE INDEX EXISTS.

THE CURRENT POSITION

THE CURRENT POSITION WITHIN A TABLE DEPENDS ON THAT TABLE'S CURRENT INDEX. WHEN YOU SPECIFY AN INDEX WITH SETINDEX, YOU SPECIFY THE TABLE (WITH THE FILENUMBER% ARGUMENT) AND THE INDEX NAME. AFTER SETINDEX IS EXECUTED, THE CURRENT RECORD IS THE FIRST RECORD ON THE SPECIFIED INDEX. THE CURRENT RECORD IS THE FOCUS OF DATA EXCHANGE.

CHANGING THE CURRENT INDEX

AFTER OPENING A TABLE, AND UNTIL YOU SPECIFY AN INDEX, THE CURRENT INDEX IS THE NULL INDEX. TO CHANGE TO ANOTHER INDEX, USE THE SETINDEX STATEMENT. IT HAS THE FOLLOWING SYNTAX:

SETINDEX #FILENUMBER%, INDEXNAME$

ARGUMENTS:

# THE OPTIONAL NUMBER CHARACTER.

FILENUMBER%.~ THE INTEGER USED TO OPEN THE TABLE FOR WHICH YOU WANT TO SET A NEW CURRENT INDEX.

INDEXNAME$.~ A STRING EXPRESSION NAMING A PREVIOUSLY CREATED INDEX. IF INDEXNAME IS OMITTED, THE NULLINDEX BECOMES THE CURRENT INDEX, OTHERWISE INDEXNAME BECOMES THE CURRENT INDEX.

MAKING A DIFFERENT RECORD CURRENT

ISAM PERMITS YOU TO MAKE RECORDS CURRENT EITHER BY THEIR POSITION WITHIN THE CURRENT INDEX (USING A MOVE DEST STATEMENT), OR BY TESTING FIELD VALUE(S) IN THE CURRENT INDEX AGAINST KEY VALUE(S) YOU SUPPLY (IN A SEEK OPERAND STATEMENT).

SETTING THE CURRENT RECORD BY POSITION

THE MOVE DEST STATEMENTS LET YOU MAKE A RECORD IN THE SPECIFIED TABLE CURRENT BASED ON ITS POSITION IN THE CURRENT INDEX. USE THE BOF AND EOF FUNCTIONS TO TEST THE CURRENT POSITION IN THE TABLE. WHEN A MOVE IS MADE, IT IS RELATIVE TO THE CURRENT POSITION ON THE TABLE SPECIFIED BY THE FILENUMBER% ARGUMENT.

THE SYNTAX FOR THE MOVE DEST STATEMENTS AND THE POSITION-TESTING FUNCTIONS IS AS FOLLOWS:

MOVEFIRST #FILENUMBER%
MOVELAST #FILENUMBER%
MOVENEXT #FILENUMBER%
MOVEPREVIOUS #FILENUMBER%
EOF(FILENUMBER%)
BOF(FILENUMBER%)

EACH RECORD IN A TABLE HAS A PREVIOUS RECORD AND A NEXT RECORD, EXCEPT THE RECORDS THAT ARE FIRST AND LAST ACCORDING TO THE CURRENT INDEX. GIVEN THE CURRENT INDEX, THE BEGINNING OF THE TABLE IS THE POSITION PRECEDING THE FIRST RECORD; THE END OF THE TABLE IS THE POSITION FOLLOWING THE LAST RECORD.

THE EFFECT OF ANY OF THE MOVE DEST STATEMENTS, OR POSITION-TESTING FUNCTIONS, IS RELATIVE TO THE CURRENT POSITION IN THE TABLE SPECIFIED BY FILENUMBER%. IF THERE IS A RECORD FOLLOWING THE CURRENT RECORD, MOVENEXT MAKES IT THE CURRENT RECORD.

IF THERE IS A RECORD PRECEDING THE CURRENT RECORD, MOVEPREVIOUS MAKES IT THE CURRENT RECORD. AN ATTEMPT TO USE MOVENEXT FROM THE LAST RECORD IN THE TABLE, OR TO USE MOVEPREVIOUS FROM THE FIRST RECORD IN THE TABLE MOVES THE POSITION TO THE END OF FILE, OR THE BEGINNING OF FILE, RESPECTIVELY.

YOU CAN TEST FOR THE END-OF-FILE AND BEGINNING-OF-FILE CONDITIONS WITH THE EOF AND BOF FUNCTIONS. EOF RETURNS TRUE (-1) WHEN THE CURRENT POSITION IS BEYOND THE LAST RECORD ON THE CURRENT INDEX; BOF RETURNS TRUE (-1) WHEN THE CURRENT POSITION PRECEDES THE FIRST RECORD ON THE CURRENT INDEX.

IF THE CURRENT RECORD IS NOT ALREADY THE FIRST OR LAST IN THE TABLE, THEN MOVEFIRST AND MOVELAST MAKE THOSE RECORDS CURRENT. WHEN A TABLE CONTAINS NO RECORDS, BOTH BOF AND EOF RETURN TRUE (-1). IF THE TABLE HAS NO RECORDS, AN ATTEMPT TO EXECUTE ANY OF THE MOVE DEST STATEMENTS WILL FAIL AND EOF WILL RETURN TRUE.

DISPLAYING THE BOOKSTOCK TABLE

WHEN YOUR PROGRAM SPECIFIES AN INDEX FOR THE FIRST TIME AFTER OPENING A TABLE, THE CURRENT RECORD IS THE ONE THAT SORTS FIRST IN THE INDEX. SOME PRELIMINARY BASIC CODE, PLUS THE ISAM RETRIEVE, BOF, EOF, MOVENEXT, AND MOVEPREVIOUS STATEMENTS, ARE ALL YOU NEED TO ALLOW THE USER TO MOVE THROUGHAN OPEN TABLE AND VIEW ITS RECORDS.

A TYPICAL ISAM PROGRAM

ALTHOUGH YOU CAN WRITE YOUR PROGRAM TO ALLOW USERS TO CREATE THEIR OWN DATABASE FILES, IT IS MORE TYPICAL TO SUPPLY THE PROGRAM WITH A DATABASE FILE HAVING NO RECORDS, WITH THE FILENAME HARD-CODED INTO THE PROGRAM. THIS DATABASE WOULD CONTAIN ALL THE PREDEFINED INDEXES YOU ANTICIPATE YOUR USER WOULD NEED.

ONCE THE FILE CONTAINS THESE INDEXES, YOUR PROGRAM DOESN'T NEED THE CODE USED TO CREATE THEM. SUPPLYING AN EMPTY DATABASE FILE THAT CONTAINS ALL THE NECESSARY INDEXES SIMPLIFIES USER INTERACTION WITH THE PROGRAM, AND ALSO MEANS ISAM CAN USE LESS MEMORY.

FOR MORE INFORMATION ON THE DIFFERENT WAYS YOU CAN INCLUDE ISAM SUPPORT IN YOUR PROGRAMS, AND HOW TO USE IT DURING PROGRAM DEVELOPMENT, SEE THE SECTIONS "STARTING ISAM FOR USE IN QBX" AND "USING ISAM WITH COMPILED PROGRAMS" LATER IN THIS CHAPTER.

THE FOLLOWING EXAMPLE SHOWS THE MODULE-LEVEL CODE OF A PROGRAM THAT OPENS SEVERAL TABLES, INCLUDING THE BOOKSTOCK TABLE (DISCUSSED EARLIER) IN THE BOOKS.MDB DATABASE. IT ALLOWS THE USER TO VIEW THE RECORDS FOR ALL THE BOOKS IN A VARIETY OF ORDERS, DEPENDING ON WHICH INDEX IS CHOSEN.

ONLY THE MODULE-LEVEL CODE AND THE RETRIEVER PROCEDURE APPEAR HERE. OTHER PROCEDURES, MOST OF WHICH CONTROL THE USER INTERFACE (TO LET THE USER ADD, EDIT, AND SEARCH FOR SPECIFIC RECORDS), ARE CALLED AS NECESSARY.

YOU CAN SEE THOSE PROCEDURES IN THE DISK FILES LISTED IN THE .MAK FILE BOOKLOOK.MAK (INCLUDING BOOKLOOK.BAS, THE MAIN MODULE; BOOKMOD1.BAS; BOOKMOD2.BAS; AND BOOKMOD3.BAS). AS NOTED PREVIOUSLY, THESE EXAMPLE FILES ARE INCLUDED ON THE MICROSOFT BASIC DISTRIBUTION DISKS AND MAY BE COPIED TO YOUR HARD DISK DURING SETUP.

NOTE THAT THE ERROR-HANDLING ROUTINE AT THE BOTTOM OF THE CODE HANDLES ERROR 86, ILLEGAL OPERATION ON A UNIQUE INDEX. WHEN AN ATTEMPT IS MADE TO UPDATE OR INSERT A RECORD CONTAINING A DUPLICATE VALUE FOR A UNIQUE INDEX, THE ERROR-HANDLING ROUTINE PROMPTS THE USER TO ENTER A NEW VALUE.

TO VIEW THE BOOKLOOK.BAS SAMPLE APPLICATION, MOVE TO THE DIRECTORY WHERE IT WAS INSTALLED DURING SETUP AND INVOKE THE ISAM TSR AND QBX BY TYPING THE FOLLOWING TWO LINES:

PROISAM /I B:24QBX BOOKLOOK

THE /IB ARGUMENT TO PROISAM SPECIFIES THE NUMBER OF BUFFERS ISAM WILL NEED TO MANIPULATE DATA. OPTIONS FOR THE ISAM TSR ARE FULLY EXPLAINED IN THE SECTION "STARTING ISAM FOR USE IN QBX" LATER IN THIS CHAPTER. YOU CAN SEE THE EFFECT OF A COMBINED INDEX BY USING THE TITLE+AUTHOR+ID INDEX ON THE BOOKSTOCK TABLE IN THE EXAMPLE.

THE LIBRARY CONTAINS FIVE COPIES OF THE TITLE STRUCTURED BASIC APPLIED TO TECHNOLOGY BY THOMAS A. ADAMSON. USING THE COMBINED INDEX, THE VARIOUS COPIES OF THE BOOK ARE PRESENTED IN ID NUMBER ORDER, WHETHER YOU ARE MOVING FORWARD IN THE TABLE OR BACKWARDS.

IF YOU JUST USE THE TITLE INDEX (OR ANY OF THE OTHER INDEXES FOR WHICH THE FIELDS ARE DUPLICATES), THE ORDER MOVING FORWARD IS PROBABLY WHAT YOU WOULD EXPECT, BUT THE ORDER MOVING BACKWARD MAY SURPRISE YOU. THIS ILLUSTRATES THE FACT THAT SPECIFIC SUBORDERING IS ONLY GUARANTEED WHEN A SPECIFIC COMBINED INDEX IS CURRENT.

THE RETRIEVER PROCEDURE ILLUSTRATES FETCHING A RECORD FROM THE DATABASE AND PLACING IT IN A DEFINED RECORDVARIABLE. CHECKPOSITION UPDATES THE VIEWING/EDITING KEYS BOX WHEN THE FIRST OR LAST RECORD IN THE TABLE IS REACHED.

THE MAKEOVER PROCEDURE REFERRED TO IN THE ERROR-HANDLING ROUTINE IS NOT INCLUDED IN THE LISTING, BUT ILLUSTRATES HOW INDEXES CAN BE CREATED. YOU CAN USE MAKEOVER (AND THE READER PROCEDURE THAT IT CALLS) TO CREATE AN ISAM DATABASE CONTAINING THE SAME TABLES AS BOOKS.MDB, BUT CONTAINING RECORDS READ FROM TEXT FILES.

THE TEXT FILES MUST BE IN THE APPROPRIATE DIRECTORY, BUT THEY DON'T ALL HAVE TO CONTAIN RECORDS. FOR EXAMPLE, YOU WOULDN'T NECESSARILY WANT TO START A DATABASE WITH ENTRIES IN THE BOOKSOUT TABLE. IF THE DATABASE ALREADY EXISTS THE NEW RECORDS ARE APPENDED TO THE APPROPRIATE TABLE.

IN THAT CASE, THE DUPLICATE DEFINITION ERROR IS GENERATED WHEN AN ATTEMPT IS MADE TO CREATE THE INDEXES. THE ERROR IS TRAPPED, AND THE PROCEDURE ENDS. THE FIELDS OF THE TEXT FILES SHOULD BE COMMA DELIMITED, AND STRINGS SHOULD BE ENCLOSED IN DOUBLE QUOTATION MARKS.

SETTING THE CURRENT RECORD BY CONDITION

YOU CAN SPECIFY CONDITIONS TO BE MET WHEN MAKING A RECORD CURRENT WITH THE SEEKGT, SEEKGE, AND SEEKEQ STATEMENTS. THEIR SYNTAX IS SUMMARIZED AS FOLLOWS:

SEEK OPERAND FILENUMBER%, KEYVALUE, KEYVALUE...

DEPENDING ON THE OPERAND AND THE CURRENT INDEX, THESE STATEMENTS MAKE THE FIRST MATCHING RECORD IN THE TABLE SPECIFIED BY FILENUMBER THE CURRENT RECORD. A MATCH OCCURS WHEN AN INDEXED VALUE FULFILLS THE OPERAND CONDITION WITH RESPECT TO THE SPECIFIED KEYVALUE.

THE FOLLOWING TABLE INDICATES THE OPERATION ASSOCIATED WITH EACH OF THE OPERAND SPECIFIERS:

SEEKGT.~ THE FIRST RECORD WHOSE INDEXED VALUE IS GREATER THAN KEYVALUE.
SEEKGE.~ THE FIRST RECORD WHOSE INDEXED VALUE IS GREATER THAN, OR EQUAL TO, KEYVALUE.
SEEKEQ.~ THE FIRST RECORD WHOSE INDEXED VALUE EQUALS KEYVALUE.

THE KEYVALUE EXPRESSION SHOULD HAVE THE SAME DATA TYPE AS THE COLUMN REPRESENTED BY THE CURRENT INDEX. ALTHOUGH TYPE COERCION IS PERFORMED BETWEEN INTEGER AND LONG VALUES, YOU CAN EXPERIENCE OVERFLOW ERRORS IF YOU RELY ON AUTOMATIC TYPE COERCION. WITH ALL OTHER TYPES, A KEYVALUE ERROR IS DETECTED WHEN A VALUE IS OF THE WRONG DATA TYPE, AND A TYPE MISMATCH ERROR RESULTS.

IF THE CURRENT INDEX IS A COMBINED INDEX, AND THE NUMBER OF KEYVALUE VALUES EXCEEDS THE NUMBER OF CONSTITUENT COLUMNS IN THE COMBINED INDEX, A SYNTAX ERROR ERROR MESSAGE IS GENERATED WHEN THE PROGRAM RUNS. A TRAPPABLE ERROR IS GENERATED IF YOU ATTEMPT TO EXECUTE A SEEK OPERAND STATEMENT WHILE THE NULL INDEX IS THE CURRENT INDEX.

IF THE NUMBER OF KEYVALUE VALUES IS FEWER THAN THE NUMBER OF CONSTITUENT COLUMNS IN A COMBINED INDEX, THE MISSING VALUES ARE REPLACED BY A VALUE LESS THAN THE SMALLEST POSSIBLE VALUE, AND THE OUTCOME DEPENDS ON THE OPERAND. FOR EXAMPLE, A SEEKEQ WILL FAIL (AND EOF WILL RETURN TRUE).

A SEEKGE OR SEEKGT WILL PERFORM THE SEEK BASED ON WHATEVER KEYVALUE VALUES ARE SUPPLIED, AND WILL FIND THE FIRST RECORD THAT MATCHES THE SUPPLIED KEYVALUE VALUES. FOR EXAMPLE, ASSUME THE FOLLOWING TYPE, VARIABLE AND INDEX ARE CREATED:

TYPE FOREXAMPLE
FIRSTNAME AS STRING * 20
LASTNAME AS STRING * 25
END TYPE

DIM EXAMPLEVARIABLE AS FOREXAMPLE
CREATEINDEX TABLENUM%, "FULLNAMEINDEX", 0, FIRSTNAME, LASTNAME
SETINDEX TABLENUM%, "FULLNAMEINDEX"

IF YOU EXECUTE THE FOLLOWING STATEMENT THE SEEK WILL FAIL BECAUSE NO LAST NAME WAS PROVIDED:

SEEKEQ TABLENUM%, "TOM"

IF YOU EXECUTE THE FOLLOWING STATEMENT THE SEEK WILL FIND THE FIRST RECORD THAT IS "TOM":

SEEKGT TABLENUM%, "TOM"

WHEN A SEEK OPERAND STATEMENT FAILS (THAT IS, WHEN NO MATCH IS MADE), THERE IS NO CURRENT RECORD, AND EOF IS SET TO TRUE. THEREFORE, ANY IMMEDIATELY SUCCEEDING OPERATION THAT DEPENDS ON THE CURRENT RECORD (SUCH AS A RETRIEVE, DELETE, INSERT, MOVENEXT, OR MOVEPREVIOUS) WILL CAUSE A TRAPPABLE ERROR. YOU CAN PREVENT GENERATION OF THIS ERROR BY ONLY EXECUTING STATEMENTS THAT DEPEND ON THE EXISTENCE OF A CURRENT RECORD IF EOF RETURNS FALSE.

SEEKING ON STRINGS AND ISAM STRING COMPARISON

WHEN SEEKING A STRING, ISAM PERFORMS COMPARISONS ON A CASE-INSENSITIVE BASIS. TRAILING BLANKS ARE IGNORED. THIS IS A LESS STRICT COMPARISON THAN THAT MADE BY THE BASIC EQUALITY OPERATOR. ADDITIONALLY, INTERNATIONAL CONVENTIONS ARE OBSERVED (SEE APPENDIX E, "INTERNATIONAL CHARACTER SORT ORDER TABLES", IN THE BASIC LANGUAGE REFERENCE FOR MORE INFORMATION).

THE TEXTCOMP FUNCTION ALLOWS YOU TO PERFORM STRING COMPARISONS WITHIN YOUR PROGRAM IN THE SAME WAY THEY ARE COMPARED BY ISAM. ITS SYNTAX IS AS FOLLOWS: TEXTCOMP (STRING1$, STRING2$)

THE STRING1$ AND STRING2$ ARGUMENTS ARE STRING EXPRESSIONS. TEXTCOMP RETURNS -1 IF STRING1$ IS LESS THAN STRING2$, 1 IF STRING1$ COMPARES GREATER THAN STRING2$, AND 0 IF THE TWO STRINGS COMPARE EQUAL. ONLY THE FIRST 255 CHARACTERS OF THE RESPECTIVE STRINGS ARE COMPARED.

FOR INSTANCE, IF BIGREC IS THE NAME OF THE RECORDVARIABLE INTO WHICH RETRIEVE PLACES RECORDS FROM THE BOOKSTOCK TABLE, AND YOU WANT TO PRINT A QUICK LIST OF THE TITLES IN THE TABLE THAT BEGIN WITH THE WORD QUICKBASIC, YOU COULD USE CODE LIKE THE FOLLOWING TO FIND THE FIRST QUALIFIED TITLE, THEN PRINT THE ENSUING QUALIFIED TITLES:

CODE APPEARS IN PRINTED BOOK, AND IN \SAMPCODE\BASIC DIRECTORY. BECAUSE THE COMPARISON PERFORMED BY TEXTCOMP IS CASE-INSENSITIVE, ALL VARIATIONS OF TITLES WHOSE FIRST WORD IS QUICKBASIC WILL BE PRINTED.

EXAMPLE

THE FOLLOWING LISTING BEGINS WITH THE FRAGMENT OF THE MODULE-LEVEL CODE OF BOOKLOOK.BAS THAT HANDLES THE SEEKFIELD CASE, WHICH IS SELECTED WHEN THE USER CHOOSES FIND RECORD. FIRST THE USER IS PROMPTED TO CHOOSE AN INDEX BY CHOOSEORDER, THE SAME PROCEDURE CALLED IN THE REORDER CASE. THEN, THE SEEKRECORD PROCEDURE IS CALLED. IT PROMPTS THE USER TO ENTER A VALUE TO SEARCH FOR ON THE CHOSEN INDEX.

AFTER THE USER ENTERS THE VALUE, HE OR SHE IS PROMPTED TO CHOOSE THE CONDITION (=, >, >=, <, OR <=) THAT CONTROLS THE SEARCH. THE DEFAULT SEARCH IS SET IN THIS EXAMPLE TO USE SEEKEQ, ALTHOUGH THE SEEKGE STATEMENT WOULD BE A BETTER DEFAULT IN MANY CASES.

SEEKRECORD CALLS PROCEDURES INCLUDING VALUESACCEPTED (TO MAKE SURE THE INPUT VALUES HAVE THE SAME FORMAT AS VALUES IN THE TABLES), CLEAREM AND SHOWIT (TO SHOW USERS WHAT WILL BE SOUGHT), GETKEYVALS (IN CASE THE USER IS SUPPLYING VALUES FOR A COMBINED INDEX), AND GETOPERAND (TO LET THE USER CHOOSE WHETHER THE SEEK WILL BE BASED ON EQUALITY, GREATER OR LESS THAN, GREATER THAN OR EQUAL TO, OR LESS THAN OR EQUAL TO).

NOTE THAT SHOWIT SHOWS WHAT THE USER HAS ENTERED IN ITS APPROPRIATE FIELD, NOT DATA FROM THE DATABASE FILE ITSELF.

OTHER PROCEDURES INCLUDE MAKESTRING, DRAWSCREEN, SHOWRECORD (WHICH SHOWS A DATABASE RECORD), SHOWMESSAGE, ERASEMESSAGE, AND INDEXBOX, ALL OF WHICH KEEP THE USER INTERFACE UPDATED WITH EACH OPERATION. SINCE NAMES ARE IN A SINGLE FIELD, WITH LAST NAME FIRST, TRANSPOSENAME CHECKS THE FORMAT OF A NAME ENTERED AND PUTS IT IN THE PROPER FORMAT FOR SEARCHING OR DISPLAYING.

A MULTI-TABLE DATABASE

SO FAR, ONLY THE BOOKSTOCK TABLE HAS BEEN SHOWN IN THE BOOKS.MDB DATABASE. EVEN IN THE DATABASE OF A HYPOTHETICAL LIBRARY, IT WOULD MAKE SENSE TO HAVE ANOTHER TABLE IN BOOKS.MDB TO HOLD INFORMATION ABOUT EACH LIBRARY-CARD HOLDER AND A THIRD TABLE TO HOLD INFORMATION THAT RELATES SPECIFIC COPIES OF BOOKS TO CARD HOLDERS WHO MAY HAVE BORROWED THEM.

IN FACT, CARDHOLDERS IS A REASONABLE NAME FOR ONE TABLE, AND BOOKSOUT WILL DO FOR THE OTHER. THE USER-DEFINED TYPE BY WHICH A PROGRAM GAINS ACCESS TO THE CARDHOLDERS TABLE LOOKS AS FOLLOWS:

TYPE CARDHOLDERS
CARDNUM AS INTEGER
ZIP AS LONG
THENAME AS STRING * 36
CITY AS STRING * 26
STREET AS STRING * 50
STATE AS STRING * 2
END TYPE

THE USER-DEFINED TYPE BY WHICH A PROGRAM GAINS ACCESS TO THE BOOKSOUT TABLE CAN HAVE ELEMENT NAMES THAT DUPLICATE THOSE IN OTHER TABLES, SINCE THEY ARE ACCESSED USING DOT NOTATION. THE OUTBOOKS TYPE LOOKS AS FOLLOWS:

FIGURE 10.10 THE BOOKSOUT TABLE IN BOOKS.MDB

TYPE BOOKSOUT
IDNUM AS DOUBLE
CARDNUM AS LONG
DUEDATE AS DOUBLE
END TYPE

WHEN THESE TABLES ARE ADDED, BOOKS.MDB CONTAINS THREE TABLES. THE BOOKSTOCK TABLE IS RELATED TO THE CARDHOLDERS TABLE THROUGH THE IDNUM COLUMN IN THE BOOKSOUT TABLE. FOR INVENTORY PURPOSES, YOU MIGHT MANIPULATE JUST THE BOOKSTOCK TABLE, AND FOR DOING A MAILING OF NEW-TITLES NOTICES YOU COULD MANIPULATE ONLY THE CARDHOLDERS TABLE.

TO FIND OUT WHICH CARD HOLDER HAS WITHDRAWN WHICH COPY OF A PARTICULAR BOOK, YOU CAN GET THE IDNUM FROM THE BOOKSTOCK TABLE, THEN LOOK UP THAT IDNUM IN THE BOOKSOUT TABLE.

IF THE BOOK WAS OVERDUE, YOU COULD GET THE CARDNUM VALUE FROM THE BOOKSOUT TABLE, THEN LOOK UP THAT CARD NUMBER IN THE CARDNUM COLUMN IN THE CARDHOLDERS TABLE TO GET INFORMATION ABOUT THE BORROWER.

EXAMPLE

AS YOUR LIBRARIAN TRAVERSES THE BOOKSTOCK TABLE, HE OR SHE MIGHT WANT TO CHECK THE DUE DATE ON SOME OF THE BOOKS. PRESSING THE W KEY CALLS THE GETSTATUS PROCEDURE TO LOOK UP THE BOOK ID NUMBER IN THE BOOKSOUT TABLE AND RETRIEVE THE CORRESPONDING BOOKSOUT RECORD.

THE SHOWSTATUS PROCEDURE IS CALLED TO DISPLAY THE DUE DATE OF THE BOOK. NOTE THAT SHOWSTATUS CURRENTLY DISPLAYS THE DATE IN RAW SERIAL FORM, AS IT APPEARS IN THE TABLE. TO CONVERT A SERIAL DATE FOR NORMAL DISPLAY, YOU CAN REPLACE THE EXPRESSION STR$(VALUETOSHOW) WITH APPROPRIATE CALLS TO THE DATE AND TIME FUNCTION LIBRARY (DTFMTER.QLB), SUPPLIED WITH MICROSOFT BASIC.

BOOKLOOK.BAS CONTAINS OTHER ROUTINES THAT USE INFORMATION FROM ALL THE TABLES TO AUTOMATE LIBRARY PROCEDURES. FOR EXAMPLE, THE BOOKSBORROWED PROCEDURE IS ACCESSIBLE WHEN THE CARDHOLDERS TABLE IS BEING DISPLAYED.

IF THE USER PRESSES B (FOR BOOKS OUTSTANDING), BOOKSBORROWED COMPILES A LIST OF THE BOOKS CHECKED OUT TO THAT CARD HOLDER. LENDEEPROFILE GIVES INFORMATION ON THE BORROWER OF THE TITLE CURRENTLY DISPLAYED FROM THE BOOKSTOCK TABLE.

THE BORROWBOOK PROCEDURE (NOT SHOWN IN THE FOLLOWING LISTING) ALLOWS A BOOK TO BE CHECKED OUT SIMPLY BY TYPING IN THE USER'S NAME. IF THE USER IS NOT A VALID CARD HOLDER, A WARNING IS DISPLAYED.

IF THE USER HAS A LIBRARY CARD, BORROWBOOK DISPLAYS THE CARD HOLDER'S INFORMATION SO IT CAN BE CHECKED TO SEE WHAT THE DUE DATE WILL BE, AND ALSO TO SEE IF THE PERSONAL INFORMATION NEEDS TO BE UPDATED. THE RETURNBOOK PROCEDURE (NOT SHOWN IN THE FOLLOWING LISTING) DISPLAYS THE NAME OF THE BORROWER, AND CALCULATES AND DISPLAYS ANY FINES THAT MAY BE DUE.

DELETING INDEXES AND TABLES

THE DELETEINDEX AND DELETETABLE STATEMENTS LET YOU DELETE AN INDEX OR A TABLE FROM THE DATABASE. THESE STATEMENTS HAVE THE FOLLOWING SYNTAX:

DELETEINDEX FILENUMBER%, INDEXNAME$
DELETETABLE DATABASE$, TABLENAME$

ARGUMENTS:

FILENUMBER%.~ A NUMERIC EXPRESSION REPRESENTING THE TABLE ON WHICH THE INDEX TO BE DELETED WAS CREATED.
INDEXNAME$.~ A STRING EXPRESSION REPRESENTING THE NAME OF THE INDEX TO BE DELETED.
DATABASE$.~ A STRING EXPRESSION REPRESENTING THE NAME OF THE DATABASE CONTAINING THE TABLE TO BE DELETED.
TABLENAME$.~ A STRING EXPRESSION REPRESENTING THE NAME OF THE TABLE TO BE DELETED.

IT ISN'T ALWAYS EASY TO ANTICIPATE WHICH COLUMNS A USER MAY WANT TO INDEX WHEN USING A DATABASE. AS AN ADVANCED FEATURE OF A PROGRAM YOU MIGHT WANT TO LET A USER CREATE INDEXES DURING RUN TIME. IF SO, DELETEINDEX CAN BE USED TO REMOVE THE SPECIFIED INDEX FROM THE DATABASE WHEN IT IS NO LONGER NEEDED.

YOU CAN USE THE DELETETABLE STATEMENT TO DELETE AN OLD TABLE WHEN YOU NO LONGER NEED ANY OF ITS DATA RECORDS. ONCE YOU DELETE A TABLE AND CLOSE THE DATABASE, THERE IS NO WAY TO RECOVER THE RECORDS. YOU SHOULD ASSUME THAT THE TABLE IS DELETED FROM THE FILE IMMEDIATELY UPON EXECUTION OF THIS STATEMENT, RATHER THAN AT SOME FUTURE TIME (FOR EXAMPLE, WHEN THE FILE IS CLOSED).

NOTE THAT WHEN YOU DELETE A TABLE, ALL INFORMATION (INCLUDING INDEXES, ETC.) IN THE DATA DICTIONARY RELATING TO THE TABLE IS ALSO DELETED. YOU CANNOT PRACTICALLY WRITE ROUTINES TO PERMIT A USER TO CREATE CUSTOM TABLES DURING RUN TIME, SINCE THE USER-DEFINED TYPE THAT DESCRIBES A TABLE MUST ALREADY EXIST WHEN THE PROGRAM BEGINS.

ALTHOUGH IT ISN'T COVERED HERE, YOU CAN WRITE ROUTINES THAT PERMIT A USER TO CREATE A TABLE THAT DUPLICATES THE COLUMNS OF A TABLE ALREADY IN THE DATABASE. FOR EXAMPLE, YOU MIGHT WANT TO PERMIT COPYING OF A SUBSET OF A TABLE'S RECORDS TO A TABLE OF THE SAME TYPE. EXECUTING DELETETABLE OR DELETEINDEX COMMITS ANY PENDING TRANSACTIONS.

ISAM NAMING CONVENTION

SOME PARTS OF AN ISAM DATABASE REQUIRE NAMES (FOR EXAMPLE, TABLES, COLUMNS, AND INDEXES), AND THESE NAMES MUST CONFORM TO THE ISAM NAMING CONVENTION. THE ISAM CONVENTION IS ESSENTIALLY A SUBSET OF THE BASIC CONVENTION, AS SHOWN IN THE FOLLOWING TABLE:

30 CHARACTERS OR FEWER.            40 CHARACTERS OR FEWER.

ALPHANUMERIC CHARACTERS ONLY,      ALPHANUMERIC CHARACTERS,
INCLUDING  A-Z, A-Z, AND 0-9.      PLUS THE BASIC
                                   TYPE-DECLARATION
                                   CHARACTERS, WHERE
                                   APPROPRIATE (VARIABLES AND
                                   FUNCTIONS).

MUST BEGIN WITH ALPHABETIC CHARACTER.  MUST BEGIN WITH
                                     ALPHABETIC CHARACTER, BUT
                                     ONLY DEF FN FUNCTIONS CAN
                                     BEGIN WITH "FN".

NO SPECIAL CHARACTERS ALLOWED. THE PERIOD IS NOT ALLOWED IN
                               THE NAMES OF ELEMENTS WITHIN A
                               USER-DEFINED TYPE. SINCE THESE
                               ARE THE NAMES BASIC AND ISAM
                               HAVE IN COMMON, THERE IS NO
                               CONFLICT.

NOT CASE SENSITIVE.            NOT CASE SENSITIVE.

STARTING ISAM FOR USE IN QBX

MICROSOFT BASIC INCLUDES TWO TERMINATE-AND-STAY-RESIDENT (TSR) PROGRAMS, PROISAM.EXE AND PROISAMD.EXE. YOU CAN USE EITHER OF THESE PROGRAMS TO PLACE THE ISAM ROUTINES IN MEMORY WHEN YOU DEVELOP OR RUN DATABASE PROGRAMS WITHIN THE QBX ENVIRONMENT.

THE BENEFIT OF THIS APPROACH IS THAT, WHEN YOU ARE WORKING ON PROGRAMS OR MODULES THAT DON'T NEED ISAM, THE AMOUNT OF MEMORY REQUIRED BY QBX IS SUBSTANTIALLY REDUCED. PROISAM.EXE CONTAINS ALL ISAM ROUTINES NEEDED TO RUN MOST DATABASE PROGRAMS.

IT DOES NOT CONTAIN THE "DATA DICTIONARY" STATEMENTS, CREATEINDEX, DELETEINDEX, AND DELETETABLE. IT CONTAINS A RESTRICTED VERSION OF THE OPEN...FOR ISAM STATEMENT THAT WILL OPEN A DATABASE OR TABLE, BUT WILL NOT CREATE IT IF IT DOES NOT ALREADY EXIST.

SINCE YOU OFTEN WILL NOT NEED TO PROGRAM THE CREATION AND DELETION OF INDEXES AND TABLES WITHIN AN END-USER PROGRAM, PROISAM.EXE IS USUALLY SUFFICIENT. PROISAMD.EXE CONTAINS ALL THE ISAM ROUTINES. YOU USE THE FOLLOWING SYNTAX TO START EITHER PROISAM.EXE OR PROISAMD.EXE:

{PROISAM | PROISAMD} /IB: PAGEBUFFERS /IE: EMSRESERVE /II:INDEXES /D

ARGUMENTS:

/IB: PAGEBUFFERS
INCREASES THE AMOUNT OF CONVENTIONAL MEMORY RESERVED FOR ISAM'S BUFFERS. THE DEFAULTS ARE 6 PAGEBUFFERS (12 KBS. OF 2 KBS. PAGES) FOR PROISAM, AND 9 PAGEBUFFERS (18 KBS. OF 2 KBS. PAGES) FOR PROISAMD. THERE IS ALSO 3-5 KBS. USED FOR DATA BY PROISAM AND 14-16 KBS. FOR PROISAMD. MAXIMUM VALUE FOR PAGEBUFFERS IS 512.

HOWEVER, SINCE DOS ONLY PROVIDES 640 KBS., THIS MAXIMUM IS NOT POSSIBLE IN CONVENTIONAL MEMORY. DETERMINE THE OPTIMAL VALUE FOR A SPECIFIC PROGRAM BY EXPERIMENTATION. NOTE THAT THE DEFAULT VALUES FOR PAGEBUFFERS ARE THE MINIMUMS NECESSARY FOR AN ISAM PROGRAM, NOT THE OPTIMAL OR AVERAGE VALUES.

IF YOU (OR YOUR ANTICIPATED USERS) DON'T HAVE EMS, MORE THAN THE DEFAULT NUMBER OF PAGEBUFFERS MAY BE NECESSARY FOR RUNNING THE PROGRAM. EVEN IF A PROGRAM RUNS WITH THE DEFAULT NUMBER OF PAGEBUFFERS, SPECIFYING THE MOST BUFFERS POSSIBLE IMPROVES ISAM PERFORMANCE.

NOTE, HOWEVER, THAT IF NO EMS IS AVAILABLE, TOO HIGH A VALUE FOR /IB COULD ALLOCATE SO MUCH MEMORY TO ISAM BUFFERS THAT THE TSR WOULD NOT BE ABLE TO REMOVE ITSELF FROM MEMORY WHEN INVOKED WITH THE /D OPTION. WHEN EMS IS AVAILABLE, THE AMOUNT SPECIFIED WITH /IB IS TAKEN FIRST FROM EMS, AND IF THAT IS NOT SUFFICIENT, THE REST IS TAKEN FROM CONVENTIONAL MEMORY.

/IE: EMSRESERVE
IF YOU HAVE EXPANDED MEMORY, ISAM WILL AUTOMATICALLY USE UP TO 1.2 MEGABYTES OF IT FOR BUFFERS (THAT IS, IT WILL PLACE UP TO 512 2 KBS. PAGEBUFFERS, PLUS ABOUT 10 PERCENT OVERHEAD SPACE, INTO EMS). ISAM TAKES AS MUCH EMS MEMORY AS POSSIBLE BY DEFAULT, WHICH FREES CONVENTIONAL MEMORY FOR OTHER USES WHILE IMPROVING PERFORMANCE.

THE NUMBER GIVEN IN THE /IE OPTION LETS YOU SPECIFY HOW MUCH EXPANDED MEMORY SHOULD BE RESERVED FOR PURPOSES OTHER THAN ISAM. THIS LIMITS THE AMOUNT OF EMS THAT ISAM USES FOR PAGEBUFFERS, SINCE ISAM WILL TAKE ONLY EMS BETWEEN THE EMSRESERVE SPECIFIED AND THE TOTAL EMS AVAILABLE. THE DEFAULT VALUE FOR EMSRESERVE IS 0. STATE VALUES IN KILOBYTES; FOR EXAMPLE, /IE:500 SPECIFIES 500 KBS. SHOULD BE LEFT AVAILABLE FOR OTHER PURPOSES.

IN PRACTICE, YOU ONLY NEED TO SPECIFY /IE IF YOUR PROGRAM CODE (OR A LOADED QUICK LIBRARY) ACTUALLY MANAGES EMS MEMORY. IN SUCH A CASE, YOU SHOULD ALSO SPECIFY THE /ES OPTION WHEN STARTING QBX OR WHEN COMPILING WITH BC. SPECIFYING A VALUE OF -1 FOR /IE RESERVES ALL EMS FOR OTHER USES, AND NONE IS USED BY ISAM.

/II:INDEXES
SPECIFIES THE NUMBER OF NON-NULLINDEXES USED IN THE PROGRAM. USE THIS OPTION IF YOUR PROGRAM HAS MORE THAN 30 INDEXES. IF YOU DON'T SPECIFY A VALUE FOR THIS OPTION, ISAM ASSUMES YOUR DATABASE CONTAINS NO MORE THAN 30 DEFINED INDEXES. IF THIS VALUE IS TOO LOW, YOUR PROGRAM MAY FAIL. MAXIMUM PERMISSIBLE VALUE IS 500.

/D REMOVES THE ISAM TSR FROM MEMORY.

NOTE.~ WHEN YOU USE TRANSACTIONS, ISAM KEEPS A TRANSACTION LOG. THE NAME OF THE LOG FILE IS GUARANTEED TO BE UNIQUE, SO MULTIPLE ISAM PROGRAMS CAN BE RUN IN SIMULTANEOUS WINDOWS IN AN OPERATING ENVIRONMENT LIKE WINDOWS 386 WITHOUT THE DANGER OF CONFLICTS WITHIN THE LOG FILES.

IN VERSIONS OF DOS EARLIER THAN 3.0 HOWEVER, THE NAME OF THE FILE IS PROISAM.LOG, AND IT IS CREATED IN THE /TMP DIRECTORY BY DEFAULT; OTHERWISE IT GOES IN THE CURRENT DIRECTORY. IF YOU SET YOUR /TMP ENVIRONMENT VARIABLE TO A RAM DRIVE, TRANSACTION LOGGING WILL BE FASTER.

DON'T CONFUSE THIS LOG FILE WITH THE PROISAM.EXE TSR. IF A LOG FILE APPEARS IN YOUR CURRENT DIRECTORY, YOU CAN DELETE IT; ISAM OVERWRITES THE OLD LOG FILE EACH TIME A TRANSACTION IS INITIATED.

ESTIMATING MINIMUM ISAM BUFFER VALUES

WHEN YOU LOAD THE ISAM TSR, IT REQUIRES MEMORY BEYOND ITS DISK-FILE SIZE BECAUSE IT RESERVES A CERTAIN AMOUNT OF MEMORY FOR BUFFERS IN WHICH IT DOES MOST OF ITS WORK. THE ACTUAL AMOUNT OF BUFFER SPACE RESERVED DEPENDS ON WHICH VERSION OF THE TSR YOU ARE USING, AND THE NUMBER YOU SPECIFY FOR THE /IB AND /II OPTIONS.

THE DEFAULTS REPRESENT THE ABSOLUTE MINIMUM REQUIRED FOR A MINIMAL ISAM PROGRAM. HAVING MORE BUFFERS AVAILABLE ALWAYS IMPROVES PERFORMANCE, AND IN SOME CASES MAY BE NECESSARY FOR A PROGRAM TO RUN AT ALL.

ADDITIONAL BUFFERS IMPROVE PERFORMANCE BECAUSE, WHEN THE BUFFERS ARE FULL, SOME OF THEIR CONTENTS IS WRITTEN BACK TO THE DISK. THIS CAUSES A DISK ACCESS TO UPDATE THE DISK FILE, AND ANOTHER ACCESS WHEN THE MATERIAL THAT WAS SWAPPED OUT HAS TO BE SWAPPED BACK IN. THE SWAPPING SYSTEM IS BASED ON A LEAST-RECENTLY-USED (LRU) ALGORITHM.

THE MORE BUFFERS THAT ARE AVAILABLE, THE LESS LIKELY IT IS THAT ANY PARTICULAR PIECE OF MATERIAL WILL NEED TO BE SWAPPED IN OR OUT. TO GET A BASIC IDEA OF THE MINIMUM NUMBER OF BUFFERS YOUR PROGRAM NEEDS, USE THE MAXIMUM OF 9 OR 6 (THE DEFAULT BUFFER SETTINGS, DEPENDING ON WHETHER YOU USE PROISAM OR PROISAMD), OR THE FOLLOWING FORMULA:

PAGEBUFFERS = 1 + W + X + 4 Y + 8 Z

IN THE PRECEDING FORMULA:
W = THE MAXIMUM NUMBER OF OPEN TABLES CONTAINING DATA
X = TOTAL OF NON-NULL INDEXES USED IN THE PROGRAM
Y = 1, IF INSERT OR UPDATE STATEMENTS ARE EXECUTED, OTHERWISE 0
Z = 1, IF A CREATEINDEX STATEMENT IS EXECUTED, OTHERWISE 0

DEPENDING ON THE DENSITY OF ISAM STATEMENTS IN ANY SECTION OF CODE, IT IS POSSIBLE THAT THE DEFAULT NUMBER OF BUFFERS WILL NOT BE ADEQUATE TO HANDLE THE NECESSARY PROCESSING. ANY EMS (UP TO 1.2O MEGABYTES) THAT IS AVAILABLE IS USED FOR ISAM BUFFERS. THIS LEAVES AN EQUIVALENT AMOUNT OF CONVENTIONAL MEMORY FOR OTHER PURPOSES.

NOTE HOWEVER, THAT ONLY THE ISAM BUFFERS ARE PLACED IN EMS, THE ISAM CODE (REPRESENTED APPROXIMATELY BY THE DISK-FILE SIZE) ITSELF RESIDES IN CONVENTIONAL MEMORY. USE THE /IE OPTION TO RESERVE ANY EMS THAT MAY BE NEEDED WHEN YOUR PROGRAM ACTUALLY MANAGES EMS INTERNALLY, OR WORKS CONCURRENTLY WITH ANY OTHER PROGRAMS THAT USE EMS.

ISAM AND EXPANDED MEMORY (EMS)

AS NOTED ABOVE, ISAM USES CONVENTIONAL AND EXPANDED MEMORY AS LONG AS THE EXPANDED MEMORY CONFORMS TO THE LOTUS-INTEL-MICROSOFT EXPANDED MEMORY SPECIFICATION (LIM 4.0). USING EXPANDED MEMORY CORRECTLY CAN ENHANCE BOTH THE PERFORMANCE AND CAPACITY OF PROGRAMS.

THE ACTUAL MANAGEMENT OF EXPANDED MEMORY IS DONE FOR YOU BY BASIC WITHIN THE LIMITS YOU SET WITH THE /IE ISAM OPTION AND THE /ES QBX OPTION. IF EXPANDED MEMORY IS AVAILABLE, ISAM USES THE DIFFERENCE BETWEEN THE TOTAL AND THE AMOUNT YOU SPECIFY WITH THE /IE OPTION, UP TO A MAXIMUM OF ABOUT 1.2O MEGABYTES.

THERE ARE SEVERAL FACTORS TO CONSIDER IN USING THE /IB AND /IE ISAM OPTIONS (AND THE /ES AND /EA QBX OPTIONS), INCLUDING THE FOLLOWING:

O.~ THE SYSTEM ON WHICH YOU DEVELOP A PROGRAM MAY HAVE DIFFERENT MEMORY RESOURCES THAN THE SYSTEM ON WHICH YOUR USER RUNS THE PROGRAM.

O.~ IF YOUR PROGRAM PERFORMS EXPLICIT EMS MANAGEMENT, OR USES A LIBRARY THAT DOES, YOU NEED TO RESERVE THE NECESSARY EMS WHEN STARTING THE TSR OR COMPILING THE PROGRAM. QBX AND BC HAVE OPTIONS DEALING WITH EMS (/ES AND /EA) THAT INTERACT WITH ISAM'S USE OF EMS IN CERTAIN SITUATIONS. YOU MAY NEED TO USE THE /ES OPTION WHEN INVOKING QBX OR BC.

IN ORDER TO PROVIDE FOR USERS WHO MAY NOT HAVE EMS, YOU SHOULD ALWAYS SPECIFY AN OPTIMAL SETTING FOR THE /IB ISAM OPTION. BEYOND THIS, IN MOST CASES, IT SHOULD SUFFICE TO ALLOW THE DEFAULTS TO DETERMINE THE APPORTIONING OF EMS BETWEEN ISAM AND OTHER EMS USAGE.

THE EMS DEFAULTS ARE DESIGNED TO MAKE THE BEST USE OF WHATEVER COMBINATIONS OF CONVENTIONAL MEMORY AND EMS MAY BE AVAILABLE. IN GENERAL, TRYING TO OPTIMIZE VALUES BETWEEN /IB AND /IE ONLY MAKES SENSE IF YOUR PROGRAM ITSELF ACTUALLY PERFORMS EXPANDED MEMORY MANAGEMENT, IN WHICH CASE YOU SHOULD BE SURE THERE IS ENOUGH EMS AVAILABLE FOR IT AT RUN TIME.

FOR EXAMPLE, IF YOU WANT ISAM TO USE 22 BUFFERS, THE BUFFERS REQUIRE 44 KBS. (EACH BUFFER REQUIRES 2 KBS.) PLUS UP TO 5 KBS. FOR DATA IN PROISAM.EXE, AND UP TO 16 KBS. FOR DATA WITH PROISAMD.EXE. THE 22 BUFFERS PLUS OVERHEAD FOR PROISAM.EXE WILL REQUIRE ABOUT 49 KBS. HOWEVER, IF EMS IS AVAILABLE AND YOU DON'T SPECIFY A VALUE FOR /IE, ISAM WILL USE ONE MEGABYTE OF EXPANDED MEMORY.

IF YOU HAVE ONLY ONE MEGABYTE OF EXPANDED MEMORY AVAILABLE, AND YOU HAVE WRITTEN YOUR PROGRAM TO EXPLICITLY MANAGE 500 KBS. OF THAT, YOU NEED TO SPECIFY 500 AS A VALUE FOR THE /IE OPTION. THIS RESERVES THE AMOUNT OF EXPANDED MEMORY YOUR PROGRAM MANAGES.

IF AN END USER OF THE PROGRAM HAS NO EMS AVAILABLE BEYOND THE 500 KBS. YOU HAVE RESERVED, ALL THE MEMORY NEEDED FOR THE ISAM PAGEBUFFERS IS TAKEN FROM CONVENTIONAL MEMORY.

THE ACTUAL ALGORITHM USED BY ISAM FOR APPORTIONING BUFFERS AND EMS IS AS FOLLOWS:

1. RESERVE EMS AS SPECIFIED BY /IE. IF LESS EMS IS AVAILABLE THAN SPECIFIED, RESERVE ALL EMS.

2. ALLOCATE NON-BUFFER MEMORY NEEDED BY ISAM. TAKE THIS FIRST FROM AVAILABLE EMS, THEN IF THAT IS INSUFFICIENT, TAKE THE REMAINDER FROM CONVENTIONAL MEMORY.

3. ALLOCATE THE NUMBER OF BUFFERS SPECIFIED BY THE /IB OPTION, FIRST FROM EMS, THEN, IF THAT IS INSUFFICIENT, FROM CONVENTIONAL MEMORY.

4. IF MORE EMS IS AVAILABLE THAN WAS NEEDED TO SATISFY /IB, KEEP ALLOCATING BUFFERS FROM EMS UNTIL ALL EMS IS CONSUMED, OR UNTIL THE ISAM LIMIT IS REACHED (512 BUFFERS, OR ABOUT 1.2 MEGABYTES INCLUDING OVERHEAD).

5. RELEASE THE EMS RESERVED IN STEP 1 (BY THE /IB OPTION) FOR USE BY OTHER PROGRAMS.

WHEN YOU CREATE AN EXECUTABLE FILE FROM WITHIN QBX, WHETHER OR NOT THE PROGRAM WILL NEED TO HAVE THE TSR INVOKED DEPENDS ON OPTIONS YOU CHOSE FOR ISAM DURING SETUP, AS EXPLAINED IN THE NEXT SECTION.

USING ISAM WITH COMPILED PROGRAMS

THERE ARE SEVERAL TYPES OF EXECUTABLE FILES YOU CAN PRODUCE FOR ISAM PROGRAMS, DEPENDING ON YOUR NEEDS AND THE NEEDS OF YOUR USERS. PROGRAMS THAT REQUIRE THE PRESENCE OF A TSR MAKE SENSE IF YOU ARE DISTRIBUTING SEVERAL DISTINCT ISAM-DEPENDENT PROGRAMS ON THE SAME DISK.

EACH INDIVIDUAL PROGRAM COULD BE SIGNIFICANTLY SMALLER IF ALL MADE USE OF THE ISAM ROUTINES FROM THE TSR. IF YOU COMPILE PROGRAMS SO THEY NEED THE RUN-TIME MODULE, YOU CAN HAVE THE ISAM ROUTINES LINKED IN AS PART OF THE RUN-TIME MODULE, OR HAVE THE USER START ONE OF THE TSR PROGRAMS BEFORE STARTING THE APPLICATION.

IN ANY OF THESE CASES, YOU HAVE A CHOICE OF TSRS. PROISAMD.EXE CONTAINS ALL THE ISAM ROUTINES, INCLUDING THE DATA DICTIONARY ROUTINES (FOR CREATING AND DELETING INDEXES, TABLES, DATABASES, ETC.). AT VARIOUS TIMES, NOT ALL THE ROUTINES ARE NECESSARY.

FOR EXAMPLE, IF YOU CREATE A PROGRAM AND SUPPLY AN EMPTY DATABASE FILE (ONE WITH TABLES AND INDEXES, BUT NO DATA RECORDS), YOUR PROGRAM WOULD HAVE NO REAL NEED TO CREATE TABLES OR INDEXES, SINCE THEY WOULD ALREADY EXIST IN THE FILE. IN SUCH CASES, YOU COULD SUPPLY THE SMALLER TSR PROGRAM (CALLED PROISAM.EXE) TO CONSERVE MEMORY.

WHEN YOU RAN THE SETUP PROGRAM, YOU HAD THE OPPORTUNITY TO CHOOSE LIBRARIES THAT WOULD CREATE EXECUTABLE FILES CONTAINING ALL THE ISAM ROUTINES. YOU COULD ALSO CHOOSE AN OPTION THAT CREATED RUN-TIME MODULES THAT CONTAINED ALL THE ISAM ROUTINES.

IF YOU DIDN'T CHOOSE THESE OPTIONS, YOUR EXECUTABLE FILES (AND RUN-TIME MODULES) WILL REQUIRE YOUR USERS TO RUN EITHER PROISAM.EXE OR PROISAMD.EXE BEFORE USING THE DATABASE PROGRAMS. TO QUALIFY FOR CASE 1 IN TABLE 10.2, YOU SHOULD HAVE SPECIFIED THE FULL ISAM OR REDUCED ISAM OPTION FOR BCL70 MSO.LIB.

THEN, WHEN THE EXECUTABLE FILE IS CREATED, YOU NEED TO HAVE EITHER PROISAMD.LIB OR PROISAM.LIB IN YOUR CURRENT DIRECTORY OR LIBRARY SEARCH PATH. FOR CASE 2 YOU SHOULD HAVE CHOSEN "ISAM ROUTINES IN TSR" DURING SETUP. IN THIS CASE YOU DON'T NEED TO HAVE PROISAM.LIB OR PROISAMD.LIB ACCESSIBLE WHEN THE EXECUTABLE FILE IS CREATED.

YOU MADE THE SAME SELECTIONS REGARDING RUN-TIME MODULES DURING SETUP (CASES 3 AND 4). WHEN YOU COMPILE A STAND-ALONE PROGRAM FROM THE COMMAND LINE (THAT IS, ONE THAT DOES NOT REQUIRE THE PRESENCE OF THE TSR AT RUN TIME), YOU CAN USE /IB, /IE, AND /II AS OPTIONS TO THE BC COMMAND.

THEIR SYNTAX AND GENERAL EFFECTS ARE THE SAME WITH THE STAND-ALONE PROGRAM AS DESCRIBED IN THE PREVIOUS SECTION. IF YOUR PROGRAM USES RUN-TIME OVERLAYS, THE EMS IS AUTOMATICALLY ALLOCATED FOR THE OVERLAYS FIRST, BEFORE ISAM.

IF YOU DON'T WANT OVERLAYS TO USE EMS YOU CAN LINK THE PROGRAM WITH NOEMS.OBJ AND OVERLAYS WILL BE SWAPPED TO DISK INSTEAD. IF OVERLAYS USE EMS, ISAM WILL TAKE WHATEVER REMAINS AFTER EMS ALLOCATION FOR THE OVERLAYS, UP TO 1.2O MEGABYTE.

IF YOUR PROGRAM DOES INTERNAL EMS MEMORY MANAGEMENT, IT CAN ONLY BE DONE FROM WITHIN A NON-OVERLAYED MODULE. HOWEVER, IN SUCH A CASE, YOU SHOULD PROBABLY LINK WITH NOEMS.OBJ. ALSO, REMEMBER TO COMPILE WITH THE /ES OPTION TO BC. YOU SHOULD ALWAYS SPECIFY A CORRECT VALUE FOR /II IF YOUR PROGRAM USES MORE THAN 30 INDEXES.

HOWEVER, NOTE THAT SHARING EXPANDED MEMORY BETWEEN ISAM AND OTHER USES INHIBITS ISAM PERFORMANCE, SINCE THE ISAM BUFFERS AND OTHER EMS USAGE MUST USE THE SAME EMS WINDOW TO ACCESS THE EXPANDED MEMORY. THIS MEANS THAT WITH EACH CALL TO THE ISAM LIBRARY, THE EMS STATE MUST BE SAVED AND RESTORED.

IF YOU MUST SHARE EMS MEMORY BETWEEN ISAM AND OTHER THINGS, USE THE RELATIVE AMOUNTS THAT OPTIMIZE ISAM PERFORMANCE. IN SUCH CASES, USE THE /ES OPTION TO GUARANTEE EMS SAVE AND RESTORE WITH MIXED-LANGUAGE CODE THAT MANAGES EMS.

IMPORTANT:

WHEN YOU COMPILE A PROGRAM FROM WITHIN QBX, ONLY THE QBX OPTIONS ARE PASSED TO THE COMPILER. THIS IS FINE IF AN ISAM STAND-ALONE PROGRAM WILL USE THE TSR, SINCE THE BUFFER AND INDEX OPTIONS ARE SPECIFIED WHEN THE TSR IS INVOKED. HOWEVER, IF THE PROGRAM IS TO HAVE THE ISAM ROUTINES INCLUDED IN THE EXECUTABLE FILE, YOU MUST COMPILE THE PROGRAM'S MAIN MODULE FROM THE COMMAND LINE, AND SPECIFY THE APPROPRIATE /I AND /E OPTIONS AS ARGUMENTS TO BC.

PRACTICAL CONSIDERATIONS WHEN USING EMS

NOTE THAT THE ISAM /IE OPTION AND THE QBX OPTIONS /E, /EA, AND /ES HAVE THE EFFECT OF RESERVING EMS FOR PROGRAMS THAT USE INTERNAL EMS MANAGEMENT (OR OTHER APPLICATIONS), RATHER THAN SPECIFICALLY LIMITING THE AMOUNT OF EMS USED BY THE PROGRAM FOR WHICH THE OPTION IS SUPPLIED. ISAM USES EMS TO IMPROVE PERFORMANCE BY RADICALLY REDUCING THE FREQUENCY OF DISK ACCESS.

IN GENERAL, THE AUTOMATIC APPORTIONING OF CONVENTIONAL AND EMS MEMORY SHOULD COVER THE WIDEST RANGE OF SITUATIONS BEST, BECAUSE WITH EACH ALLOCATION OF EMS, WHATEVER IS AVAILABLE IS USED WHENEVER IT CAN BE.

DURING DEVELOPMENT OF A VERY LARGE PROGRAM, IT MAY BE MORE BENEFICIAL TO RESERVE MOST AVAILABLE EMS FOR QBX (EXCEPT THE MINIMUM ISAM NEEDS FOR BUFFERS AND INDEXES), SINCE THE SPEED OF ISAM IS PROBABLY NOT AS IMPORTANT AS THE ABILITY TO HAVE QBX PLACE UNITS OF CODE IN EMS, THUS INCREASING THE POTENTIAL SIZE OF THE SOURCE FILES YOU CAN FIT IN QBX.

HOWEVER, SINCE QBX ONLY PLACES UNITS OF CODE IN THE 0.5-16 KBS. RANGE IN EXPANDED MEMORY, THIS IS ONLY OPTIMAL IF YOUR CODING STYLE IS TO USE SMALL TO MODERATE CODE UNITS (SUB AND FUNCTION PROCEDURES, AND MODULE-LEVEL CODE). IN A COMPILED PROGRAM, THE ISAM PERFORMANCE IN THE EXECUTABLE FILE IS THE MOST IMPORTANT FEATURE, SO COMPILING WITH HIGH VALUES FOR /IB (JUST TO PROVIDE FOR USERS WITH NO EMS) AND NO SPECIFICATION FOR /IE SHOULD OFFER THE BEST RESULTS.

ISAM NEVER USES MORE THAN 1.2 MEGABYTES, SO ALL REMAINING EMS IS AUTOMATICALLY AVAILABLE FOR OTHER USES. SUCH OTHER USES INCLUDE YOUR PROGRAM'S CODE UNITS, ARRAYS UP TO 16 KBS. (IF /EA IS SPECIFIED), OR EXPLICIT EMS MANAGEMENT WITHIN THE PROGRAM.

NOTE HOWEVER, THAT DIVIDING EMS BETWEEN ISAM AND OTHER USES SLOWS ISAM AND QBX PERFORMANCE TO SOME DEGREE. IT MAY MAKE SENSE DURING PROGRAM DEVELOPMENT, BUT MIGHT NOT BE SATISFACTORY IN A COMPILED PROGRAM. IF YOU WANT THIS TYPE OF SHARING, USE THE /IE OPTION TO RESERVE EMS FOR THE OVERLAYS, PLUS THE /ES OPTION TO ENSURE EMS SAVING AND RESTORATION. EMS IN A COMPILED PROGRAM IS AUTOMATICALLY USED FOR RUN-TIME OVERLAYS (IF YOU USE THEM).

TO PREVENT EMS SHARING, COMPILE WITH BC WITHOUT USING THE /IE OPTION OR /ES, AND SPECIFY /E:0 TO PREVENT USE OF EMS FOR ANYTHING BUT ISAM. FOR MORE INFORMATION ON USING THE /ES OPTION FOR QBX, SEE CHAPTER 3, "MEMORY MANAGEMENT FOR QBX", IN GETTING STARTED. RUN-TIME OVERLAYS ARE DISCUSSED IN CHAPTERS 15, "OPTIMIZING PROGRAM SIZE AND SPEED", AND 18, "USING LINK AND LIB", OF THIS BOOK.

NOTE.~ BASIC RELEASES EMS WHEN THE PROGRAM TERMINATES DUE TO A RUN-TIME ERROR, AS WELL AS AN END, STOP, OR SYSTEM STATEMENT. IF A PROGRAM TERMINATES FOR SOME OTHER REASON WHILE EMS IS BEING USED, THAT PORTION OF EMS WILL NOT BE AVAILABLE AGAIN UNTIL THE EMS MANAGER IS RESTARTED.

IF THE EMS MANAGER IS THE ONE USED IN MICROSOFT WINDOWS 386, YOU CAN SIMPLY EXIT FROM WINDOWS, THEN START WINDOWS AGAIN TO RECOVER THE EMS. IF YOUR EMS MANAGER IS STARTED BY AN ENTRY IN A CONFIG.SYS FILE, YOU MAY NEED TO REBOOT TO RECOVER USE OF EMS.

TSRS AND INSTALLATION/DEINSTALLATION ORDER

IF YOU (OR YOUR USERS) WILL BE USING OTHER TSR PROGRAMS BESIDES THE ISAM TSR, THEY SHOULD BE INSTALLED BEFORE THE ISAM TSR. THE REASON FOR THIS IS THAT THE ISAM TSR IS ONLY NEEDED WHEN THE ISAM PROGRAM IS RUN. IF YOU FINISH WITH YOUR ISAM PROGRAM AND HAVE INSTALLED ANOTHER TSR AFTER ISAM, YOU WILL HAVE TO REMOVE ANY MORE-RECENTLY INSTALLED TSR PROGRAMS BEFORE YOU CAN SUCCESSFULLY REMOVE THE ISAM TSR.

OTHERWISE, THE /D OPTION TO THE ISAM TSR WILL REMOVE ISAM FROM MEMORY, BUT THE MEMORY CANNOT BE USED BY THE OTHER PROGRAMS, AND THE OPERATING SYSTEM MAY BE DESTABILIZED. IF YOU ATTEMPT TO REMOVE TSRS IN AN IMPROPER ORDER, A WARNING MESSAGE IS DISPLAYED.

BLOCK PROCESSING USING TRANSACTIONS

TO ACCOMMODATE DATA ENTRY ERRORS, ISAM INCLUDES THREE TRANSACTION STATEMENTS AND ONE TRANSACTION FUNCTION THAT ALLOW YOU TO RESTORE A DATABASE TO A PREVIOUS STATE. BY USING THESE IN CONJUNCTION WITH THE CHECKPOINT STATEMENT (WHICH LETS YOU EXPLICITLY WRITE ALL OPEN DATABASES TO DISK) YOU CAN ENHANCE THE INTEGRITY OF YOUR USER'S DATABASES.

WHEN YOU USE UPDATE TO CHANGE A RECORD IN A TABLE, THE CHANGE IS MADE IMMEDIATELY. HOWEVER, THE ACTUAL WRITING OF DATA TO DISK IS DONE AT PERIODIC INTERVALS DETERMINED BY THE ISAM ENGINE. THE CHECKPOINT STATEMENT REQUIRES NO ARGUMENT. IT SIMPLY FORCES THE CURRENT STATE OF ALL OPEN DATABASES TO BE WRITTEN TO DISK.

CONVERSELY, YOU CAN CODE YOUR PROGRAM TO ALLOW A USER (OR A ROUTINE IN THE PROGRAM) TO RETRACT A SEQUENCE OF OPERATIONS EITHER SELECTIVELY OR AS A BLOCK. USING TRANSACTIONS (BLOCK PROCESSING) CAN HELP ENSURE CONSISTENCY TO OPERATIONS PERFORMED ON MULTIPLE TABLES AND MULTIPLE DATABASES.

THE FOLLOWING TABLE BRIEFLY DESCRIBES THESE BLOCK-PROCESSING STATEMENTS:

STATEMENT:

BEGINTRANS.~ STARTS A TRANSACTION LOG OF ALL OPERATIONS.
COMMITTRANS.~ ENDS MAINTENANCE OF THE TRANSACTION LOG.
SAVEPOINT.~ MARKS POINTS WITHIN THE TRANSACTION LOG TO WHICH THE TRANSACTION CAN BE ROLLED BACK.
ROLLBACK ALL.~ RESTORES THE STATE OF THE DATABASE TO WHAT IT WAS AT A SPECIFIED SAVE POINT OR AT THE BEGINNING OF THE TRANSACTION.

SPECIFYING A TRANSACTION BLOCK

BRACKETING CERTAIN PORTIONS OF YOUR CODE WITH BEGINTRANS AND COMMITTRANS STATEMENTS PROVIDES A MECHANISM TO RETRACT ALL CHANGES MADE TO A DATABASE WITHIN THE TRANSACTION BLOCK. NO RESULTS OF PROCESSING WITHIN THE BLOCK WILL BECOME PART OF THE DATABASE UNLESS EVERYTHING RESULTING FROM PROCESSING IN THE BLOCK BECOMES PART OF THE DATABASE.

BY FOLLOWING THE BLOCK WITH A CHECKPOINT STATEMENT, YOU CAN GUARANTEE THAT ALL RESULTS OF THE BLOCK ARE WRITTEN IMMEDIATELY TO DISK. SAVE POINTS ALLOW YOU TO DEFINE POINTS WITHIN TRANSACTIONS TO WHICH THE STATE OF THE DATABASE CAN BE ROLLED BACK.

DON'T CONFUSE A SAVE POINT WITH THE CHECKPOINT COMMAND. THE SAVEPOINT FUNCTION DOESN'T WRITE RECORDS TO DISK. IT SIMPLY RETURNS INTEGER IDENTIFIERS FOR EACH OF THE MARKERS IT SETS IN THE TRANSACTION LOG.

THE TRANSACTION LOG

THE BEGINTRANS STATEMENT CAUSES ISAM TO START LOGGING EVERY CHANGE MADE TO THE DATABASE. NOTE THAT ISAM ONLY LOGS CHANGES MADE TO THE DATABASE, IT DOES NOT KEEP TRACK OF EXECUTION FLOW OF YOUR PROGRAM. AFTER BEGINTRANS IS EXECUTED, CHANGES ARE STILL MADE TO THE DATABASE, BUT ISAM CAN BACKTRACK THROUGH THOSE CHANGES BY REFERRING TO THE TRANSACTION LOG.

INCLUDED IN THE LOG ENTRIES ARE EACH OF THE SAVE POINTS YOU SET WITH THE SAVEPOINT FUNCTION. IF A ROLLBACK STATEMENT IS EXECUTED AT SOME POINT WITHIN THE TRANSACTION BLOCK, ISAM CHECKS THE LOG AND RESTORES THE DATABASE TO THE STATE IT WAS IN WHEN THE SPECIFIED SAVE POINT WAS EXECUTED.

THIS INCLUDES REMOVING ANY CHANGES THAT WERE MADE TO DATA RECORDS SINCE THE SAVE POINT, AND RESTORING ALL INDEXES TO THE STATE THEY WERE IN AT THE SAVE POINT. BEGINTRANS AND COMMITTRANS TAKE NO ARGUMENTS.

USING SAVE POINTS

SINCE ISAM MAINTAINS ONLY ONE TRANSACTION LOG, YOU CANNOT NEST ONE TRANSACTION WITHIN ANOTHER. HOWEVER, THE ABILITY TO SET MULTIPLE SAVE POINTS WITHIN A TRANSACTION SUPPLIES SIMILAR FUNCTIONALITY WITH GREATER FLEXIBILITY.

WHILE BEGINTRANS AND COMMITTRANS SERVE AS BLOCK DELIMITERS FOR MULTIPLE ISAM DATA EXCHANGE CALLS, YOU CAN USE SAVE POINTS TO DELIMIT SMALLER DATA-EXCHANGE BLOCKS WITHIN A TRANSACTION. THE SAVEPOINT FUNCTION TAKES NO ARGUMENT, BUT RETURNS AN INTEGER THAT IDENTIFIES THE SAVE POINT THAT WAS SET.

ROLLBACK USES TWO FORMS, AS SHOWN IN THE FOLLOWING TABLE:

ROLLBACK SAVEPOINT
THE SAVEPOINT IS AN INTEGER IDENTIFIER CORRESPONDING TO A SAVE POINT RETURNED BY THE SAVEPOINT FUNCTION. THE EFFECT OF A ROLLBACK STATEMENT IS THE RESTORATION OF THE DATABASE TO THE STATE IT WAS AT THE NAMED SAVE POINT. IF NO SAVEPOINT IS SPECIFIED, THE ROLLBACK PROCEEDS TO THE NEXT AVAILABLE SAVE POINT.

ROLLBACK ALL
RESTORES THE DATABASE TO THE STATE IT HAD WHEN THE MOST RECENT BEGINTRANS WAS EXECUTED.

IF YOUR PROGRAM EXECUTES A ROLLBACK STATEMENT OUTSIDE A TRANSACTION BLOCK, SPECIFIES A NON-EXISTENT SAVE POINT, OR EXECUTES A QUALIFIED ROLLBACK WHEN THERE ARE NO SAVE POINTS WITHIN THE TRANSACTION BLOCK, A TRAPPABLE ERROR IS GENERATED. DATA DICTIONARY OPERATIONS (FOR EXAMPLE, DELETEINDEX) CANNOT BE ROLLED BACK, SINCE NO RECORD OF THEM IS KEPT IN THE TRANSACTION LOG.

A TRANSACTION CAN BE COMMITTED WITHOUT AN EXPLICIT COMMITTRANS BEING EXECUTED. FOR EXAMPLE, IF THERE IS AN ERROR IN AN ATTEMPT TO OPEN AN ISAM TABLE OR DATABASE, A CLOSE STATEMENT IS IMPLICITLY EXECUTED ON THE TABLE OR DATABASE. ANY TIME AN ISAM CLOSE IS PERFORMED (EITHER EXPLICITLY OR IMPLICITLY), ANY PENDING TRANSACTION IS COMMITTED.

IT IS NOT GOOD PRACTICE TO EXECUTE TABLE-LEVEL AND DATABASE-LEVEL OPERATIONS WITHIN A TRANSACTION, SINCE ERRORS CAN COMMIT THE TRANSACTION INDIRECTLY. EVEN IF AN ERROR DOESN'T OCCUR, AN IMPLICT CLOSE MAY OCCUR, COMMITTING THE TRANSACTION. FOR EXAMPLE, IF YOU DELETE A TABLE FROM A DATABASE, AND THERE IS NO OTHER TABLE OPEN WITHIN THE DATABASE, AN IMPLICIT CLOSE IS PERFORMED ON THE DATABASE.

SUCH A CLOSE CAUSES ALL PENDING TRANSACTIONS (EVEN IN ANOTHER OPEN DATABASE) TO BE COMMITTED. YOU SHOULD LIMIT THE USE OF TRANSACTIONS AS A PROGRAMMING TOOL FOR CONTROLLING RECORD-LEVEL OPERATIONS.

THE FOLLOWING EXAMPLE CODE ILLUSTRATES A TRANSACTION BLOCK ABSTRACTED FROM THE PROGRAM BOOKLOOK.BAS. THE INITIAL FRAGMENT FROM THE MODULE-LEVEL CODE INTERCEPTS THE CODE REPRESENTING WHAT THE USER WANTS TO DO AND CALLS THE EDITCHECK PROCEDURE TO DETERMINE WHETHER A TRANSACTION IS PENDING, TO BE BEGUN, OR TO BE COMMITTED.

THE EDITRECORD CASE IN THE BOOKLOOK.BAS MODULE-LEVEL CODE IS THE ONLY CASE THAT USES TRANSACTIONS. EACH TIME THE USER PRESSES ENTER AFTER EDITING A FIELD IN A TABLE, A SAVEPOINT STATEMENT IS EXECUTED JUST BEFORE THE RECORD IS UPDATED.

THE VALUE RETURNED BY SAVEPOINT IS SAVED IN AN ELEMENT OF THE ARRAY VARIABLE MARKER, AS LONG AS THE USER KEEPS EDITING FIELDS, WITHOUT PERFORMING OTHER MENU OPERATIONS, SUCH AS DISPLAYING OR SEARCHING FOR A NEW RECORD.

SAVEPOINT STATEMENTS ARE EXECUTED AFTER EACH SUCCEEDING EDIT. WHEN THE USER MAKES A MENU CHOICE OTHER THAN EDIT, THE TRANSACTION IS COMMITTED. ANY TIME PRIOR TO THE COMMITMENT, THE USER CAN CHOOSE TO UNDO EDITS WITHIN THE TRANSACTION, EITHER AS A GROUP (ROLLBACK ALL), OR SINGLY IN THE REVERSE ORDER FROM WHICH THEY WERE ENTERED, BY PRESSING U (UNDO) OR CTRL+U (UNDO ALL).

MAINTAINING PHYSICAL AND LOGICAL DATA INTEGRITY

THE "PHYSICAL INTEGRITY" OF A DATABASE IS WHAT GUARANTEES YOU WILL BE ABLE TO USE THE DATABASE. ISAM MAINTAINS THIS PHYSICAL INTEGRITY AS A MATTER OF COURSE WHENEVER YOU USE THE DATABASE.

HOWEVER, CIRCUMSTANCES CAN INTERVENE THAT CORRUPT A DATABASE. FOR EXAMPLE, POWER TO YOUR SYSTEM COULD BE INTERRUPTED WHILE ISAM IS IN THE PROCESS OF ACTUALLY WRITING DATA TO DISK. WHEN SUCH A "CRASH" OCCURS WHILE A FILE IS OPEN, THE CONSEQUENCES ARE UNPREDICTABLE.

FOR EXAMPLE, EVEN IF NO DRASTIC DAMAGE IS DONE, THE CRASH MAY OCCUR BEFORE ALL RELEVANT INDEXES IN THE DATABASE CAN BE UPDATED.

SIMILARLY, SOME PHYSICAL MISHAP COULD CORRUPT THE FILE WHILE YOU ARE NOT WORKING ON THE DATABASE. IF SOMEONE OPENED THE FILE WITH ANOTHER PROGRAM SUCH AS A WORD PROCESSOR, AND MODIFIED IT, ITS PHYSICAL INTEGRITY WOULD BE COMPROMISED. IN THESE TYPES OF SITUATIONS, YOU CAN USE THE ISAMREPR UTILITY TO RECOVER THE UNDAMAGED PARTS OF THE DATABASE AND RESTORE ITS PHYSICAL INTEGRITY.

MAKING FREQUENT BACKUPS OF DATABASE FILES IS AN IMPORTANT ELEMENT OF MAINTAINING PHYSICAL INTEGRITY. YOU CAN DO THIS WITH ANY COMMERCIAL BACKUP PROGRAM, OR SIMPLY WITH THE OPERATING SYSTEM COPY COMMAND, SINCE ALL THE PARTS OF AN ISAM DATABASE ARE CONTAINED WITHIN A SINGLE DISK FILE.

ISAM SPEED DEPENDS ON THE FACT THAT IT WRITES CHANGES TO THE DISK PERIODICALLY, RATHER THAN IMMEDIATELY. AT THE SAME TIME, SOMETHING LIKE AN EQUIPMENT FAILURE COULD OCCUR BETWEEN THE TIME A CHANGE IS MADE IN A TABLE AND THE TIME THE CHANGE IS WRITTEN TO DISK. IN SUCH A CASE, THE DATA WOULD BE LOST.

THIS TYPE OF LOSS CAN OCCUR WHEN THE PROGRAM SITS IDLE FOR A WHILE AFTER CHANGES ARE MADE TO A TABLE. TO MINIMIZE THE DANGER, BASIC CHECKS THE AMOUNT OF TIME THAT PASSES AND COMPARES THAT TO THE NUMBER OF TIMES THE KEYBOARD IS POLLED WHILE A PROGRAM IS SITTING IN AN "IDLE LOOP".

FOR EXAMPLE, WHEN INKEY$ OR AN INPUT STATEMENT IS USED TO POLL THE KEYBOARD FOR INPUT, IF A CERTAIN NUMBER OF KEYBOARD CHECKS ARE MADE, OR A CERTAIN AMOUNT OF TIME PASSES WITHOUT A KEYSTROKE, ISAM WRITES ALL CHANGED BUFFERS TO DISK. AS SOON AS A KEYSTROKE OCCURS OR BUFFERS ARE FLUSHED, THE CHECKING PROCESS STARTS ANEW.

DURING TRANSACTIONS, THE CHANGES ARE ACTUALLY MADE TO THE FILE ON THE NORMAL BASIS. THEIR PURPOSE IS AS A PROGRAMMING AID, RATHER THAN A FORM OF INTEGRITY INSURANCE. IF CHANGES ARE RESCINDED BY ROLLBACKS BEFORE THE TRANSACTION IS COMMITTED, THE TRANSACTION LOG IS USED TO RESTORE THE DATABASE TO THE PROPER STATE.

IF AN EQUIPMENT FAILURE OCCURS BEFORE A TRANSACTION IS COMMITTED, THE DISK FILE REPRESENTS THE STATE TO WHICH THE TRANSACTION HAD PROGRESSED, RATHER THAN THE STATE PRIOR TO THE TRANSACTION. THIS MEANS THAT THE CHANGES CANNOT BE RESCINDED BY ROLLBACKS IF THE FAILURE OCCURS WITHIN THE TRANSACTION.

CONVERSELY, WHEN A TRANSACTION CONCLUDES, NOT EVERYTHING IS NECESSARILY WRITTEN IMMEDIATELY TO THE PHYSICAL DISK FILE. THE ISAM ENGINE PERFORMS DISK WRITES USING ALGORITHMS THAT GIVE PRIORITY TO PERFORMANCE.

THEREFORE, THERE MAY BE A LAG BETWEEN THE TIME WHEN A TRANSACTION IS COMMITTED AND TIME THE FINAL PIECES OF DATA ARE WRITTEN TO DISK. AS IN OTHER SITUATIONS, IF NO KEYBOARD INPUT OCCURS WITHIN A CERTAIN PERIOD AFTER THE TRANSACTION IS COMMITTED, ISAM AUTOMATICALLY WRITES THE STATE OF THE TABLES TO DISK.

IN THE EVENT OF A LOSS OF POWER BETWEEN THE END OF THE TRANSACTION AND AUTOMATIC DISK WRITE, CHANGES NOT YET WRITTEN TO DISK CAN BE LOST. THIS COULD INCLUDE SOME PART OF THE END OF THE TRANSACTION. THEREFORE, ALTHOUGH THIS EVENTUALITY IS VERY UNLIKELY, ISAM INTERNALLY CANNOT GUARANTEE A PER-TRANSACTION LEVEL OF DATA INTEGRITY.

IN A PROGRAM COMPILED WITH THE /D OPTION, AN IMPLICIT CHECKPOINT STATEMENT IS PERFORMED EACH TIME A DELETE, INSERT, UPDATE, OR ISAM CLOSE STATEMENT IS EXECUTED. IF YOU ARE VERY CONCERNED WITH OBTAINING MAXIMUM DATA INTEGRITY, AND ARE WILLING TO SACRIFICE SPEED, COMPILE YOUR PROGRAM WITH /D.

YOU CAN WRITE CODE TO ENHANCE THE LOGICAL AND PHYSICAL INTEGRITY OF YOUR DATABASE. THE CHECKPOINT STATEMENT FORCES A PHYSICAL WRITE TO DISK OF ALL DATA IN THE ISAM BUFFERS. HOWEVER, WITH A LARGE DATABASE, PLACING CHECKPOINT STATEMENTS IN TOO MANY POINTS IN A PROGRAM CAN SIGNIFICANTLY INHIBIT PERFORMANCE.

RECORD VARIABLES AS SUBSETS OF A TABLE'S COLUMNS

YOU CAN OPEN A TABLE WITH A TABLETYPE THAT IS A SUBSET OF A RECORD WITHIN THE TABLE. TO ASSOCIATE THE SUBSET DATA TYPE WITH THE COLUMNS IN THE TABLE, YOU SPECIFY IT AS THE TABLETYPE ARGUMENT TO THE OPEN STATEMENT YOU USE TO OPEN THE TABLE. WHEN YOU ACTUALLY FETCH A RECORD FROM THE TABLE, YOU SPECIFY THE VARIABLE (OF TYPE TABLETYPE) IN A RETRIEVE STATEMENT.

THAT VARIABLE MUST HAVE THE SAME TYPE AS THE TABLETYPE ARGUMENT. IF IT IS NOT THE SAME TYPE (EVEN THOUGH IT MAY BE A VALID SUBSET IN THE SENSE THAT THE ELEMENT NAMES ARE PRECISELY THE SAME, ETC.), A TRAPPABLE ERROR OCCURS. NOTE HOWEVER, THAT ERROR CHECKING IN THE QBX ENVIRONMENT IS MORE ELABORATE THAN IN PROGRAMS COMPILED FROM THE COMMAND LINE.

IN A SEPARATELY COMPILED PROGRAM SUCH AN ERROR MAY NOT BE GENERATED. IN THE BOOKLOOK.BAS EXAMPLE DISCUSSED EARLIER, IF YOU WANTED TO OPEN THE TABLE BOOKSTOCK, BUT NOT ACCESS VALUES IN THE PUBLISHER AND PRICE COLUMNS, YOU COULD DECLARE A USER-DEFINED TYPE AS FOLLOWS:

TYPE SMALLSTOCK
IDNUM AS DOUBLE
TITLE AS STRING * 50
AUTHOR AS STRING * 36
END TYPE

THE ORDER IN WHICH THE ELEMENTS ARE SPECIFIED IS UNIMPORTANT AS LONG AS THE NAMES ARE THE SAME. YOU CAN STILL USE INDEXES BASED ON ALL THE COLUMNS IN THE TABLE, BUT YOU WOULD NOT BE ABLE TO TRANSFER VALUES TO AND FROM FIELDS IN THE PUBLISHER AND PRICE COLUMNS.

ISAM TRANSFERS VALUES BETWEEN A TABLE AND ITS CORRESPONDING STRUCTURED VARIABLE BY NAME, RATHER THAN BY POSITION. THEREFORE, IN CREATING A SUBSET THE ORDER OF THE ELEMENTS CAN VARY, AS LONG AS THE NAMES ARE THE SAME.

IN OTHER WORDS, YOU CAN SUBTRACT COLUMNS AS LONG AS YOU PRESERVE THE ORIGINAL NAMES PRECISELY, REGARDLESS OF THEIR POSITION. IF THE DATA TYPES ASSOCIATED WITH THE ELEMENT NAMES DO NOT CORRESPOND TO THOSE IN THE TABLE, OR IF A COLUMN NAME IS SPELLED DIFFERENTLY, A TRAPPABLE ERROR OCCURS.

THEREFORE, YOU CANNOT SIMPLY CHANGE THE DATA TYPE, OR THE NAME, OF A COLUMN. NOTE THAT IF THE LENGTH OF ONE OF THE STRINGS WAS GREATER THAN THE LENGTH ORIGINALLY DECLARED IN BOOKSTOCK, AN ERROR WOULD BE GENERATED. ONCE THE SUBSET TYPE IS DECLARED, YOU CAN OPEN THE BOOKSTOCK TABLE IN BOOKS.MDB AS FOLLOWS:

OPEN "BOOKS.MDB" FOR ISAM SMALLSTOCK "BOOKSTOCK" AS #1

USING MULTIPLE FILES: "RELATIONAL" DATABASES

BECAUSE ISAM MAINTAINS EVERYTHING YOU NEED FOR A DATABASE IN THE MULTIPLE TABLES IN THE SINGLE DATABASE FILE, YOU RARELY NEED TO WORK WITH OTHER FILES. SINCE A SINGLE ISAM DATABASE FILE CAN BE AS LARGE AS 128 MEGABYTES, MOST OF WHAT OTHER DATABASE SYSTEMS DO WITH MULTIPLE FILES CAN BE HANDLED IN A SINGLE ISAM DATABASE FILE.

IN A PROGRAM THAT ACCESSES MULTIPLE FILES, TABLE NAMES IN DIFFERENT FILES CAN BE IDENTICAL BECAUSE AN ISAM TABLE NAME IS MAINTAINED INTERNALLY AS A COMBINATION OF THE DATABASE NAME PLUS THE TABLE NAME. WHEN YOU OPEN MULTIPLE DATABASE FILES, THE NUMBER OF TABLES THAT CAN BE OPENED SIMULTANEOUSLY DEPENDS ON HOW MANY DATABASE FILES ARE OPEN, AS SHOWN IN THE FOLLOWING TABLE:

/I
SPECIFIES THAT AN ISAM TABLE IS TO BE CREATED FROM AN ASCII TEXT FILE. /I STANDS FOR "IMPORT".

/E
SPECIFIES THAT AN ASCII TEXT FILE IS TO BE CREATED FROM AN ISAM TABLE. /E STANDS FOR "EXPORT".

/H OR /?
DISPLAYS HELP FOR USING THE ISAMIO UTILITY. ANYTHING FOLLOWING THESE OPTIONS IN THE COMMAND LINE IS IGNORED.

ASCIIFILE.~ NAMES THE ASCII FILE TO BE IMPORTED (/I) OR EXPORTED (/E).

DATABASENAME.~ NAMES THE DATABASE FILE INTO WHICH THE TABLE SHOULD BE PLACED (/I) OR FROM WHICH THE DATA FOR THE ASCII FILE SHOULD BE TAKEN (/E).

TABLENAME.~ NAMES THE TABLE WITHIN THE DATABASE FILE INTO WHICH THE RECORDS FROM THE ASCII FILE SHOULD BE PLACED (/I), OR THE TABLE WITHIN THE DATABASE FROM WHICH THE DATA FOR THE ASCII FILE SHOULD BE TAKEN (/E).

/A
SPECIFIES THAT DATA BEING IMPORTED (/I) SHOULD BE APPENDED TO TABLENAME. IF TABLENAME DOES NOT EXIST, AN ERROR MESSAGE IS DISPLAYED. IF /A IS NOT SPECIFIED, ISAMIO IMPORTS THE DATA INTO THE NAMED TABLE BASED ON THE TABLE DESCRIPTION GIVEN IN SPECFILE (DESCRIBED LATER INTHIS TABLE). IF NO SPECFILE IS NAMED (OR FOUND), AN ERROR MESSAGE IS DISPLAYED.

/C
WHEN AN ISAM TABLE IS BEING IMPORTED (/I) FROM AN ASCII FILE, /C SPECIFIES THAT THE TABLE'S COLUMN NAMES SHOULD BE TAKEN FROM THE FIRST ROW OF DATA IN THE ASCII FILE. IF ANY OF THE SPECIFIED COLUMN NAMES ARE INCONSISTENT WITH THE ISAM NAMING CONVENTION, ISAMIO TERMINATES AND DISPLAYS AN ERROR MESSAGE.

WHEN AN ISAM TABLE IS BEING EXPORTED (/E), /C SPECIFIES THAT THE TABLE'S COLUMN NAMES SHOULD APPEAR IN THE ASCII FILE AS THE FIRST ROW OF DATA (WHEN AN ASCII FILE IS BEING CREATED FROM AN ISAM TABLE). IF /A AND /C ARE SPECIFIED, AN ERROR MESSAGE APPEARS.

IF /C IS NOT SPECIFIED WHEN A TABLE IS IMPORTED, ISAMIO INTERPRETS THE FIRST ROW IN ASCIIFILE AS THE BEGINNING OF THE DATA RECORDS, AND LOOKS FOR COLUMN NAMES IN SPECFILE. IF /C IS NOT SPECIFIED WHEN A TABLE IS EXPORTED, THE COLUMN NAMES ARE NOT EXPORTED.

/F:WIDTH
STIPULATES THE DATA BEING IMPORTED (/I) IS OF FIXED WIDTH, OR THAT DATA BEING EXPORTED (/E) SHOULD BE EXPORTED IN FIXED-WIDTH FORMAT (I.E., NO SEPARATORS APPEAR IN THE DATA FILE). THE SIZE OF THE FIXED WIDTH FIELDS ARE SPECIFIED IN THE FIRST FIELD OF THE SPECFILE, IF /F IS SPECIFIED.

IF YOU DON'T USE /F, THE FIELDS ARE ASSUMED TO BE COMMA DELIMITED, WITH DOUBLE QUOTATION MARKS ENCLOSING STRING DATA. WHEN EXPORTING FIXED WIDTH, WIDTH SPECIFIES THE WIDTH OF BINARY FIELDS. THE DEFAULT WIDTH IS 512.

SPECFILE
A FILE THAT SPECIFIES THE DATA TYPE AND SIZE (FOR STRINGS, ARRAYS, AND USER-DEFINED TYPES) FOR EACH COLUMN OF A TABLE. THE FILE'S FORMAT IS AS FOLLOWS:
FIXEDWIDTHSIZE, TYPE, SIZE, COLUMNNAME
FIELDS CAN BE SEPARATED WITH SPACES OR COMMAS. THE FIXEDWIDTHSIZE MAY ONLY APPEAR IF THE /F OPTION WASSPECIFIED.

THE OTHER ARGUMENTS APPEAR ONLY IF THE /A OPTION WAS NOT SPECIFIED (OTHERWISE, IT IS IGNORED). THE TYPE IS ONE OF THE INDEXABLE ISAM DATA TYPES. IN THE CASE OF ARRAYS, USER-DEFINED TYPES, AND STRINGS LONGER THAN 255 CHARACTERS, SPECIFY TYPE AS BINARY. THE COLUMNNAME IS ANY VALID ISAM COLUMN NAME, BUT IS IGNORED IF THE /C OPTION IS GIVEN.

IF SPECFILE IS NOT VALID, A DESCRIPTIVE ERROR MESSAGE IS DISPLAYED. VALID DESIGNATIONS FOR TYPE INCLUDE BINARY, INTEGER, LONG, REAL, AND CURRENCY; YOU CAN ALSO SPECIFY VARIABLETEXT (VT), AND VARIABLESTRING (VS).

IF THE TYPE IS ONE OF THE LATTER, THE SIZE FIELD MUST APPEAR. IF SPECFILE APPEARS ON THE COMMAND LINE WHEN EXPORTING, A SPECFILE SUITABLE FOR IMPORTING IS CREATED.

TO SEE AN EXAMPLE OF A SPECFILE, YOU CAN EXPORT AN EXISTING TABLE, SUCH AS ONE OF THE SYSTEM TABLES, WITH A COMMAND LINE HAVING THE FOLLOWING FORM:ISAMIO /E NUL DATABASENAME MSYSOBJECTS CONTHIS LINE SENDS THE CONTENTS OF THE SYSTEM TABLE TO NUL, THEN PRINTS THE SPECFILE TO THE SCREEN.

/D
SPECIFIES THAT A DB/LIB FILE IS TO BE CONVERTED.
/M
SPECIFIES THAT AN MS/ISAM FILE IS TO BE CONVERTED.
/B
SPECIFIES THAT A BTRIEVE DATABASE IS TO BE CONVERTED.

FILENAME.~ THE NAME OF A DATA FILE TO BE CONVERTED.
TABLENAME.~ THE NAME OF THE ISAM TABLE INTO WHICH THE CONVERTED RECORDS WILL BE ORGANIZED. THIS NAME MUST FOLLOW THE ISAM NAMING CONVENTION.

DATABASENAME.~ THE NAME OF THE ISAM DATABASE FILE INTO WHICH THE TABLE WILL BE PLACED.
SPECFILE.~ YOU MUST SUPPLY THIS FILE WHEN CONVERTING BTRIEVE AND MS/ISAM FILES. IT HAS THE FOLLOWING FORM:

BASICTYPE, SIZE, COLUMNNAME

THE BASIC TYPE IS THE TERM USED BY BTRIEVE TO IDENTIFY THE DATA TYPE; THE SIZE IS THE LENGTH OF THE FIELD IN THE BTRIEVE FORMAT. THE COLUMNNAME IS ANY VALID ISAM COLUMN NAME. THE SIZE IS IGNORED FOR ALL TYPES EXCEPT STRING.

IF DATABASENAME DOES NOT EXIST, IT IS CREATED. THE UTILITY USES THE FILE UTILITIES SUPPLIED WITH THE DATABASE PACKAGE THAT CREATED THE FILE. FOR EXAMPLE, THE BTRIEVE TSR MUST BE LOADED WHEN CONVERSION IS ATTEMPTED. IF THE OTHER-PRODUCT FILE UTILITIES ARE NOT AVAILABLE TO ISAMCVT, A MESSAGE IS DISPLAYED.

TO CONVERT THE INDEXES OF THE ORIGINAL BTRIEVE, MS/ISAM, OR DB/LIB FILE, RUN ISAMCVT ON THE FILE THAT CONTAINS THE INDEX AND NAME THE ISAM TABLE AND DATABASE TO WHICH THE INDEX APPLIES.

EXAMPLE ENTRIES IN A BTRIEVE SPECFILE MIGHT LOOK AS FOLLOWS:

STRING 4 STRINGCOL
INTEGER 2 INTCOLUMN
LONG 10 LONGCOLUMN
DOUBLE 5 DOUBLECOL

IN ADDITION TO DOUBLE AND SINGLE, DMBF AND SMBF (FOR THE CORRESPONDING MICROSOFT BINARY FORMAT) ARE ALSO VALID BTRIEVE COLUMN TYPES. WHEN THE CONVERSION IS DONE, YOU CAN OPEN THE TABLES FROM WITHIN A BASIC PROGRAM AND BEGIN USING THEM RIGHT AWAY. THE FOLLOWING TABLE DESCRIBES HOW THE DATA TYPES ASSOCIATED WITH THE OLD FILE MAP TO THE TYPE...END TYPE VARIABLES YOU WILL BE USING IN YOUR BASIC PROGRAM:

DB/LIB BTRIEVEMS/ISAMBASIC'S ISAM

THE REPAIR UTILITY

THE MICROSOFT BASIC PACKAGE INCLUDES THE (ISAMREPR.EXE) UTILITY TO HELP RECOVER DATABASES THAT BECOME CORRUPTED. ISAMREPR CAN ONLY RESTORE PHYSICAL INTEGRITY TO A DATABASE (I.E., CONSISTENCY AMONG THE TABLES IN THE DATABASE). ISAM DOES NOT PRE-IMAGE CHANGES MADE IN A DATABASE AND WRITE THEM TO A TEMPORARY FILE.

THEREFORE, IT IS NOT POSSIBLE TO RESTORE INDIVIDUAL RECORDS ENTERED IF A CRASH OCCURS BETWEEN THE TIME THE RECORDS ARE ENTERED IN THE TABLE AND THE NEXT PHYSICAL DISK WRITE. IF THIS TYPE OF SITUATION IS A MAJOR CONCERN, YOU CAN REDUCE THE CHANCE OF LOSING SUCH RECORDS BY COMPILING PROGRAMS WITH THE /D OPTION AND MAKING JUDICIOUS USE OF CHECKPOINT STATEMENTS IN YOUR PROGRAM.

WHEN ISAMREPR RESTORES THE DATABASE, IT SYSTEMATICALLY GOES THROUGH EVERY TABLE AND INDEX AND RECREATES THE DATABASE, USING EVERY PIECE OF INTERNALLY CONSISTENT INFORMATION IN THE FILE. IF ANYTHING IS FOUND THAT CANNOT BE RECONCILED WITH THE OTHER INFORMATION IN THE FILE, IT IS DELETED.

THIS RESTORES CONSISTENCY TO THE DATABASE. THERE IS A CHANCE THAT YOU WILL NEED TO RECREATE SOME INDEXES. SIMILARLY, IT IS POSSIBLE THAT SOME BLOCKS OF DATA WILL NEVER BE RECONCILED WITH THE REST OF THE DATABASE, AND WILL THEREFORE BE LOST.

THE SYNTAX FOR ISAMREPR.EXE IS AS FOLLOWS: ISAMREPR DATABASENAME

THE DATABASENAME IS THE FILENAME OF THE DATABASE YOU NEED TO REPAIR. ISAMREPR RESTORES PHYSICAL INTEGRITY TO THE DATABASE AND PRINTS MESSAGES TO THE SCREEN WHENEVER IT TAKES AN ACTION THAT RESULTS IN THE LOSS OF DATA. THESE MESSAGES DESCRIBE THE TYPES OF PROBLEMS THAT WERE DISCOVERED AND CORRECTED. YOU CAN REDIRECT THIS OUTPUT TO A FILE. THE MESSAGES THAT MAY APPEAR, WITH DESCRIPTIONS, ARE INCLUDED IN THE FOLLOWING TABLE:

MESSAGES:

TABLE NAME WAS TRUNCATED: DATA LOST
DURING STRUCTURAL ANALYSIS OF THE TABLE'S DATA PAGES, AN INCONSISTENT PAGE CAUSED THE TABLE TO BE TRUNCATED AS OF THE LAST UNCORRUPTED PAGE. THIS MESSAGE IS ONLY GIVEN ONCE FOR ANY AFFECTED TABLE.

ONE OR MORE RECORDS WERE DELETED FROM TABLENAME.
ONE OF ISAM FILE'S INTERNAL TABLES (MSYSOBJECTS, MSYSINDEXES, OR MSYSCOLUMNS) WAS FOUND TO HAVE INCONSISTENT DATA, OR DURING THE STRUCTURAL ANALYSIS OF A TABLE'S DATA PAGES, AN INCONSISTENT DATA PAGE WAS REMOVED FROM THE TABLE (WHICH RESULTED IN THE DELETION OF ANY TABLE RECORDS ON THAT PAGE).

ONE OR MORE LONG VALUES WERE DELETED FROM TABLENAME.
"LONG VALUES" REFER TO STRINGS LONGER THAN 255 CHARACTERS, ARRAYS, AND USER-DEFINED TYPES (NOT TO THE LONG DATA TYPE). THEIR CONNECTIONS TO THE TABLE WERE CORRUPTED, SO THEY WERE DELETED. THIS MESSAGE IS ONLY GIVEN ONCE FOR ANY AFFECTED TABLE.

CANNOT REPAIR NAME: NOT A DATABASE FILE.
REPAIR PROCESS HAS BEEN ABORTED BECAUSE THE FILE WAS NOT RECOGNIZABLE AS A DATABASE.

CANNOT REPAIR DATABASE NAME: UNCORRECTABLE PROBLEMS.
REPAIR PROCESS HAS BEEN ABORTED BECAUSE THE DATABASE CANNOT BE REPAIRED. SOME COMMON REASONS INCLUDE: SYSTEM TABLES NOT FOUND IN THE EXPECTED LOCATIONS; ANY OF THE SYSTEM TABLES IS STRUCTURALLY INCONSISTENT; INFORMATION TO RECONSTRUCT SYSTEM DATA IS UNAVAILABLE; REBUILT SYSTEM DATA IS INCONSISTENT; RECORDS DESCRIBING SYSTEM TABLES, COLUMNS, OR INDEXES ARE INCONSISTENT OR MISSING.

REPAIR OF NAME COMPLETED SUCCESSFULLY: REPAIR PROCESS COMPLETED.

A REPAIR MAY ALSO BE ABORTED FOR REASONS HAVING NOTHING TO DO WITH THE STATE OF THE DATABASE. MESSAGES RESULTING IN SUCH CASES INCLUDE (BUT ARE NOT LIMITED TO): DISK FULL, OUT OF MEMORY, AND FILE NOT FOUND. WHEN YOU USE THE ISAMREPR UTILITY IT REQUIRES ADDITIONAL SPACE WITHIN YOUR DATABASE TO ACCOMPLISH ITS WORK.

THIS ADDS AT LEAST 32 KBS. TO THE SIZE OF THE DATABASE. DO NOT RUN THE UTILITY IF YOUR DISK DOES NOT HAVE THIS AMOUNT OF SPACE AVAILABLE IN THE CURRENT WORKING DIRECTORY. ISAMREPR DELETES INCONSISTENT RECORDS IN TABLES, BUT DOES NOT COMPACT AFTER DOING SO. COMPACTING A DATABASE IS DESCRIBED IN THE NEXT SECTION.

THE ISAMPACK UTILITY

WHEN TABLES OR RECORDS ARE DELETED FROM A DATABASE (EITHER BY YOUR PROGRAM, OR THE ISAMREPR UTILITY), THE SIZE OF YOUR DISK FILE DOES NOT CHANGE. INSTEAD, THE DELETED DATA IS MARKED, AND ISAM BEGINS TO REUSE THE SPACE IN THE FILE AS YOU ADD TO THE DATABASE. THE ISAMPACK UTILITY PERFORMS TWO FUNCTIONS.

FIRST, IF THERE IS A TOTAL OF 32 KBS. OF DATA MARKED FOR DELETION, ISAMPACK ACTUALLY SHRINKS THE DISK FILE IN INCREMENTS OF 32 KBS. IF THERE IS NOT 32 KBS. OF DATA MARKED FOR DELETION, ISAMPACK HAS NO EFFECT ON THE SIZE OF THE DISK FILE.

HOWEVER, ANY TIME YOU RUN ISAMPACK, IT REMOVES RECORDS MARKED FOR DELETION AND THEN COPIES THE DATABASE, TABLE BY TABLE, AND INDEX BY INDEX INTO A DATABASE HAVING THE SAME NAME (IF NO NEWDATABASENAME IS SPECIFIED). THE EFFECT OF COMPACTION IS IMPROVED PERFORMANCE, IN THE SAME WAY THAT COMPACTING A HARD DISK IMPROVES PERFORMANCE.

AS IT COMPACTS THE DATABASE, ISAMPACK PRINTS A REPORT TO THE SCREEN THAT LISTS THE DATABASE'S TABLES (INCLUDING THE TYPES AND MAXIMUM LENGTHS OF EACH OF THEIR COLUMNS), AND THE NUMBER OF RECORDS IN EACH TABLE. IT ALSO LISTS (BY TABLE), ALL THE DATABASE'S INDEXES, THE COLUMNS THEY ARE BASED ON, AND WHETHER OR NOT EACH ONE IS UNIQUE. YOU CAN REDIRECT THIS REPORT TO A FILE IF YOU CHOOSE. THE SYNTAX FOR ISAMPACK.EXE IS AS FOLLOWS:

ISAMPACK DATABASENAME NEWDATABASENAME

THE DATABASENAME IS THE FILENAME OF THE ISAM DISK FILE. THE NEWDATABASENAME IS AN OPTIONAL ALTERNATE NAME FOR THE COMPACTED DATABASE. IF NO NEWDATABASENAME IS GIVEN, THE ORIGINAL DATABASE FILE IS RENAMED WITH THE FILENAME EXTENSION .BAK EITHER APPENDED TO DATABASENAME OR REPLACING THE ORIGINAL EXTENSION.

CONVERTING BTRIEVE CODE

IF YOU HAVE BEEN USING BTRIEVE AS A DATABASE FILE MANAGER, YOU MAY FIND THAT THE ISAM INTEGRATED INTO MICROSOFT BASIC IS A CONVENIENT AND FAR LESS COMPLICATED SUBSTITUTE. IF YOU'VE READ THE PRECEDING PORTIONS OF THIS CHAPTER, YOU PROBABLY HAVE A GOOD IDEA ALREADY OF HOW USING ISAM CAN CLEAN UP YOUR FILE-ACCESS CODE.

WITH ISAM, THE INTERFACE BETWEEN YOUR PROGRAM AND YOUR DATABASE CONSISTS ONLY OF THE ISAM STATEMENTS AND THE STRUCTURED VARIABLES YOU DEFINE TO TRANSFER VALUES BETWEEN YOUR PROGRAM AND DATABASE TABLES. USING ISAM REQUIRES NO ELABORATE INITIALIZATION, AND USING ISAM STATEMENTS IS MUCH MORE DIRECT THAN PASSING A LONG LIST OF ARGUMENTS TO BTRV. WHEN YOU USE ISAM, YOU DON'T NEED ANY OF THE FOLLOWING:

O.~ DEF SEG
O.~ ISAM MANAGES MEMORY ADDRESSING FOR YOU.
O.~ OPEN NUL
O.~ WITH ISAM, YOU ONLY HAVE TO WORRY ABOUT YOUR ACTUAL DATABASE FILE.
O.~ FIELD STATEMENTS
O.~ ISAM LETS YOU USE REAL, STRUCTURED VARIABLES FOR RECORDS.
O.~ OPERATION CODES
O.~ ISAM PROVIDES EASY-TO-USE (AND UNDERSTAND) STATEMENTS FOR DATABASE ACCESS AND MANIPULATION.

O.~ STATUS CODES
O.~ ERRORS IN ISAM ARE TRAPPED LIKE ANY OTHER BASIC ERRORS.
O.~ FCB ADDRESSES AND BUFFER LENGTHS
O.~ ISAM HANDLES ALL DOS INTERACTIONS INVISIBLY.
O.~ KEY BUFFERS AND KEY NUMBERS
O.~ ISAM USES INDEXES AND MAINTAINS THEM FOR YOU.
O.~ POSITION BLOCKS
O.~ ISAM HANDLES FILE POSITION INVISIBLY.

YOUR DATABASE FILES ARE CREATED FROM WITHIN YOUR BASIC PROGRAMS, AS ARE ALL TABLES AND INDEXES. ALTHOUGH YOU DO NEED TO INVOKE A TSR BEFORE LOADING QBX WHEN YOU WANT TO DEVELOP DATABASE CODE WITHIN THE ENVIRONMENT, WHEN YOU CREATE A STAND-ALONE VERSION OF THE DATABASE PROGRAM, YOU CAN HAVE ALL FILE MANAGEMENT SUPPORT BUILT INTO THE EXECUTABLE FILE, SO YOUR USER NEVER HAS TO DO ANYTHING BUT FIRE UP THE PROGRAM TO WORK WITH A DATABASE.

SIMILARLY, BECAUSE THE ISAM DATA DICTIONARY AND ALL YOUR TABLES OF DATA ARE SAVED WITHIN THE SAME DISK FILE, YOU DON'T HAVE TO WORRY ABOUT KEEPING TRACK OF MULTIPLE FILES. BTRIEVE'S TRANSACTION PROCESSING IS LIMITED TO ENHANCING DATA INTEGRITY.

ISAM'S SAVEPOINT AND ROLLBACK FEATURES HELP INSURE DATA INTEGRITY, BUT EVEN MORE IMPORTANTLY, THEY SIMPLIFY PROGRAMMING IN WHICH YOU WANT TO ALLOW A USER TO RESCIND A BLOCK OF DATA EXCHANGES.

HOWEVER, BTRIEVE OFFERS THE FOLLOWING FEATURES NOT YET AVAILABLE IN ISAM:

O.~ SUPPORT FOR MULTI-USER NETWORKS
O.~ SOME VERSIONS OF BTRIEVE SUPPORT SIMULTANEOUS MULTIPLE-USER ACCESS TO THE SAME FILE, WHILE THE ISAM IN BASIC DOES NOT.
O.~ AUTOMATIC LOGICAL INTEGRITY PROTECTION

O.~ BTRIEVE USES A PRE-IMAGING SYSTEM OF TEMPORARY FILES FOR ENSURING LOGICAL RECORD INTEGRITY AND CONSISTENCY AMONG FILES. ISAM GUARANTEES ONLY PHYSICAL INTEGRITY.

IF YOUR SYSTEM CRASHES IN THE MIDDLE OF A DATABASE OPERATION, YOUR ISAM FILE AUTOMATICALLY MAINTAINS ITS INTERNAL CONSISTENCY, BUT THE ONE OR TWO MOST RECENT EDITS TO RECORDS MAY BE LOST. SINCE UPDATES TO RECORDS TAKE PLACE SIMULTANEOUSLY IN THE RECORD TABLES AND THE DATA DICTIONARY, THE POSSIBILITY OF INCONSISTENT FILES IS REDUCED GREATLY.

THE WORST THAT CAN HAPPEN TO A ISAM FILE IS THE LOSS OF THE LATEST EDITS TO SEVERAL RECENTLY MODIFIED RECORDS. YOU CAN MINIMIZE THE EFFECTS OF SUCH LOSSES BY CAREFUL USE OF THE CHECKPOINT STATEMENT, BUT UNWRITTEN RECORDS CAN BE LOST AS A RESULT OF SYSTEM CRASHES.

O.~ SAME DATABASE ACROSS DIFFERENT DISKS
O.~ BTRIEVE PERMITS YOU TO EXTEND A DATABASE ACROSS SEVERAL DIFFERENT DISKS. WHILE THE MAXIMUM SIZE (128 MEGABYTES) OF A SINGLE ISAM FILE MEANS YOU WILL PROBABLY NEVER HAVE TO PARTITION A DATABASE IN THIS WAY, IF YOU HAVE BEEN USING THIS BTRIEVE FEATURE, YOU WILL HAVE TO REDESIGN YOUR DATABASE FOR ISAM.

O.~ SEEK IN DESCENDING ORDER
O.~ WHEN YOU USE GET LOWER FROM SOMEWHERE OTHER THAN THE SECOND RECORD IN AN INDEX, BTRIEVE MOVES TO THE FIRST MATCH IT MAKES BY DESCENDING DOWN THE RECORDS IN THE INDEX. ISAM DOES NOT OFFER AN EQUIVALENT STATEMENT. IF YOUR CODE RELIES HEAVILY ON BTRIEVE'S DESCENDING-ORDER SEEKING, YOU CAN SUBSTITUTE COMBINATIONS OF SEEKEQ AND MOVEPREVIOUS.

O.~ NULL KEY
O.~ IN BTRIEVE, WHEN YOU DESIGNATE A KEY AS NULL, IT IS OMITTED FROM THE INDEX. THERE IS NO WAY TO OMIT RECORDS HAVING NO VALUE FROM THE SORTING ORDER OF A GIVEN INDEX IN MICROSOFT ISAM. RECORDS WITH ZERO VALUE IN THE CURRENT ISAM INDEX SIMPLY SORT AS THOUGH THEY HAD THE LOWEST VALUE FOR THAT INDEX. IF YOUR PROGRAM RELIES ON NULL KEYS IN BTRIEVE, YOU WILL NEED TO RECODE IN ISAM TO PRODUCE THE SAME BEHAVIOR.

THE FOLLOWING TABLE ILLUSTRATES THE CORRESPONDENCE BETWEEN BTRIEVE OPERATION CODES AND THE ISAM STATEMENTS AND FUNCTIONS:

BTRIEVE CODE       DESCRIPTION          BASIC  EQUIVALENT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0 (OPEN)       MAKES FILE AVAILABLE     OPEN STATEMENT MAKES
               FOR ACCESS.              TABLES ACCESSIBLE
                                        WITHIN THE DATABASE
                                        FILE.

1 (CLOSE)       RELEASES BTRIEVE FILE.  CLOSE STATEMENT CLOSES
                                        ISAM TABLES (AND ITS
                                        DATABASE FILE).

2 (INSERT)     INSERTS A NEW RECORD IN  INSERT STATEMENT
               THE FILE.                INSERTS RECORD INTO
                                        ISAM TABLE.

3 (UPDATE)     OVERWRITES CURRENT       UPDATE STATEMENT.
               RECORD.

4 (DELETE)     DELETES CURRENT RECORD.  DELETE STATEMENT.

5 (GET EQUAL)  FETCHES THE FIRST        SEEKEQ + RETRIEVE
               RECORD  WHOSE FIELD      STATEMENTS FETCHES THE
               VALUE MATCHES THE        FIRST MATCHING RECORD
               SPECIFIED KEY VALUE.     IN THE CURRENT INDEX.

6 (GET NEXT)   FETCHES THE RECORD       MOVENEXT +  RETRIEVE
               IMMEDIATELY FOLLOWING    STATEMENTS.
               THE CURRENT RECORD.

7 (GET PREVIOUS) FETCHES THE RECORD      MOVEPREVIOUS +
                IMMEDIATELY PRECEDING    RETRIEVE STATEMENTS.
                THE CURRENT RECORD.

8 (GET GREATER) FETCHES THE FIRST       SEEKGT + RETRIEVE
                RECORD WHOSE FIELD      STATEMENTS FETCH THE
                VALUE EXCEEDS THE       FIRST MATCHING RECORD
                SPECIFIED KEY VALUE.    IN THE CURRENT INDEX.

9 (GET GREATER OR FETCHES THE FIRST        SEEKGE + RETRIEVE
EQUAL)            RECORD WHOSE FIELD       STATEMENTS FETCH
                  VALUE EQUALS OR EXCEEDS  THE FIRST MATCHING
                  THE SPECIFIED KEY VALUE. RECORD IN THE
                                           CURRENT INDEX.

10 (GET LESS THAN) FETCHES THE FIRST     SEEKGE+MOVEPREVIOUS
                  RECORD WHOSE FIELD     + RETRIEVE FETCH THE
                  VALUE IS LESS THAN     FIRST MATCHING
                  THESPECIFIED KEY       RECORD IN THE CURRENT
                  VALUE.                 INDEX.

11 (GET LESS THAN  FETCHES THE FIRST      SEEKGT+ MOVEPREVIOUS
OR EQUAL)          RECORD WHOSE FIELD     +RETRIEVE FETCH THE
                   VALUE IS LESS THAN OR  FIRST MATCHING
                   EQUALS THE SPECIFIED   RECORD IN THE
                                          CURRENT INDEX.
                   KEY VALUE.

12 (GET LOWEST)    FETCHES THE FIRST        MOVEFIRST+RETRIEVE
                   RECORD.                  STATEMENTS.

13 (GET HIGHEST)   FETCHES THE LAST RECORD.  MOVELAST+RETRIEVE
                                             STATEMENTS.

14 (CREATE)    CREATES A BTRIEVE FILE.  OPEN STATEMENT. IN
                                      BASIC THE DATABASE FILES
                                      (AND TABLES) ARE CREATED
                                      BY THE  OPEN STATEMENT,
                                      IF THEY DON'T ALREADY
                                      EXIST.

15 (STAT)      RETURNS NUMBER OF        LOF RETURNS THE NUMBER
               RECORDS IN THE FILE,     OF RECORDS IN THE
               PLUS OTHER FILE          SPECIFIED TABLE.
               CHARACTERISTICS.

16 (EXTEND)     ALLOWS A FILE TO BE      NO EQUIVALENT.
               CONTINUOUS ACROSS TWO
               DRIVES.

17 (SET DIRECTORY)    CHANGE CURRENT           CHDRIVE, CHDIR.
                     DIRECTORY.

18 (GET DIRECTORY)    RETURNS CURRENT          CURDIR$.
                     DIRECTORY.

19 (BEGIN TRANSACTION)  MARKS START OF A BLOCK     BEGINTRANS
                       OF RELATED OPERATIONS.     STATEMENT.

20 (END TRANSACTION)    MARKS END OF A BLOCK OF    COMMITTRANS
                       RELATED OPERATIONS.        STATEMENT.

21 (ABORT TRANSACTION)  RESTORES FILE TO ITS     ROLLBACKALL.
                       CONDITION PRIOR TO THE
                       BEGINNING OF THE
                       TRANSACTION.

22 (GET POSITION) RETURNS POSITION OF    ISAM HAS NO
                  THE CURRENT RECORD.    EQUIVALENT BECAUSE
                                         THERE ARE NO RECORD
                                         NUMBERS IN ISAM.

23 (GET DIRECT) FETCHES THE RECORD WITH  ISAM HAS NO
                THE SPECIFIED RECORD     EQUIVALENT BECAUSE
                NUMBER.                  THERE ARE NO RECORD
                                         NUMBERS IN ISAM.

24 (STEP DIRECT) FETCHES THE RECORD IN    ISAM HAS NO
                 THE NEXT PHYSICAL        EQUIVALENT BECAUSE
                 LOCATION, REGARDLESS OF  PHYSICAL LOCATION IS
                 THE INDEX.               NOT A MEANINGFUL
                                          MAPPING IN ISAM.

25 (STOP)   UNLOADS BTRIEVE RECORD  IN COMPILED BASIC PROGRAMS
            MANAGER.                USE OF AN EXTERNAL
                                    DATABASE MANAGER IS
                                    OPTIONAL. AFTER USING ISAM
                                    WITHIN THE QBX
                                    ENVIRONMENT, YOU SHOULD
                                    UNLOAD THE TSR WITH ITS /D
                                    OPTION.

26 (VERSION)   RETURNS THE BTRIEVE  NO EQUIVALENT.
               VERSION NUMBER.

27-30   +100

RUN-TIME ERROR MESSAGES AND CODES

THE FOLLOWING BASIC ERRORS MAY OCCUR AS A RESULT OF ISAM STATEMENTS:

2 SYNTAX ERROR
SOME SYNTAX ERRORS ARE NOT DETECTED UNTIL RUN TIME. FOR EXAMPLE, SUPPLYING TOO FEW KEYVALUES TO A SEEK OPERAND STATEMENT.

5 ILLEGAL
MANY POSSIBLE FUNCTION CALL CAUSES.

6 OVERFLOW
CAN OCCUR WHEN AUTOMATIC COERCION IS PERFORMED BETWEEN INTEGER AND LONG DATA ENTERING THE ISAM FILE.

7 OUT OF MEMORY
/IB: OR /II SET TOO SMALL OR TOO LARGE; OR AFTER EMS HAS BEEN ALLOCATED FOR ISAM, THERE IS NOT ENOUGH LEFT FOR QBX TO USE FOR TEXT TABLES.

10 DUPLICATE DEFINITION
AN ATTEMPT WAS MADE TO EXECUTE A CREATEINDEX STATEMENT FOR AN INDEX THAT ALREADY EXISTS IN THE DATABASE.

13 TYPE MISMATCH
ELEMENTS OF THE RECORDVARIABLE ARE INCONSISTENT WITH THE TYPES OF THE COLUMNS IN THE TABLE.

16 STRING FORMULA TOO COMPLEX

52 BAD FILE MODE
ATTEMPTED AN ISAM OPERATION ON A NON-ISAM FILE.

54 BAD FILENAME OR NUMBER
THE SPECIFIED FILE NUMBER DOES NOT IDENTIFY AN ISAM TABLE OR DATABASE FILE.

55 FILE ALREADY OPEN
THE SPECIFIED TABLE OR FILE IS ALREADY OPEN; OR YOU SPECIFIED A NON-ISAM FILE IN A DELETETABLE STATEMENT.

64 BAD FILENAME
TABLE NAME OR DATABASE NAME EXCEEDS LEGAL LENGTH OR CONTAINS ILLEGAL CHARACTER.

67 TOO MANY FILES
YOU HAVE TRIED TO OPEN MORE THAN THE MAXIMUM NUMBER OF FILES. THERE ARE NO MORE FILE HANDLES AVAILABLE.

70 PERMISSION DENIED YOU ATTEMPTED TO OPEN A FILE THAT WAS LOCKED, OR ATTEMPTED TO PERFORM A FILE OPERATION ON A READ-ONLY FILE.

73 FEATURE UNAVAILABLE
USER FORGOT TO START THE ISAM TSR BEFORE STARTING PROGRAM, OR TRIED TO PERFORM A DATA-DICTIONARY OPERATION USING THE REDUCED TSR (PROISAM, RATHER THAN PROISAMD) OR .LIB CONFIGURATION.

76 PATH NOT FOUND
THE PATH WAS INVALID; FOR EXAMPLE, A NAMED DIRECTORY DID NOT EXIST.

81 INVALID NAME
TABLE OR INDEX NAME IS TOO LONG OR CONTAINS ILLEGAL CHARACTERS.

82 TABLE NOT FOUND
A TABLE WAS SPECIFIED THAT IS NOT IN THE DATABASE, FOR EXAMPLE, IN A DELETETABLE STATEMENT.

83 INDEX NOT FOUND
THE INDEX SPECIFIED BY SETINDEX WAS NOT ASSOCIATED WITH THE SPECIFIED TABLE.

84 INVALID COLUMN
THE NAME SPECIFIED FOR A COLUMN IN A CREATEINDEX STATEMENT DOES NOT EXIST.

85 NO CURRENT RECORD
OCCURS TYPICALLY FOLLOWING AN UNSUCCESSFUL SEEK OPERAND STATEMENT OR MOVE DEST TO END OF FILE OR BEGINNING OF FILE.

86 DUPLICATE VALUE FOR UNIQUE INDEX
AN ATTEMPT WAS MADE TO CREATE A UNIQUE INDEX ON A COLUMN THAT ALREADY CONTAINED DUPLICATE VALUES; OR A USER ATTEMPTED TO ENTER A DUPLICATE VALUE IN A COLUMN FOR WHICH A UNIQUE INDEX EXISTS.

87 INVALID OPERATION ON NULL INDEX
FOR EXAMPLE IT IS ILLEGAL TO EXECUTE A SEEK OPERAND STATEMENT WHILE THE NULL INDEX IS CURRENT.

88 DATABASE INCONSISTENT
THERE IS A PROBLEM IN THE DATABASE, RUN THE ISAMREPR UTILITY.

( TOP )