Undocumented Database Classes and
Other Undocumented Features

Note: This paper was written in 1998 for Visual dBASE 7.01. The documentation team at dBASE, Inc. is working on documenting all of these features for dB2K, so if you have dB2K, and an updated verson of the OLH, then this paper may not be useful at all. Ken Mayer, January 31, 2001.

The following topics are covered in this paper. You can link directly to them, or scroll through the whole paper, depending on your needs ...

The TableDef Class
The DBFIndex and DBIndex classes and createIndex(), dropIndex()
DateTime Functions
RefCount()
The File() function
The Math Class
Easter Egg
DBF7 Table Format
Summary

TableDef

In order to use the TableDef object, you must instantiate it like any other dBASE class:

   t = new TableDef()

Once you have created an instance of this object, you must then give a table name:

   t.tableName = "animals"

And finally, you must load the table definition with the load() method:

t.load()

At this time, the table definition is now loaded, and quite a variety of information is available at your fingertips. The best way to view this information is with the inspector:

   inspect( t )

The useful information is under the "Misc. Properties" section:

The version information is the version of dBASE that created the table, tableType is going to refer to, for local tables, DBASE, Paradox, etc.

One of the more useful things you can do here, however, is to examine the arrays attached to a table definition, which include index tag information, field information, and even constraints (if you are using constraints for a DBF7 table).

Fields: appears as an object in the inspector. If you click on it, you will see the 'I' button, which means you can inspect this object -- clicking on it shows:

From here, you can then inspect each field of your table -- however you cannot change the information (you are allowed to change it but the changes are not permanent). The purpose of the tableDef object is to show you this information, not change it -- this is because you are working with the table definition not the actual table itself. If you wish to change some part of the table definition, there are other places to do this (SQL ALTER TABLE command, the Table Designer ...)

Indexes: works very much like the Fields object, but allows you to inspect the settings for each of your index tags.

Now, you can look, but you can't touch, right? So what's the point? Well, it might be useful to create a routine that looped through the index tags, found all the information about them, and saved it somewhere, so that if your index tags got messed up by a power outage, you could restore them. Since the index object is really just an array, you could get this information in the following manner:

   t= new tableDef()
   t.tableName = "animals"
   t.load()
   
   for i = 1 to t.indexes.size // size of index array
       ? t.indexes[i].name
       ? t.indexes[i].descending
       ? t.indexes[i].expression
       // and so on for each property
   next

This small routine will not store the information, only display it. However, a program is included on the ICon CD with this paper that will show you how to do this (using the file object as well as the DBFIndex, discussed below).

Copying a Table Structure
It is also possible to copy a table structure in a similar fashion to what is discussed above and later in this paper. Why would you want to do this? Well, the updateSet() object in Visual dBASE works pretty well copying a table of one type to the same type (i.e., .DBF to .DBF) but sometimes has problems copying from one type to another (say, .DBF to .DB, or even to a SQL server such as MSSQL).

In order to facilitate this, you could write a program that opens the table to find information about the fields, stores the fields information in an array, and then writes a new table definition, creates the indexes, and so on ...

Sounds like a tall order, eh? Well, parts of it can get a bit complicated. Parts of a sample program that is on the ICon CD for this paper are discussed below:

Getting The Fields Information
First you must load the tableDef with a table's definition:

   // ------------------------------------------
   // First, copy the table structure:
   // Create an instance of the tableDef object:
   t = new TableDef()
   
   // -------------
   // assign table
   t.tableName := cOldTable
   // Load it ...
   t.Load()

Once the table definition is loaded, there are some considerations to be concerned with. For example, is there a primary key for the table you are copying?

   // ------------------------------------
   cPrimary = t.primaryKey  // primary key

In any case, if you want your code to be fairly generic, you would need to put the primary key field as the first one in the new table structure just to be safe (the DBF7 table format, while it uses primary keys, does not care if the primary key is the first field in the table or not ...). To do this you will need an array to store the vital information about the fields:

   /* -------------------------------------------------
      Due to the fact that a .DB table must
      have the primary key first, we are going
      to load a second array that will contain
      the field names, types, decimal and decimalLength
      properties of the fields in the table being
      copied. This will have the primary key
      (if any) as the first field, and all other
      fields will come after in order ...
      ------------------------------------------------- */
   aFields = new Array( t.fields.size, 4 )
   // Get the first field:
   if not empty( cPrimary )
      aFields[ 1, 1 ] = cPrimary
      // unfortunately, most of the standard
      // array methods don't exist here, so can't just
      // use scan():
      for i = 1 to t.fields.size
          if t.fields[ i ].fieldName == cPrimary
             nPrimary = i
             exit
          endif
      next
   else
      // no primary key:
      nPrimary = 1
      aFields[ 1, 1 ] = t.fields[ nPrimary ].fieldName
   endif
   aFields[ 1, 2 ] = t.fields[ nPrimary ].type
   aFields[ 1, 3 ] = t.fields[ nPrimary ].length
   aFields[ 1, 4 ] = t.fields[ nPrimary ].decimalLength

   // ---------------------------------------------------
   // load the rest of the fields skipping the primary 
   // (or first field) which we already have ...
   nFields = 0
   for i = 2 to ( t.fields.size )
       nFields++
       // Skip primary key OR if no primary, skip first field
       if ( empty( cPrimary ) and nFields == 1 );
          OR t.fields[ nFields ].fieldName == cPrimary
          nFields++
       endif
       aFields[ i, 1 ] = t.fields[ nFields ].fieldName
       aFields[ i, 2 ] = t.fields[ nFields ].type
       aFields[ i, 3 ] = t.fields[ nFields ].length
       aFields[ i, 4 ] = t.fields[ nFields ].decimalLength
   next

The next step would be to build a "CREATE TABLE" command, looping through the fields and assigning the appropriate properties based on the fieldType. This is very lengthy, and will not be shown in the paper, but is in some sample code on the ICon CD.

The last step, also a bit lengthy, deals with setting indexes by looping through the index array, and so on. This type of code is discussed below, and is also in the final code for the ICon CD.

Included is a sample program called COPYTABL.PRG -- this copies the table structure, the indexes, but not the data. You could add a loop and loop through the data if you wanted, but this code is not included in this version of the program.

A Custom Query That Uses TableDef
Using the TableDef object, it is possible to do a lot of different things. One suggestion I received when looking for ideas for this paper was to create a custom query that allowed the developer or user to select an index for the current table.

The query (on the conference CD, see below) needs to act like a standard query object in Visual dBASE, but we are adding a new method and a new property to the rowset, with a child object (the tableDef) in the query itself. All of this is done by overriding the EXECUTE method of the query:

   Procedure execute
     super::execute()

     // the method called getTableName is one
     // written by Romain Strieff, and used with his
     // permission. It is not shown here, but is
     // in the sample code on the CD:
     cName = this.GetTableName()
     if empty( cName )
        msgbox( "Error -- invalid table name" )
        return
     endif

     // setup a tableDef:
     this.t = new TableDef()
     this.t.tableName   := cName
     this.t.database    := this.database
     this.t.load()
     this.rowset.indexFlag = iif( this.t.indexes.size > 0, true, false )

     // Assign method to rowset:
     this.rowset.selIndex = class::SelIndex

The code shown above does a check for a table name in the SQL property of the query -- this is vital, because we are adding a tableDef object as a child object of the query, and the tableDef needs to have a tableName.

Once we have a tableName, we create an instance of a tableDef as a child object of the query, and assign the tableName and the dataBase properties, and load the table. Then we check to see if there are any indexes -- if there are, we set a flag (this.rowset.indexFlag) to true. Finally, we assign a special method to the rowset (SelIndex).

The actual method of the rowset

   Procedure SelIndex( cIndexName )

Note that we can pass a parameter to the method -- this is important, because the developer may simply want to call the method with the name of the index that they wish to use.

     if this.indexFlag

We only execute any code in this method if the indexFlag is set to true ...

        // if no indexName passed, we must
        // assume the user wants to _select_ an
        // index tag from a list:
        if empty( cIndexName )
           fQry = new IndexQryForm()
           fQry.Indexes = new Array()
           // Load array used in form for listbox:
           fQry.Indexes.add( "<No Index>" )
           for i = 1 to this.t.indexes.size
               fQry.Indexes.Add( this.t.indexes[i].indexName )
           next
           fQry.indexes.sort()

The code above loads an array of index names by looping through the indexes array of the tableDef. This array gets used in the following code and in the form that is called to display all the index names in the table.

           fQry.indexListBox.dataSource := "array fQry.indexes"
           fQry.indexListBox.curSel     := 1
           // Open it!
           fQry.ReadModal()

           // set it!
           if fQry.indexListBox.value == "<No Index>"
              this.rowset.indexName := ""
           else
              this.rowset.indexName := fQry.indexListBox.value
           endif
           this.rowset.first()

The above code sets the form up, and opens it as a dialog (readModal), and when the user clicks the button it checks to see which item in the lisbox on the form was chosen, and sets the rowset object's indexName property to the appropriate indexname, and moves the rowset pointer to the top of the table (you may want to comment this code out if you do not wish to move the pointer ...).

        else
           // let's try to find it ...
           this.rowset.indexName := "" // empty it out
           for i = 1 to this.parent.t.indexes.size
               if upper(cIndexName) == upper( this.t.indexes[i].indexName )
                  this.rowset.indexName := cIndexName
                  this.rowset.first()
                  exit
               endif
           next

If the user passed a name in the call to the method, we need to try to find the index name. Because the tableDef's indexes array is not a standard dBASE array, the seek() method does not exist, so we have to put a little more code into the search. If we find the indexname, we need to set the indexName property to that name, and again we move the rowset pointer to the first row in the index.

           // if the indexName is still empty:
           if empty( this.rowset.indexName )
              msgbox( "Could not find indexName: "+cIndexName+"." )
           endif
             
        endif // empty( cIndexName )
     else
        // Let user know somewhere there's a mistake ... 
        msgbox( "There are no indexes for this table" )
     endif

In this last bit of code we handle error messages to the user.

Included is a copy of this custom control, called INDEXQRY.CC. Please read the header for examples of it's use ...

Menu/Top

DBFIndex, Index and Database.createIndex(), Database.dropIndex()

NOTE: the xBASE DML command PACK, and the OODML packTable() method will recreate an index from scratch, but if the header of the .MDX which describes the .MDX keys is damaged or the entire .MDX file has been zeroed out, then these will not solve the problem. That is why this routine was created ...

First, examine an instance of the DBFIndex object:

   d = new DBFIndex()
   inspect(d)

Notice that you have the same properties that are shown when displaying the index array of the tableDef object for a .DBF. The only required parts to create an index with a DBFIndex object are the indexName, and the expression. Once you have those, you can easily create a new index for a table using the database class method: createIndex().

If you are not using an active database (and with local tables you may not be), there is always one database that is available:

   _app.databases[1]

To create an index using this:

   d = new DBFIndex()
   d.indexName = "indextagname"
   d.expression = "indexexpression"
   // other properties
   _app.databases[1].createIndex( "tablename", d )

Note in the last statement shown above the "tablename" part of the statement -- this is where you insert the name of the table on which you are creating the index.

---

The examples shown here are for the DBFIndex class, but there is also an Index class that can be called for any table type (the Index class is the base class for the DBFIndex class):

      i = new Index()
      inspect(i)
   

Will show you the properties of the index class -- rather than the 'expression' property shown for the DBFIndex, you must give a 'fields' property.

---

There's one more aspect to all this that is useful to know about -- the dropIndex() method of the database class. This method is used to remove an index (index tag for a .DBF table). This is pretty easy to use, and if you needed to create a temporary index tag, this might be a way to do it (since the database classes do not allow you to use non-production .MDX files or .NDX files with your .DBFs). You could use the createIndex() method shown above to create your temporary index tag, and then use the dropIndex() method to remove it:

   _app.databases[1].dropIndex( "tablename", "indextagname" )

Menu/Top

DateTime Functions

NOTE: DateTime() and "new date()" are almost identical; dBASE will do an on-the-fly conversion from one to the other when necessary. The main reason DateTime() is undocumented is that there is so little difference ...

   dDT = DateTime()
   ? type( 'dDT')

Notice that the value that you get is "DT".

Internally, the value being stored is fractional days in scientific notation (i.e., .25 is six hours) -- the only reason that this might be important is that you can attempt to perform math using the DateTime() function, but the values appear meaningless:

   d1 = DateTime()
   // Wait a minute or so ...
   d2 = DateTime()
   ? d1 - d2
   // In the output pane ...:
   -.92245370370436E-4

The dateTime() function can be useful if you wish to compare the date and time that some event occurs to another occurance of the same event, however you should convert the values to something more useful than that shown above. This can be done by extracting the date and/or the time strings out of the returned value.

You can convert the value returned by the DateTime() function to character, using the function DTtoC() (DateTime to Character), and you can convert this back to a DateTime type by using the CtoDT() function (Character to DateTime).

If you are using the TimeStamp field in a (dBF 7 table), this is useful -- you could store the current date and time to a field that was defined as a TimeStamp type:

   queryName.rowset.fields["timestampfield"].value = DateTime()

This might be used for an audit trail ... or a variety of other situations.

NOTE: You can also use the date class to assign a value of this sort:

   queryName.rowset.fields["timestampfield"].value = new Date()

TTime()
This function displays the time (which can also be gotten with time() ) with the AM/PM indicator. This returns its own type, which can be shown by using the type() function:

   tT = TTime()
   ? type( 'tT')

This will return "T" for the value.

The actual value being used for the TTime() function is seconds.

You might want to convert to character and back using the TtoC() and CtoT() functions.

You can do math on the value returned by TTime(), which can be useful to find elapsed time values. Example:

   tBeginTime = TTime()
     // do some long process
   tEndTime = TTime()

   ? "Elapsed time = ", (tEndTime - tBeginTime), "seconds"

Menu/Top

RefCount()

RefCount( object )

This is mostly useful if you are attempting to debug an application, and for some reason you are using more memory than you think you should. If an object has more than one reference to it, releasing the reference may not actually have released the memory until all references are accounted for. Examples:

   x = new Object()
   ? refCount(x)
   // should return 1
   y = x
   ? refCount(x)
   // should now return 2
   
   f = new Form()
   ? refCount( f )
   // should return 1
   f.open()
   ? refCount( f )
   // returns 2 -- once for the open window
   f.close()
   ? refCount( f )
   // returns 1 again

Menu/Top

The File() Function

The File() function is pretty basic, and is documented in the OLH. It is used first and mostly to determine if a file exists, and returns a logical value:

     if file("myfile.txt")
        // do something
     endif

However, there is an undocumented feature -- a second parameter that can be used to find a file from within a deployed executable. Why in the world would you need to do this? Well, you may have deployed the executable with some special tables that you do not want the user to be able to modify outside of your program. A trick would be to deploy the table inside the executable, and then extract it -- use it for your system's code, and then erase it ... the user would never really see the table! You can also deploy images inside the .exe and extract them as they are needed, and so on.

The syntax to find a file from the executable is:

   file( "myfile.ext", true )  // this still returns a logical value

You could even create a handy function that you could call to extract any file from the executable (this is based on some code in the sample application MUGS that ships with Visual dBASE):

   function copyOutOfEXE( myfile )
      local lCopied
      lCopied = false
      // if file is not on disk, but is in the executable
      if (( not file( myfile ) and file( myfile, true ) )
         copy file (myFile) to (myfile) // extract from .exe
         lCopied := true
      endif
   return ( lCopied )

Menu/Top

Easter Egg

Menu/Top

The Math Class

There are a set of constants which are simply properties of the math class (shown in the inspector below) -- Note that in order to show the number of decimals that you see in the inspector image below you must change your default decimal setting to 16 (set decimals to 16):

You could use these constants by doing something along these lines:

   m=new Math()
   ? m.pi
   // Note that you get 3.14
   set decimals to 16
   ? m.pi
   // Note you get 3.1415926535897933

Other than the use of the constants, the only real benefit to this class for a developer might be to create a subclassed Math object that included your own methods into a single place for completeness' sake.

The following table is a simple description of the constants that are properties of the math object. These are all with the number of decimal places set to 16 (SET DECIMALS TO 16 at the command window).

Menu/Top

DBF7 Table Format

With Visual dBASE 7, and the Borland Database Engine (version 4.5), there are some enhancements to the DBF table format. It should be noticed, however, that this format is not backward-compatible with earlier versions of dBASE, and software that reads the .DBF table format may not be able to read the new table format unless it does so via the BDE (32-bit BDE, i.e., Visual dBASE 5.x won't work).

There are several new field types that have been created: Long, Double, AutoIncrement, and TimeStamp.

In addition to these new field types you can have custom Properties associated with individual fields; you can set constraints on data entry in the table itself, so that your forms do not need to have validation set; you can declare default values for fields; and through the BDE you can set relational integrity options.

Custom Properties
Custom properties are fun, because they allow you to define just about anything you might want to for your fields. If you want, you can set custom properties that are the equivalent of properties and events of UI controls (colorNormal, colorHighlight, picture ...); database classes (lookupSQL); you can set your own properties that you may want to use for your own purposes, and more.

There is no way, however, to add these properties programmatically -- you must use the table designer to do so.

Constraints
Constraints placed on fields can be useful, because it means that you can define specific values in the table itself, and when you design your forms, you do not have to set your rules in each form. In addition, if you need to change your rules, you only have to change them in the table design, rather than going through your forms (or if properly designed, your custom controls) and updating those. A spinbox will inherit, for example, the max/min properties (these will be assigned to the rangeMax/rangeMin properties) ...

Constraints are added in the same way that custom properties are -- this cannot be done programmatically.

You have four constraints that can be used: default -- which allows you to set a default value for a field; minimum and maximum values; and a required flag, useful to determine if a field must contain a value (very important if you wish to use that field for your primary index!) ...

Referential Integrity
This is done by selecting the Tables page of the navigator, and either selecting a database alias from the "Look In" box, or a directory on the hard drive that contains the tables you wish to establish your RI rules for.

Using menu options, you can then start defining your RI rules. (This is covered pretty well in the Developer's Guide, pp. 14-26 through 14-28.)

AutoIncrement
The value of the AutoIncrement field type is stored in the header of the table. Using low-level I/O a developer might be able to reset this value.

Included is a sample custom control called DBF7FILE.CC (and it uses the file BFILE.CC) -- among the methods included is one that will reset the value of an AutoIncrement field (in a DBF7 table only) to a value that the developer needs it to be. The code was started by Jay Parsons, tinkered with my the author of this paper, and completed by Bowen Moursund.

Menu/Top

Menu/Top