Doug owns Access Microsystems, a software development house specializing in C/C++ software development. He is also the author of BTFILER and BTVIEWER Btrieve file utilities. Doug can be contacted at 404 Midstreams Road, Brick, NJ 08724, or on CompuServe at 74040,607.
Multiple ways of storing information have led us to a virtual DataTower of Babel. Even something as "simple" as a string, for example, can be represented by familiar programming languages in at least three ways: "normal" strings, where a 20-character string is made up of just 20 characters (what you see is what you get); Pascal-style Lstrings, where a 20-character string takes 21 characters, with the first byte being a length byte (thus limiting a normal Pascal string to 255 bytes); and C-style Zstrings, where a 20-character string also takes up 21 characters, with the extra character used for a Null terminator signaling the end of the string.
One solution to the problem of getting to your data is the use of a higher-level interface that understands a variety of formats. Novell's NetWare SQL (and XQL, its DOS cousin, also from Novell) provide just such a solution.
NetWare SQL provides a convenient way to access information within a database without regard to the physical characteristics of the data files. NetWare SQL data files can contain data with field types ranging from Intel-format integers to Decimal fields (also known as BCD, binary coded decimal). Table 1 lists the NetWare SQL-supported data types. However, while NetWare SQL provides this ability, the memory required to use it (possibly more than 200K) can prohibit its use.
Thankfully, there's another way. Since Btrieve is the file manager engine NetWare SQL uses, and since NetWare SQL files are simply Btrieve files that have available additional descriptions of fields, you can duplicate the functionality of NetWare SQL you want (that is, have access to the data without regard to the physical layout of the file) with considerably less overhead. In effect, this gives you access to NetWare SQL files without requiring your users to have NetWare SQL.
What is a NetWare SQL Database? Btrieve files, unlike many other types of database files, don't contain field information within the files. Btrieve files do contain information on record length, key types and positions, and details about keys, such as whether the key is modifiable and whether duplicate values are allowed. Keys can be any of the types listed in Table 1.
To provide access to the Btrieve files that make up its database, NetWare SQL uses external files to describe how data is stored within the files. Unsurprisingly, these data dictionary files are Btrieve files and have .DDF extensions; see Table 2.
FILE.DDF, Table 2(a), contains information on each file in the database, as well as each of the .DDF files. The File ID is an integer assigned by SQL to uniquely identify the file, and link it to fields and indices stored in the other data dictionary files. The File Name is used within SQL queries to access the file. It's the "human readable" name of the file, like "PO Header" or "Item Master." The File Location is used to store the file name the operating system should use. Often, this field contains only the file name and extension. The final file name is then the result of the search of a path-like environment variable. The only current documented use of the File Flags field is to use bit 4 signal if a file is a dictionary file (one of the .DDF files) or a user-defined file.
FIELD.DDF, Table 2(b), contains information on each field in each file in the database, as well as each field in the .DDF files. The Field ID, assigned by SQL, identifies the field and links this file back to the FILE.DDF file. The Field Name is used to identify the field within queries. The Data Type describes the format of the data in this field. The Offset, Field Size, and Decimals fields locate and help format the data in the field. The Flags field uses bit 0 as a case flag for strings.
INDEX.DDF, Table 2(c) contains information on each index of each file in the database. The File ID and Field ID fields correspond to the fields in FILE.DDF and FIELD.DDF. The Index Number field identifies the index number. The Key Segment field is a further qualifier. NetWare SQL supports multisegment indices and this segment number is used to give the order of segments in a multisegment key. Prior to NetWare SQL 3.0, the limit on index segments was 24. NetWare SQL 3.0 allows up to 119 key segments. The Key Flags field is a bit-mapped integer used to describe the index more fully.
While there are other DDF files (VIEW.DDF, ATTRIB.DDF, and so on), I haven't covered them here since most applications expect these three.
A convenient way to access NetWare SQL files is with a C++ class. With some minor changes (a different data dictionary reading routine, a different raw file handling class, and the like) the techniques presented here are applicable to any file format that handles a variety of data types.
Listings One and Two show the class definition for the ddfFIle class and supporting classes. Listing One (page 92) is the .H file, while Listing Two (page 92) .CPP source. Class Value is used to contain the values of each field. Value has private data members strValue, a character pointer, dValue and iValue (double and long types, respectively). Note that even if a value can be represented by a C/C++ data type of smaller size (a float or an int), Value always stores values using the largest comparable C/C++ type. The len and dec private data elements keep track of length and number of decimals used for string formatting.
Constructors initialize the value with a character pointer, double and long, as well as a constructor that requires no initial value. Operator= and casting operators are overloaded to provide a convenient syntax to set and get values.
Next some structures that generally mirror the layout of the DDF files are described. One difference is the inclusion of a Value member in the FIELDDDF structure. An additional structure (BOOKMARK) allows you to set and return to a "bookmark" in the file. Btrieve allows you to record the physical position in a file (using a four-byte value), as well as keep track of the key number. With this, you can look at a record, set a "bookmark," look around at other records in the file, and then return to the records you first examined.
ddfFile has private data elements to keep track of the physical file name and the file name used by NetWare SQL to refer to the file. A pointer to a bfile class is what is used to do the actual manipulations of the raw Btrieve file (also see my article "Wrap It Up" in Windows Tech Journal, May 1992). An array of 256 pointers to the FIELDDDF structure allows you to get at all fields in a NetWare SQL file (field limit is 255) as well as an additional field used to point to somewhere harmless if an invalid field name is passed.
The constructor takes a single mandatory argument, the logical file name, and a single optional argument, an owner name (used if security is enabled on the underlying Btrieve file). The constructor gets the actual DOS file name from the passed logical file name by reading through FILE.DDF, creates the bfile object (which handles opening the file and allocating buffers, and so on), and loads the field names and descriptions into the fields[] array of FIELDDDF structures.
The destructor performs memory clean up, and as the bfile object goes out of scope, its destructor is called. It cleans up memory used for buffers and closes the actual Btrieve file.
Several member functions (set_key_num(), get_status(), and to a lesser extent, get_rec(), put_rec(), insert() and del_rec()) act as simple wrappers around the bfile class. For getting and saving records, one additional step must be taken. ddfFile supplies two member functions to convert from the raw data into fields and back: dataToFields() and fieldsToData(), respectively.
To provide access to fields in the file, two getValue() member functions are provided. One accepts as an argument a field number (to be used if you know the relative field number in the file) and the other accepts the field name as an argument. Using the field name to access the value removes dependence on the physical details of the file's structure. Each function within ddfFile that gets information from the file uses the dataToFields() member function and each function that saves information to the file uses fieldsToData().
These functions use information from the FIELD.DDF file and pass that information along to a btrvConvert() function to transform the raw data to displayable values and back again.
ddfFile uses several conversion routines, only one of which (NUMERIC.CPP) is presented here (due to space constraints). Other conversion routines (BTRVCNVT.CPP, BCD.CPP, DBLE_DB.C, and so on) are provided electronically; see "Availability," page 3. Note that each field type allowed by NetWare SQL has a #defined name. Within btrvConvert(), this field type is used to signal the type of conversion required. The field type plus 100 decimal, signals a conversion back from a displayable value to the format that exists in the file.
String Conversions: The Easy Stuff First
When it comes to data conversions, strings are relatively easy. The only tricky part is ensuring that our C/C++ centric mindset doesn't get you into trouble. If a straight String type is defined, be sure that when you do conversions to that type you don't use a null terminator. When the destination of the string is a field in a data file, this null will become the first character in the next field. For this reason, the string is first formatted into a C-style string in a temporary buffer that actually takes up dlen bytes plus 1 for the null terminator, then memcpy() is used rather than strcpy() to copy only the bytes before the null terminator. For Pascal-style LSTRINGS, read the length byte at the head of the string, rather than what NetWare SQL tells you the destination length is--unless the length byte tells you that the string is longer than the NetWare SQL field length. C-style ZSTRINGS are, of course, not a problem for C++ class.
ASCII Representations of Numbers
Two of the number types allowed by NetWare SQL use standard ASCII to represent numbers. The most common of the two types is the Numeric type in which numbers are represented by their ASCII code, and the decimal is implied. Thus, a seven-character numeric field containing the value 123.45 would appear in the actual data as 0012345. One twist to this encoding is handling negative values. The solution is to embed the sign into the last digit. Table 3 lists the digits and the associated character to use in the last digit if the value is negative. The values supplied for positive values are hardly ever used, but the conversion routine presented does support them. See NUMERIC.CPP, Listing Three, page 94.
Another approach to handling negative values is the NUMERICST type, added with NetWare SQL 3.0. This field is made up of the absolute value in the first n-1 characters and the sign (+ or -) as the last character. Clarity for browsing through the raw data is gained, at the loss of a single character per number represented. 123.45 would be represented in a seven-character field as 012345+.
Binary Representations of Numbers
NetWare SQL supports seven binary representations of numbers. Combining essentially identical types, you're still left with four distinct types of representations. Two of the types are various sizes of native C/C++ types. INT values can be either char (one byte wide), int (two bytes wide), or long (four bytes wide). Add the unsigned modifier to the types discussed and the UNSIGNED BINARY type is handled. An additional binary integer type is AUTOINC, which allows you to insert a record with the AUTOINC field set to binary 0. After the insert the field will contain a value one higher than the previous highest value for the field.
The NetWare DECIMAL and MONEY types are very similar. MONEY fields are DECIMAL fields with the number of decimals fixed at two. In addition, MONEY-type fields traditionally are displayed beginning with a dollar sign. DECIMAL numbers are equivalent to standard ANSI-74 Cobol's COMP-3 type. Numbers are represented by a nibble (half a byte, or four bits). The trailing nibble contains the sign, with either an F or C for positive numbers, or a D for negative numbers. For instance, the number 123.45 set up to display seven digits would be encoded as the hex digits 0012345F, taking up four bytes. bcdtof() (available electronically) provides for details on the conversion.
Both types of floating-point data contain a sign bit and some number of bits for the exponent and the mantissa. The secret, of course, is deciding where each type of information is stored. The FLOAT type can be a C/C++ float type (four bytes wide) or double (eight bytes). These values can simply be reformatted using standard C conversion routines (atof(), sprintf(), and the like).
BFLOAT values are Basic floating-point values. Before IEEE floating point took over the world, Microsoft came up with its own way of storing floating-point numbers. BFLOAT fields can be either four or eight bytes wide. REALCNVT.H, DBLE_BD.C, and DBLE_BS.C (available electronically) provide details on the conversion. Keep in mind, however, that some precision could be lost in the conversion between eight-byte wide FLOAT and BFLOATs, since the number of digits used for the mantissa and exponent vary. Another complication for the conversion of any type of floating-point value is that the number of decimals to display is not stored in the decimals field of the FIELD.DDF file. Formatting information on floating-point values traditionally is derived from the ATTRIB.DDF file, a file not discussed in detail here.
Times and Dates NetWare SQL, seemingly like every other file-handling system ever invented, has its own internal representation for dates and times. TIME fields are stored with one byte each for 100ths of a second, seconds, minutes, and hours. DATE fields are represented by a two-byte integer year and single-byte integer month and day. Dates are formatted as MM/DD/YY, or if the destination length is sufficient, MM/DD/YYYY. (Remember, the year 2000, or the year 00 with only two digits, is right around the corner!)
Putting It All Together
Listing Four (page 96) presents an example main() that accesses two files identical in content but different in format. (The data files and the DDF files are also available electronically). The function showData() is called twice, first to show data in TEST 1, then showing data in TEST 2. See Figure 1. The output from this sample program (see Figure 2) is identical for both files, even though the files are different in structure, and in length.
Note that the example does expect you to have some "magic" knowledge of the key structure of the file. It wouldn't be a big step from here to have the ddfFile class read INDEX.DDF to get information on the key fields and allow the user of the class to set the index simply by passing by name the field or fields in the index.
By isolating the details of the data from the substance of the data, applications can continue to work even if additional fields are added or existing fields are moved around. Using ddfFile, you can get one step closer to the ideal of a program acting as the "engine" fueled by, but independent from, the data.
Figure 1: Sample File Layouts Figure 2: Sample Output Table 1: NetWare SQL field types. * Treated like one character string in ddfFile class. ** Not supported by ddfFile class. *** New with NetWare SQL 3.0 and Btrieve 6.0, supported by ddfFile class. =========================================================================== Data Type Keyword Code Description =========================================================================== INT 1 1, 2 or 4 byte integer FLOAT 2 IEEE floating point DATE 3 4 byte encoded date TIME 4 4 byte encoded time DECIMAL 5 Like Cobol's COMP-3 MONEY 6 DECIMAL with 2 decimal places LOGICAL 7 True/False* NUMERIC 8 ASCII number string, implied decimals BFLOAT 9 BASIC floating point LSTRING 10 Pascal-style strings ZSTRING 11 C-style strings NOTE 12 Variable length** LVAR 13 Variable length** AUTOINC 15 2 or 4 byte integer, gets next value BIT 16 Logical fields NUMERICST 17 NUMERIC with trailing sign*** =========================================================================== Table 2: DDF file layouts: (a) FILE.DDF; (b) FIELD.DDF; (c) INDEX.DDF. =========================================================================== Field Name Type Size Description =========================================================================== (a) File ID INTEGER 2 ID generated by NetWare SQL File Name STRING 20 Logical Name File Location STRING 64 Physical (DOS) file name Flags INTEGER 1 if bit 4=1, this is a dictionary, file, else this is a user-defined file (b) Field ID INTEGER 2 ID generated by NetWare SQL File ID INTEGER 2 Links to File ID in FILE.DDF Field Name STRING 20 Logical Field Name Btrieve Data Type INTEGER 1 Value from Table 1 Offset INTEGER 2 Offset in file Size INTEGER 2 Size of data in file Decimals INTEGER 1 Decimal places for DECIMAL, NUMERIC and MONEY types Flags INTEGER 2 Bit 0 is case flag for STRINGs (c) File ID INTEGER 2 Links to File ID in FILE.DDF Field ID INTEGER 2 Links to Field ID in FIELD.DDF Key Number INTEGER 2 Key Number, 0 through 23, or through 118 in NetWare SQL version 3.0 Key Segment INTEGER 2 Segment number for multi-segment keys Flags INTEGER 2 Index flag attributes =========================================================================== Table 3: Sign bytes for NUMERIC types. ================================== Digit Positive Negative ================================== 1 A J 2 B K 3 C L 4 D M 5 E N 6 F O 7 G P 8 H Q 9 I R 0 { } ================================== [LISTING ONE] #ifndef DDFFILE_H #define DDFFILE_H #include "bfile.h" class Value { char *strValue; double dValue; long iValue; int len; int dec; public: Value(int l=0,int dec=0); Value(char *s,int l=10,int dec=0); Value(double t,int l=10,int dec=0); Value(long i,int l=10); Value() { if ( strValue!=0 ) { my_free(strValue,__FILE__,__LINE__); } } void setVal(char *s); void setVal(double t); void setVal(long i); void operator=(long l) { setVal(l); } void operator=(double d) { setVal(d); } void operator=(char *s) { setVal(s); } void setLenDec(int l,int d); void clear(); operator char *() { return strValue; } operator long() { return iValue; } operator double() { return dValue; } int getLen() { return len; } int getDec() { return dec; } }; struct FILEDDF { int fileID; char fileName[20]; char fileLoc[64]; int flags; }; struct FIELDDDF { int fieldID; int fileID; char fieldName[20]; char btDataType; int offset; int fieldSize; char decimals; int flags; class Value value; } ; struct INDEXDDF { int fileID; int fieldID; int keyNumber; int segNumber; int flags; }; // this is what is needed to keep track of Btrieve currency. struct BOOKMARK { char pos[4]; int key; }; class ddfFile { char physicalName[22]; char logicalName[70]; bfile *theFile; struct FIELDDDF *fields[256]; struct FILEDDF fileDDF; int numFields; int fieldsToData(); int dataToFields(); public: ddfFile(char *lname,char *owner=0); ddfFile(); void setValue(char *t,int fnum) { fields[fnum]->value.setVal(t); } void setValue(long l,int fnum) { fields[fnum]->value.setVal(l); } void setValue(double d,int fnum) { fields[fnum]->value.setVal(d); } void nullOut(); int getFieldID(char *); char *getFieldName(int); char *getValue(char *); char *getValue(int fnum); void toData(int fnum,void *t); int get_status() { if ( theFile==0 ) { return 9999; } else { return theFile->get_status(); } } int unlock() { return theFile->unlock(); } void set_key_num(int key_num) { theFile->set_key_num(key_num); } // overload operator++ int operator++(int ) { char temp[255]; return(get_rec(temp,B_GET_NEXT)); } // overload operator-- int operator--(int ) { char temp[255]; return(get_rec(temp,B_GET_PREV)); } // Gets a record. Uses key 0 unless you have set key number. int get_rec(char far *keystr,int op=B_GET_EQ); // SEE BELOW... int put_rec(char far *keystr=0,unsigned tlen=0,int just_update=0); // Insert. made seperate functions to force insert. int insert(char far *keystr=0,unsigned tlen=0); // self explanatory...except that if keystr==0, positioning is not done int del_rec(char far *keystr=0) { return(theFile->del_rec(keystr)); }; int getBookmark(struct BOOKMARK *theMark); int gotoBookmark(struct BOOKMARK *theMark); }; #endif [LISTING TWO] #include "stdio.h" #include "stdlib.h" #include "ddffile.h" int loadFields(int currentFileID,struct FIELDDDF *fields[]); char *btrvConvert(int type,char *src,char *dst,int slen,int sdec, int dlen,int ddec); Value::Value(int l,int d) { strValue=0; dValue=0.0; iValue=0L; len=l; dec=d; } Value::Value(char *s,int l,int d) { strValue=my_malloc(l+2,__FILE__,__LINE__); strcpy(strValue,s); dValue=(atof(s)); iValue=(atol(s)); len=l; dec=d; } Value::Value(double t,int l,int d) { strValue=my_malloc(l+2,__FILE__,__LINE__); sprintf(strValue,"%*.*f",l,dec,t); dValue=t; iValue=(long)t; len=l; dec=d; } Value::Value(long i,int l) { strValue=my_malloc(l+2,__FILE__,__LINE__); sprintf(strValue,"%*ld",l,i); dValue=(double)i; iValue=i; len=l; dec=0; } void Value::setLenDec(int l,int d) { if ( strValue!=0 ) { my_free(strValue,__FILE__,__LINE__); strValue=0; } strValue=my_malloc(l+2,__FILE__,__LINE__); len=l; dec=d; } void Value::clear() { if ( strValue!=0 ) { sprintf(strValue,"%*s",len," "); } dValue=0.0; iValue=0; } void Value::setVal(char *s) { if ( strValue==0 ) { strValue=my_malloc(len+2,__FILE__,__LINE__); } strcpy(strValue,s); dValue=(atof(s)); iValue=(atol(s)); } void Value::setVal(double t) { if ( strValue==0 ) { strValue=my_malloc(len+2,__FILE__,__LINE__); } sprintf(strValue,"%*.*f",len,dec,t); dValue=t; iValue=(long)t; } void Value::setVal(long i) { if ( strValue==0 ) { strValue=my_malloc(len+2,__FILE__,__LINE__); } sprintf(strValue,"%*ld",len,i); dValue=(double)i; iValue=i; } ddfFile::ddfFile(char *lname,char *owner) { strcpy(physicalName,pNameFromLName(lname,fileDDF)); strcpy(logicalName,lname); theFile=new bfile(physicalName,0,owner); if ( theFile !=0 && theFile->get_status()==BERR_NONE ) { numFields=loadFields(fileDDF.fileID,fields); } } ddfFile::ddfFile() { int loop; delete theFile; for ( loop=0 ; fields[loop]!=0 ; loop++ ) { delete fields[loop]; } } int ddfFile::getFieldID(char *name) { int loop; for ( loop=0 ; fields[loop]!=0 && name!=0 ; loop++ ) { if ( !(memicmp(name,fields[loop]->fieldName,(strlen(name)))) ) { return(loop); } } return(255); } char *ddfFile::getFieldName(int i) { if ( ifieldName); } return(fields[255]->fieldName); } char *ddfFile::getValue(char *fname) { return((char *)fields[getFieldID(fname)]->value); } char *ddfFile::getValue(int fnum) { return((char *)fields[fnum]->value); } int ddfFile::get_rec(char far *keystr,int op) { theFile->get_rec(keystr,op); dataToFields(); return(theFile->get_status()); } int ddfFile::put_rec(char far *keystr,unsigned tlen,int just_update) { fieldsToData(); return(theFile->put_rec(keystr,tlen,just_update)); } int ddfFile::insert(char far *keystr,unsigned tlen) { fieldsToData(); return(theFile->insert(keystr,tlen)); } int ddfFile::fieldsToData() { int loop; for ( loop=0 ; loop btDataType+100,(char *)fields[loop]->value, theFile->get_data()+fields[loop]->offset, fields[loop]->value.getLen(),fields[loop]->value.getDec(), fields[loop]->fieldSize,fields[loop]->decimals); } return(loop); } int ddfFile::dataToFields() { int loop; char temp[255]; for ( loop=0 ; loop btDataType, theFile->get_data()+fields[loop]->offset,temp, fields[loop]->fieldSize,fields[loop]->decimals, fields[loop]->value.getLen(),fields[loop]->value.getDec()); fields[loop]->value.setVal(temp); } return(loop); } void ddfFile::nullOut() { int loop; for ( loop=0 ; loop value.clear(); } return; } void ddfFile::toData(int fnum,void *t) { if ( fnum<255 ) { btrvConvert(fields[fnum]->btDataType+100,(char *)fields[fnum]->value, (char *)t, fields[fnum]->value.getLen(),fields[fnum]->value.getDec(), fields[fnum]->fieldSize,fields[fnum]->decimals); } } int ddfFile::getBookmark(struct BOOKMARK *theMark) { theMark->key=theFile->get_key_num(); theFile->get_pos(theMark->pos); return(theFile->get_status()); } int ddfFile::gotoBookmark(struct BOOKMARK *theMark) { theFile->set_key_num(theMark->key); theFile->set_pos(theMark->pos); dataToFields(); return(theFile->get_status()); } [LISTING THREE] #include "stdio.h" #include "stdlib.h" #include "string.h" #include "ctype.h" extern "C" int del_trsp(char *); char negchars[]={'J','K','L','M','N','O','P','Q','R','}',0}; char poschars[]={'A','B','C','D','E','F','G','H','I','{',0}; double numerictof(char *numstr) { int lastchar; int isneg=0; double retf=0.0; del_trsp(numstr); lastchar=numstr[(strlen(numstr))-1]; if ( !(isdigit(lastchar)) && lastchar!='.' ) { int loop; for ( loop=0 ; negchars[loop]!='\0' && !(isneg) ; loop++ ) { if ( negchars[loop]==lastchar ) { numstr[(strlen(numstr))-1]='0'+loop; isneg=1; } } for ( loop=0 ; poschars[loop]!='\0' ; loop++ ) { if ( poschars[loop]==lastchar ) { numstr[(strlen(numstr))-1]='0'+loop; break; } } retf=(atof(numstr)); if ( isneg ) { retf*=-1; } } else { retf=(atof(numstr)); } return(retf); } char *numerictostr(double numf,int len=9,int decimals=2) { static char retstr[30]; int lastcharpos; int lastchar; if ( numf>=0.0 ) { sprintf(retstr,"%0*.*f",len,decimals,numf); } else { sprintf(retstr,"%0*.*f",len,decimals,(numf*(-1))); lastcharpos=(strlen(retstr))-1; lastchar=retstr[lastcharpos]; retstr[lastcharpos]=negchars['0'-lastchar]; } return(retstr); } [LISTING FOUR] #include "stdio.h" #include "stdlib.h" #include "string.h" #include "bfile.h" extern "C" { char *my_malloc(int num,char *pfile,int line); int my_free(char *,char *pfile,int line); }; #include "ddffile.h" #include "realcnvt.h" void showData(ddfFile& file) { char temp[255]; if ( (file.get_rec(temp,B_GET_LO))==BERR_NONE ) { do { printf("\nName: %-12.12s, %-12.12s ID: %10ld Zip Code: %s", file.getValue("LAST NAME"), file.getValue("FIRST NAME"), (atol(file.getValue("ID"))), file.getValue("ZIP")); } while ( ((file)++)==BERR_NONE ); } } void main() { ddfFile file1("TEST 1"); ddfFile file2("TEST 2"); if ( file1.get_status()==BERR_NONE ) { printf("\n\nFILE 1\n\n"); showData(file1); } else { printf("\nStatus %d on FILE 1",file1.get_status()); } if ( file2.get_status()==BERR_NONE ) { printf("\n\nFILE 2\n\n"); showData(file2); } else { printf("\nStatus %d on FILE 2",file2.get_status()); } }
Copyright © Madis Kaal 2000-