Developing a Database Application:
The dBASE™ PLUS Tutorial

Ken Mayer


Phase IIa: DBFs and Indexes (Supplemental and Detailed Information)


  Back to the tutorial menu     Back to previous part of tutorial: Databases   Proceed to the next part of the tutorial: Creating Data Modules 

Goals and Objectives

The goals of phase IIa of this tutorial are:

Additional Reading

The User's Guide mentioned above comes with dBASE™ PLUS 11 as a PDF in the "docs" folder for dBASE™ PLUS. On my Windows 10 computer, this is the path:
   C:\Program Files (x86)\dBASE\Plus11\Docs
The name of the file for dBASE™ PLUS 11 is: Plus_11_Users_Guide.pdf

Useful Code

The dUFLP -- if you have not downloaded this and installed it, you should. It is a library of freeware code, with a lot of useful tools in it. Being a firm believer in "don't reinvent the wheel", the odds are good if you need something specific there is something in the dUFLP that you can use to achieve the task.

Overview of This Part of the Tutorial

Funny how thinking about something leads to a realization that you didn't expect. In these pages -- the Tutorial, my books, etc., there are some topics that are discussed briefly, that perhaps could be detailed more. There is a lot of confusion about table structures, fields, how a lot of these things work, and indexes and how they work. The difficulty with having worked with the software for many years is that it is easy to just assume "everyone knows that!" and not bother to explain, so here we are. This extra page to the tutorial is designed to fill in some gaps in knowledge that may be useful to any developer who uses DBF tables with their applications.

The DBF -- The Native Table Format for dBASE

Since dBASE started as Vulcan back in the earliest days of the software (see note below), it has always been able to work with its own native table format, the DBF. In the earliest days there were only a few types of data allowed: Character, Number, Date, Logical, and Memo. This evolved over time as the needs of the users changed.

A Bit of History -- dBASE was created by C. Wayne Ratliff as Vulcan in 1979 (named after a certain alien race from a very popular Science Fiction TV show). It was purchased by Ashton-Tate and sold as dBASE II in 1980, and we go forward from there. You can learn more history of the software from the Wikipedia page here: Wikipedia dBASE Page.

The Appendix for The dBASE Book gives a table showing some of the evolution of the table formats on page 344 (of the second volume, third edition). We won't go into all of that here.

If you are starting a new application, the likelihood is that you will be working with DBF tables to start, such as we are using for the tutorial. The focus of this part of the tutorial will be working with DBF tables, and most specifically Level 7 (the current version) of those tables.

If you are learning dBASE to work with older tables and update applications to the current version of dBASE, you might want to consider upgrading the table formats to the new table format. If for any reason you cannot do that, you will want to work with the Borland Database Engine to ensure that you do not accidentally change the table to the latest table format, something that the software automatically does in some cases. The primary reason for not upgrading the tables would be if you have other software that relies on the format (using Excel to access or create data, or an existing application that needs access to the data, although in theory you should be working with a copy of the data if you are upgrading the application, not the production version of the data ...).

If you need to work with older format tables and keep their structure limited to that format, here is what I suggest:

Limitations to older versions of table formats (anything older than level 7):

dBASE Field Types

The fields are, if you are used to thinking in Excel or other spreadsheet formats, are the columns of data. dBASE is a structured database, meaning that once you define a field as a specific type you cannot place other types of data into that field. An example would be if you defined a field as logical -- you cannot store a character value in that field. Once the type is set that is it.

In the Level 7 DBF table format we have the following field types available:

Field Type Default
Size
Maximum
Size
Index
Allowed?
Allowable Values
Character 10 254 Yes All keyboard characters
Numeric 10 digits, zero decimals 20 digits Yes Positive or negative numbers
Float 10 digits, zero decimals 20 digits Yes Positive or negative numbers (kept for compatibility with level 4 tables)
Long 4 Bytes N/A Yes Signed 32-bit integer, range approximately +/- 2 billion
Double 8 Bytes N/A Yes Signed 32-bit integer, range approximately +/- 2 billion
Autoincrement 4 Bytes N/A Yes Read-only version of a Long field type, automatically increments when a new row is added to the table
Date 8 Bytes N/A Yes Any date from AD 1 to AD 9999
Timestamp 1 Double N/A Yes Date/Time stamp, includes date format plus hours, minutes and seconds
Logical 1 Byte N/A No True/False (T/t, F/f, Y/y, N/n)
Memo 10 Bytes N/A No Usually just text, all keyboard characters, can contain binary data but a binary field is suggested ... data stored in .DBT file
Binary 10 Bytes N/A No Binary files (sound and image data, for example)
OLE 10 Bytes N/A No OLE objects from other Windows Applications (ex. an Excel spreadsheet)

This table is a simplified version of the one in Appendix 4 in The dBASE Book ...

We won't get into every field type in this part of the tutorial -- some are pretty straight-forward, such as Character fields. Some are a bit more complex, but quite useful, such as Autoincrement fields. Some we'll discuss, some we will not, just for space.

Custom Field Properties

The documentation for dBASE and the Users' Guide discusses custom field properties, but honestly I found that they can be quite problematic in a variety of ways. Typically you can do everything you need without them, and I won't get into discussing them here. These are only available with level 7 tables.

DBFs and Indexes

In dBASE III and III+ the index was kept in a separate file for each index you needed to use. If you had three ways of sorting the data, you had three separate .NDX files that were associated with that table. When Ashton-Tate introduced dBASE IV, one of the bigger changes was to add what are called "Production" indexes.

When using .NDX files, the hardest thing to deal with is keeping them synchronized with the data -- as you work with the table, adding, deleting, editing the data, the indexes were not kept-up for you. This meant your code had to include commands to reindex the tables, and so on.

With the advent of Production indexes, or index tags, dBASE freed the developer from some work. If you create an index with dBASE level 4 tables or later, by default they are stored in a .MDX ("Multiple Index") file. These can contain up to 47 index tags in one file. The true advantage is that when you modify the data, including adding or deleting records, the index tags are automatically updated for you. By default the .MDX file will have the same name as the table itself.

There will be more information on indexes later. This is a fairly large topic, and needs specific attention. The information here is just a quick overview.

Memo and Binary Fields -- The DBT File

When you add either memo or binary field types to a table in dBASE, a special file is created: .DBT -- this will have the same name as the table, but with the different file extension. This is where the data is actually stored.

Memo fields are effectively unlimited character fields. Character fields have a maximum field length of 255 characters, but memo fields are unlimited. You can store large quantities of information in a single memo. You can also format the text using some limited HTML capabilities in dBASE (the memo field doesn't recognize, for example, Cascading Style Sheets ...). In theory you can store binary data, such as images in a memo field, but with the advent of the binary field type, it is suggested that you use that instead.

Some developers have had issues with memo fields being corrupted, typically these seem to occur on a networked application, where a user was editing a memo and the computer crashed or was shut down without closing the data/record properly. There are tools that can attempt to fix a corrupted memo field, but there are no guarantees they will work properly.

I have built applications over the years where I have never had these issues. This is not to state they do not exist, but they are more rare these days. Part of that is the stability of the software, and some internal mechanics that have been added to help avoid issues.

There are ways to simulate memo fields, but we aren't going to get into that here.

To sum up so far, there are some nice features built into the DBF Level 7 table format, and if you use some of these features, these are the files you will encounter:

Renaming Tables?

One caveat: If you need to rename a table, do not just use the Windows File Explorer to do so, and do not use the RENAME command in dBASE. The problem is that if you rename the DBF but do not rename the MDX and DBT files properly, the link between them goes away. There is information stored in the header of the DBF file itself that you don't have direct access to that points to these other files. There is a SQL statement you can use in dBASE to rename a table, which will automatically update everything that needs updating:

               rename table old_table_name to new_table_name
This will ensure that dBASE knows where the index tags and memo/binary fields are. Pretty much anything else you do to rename a table will cause problems unless there are no index tags (no .MDX file), and no associated .DBT file (no memo/binary fields).

This command can be used in the Command Window, and it can be used in your code. It should be noted that the database object has a renameTable() method which will accomplish the same task.

Specific Field Types

As noted earlier, we aren't going to discuss all field types, but there are some that need some special consideration.

Null Values

By default most field types in dBASE, when you add a new record, are "null". This can actually be a problem.

Null values in fields are not, by themselves, a problem, except that when you are manipulating the data. There is a saying that we've been using in the dBASE newsgroups for many years: null+anything = null. Why is this important?

If you are concatenating fields in a table to create a string you want to output for a report, for example, with code like:

               cOutput = q.rowset.fields["FName"].value.rightTrim() + " " +;
                         q.rowset.fields["MName"].value.rightTrim() + " " +;
                         q.rowset.fields["LName"].value.rightTrim()
The problem surfaces if one of these fields is empty (for example, many people don't use a middle name or middile initial when filling out forms). The value of cOutput in this example would be null if the middle name field was empty.

Working with logical fields, you would think you would have true or false as possible values. But as it turns out, null is also valid for a logical field. There is some discussion of this below.

Numbers (including float, long and double) can be null, and doing math of any sort with null values can trip you up, getting zeroes or null values in unexpected ways.

Dates and Timestamp fields can be null. This gets weirder because an empty date or timestamp if used in an index may sort to the bottom of the sequence, which may seem odd. (If used in a complex index they often end up at the top, depending on how you do that, we'll discuss this in more detail later.)

And so on ... various field types can have unpredictable results if the values are null.

When testing for null, you have to actually use the keyword "null". Some interesting things that come up: the empty() function may return true if a field is null, which may be confusing, you can add an additional test:

               if empty( q.rowset.fields["FName"].value ) or
                  q.rowset.fields["FName"].value == null
Which can help.

Another aspect of the above is that empty() with a null value doesn't produce a true result. We can also see this if we compare some values -- all of the following should return a value of false:

               ? "" == null // is empty string equal to null?
               ? null == "" // is empty string equal to null?
               ? 0 == null // is zero equal to null?
               ? null == 0 // is zero equal to null?
               ? {} == null // is an empty date equal to null?
               ? null == {} // is an empty date equal to null?

So, how do you resolve this issue? There are, because it is dBASE, several methods of doing so. (I say "because it is dBASE" as the software is very flexible, and there are almost always multiple methods to get to the result you want.) We're going to look at a few means of dealing with these issues.

The first method of making sure your fields don't end up with null values is:

               set autonullfields off
This is a standard command in dBASE, and you can set this command in a startup routine for an application. Doing this when the application starts, you don't have to remember to set it. This affects all tables, all rowsets, it is a truly global command.

You may actually want to leave the global setting alone. In a case like that, but you have a rowset that you need to have the values filled in as "non-null", there is a property of the rowset object that matches the global command, but affects just the one rowset:

               q.rowset.autoNullFields := false

Another method of handling this is to set up a routine that forces the values to be "non-null" values. This might be done with a rowset's canAppend() event handler. If you do this, you might want to set it in a Data Module (if you're using one), or if it is only for a specific rowset, in a form, etc. The following is an example:

            function rowset1_canAppend()
               // set default values for fields
               q.rowset.fields["FName"].value := "" // empty character
               q.rowset.fields["MName"].value := "" // empty character
               q.rowset.fields["LName"].value := "" // empty character
               q.rowset.fields["Birthdate"].value := {} // empty date
               q.rowset.fields["SomeNumber"].value := 0 // non-null number would be zero
               q.rowset.fields["SomeLogical"].value := false // not true
            return true

This is a bit tricky, because of course you have to get every single field in the table, and make sure it is set to the default value(s) you need. The advantage to this is that you could, for example, set a field to a specific value, such as an invoice date -- you could set that to the current date:

               q.rowset.fields["InvoiceDate"].value := date() // Today's date

You could instead create a more generic routine that loops through the fields in the table and looks at the field type, setting the appropriate values, something like the following might be useful (you would pass it a reference to a rowset's fields array, as noted in the comments):

            function NonNullValues( oFields )
               // Generic "non-null" values for fields in tables, should
               // be called from a rowset's canAppend() event handler.
               // To use this, call it with:
               //    NonNullValues( this.fields )
               // if you then need to add default values for
               // specific fields:
               //    this.fields[ 'fieldname' ].value := default_value
               
               // Check for missing fields array:
               if pCount() == 0
                  msgbox( "Error, no fields array passed to this function!",;
                          "NonNullValues Function Error", 16 )
               endif
               
               // loop through fields array:
               for i = 1 to oFields.size // number of fields
                   // The 'type' property returns a value in all caps:
                   do case
                      case oFields[ i ].type == "CHARACTER"
                           oFields[ i ].value := "" // empty character
                      case oFields[ i ].type == "NUMERIC" or;
                           oFields[ i ].type == "FLOAT" or;
                           oFields[ i ].type == "LONG" or;
                           oFields[ i ].type == "DOUBLE"
                           oFields[ i ].value := 0 // empty number defaults to zero
                      case oFields[ i ].type == "DATE" or;
                           oFields[ i ].type == "TIMESTAMP"
                           oFields[ i ].value := {} // empty date
                      case oFields[ i ].type == "LOGICAL"
                           oFields[ i ].value := false // non-true
                   endcase
               next // end of loop
            return

With the 'generic' code above, any field type that isn't included (such as an AUTOINC field) would simply be ignored, avoiding errors.

Contatenating Field Values

As mentioned above, you can combine the values returned from character fields, which is useful in a wide range of cases, but it is also possible to combine data from different types of fields. dBASE uses a feature that doesn't get discussed much, called autoCasting -- it will automatically "cast" the type ... This can be tricky, however. In most cases you typically want to output or display data combined with strings. dBASE handles this quite well. A simple example might be a case of just outputting information to the Command Window:

               ? "The date for this record is: " + query.rowset.fields["MyDate"].value
In DOS versions of dBASE you had to jump through hoops to convert the date to a string, for output before you displayed it. dBASE automatically converts the date to a string. We'll come back to this when we discuss indexes, because with dates in particular there are some tricks you will want to be aware of.

Character Fields

When I started this, I didn't think there was much I needed to say about character fields besides the issue mentioned with null values above, but I started thinking a bit, and realized ... there might be a few things.

Null Values
There is a particular trick that is not obvious that you can use to ensure that a value returned from a character field is not null, and that is to pass it through the string object in dBASE:

               cString = new string( queryname.rowset.fields["MyCharacterField"].value )

Character Fields Are Automatically Strings
What do I mean by that? What I mean is the string object itself has a ton of methods associated with it that can be used to manipulate the string. The most commonly used items include:

There are other methods of the string class that can be used, but these are probably the most common ones. There are corresponding functions for all of these in dBASE, but the nice thing is that you can do something like the following:
               queryname.rowset.fields["MyCharacterField"].value.rightTrim().toUpperCase()
This combines a rightTrim() and converting the string value to upper case in one statement, rather than performing multiple commands. This would be the equivalent of:
               trim( upper( queryname.rowset.fields["MyCharacterField"].value ) )

Logical Fields

Logical fields have changed over the years. You can usually tell a programmer in the dBASE newsgroups who started out with the DOS versions of dBASE, because the logical operators always were surrounded with dots (.T., .F., .AND., etc.). When the Windows version of dBASE was created the dots were no longer necessary, so a developer can use the letters 't' and 'f', or 'true' and 'false, for example, and the operators 'and', 'or', without the dots, and so on.

One issue that trips up developers very easily (including those of us who have worked the the software for years), as noted above, most fields are null when a new record is added. This means that a logical field can be true, false, or null.

Most developers get tripped up when they are checking to see if a logical field is true:

               if q.rowset.fields["myLogicalField"].value

The problem is that the default in DOS-based versions of dBASE was always false. So a logical field was either true or it wasn't ... This gets worse if you are checking to see if the logical field is not true, because if it is null it is also not true:

               if not q.rowset.fields["myLogicalField"].value

There are a few ways around this. As noted above, you can set the rowset or the dBASE setting for autoNullFields to false. That's the simplest and one I recommend. You can set a default value for the field in the table designer (not always a good idea), or you can set code that creates default values for logical (and other) fields as noted above.

For some applications, null might be useful, so don't automatically discard the concept. A nice feature of dBASE is that you can check to see if a logical field (or any other, really) is null:

               if q.rowset.fields["myLogicalField"].value == null

One last note: for readability many developers actually perform a comparison against true or false when checking the value:

               if q.rowset.fields["myLogicalField"].value == true
The comparison is not really needed, but sometimes it is easier to read the code. This is a style issue, more than anything else.

Autoincrement Fields

Autoincrement fields are really handy, especially if you need a field to be a primary key, as by definition, these are always unique. When you add a new row to a table, these automatically increment the value. If the last record added was 35, the next will be 36, without your having to do anything. Note that the value is not actually created until the record is saved to the table, so you cannot query the value of an autoincrement field until that point. This avoids issues with networked applications and multiple users saving data at the same time -- the first one gets the next number, the one who saved right after gets the next, and so on.

For all this to work, dBASE (the Borland Database Engine, actually) stores a special value in the header of the table that contains the "next value" for the autoincrement field. When a row is added (saved) dBASE looks for this value, stores it in the new record, then increments the value in the DBF header.

If you think about this, it makes sense, but it can be confusing at first, there can only be one Autoincrement field in a table.

However, there are some things to be aware of when working with Autoincrement fields.

Adding an Autoincrement Field to an Existing Table
Any time you modify a table with the table designer dBASE makes a backup copy of the table (you can see it: "Backup of table_name" will appear in the Navigator window); the changes are made, and then data is appended from the backup.

The first problem that comes up (and I recently got bit by this, as I had forgotten what happens) is if you have a table that has data in it, and you add an autoincrement field to the table. What appears to happen is that dBASE adds the field, and fills in the values appropriately. What does not happen is the value stored in the table header for the "next value" is not updated.

There are a couple of ways to get around this. One that seems a little "brute force", but works well is (I did this in the Command Window with several tables and it works fine) -- the "with production" part ensures that production indexes (.mdx) are copied appropriately:

               use MyTable
               copy structure to new_MyTable with production
               use new_MyTable
               append from MyTable
               use
               drop table MyTable
               rename new_MyTable to MyTable

There are other ways to handle this, though. Because the "next value" information is stored in the header of the DBF, if you know how, you can access it, modify it, etc. However, I hate dealing with that level of file management, and someone else has already come up with code that allows you to deal with it.

In the dBASE Users' Function Library Project (you can get this freeware library of code from: The dUFLP, follow the instructions for setting it up if you haven't already) is a custom class called "DBF7File.cc", written by Bowen Moursund many years ago. This allows you to do things like find the "next value", and set the "next value".

To use this code, you would need to do something like the following:

               set procedure to :dUFLP:DBF7File.cc
               oDBF = new DBF7File( "MyTablename" )
               ? oDBF.GetNextAutoIncValue() // return "next value"

In the same situation that I suggested above where you have an existing table that you added an AutoIncrement field to, but the "next value" is still set to 1, you can force the value with something like the following:

               set procedure to :dUFLP:DBF7File.cc
               oDBF = new DBF7File( "MyTablename" )
               // set up a query:
               q = new query()
               q.sql := "select * from MyTablename"
               q.active := true
               q.rowset.last() // last record in table
               // get current "last" value
               nAuto = q.rowset.fields["MyAutoincField"].value
               // set next value with nAuto but add one to it ...:
               oDBF.SetNextAutoIncValue( nAuto+1 )
               // good idea to do some cleanup:
               close procedure :dUFLP:DBF7File.cc
               release object oDBF
               oDBF = null
               q.active := false
               release object q
               q = null

Once you have done this, you should never need to do it again for that table, although frankly it is a lot of work, and the first method works quite well.

Empty a Table, AutoIncrement Not Reset
One thing that may seem odd, if you empty a table (using the XDML command ZAP, or the OODML database object's emptyTable() method), the "next value" is not reset. If your table, had 44,000 records in it, and you emptied the table, the next autoincrement value would be 44,001, which may be confusing.

In that case you could just force the value to one, using the same code as above, although a bit simpler:

               _app.databases[1].emptyTable( "MyTablename" )
               set procedure to :dUFLP:DBF7File.cc
               oDBF = new DBF7File( "MyTablename" )
               oDBF.SetNextAutoIncValue( 1 ) // set value back to 1
               // good idea to do some cleanup:
               close procedure :dUFLP:DBF7File.cc
               release object oDBF
               oDBF = null

Query the "Next Value" for AutoIncrement
The following may be useful in some cases, but if you are working with a network application, and data is constantly being added by multiple people, this is not very accurate ...

                _app.databases[1].emptyTable( "MyTablename" )
               set procedure to :dUFLP:DBF7File.cc
               oDBF = new DBF7File( "MyTablename" )
               nNextValue = oDBF.GetNextAutoIncValue() // get value
               ? "The next value is: " + nNextValue
               // good idea to do some cleanup:
               close procedure :dUFLP:DBF7File.cc
               release object oDBF
               oDBF = null

Need AutoIncrement Type Field, But Don't Want Problems?
Periodically someone comes into the newsgroups and complains about how the autoincrement field messed them up completely and they don't want to use autoincrement fields, but they need that functionality. (Typically these are the issues mentioned above -- if you spend a little time with them, you can deal with the issues. Using Bowen's code above most people have few problems with them.)

The dUFLP (mentioned above) has a bit of code you can use in your own application, created years ago, before the AutoIncrement field type was created. The code in the dUFLP was created based on code by Romain Streiff for older versions of dBASE. If you examine the header it gives a lot of detail, but basically this is how you work with it:

               set procedure to :dUFLP:SeqValue.cc
               // create object
               oSeq = new SeqValue()
               nNewValue = oSeq.Increment( "MyTablename", "MyPrimaryKeyField" )
               // assume q is a reference to a valid query:
               q.rowset.beginAppend()
               q.rowset.fields["MyPrimaryKeyField"].value := nNewValue
               q.rowset.save()
               // cleanup
               close procedure :dUFLP:SeqValue.cc
               oSeq.release()

Side effects: a new table will appear in your database named "SeqValue.dbf".

Plusses: you can use the same code for multiple tables in a database, because you pass the tablename, which is stored in a record. Each table gets its own record in the SeqValue table.

Minuses: you have to manually call the code to increment the value in the SeqValue table and store the value in your own table as you add records.

There are further instructions in the comments at the beginning of the class file, including how to use the Reset method of the class, to set the start value back to the beginning. Just open it in the source code editor and read the details.

Date and Timestamp Fields

If you use dates in tables, you need to be aware of many features. There is a lot of detail about the date class in various places (including The dBASE Book, the dBASE Knowledgebase, and so on), but the date field is pretty special in general.

If you sort a table using dates (for an index, for example). The problem is not with a simple index -- simple index works pretty much as expected. What is not expected however, is if you use a complex index. We'll come back to that later, but I thought I'd surface it here.

Dates run into international concerns a lot in dBASE. What do I mean by that? The following two dates are the same date, but formatted differently:

01/13/2017

     versus

13/01/2017

Both of these are January 13, 2017. However, the format for the second one would be for a variety of nations that are not the U.S. (Not to mention some SQL server software returns dates in similar formats ...)

To find out what your current date format is, try typing the following in the Command Window:

               ? set("date")

On my computer, I get MDY, depending on how dBASE is set up for you, this may be different. You can always query the current date in the Command Window as well:

               ? date()

Dates are useful of course, and a lot of nifty things can be done with them. One of the oddest things is the way dates are actually stored -- they are numeric values. This is kind of cool, because you can do date math with them. For example, 30 days from today:

               ? date()+30

There are a lot of things you may need to do with dates -- I suggest, returning back to the dUFLP, there is a file called DateEx.cc -- this contains a ton of very useful functions, including the ability to add a month to a date, which may seem as simple as adding 30, but it isn't ... some months are 30 days, some are 31, and then there is February. You can get the first day of a month, the last day of a month, format the display of dates without having to do all the work yourself, and a lot more. The math is already defined for you. Rather than reinventing the code needed, I really suggest you look at this.

Timestamp fields are just dates with the time added. The date class in dBASE handles a lot of this, and in the dUFLP is a class called "Time.cc" which allows you to work with time values as well.

There isn't a lot else I can add here without spending a huge amount of time on this particular set of field types, because any specific application will have specific needs, and what you need out of dates and timestamps are going to be different from what I do. If you aren't sure, I recommend using the newsgroups ...

Memo Fields

Memo fields can be a very useful tool. The tricky thing is that the memo(s) associated with the records are not stored in the table itself, but in a .DBT file that has the same name as the the table itself. They are effectively unlimited in size (the big limitatio is disc space, which these days is, in most cases, not an issue). They are treated as really big strings by dBASE, you can read a whole memo into memory and do things with the value. You can extract just part of a memo, and more. For the most part there isn't a lot to say here. I honestly have not had any issues with memos, and I use them extensively in some of the tables I work with. I haven't had corrupted memos, and so on. If you do, in the dUFLP is code (I haven't had to work with it in a long time): FixDBF7.prg, which can help with issues with AutoIncrement and Memo fields. It produces a log, and lets you know what it did, etc. Be sure to read the instructions in the comments at the top, don't just run it.

Binary Fields

Binary fields were created to allow you to store images and sound files, or other binary type files. Honestly, if you have a lot of these, you may be better served to save them in a folder with the application, and just store in a character field the name of the file, and possibly (if needed) the path to the file, and so on.

If you do wish to use Binary fields in a table, note that these also use the .DBT file that memo fields use.

Binary fields are a bit odd, in that while you can store files into them, it is not as easy as some fields. Most fields you assign a value to the field property and when you save the record, the value is saved into the field. With Binary fields, you have to do something a little different:

               cFile = getfile( "*.jpg", "Import image" ) 
               if not empty( cFile )
                  q.rowset.fields["MyBinaryField"].replaceFromFile( cFile )
               endif

If you want to save the contents of a Binary field back out to a file outside of the table, you have to do something similar:

               cFile = putfile( "Save File", "*.jpg" ) 
               if not empty( cFile )
                  q.rowset.fields["MyBinaryField"].copyToFile( cFile )
               endif

Interestingly, there does not appear to be a way to empty out a Binary field. You cannot assign an empty string to the value property and save the record, etc. The best you could do is to have an empty .jpg or similar binary file, and issue a call to replaceFromFile() using that empty file.

OLE Fields

OLE stands for Object Linking and Embedding, and you can use either method of working with external objects -- you can link to a file, or you can embed the file in the field. This is fairly complex, and is covered in some detail in The dBASE Book, 3rd Edition, Volume 2 (pp 175-178). Rather than getting into them here, if you really need to use OLE, I suggest you check out the details in my book.

Modifying Tables

One of the things people often need to do is to modify a table's structure programmatically. If you used the tutorial instructions, everything you did with tables used the Table Designer. However, you may need to have code that works with table structures for your clients -- check to see if a field exists, if not, add it to the table, and things of that nature. This is a huge topic, and rather than trying to get into all the details here, I want to suggest further reading ...

The following commands can be used to work with tables:

For more information on the items not in the dUFLP, see online help for dBASE, The dBASE Book, etc. For items in the dUFLP, you can open the source code in the Source Editor, and there details in the comments at the top of the individual files.

Before you modify the structure of a table you should always back it up. There are a variety of reasons, but one really good one is the following scenario:

In a case like this you would probably have needed to write some code to loop through the data and add the missing information, before deleting the backup (if you delete it at all).

Changing the type of a field (from numeric to character) can sometimes work okay, but in some cases (character to numeric, character to date, etc.) you may lose data.

Deleted Records

Deleted records are interesting in dBASE's native DBF table format. To the best of my knowledge, the DBF table format is the only table type that uses what are called "soft deletes" -- meaning that while a record has been marked as deleted, it is still in the table. This makes it possible to deal with an accidental deletion. However, most, if not all, other databases on the market use what is called a "hard delete" -- meaning that the record once deleted is gone-gone-gone and not retrievable, except perhaps from a backup copy of the data.

In XDML, there is a command that has not been repeated in the OODML code, and frankly for good cause: recall -- this command will recall or turn-off the "deleted flag" for a specific record.

A command in dBASE allows you to display deleted records in a table:

               set deleted off
The default setting for this command is "on".

If you then navigate through a table using XDML, next to the record counter in the StatusBar, you will see for any "deleted" record, the word "Deleted":


Note, however, that there is no such indicator for OODML. This is because when the development team at Borland was creating the data object classes, it was felt that dBASE's native table format should act more like most or all of the other databases on the market in this area. Hence, dBASE's OODML classes/objects don't display deleted records, they don't allow you direct access to deleted records, and they do not allow you the ability to "undelete" or recall deleted records.

Despite all that, if you know that you can work with deleted records, and recall them, if a user deletes a record by accident (or you -- I sometimes delete a record I didn't mean to ...), you can still recall it. Here is what I recommend you do (this can be done in the Command Window):

            set deleted off
            use table_name
            set filter to deleted() // only display records that have the deleted flag set
            browse
            // find the record you want to restore:
            recall
            use
            set deleted on // restore the standard setting

The other issue with soft deletes for records is that these records are "taking up space" in your tables. It is a good idea to periodically remove them. This will update your indexes (if you are using .MDX tags) automatically, and if you are using memo and/or binary fields, reduce the size of the .DBT file as well.

In either case you should do these things when no one else is using the table, as the commands (we'll look at XDML and OODML) typically require exlusive use of the table. In XDML you would do something like:

            use table_name exclusive
            pack
            use

In OODML, as you might expect, the commands are a bit more wordy typically, but you still gain the advantages of the various object-oriented coding techniques:

            d = new database()
            d.databaseName := "MyDatabase"
            d.active       := true
            d.packTable( "table_name" )
            d.active       := false
            release object d
            d = null

If you are not using a database alias (which you really ought to be using, such as the one used for the tutorial), you can do the following instead:

            _app.databases[1].packTable( "table_name" )

This assumes that you are working in the folder that has the tables, and uses the _app object's databases array, database number 1 is always the current folder.

Something to look at for your own applications, in the dUFLP is a program called PackTables.prg -- it is designed to take a database alias, and pack every table in the database. It uses other code in the library to get that information from the database engine (the names of the tables). This is something you might want to use as a maintenance tool for your software.

Emptying Tables

Sometimes you need to remove the contents of a table. Maybe it is because the table is a temporary table you use for reports, or maybe there are other reasons. In any case, there are two ways to do this -- one is XDML the other is OODML. The XDML version is:

            use table_name exclusive
            zap
            use

Typically the zap command will ask if you want to do this. It is a good idea, but in your code you may not want it to ask. In that case you could add just a bit more code:

            set safety off
            use table_name exclusive
            zap
            use
            set safety on

In OODML you have code similar to the pack option given above:

            d = new database()
            d.databaseName := "MyDatabase"
            d.active       := true
            d.emptyTable( "table_name" )
            d.active       := false
            release object d
            d = null

If you are not using a database alias:

            _app.databases[1].emptyTable( "table_name" )

Emptying Tables with AutoIncrement Fields
One issue that comes up a lot is if you are using AutoIncrement fields, and you empty a table, the "next value" is not set correctly for the AutoIncrement field. This can be resolved as explained in the discussion of AutoIncrement fields, by using some code to force the value back to 1.

dBASE Tables and Networks -- Record Locks and More

I recently added a section to the tutorial that discusses this topic in some pretty good detail. This includes using a special field type for DBF tables called _DBASELOCK or a Lock Field, how you can work with it, working with sessions, and more. This section of the tutorial is here: Shared Data.

Looking for an Issue With DBFs and/or DBF Fields That is Not Listed Here?

If there is something you were hoping to find here, please drop me a note (details are on the last page of the tutorial), and let me know. I tried to think of the most common items that come up in the newsgroups and may have missed something important.


Sorting Data

Sorting or "collating" data can be a really interesting, albeit odd and sometimes frustrating aspect of working with data. Trying to get your data to sort exactly the way you want it is not simple sometimes.

In the earliest days of dBASE, people often used a command called SORT, which would create a new copy of the data with quite a few options, including the ability to apply filters (FOR, WHILE ...) and so on. The problem is, this was stored in a different table, and there was no way to update the "sorted" table, except to issue the SORT command again. Keeping multiple copies of the data is generally considered a bad idea, as it defeats many database "rules" ...

This command is still available in dBASE, but is hardly ever used anymore. Instead, developers tend to use indexes, although if you are working with SQL databases you may be using the "ORDER BY" clause to sort the data instead. We're focused on indexes in this part of the tutorial.

Index Terminology

The following are terms you should know when dealing with indexes (this is based on information in The Plus 11 Users Guide):

The .MDX file, as noted earlier in this document, can hold a limited number of index tags, but the advantage to using the production index tags is that updating data (adding, modifying, deleting) automatically updates each index that uses the values modified. It is possible to have more index tags in what are called "non-production .MDX files", these do not have the same name as the table, and can store more index tags. The disadvantage is that you would need to update these periodically or they will not match the current data.

Primary Key vs. Secondary Key

There are two major types of indexes in dBASE, although one might argue that as a rather simplistic concept. We're going to roll with it though. The index type that most developers of dBASE apps are used to working with are "Secondary" indexes, largely because until the DBF Level 7 table format was created, a "Primary" index or "Primary Key" index wasn't built into the structure.

Primary Key / Primary Index
By definition a primary key index is one that maintains a unique value, a key that can always be used to reference that individual record. You would not want to typically use, for example, names for a primary key. If you have a customer table, the odds are good that you will have several customers with the same last name, which would cause problems. Many developers use the AutoIncrement field for this purpose, as by definition each value is unique.

The key value -- what is used to sort the data -- may be a single field, it may be a compound index -- meaning that it uses the values from more than one field or uses an expression. The ultimate goal is that the value be unique.

Secondary Key / Secondary Index
As noted above, secondary keys are the more commonly used index type for DBF tables, as you can have several of them.

Creating Indexes

Using the Table Designer
To create a Primary Key in the Table Designer, you would need to use the "Structure" menu, select "Define Primary Key", and select the fields you want to use for your key expression.

In the Table Designer, a simple index can be created in the "Index" column by changing "None" to "Ascending" or "Descending".

In the Table Designer, a complex index can be created by clicking the "Structure" menu item, and selecting "Manage Indexes ...". A dialog appears that shows any existing index tags. From there to add a new index tag, click the "New" button. A more complex dialog appears giving you many options to create your indexes. All of this was shown in Phase 2 of the tutorial, and will not be repeated here.

Using dBASE Code (dBL: XDML, SQL, OODML)
You can create indexes in code, or in the Command Window as well, through the use of various commands in dBASE.

The XDML command requires the table be open, and it should be opened using the Exclusive flag, so that no other user can work with the table while you are creating the index:

               index on <key expression> tag <tag name>
There are some options that can be used, for example if you wish the index to be the primary key for the table, you would add the word primary at the end. You can add a filter option using the word for followed by the expression, such as "for salary > 24000", which would limit the data in the index those records that match the condition. Use of the word descending will sort the data in "reverse" sequence. Use of the word unique will ensure that the index contains unique values only (if you use primary there is no need to also use unique -- primary keys by definition are unique ...). There is a lot explanation of this command in the online help (in the Command Window: Help Index, when the dialog appears, click "INDEX" and click the "Display" button).

The SQL command to create an index using the Borland Database Engine's "Local SQL" is actually rather limited. It can only create a simple key expression (a single field), cannot be used to create a primary key for a DBF table and the syntax is quite specific:

               create index <tag name> on <table name> (<field name>)
This was actually placed into the BDE not for use with DBF tables, but for use with Paradox (.DB) tables.

As you might expect, the OODML option for creating index tags is a bit more wordy, but it is easy to read, and because it is all object-oriented code, can be quite flexible.

OODML uses a combination of the DBFIndex object and the Database object to create index tags. Anything noted as "default" does not actually need to be set if you do this, it is being show here just to be complete:

               oIndex = new DBFIndex()
               oIndex.indexName     := "MyIndexTag"
               oIndex.expression    := "MyFieldName" // key expression as noted above, 220 characters, field names, etc.
               oIndex.descending    := false // default
               oIndex.unique        := false // default
               oIndex.forExpression := "" // default (empty string)
               // using a database object, in this case the default _app.databases array:
               _app.databases[1].createIndex( "MyTable", oIndex )

Modifying an Index Tag

There is no command "modify index", or anything like it in dBL. However, you can always open a table in the Table Designer, and modify any existing index tag that you have. This is useful if you decide that maybe (if you're using one) your for expression is too limited, or you need to change an index to descending, etc.

In code, the only way to modify the index is to re-create it. You might want to either as shown elsewhere use "set safety off" to turn off the safety setting that asks about the index already existing, and overwriting it, or you might want to delete an existing index, and re-create it. Sometimes that is a good way to go.

Deleting an Index Tag

You can delete index tags in the Table Designer pretty easily. Simple tags can be deleted by changing the "Ascending" or "Descending" option to "None" in the table designer. When you close the table, and save changes, the tag will be gone. For compound index tags, use the "Structure" menu, "Manage Indexes ...", and in the dialog, click the index tag you wish to delete, click the "Delete" button.

In dBL or code, as usual there are multiple ways to delete an index tag. For XDML, the command:

               delete tag <tag name>
Will usually do what you need. The table needs to be open, and should be opened exclusive (to avoid issues with other users trying to modify the table).

The SQL method is similar:

               drop index <table name>.<tag name>
The trick is that you have to include the table name, followed by a dot, and then the index tag name:
               drop index MyTable.MyIndexTag

In OODML, the object-oriented version, you would use the database object's dropIndex() method, which again requires the table name and the index tag name:

               _app.databases[1].dropIndex( "MyTable", "MyIndexTag" )

"Corrupt" Indexes

No matter how hard a developer works at things, somehow something will occaisionally happen that "corrupts" or causes issues with an index tag. One difficulty is determining what causes this kind of thing to happen in the first place. The simple answer: "There is no simple answer." The most common issue is that something was happening with a table, perhaps a long process (I have several routines that take a while, updating tables, comparing data, etc. in some of my apps) that was interrupted by a crash, shutting down the computer while the process was occurring, a variety of things of that nature. What is the solution?

The simple solution is to find the index tag in question, delete it, then re-create it.

However, in a production application, one that is being used by your businses, or a client, that is not always easy to do.

One solution is in the dUFLP, a program that can be used in the development environment to create a program you can deploy with your application that will fix the index tags by deleting and re-creating them.

In the dUFLP (mentioned elsewhere on this page), is a program: CreateReindex.prg. When run (see the comments at the beginning of the program), this creates a new programm called "Reindex.prg" that can be deployed with your application. If you pass it the name of a database alias, it will use that, otherwise it will work off the tables in the current folder (I suggest using a database alias). The program that is output examines each table in the alias (or local folder) and deletes it, then re-creates it. I recommend setting this up off a maintenance menu option for your application, possibly (if you do this) setting it as something only you or a system administrator can access. How, I leave up to you.

Issues with Indexes and Specific Field Types

When indexing data, there are things you should be aware of when sorting data with indexes, I have attempted to put everything I can think of here ...:

Compound / Complex Indexes
As mentioned elsewhere, and to be discussed below when talking about various field types, you may want to use compound indexes -- ones that include multiple fields and/or other expressions. A lot of the discussion here is going to center around these issues. There are some basic rules for complex indexes in the Users' Guide, summarized here:

Field Names with Spaces
Spaces are used as delimiters in commands in dBASE, so if you have spaces in a field name you could run into problems. However, dBASE provides a method of handling this that works in most situations. If you surround a field name with colons dBASE will understand that any spaces are part of the field name:

               index on upper( :last name:+:first name: )
For example, will work fine.

Character Fields
One of the most frustrating things for someone starting out is getting data sorted exactly the way they like. A simple index, Ascending sequence, on a character field, may return what you need. However, when you start adding mixed case or special characters (è, for example) into your data, things can get complicated.

If you have a set of data (the following is based on my Science Fiction Library), last names:

Now, if you created an index, using (for the moment) the XDML command:
               index on lastname tag lastname
You might expect the list to come out as above. However, the list will not comply, as the internal mechanics of the way characters are stored in the computer do not match what we, as humans, might expect. The list instead would come out:

"Why?" you might ask. The reason is that dBASE, like all software, works with the ASCII or ANSI values associated with each character in the character set. You can find in The dBASE Book (3rd Ed, Volume 2) in the Appendices, an ANSI chart that shows the Windows standard character set, and the numbers associated with each. You will see that a Capital "A" is given a value of: 65, where a lower-case "a" is given a value of: 97. These are not considered the same at all. In the example above, "deGrasse Tyson" starts with "d" which has a value of 100, and "van Vogt" starts with "v" which has a value of 118. Both of these are greater than the value of a capital "Z" which is 90. Hence, they would sort at the bottom of the list.

This can also be a problem when searching for data. In the main tutorial, we created indexes using the upper() function. What this does is store a copy of the data in the index in upper case. So if we changed the command above just a little:

               index on upper( lastname ) tag lastname
The data would sort exactly the way we might expect. We'll come back to this later ...

Things can get even more interesting based on the character set being used for indexes. When I install dBASE on a new computer, the first thing I always do is make sure that the BDE Administrator has the DBASE driver's "LANGDRIVER" setting as "'WEurope' ANSI", because some of the other character sets can do funny things. A lot of the data I work with the most uses characters with diacritical marks, accents, umlauts, that kind of thing and if I don't use the correct drivers, the data will sort incorrectly. For example, if I need to use "é" and I don't have the correct language driver, that will not sort with "e", it will sort after a lower-case "z".

The Table Designer does not tell you what the current language driver is, but you can find this out -- in the Command Window:

               inspect( _app )
Look for the "lDriver" property. If it is "DBWINWE0 ('WEurope' ANSI)" you should probably be fine for most situations. If it is something else, you may need to check. Some of the users in the dBASE newsgroups prefer other drivers, but this is the most common one.

Character Fields and Trim()
One common mistake many developers make is to use the trim() function with character fields, in order to be more "efficient". This is often because many examples, particularly with names or addresses, output of the data is often trimmed:

               ? trim( last_name )+", "+trim( first_name )
as a simple example. The first problem is that if you have a set of data such as:

Last NameFirst Name
FredericksTed
FredericksonAlbert
TylerJeanne

And you an index tag like (using upper() to provide case insensitive:

               index on upper( trim(last_name)+trim(first_name) )
The data would stored in the index as:

FREDERICKSONALBERT
FREDERICKSTED
TYLERJEANNE

In this example the data might actually sort the way you wanted, but note the lack of spaces delineating the end of the fields. Note that Ted Fredericks sorts after Albert Frederickson, because of where the letters fall ... This is not the greatest example, but hopefully it helps get the idea across.

By leaving out the trim() function, the data would be stored differently:

FREDERICKSON   ALBERT
FREDERICKS     TED
TYLER          JEANNE

There can also be problems if your index tags are not all the same length, which these would not be. General rule of thumb: never use a trim() function in an index tag.

Date Fields
I mentioned earlier that null dates end up at the bottom of a sort, but empty dates will also end up there. This may seem a bit odd, you would think if it were empty the date would be at the top. Well, if the dates were sorted in descending sequence (most recent date at top), they would be. I cannot tell you the reasoning behind this, but it is a fact.

If you are using simple index tags with a date field, there is nothing to worry about, they work just fine, except for the discussion about empty or null values discussed earlier (empty or null dates end at the bottom of the index or if in descending sequence they end up at the top ...).

If, however, you use compound index tags, things can get a lot more interesting. If your date format is set to the default for "American" dates: MDY, and you concatenate a date with a character string, the dates will sort differently than you might expect. Assuming a simple set of three records in a table:

Last Name Birthday
Smith10/25/1965
Jones5/30/1960
Smith8/15/1970

If you created an index tag that used the last name and birthday fields:

               index on :last name:+Birthday tag lastbirthday
and you were to use this index and view it, the sequence would look like:

Last Name Birthday
Jones5/30/1960
Smith8/15/1970
Smith10/25/1965

The record for "Jones" you would expect to be at the top, but most of us would expect that the last record would become the second one in the list, because we assume that dates are sorted by year first, then month, then day of month.

The problem is that when you concatenate a string and a date, the date becomes a string, so the data would appear (if you could read the index tag, which normally you can't) as:

Jones     05/30/1960
Smith     08/15/1970
Smith     10/25/1965

Note that first, dBASE adds a leading zero, the names are padded out to the field length (for the example I assumed a length of 10), and the data is sorting the two "Smith" records on the month, then the day, then the year. If the month and day were identical, then they might have sorted correctly by accident.

The solution is to use a function in dBASE called dtos() which stands for "date to string". This forces the string to be output as four digits for the year (even if you have SET CENTURY turned off), two digits for the month, and two digits for the day with no separators. If you modified the index:

               index on :last name:+dtos(Birthday) tag lastbirthday

If we then looked at the data in the index we would see:

Jones     19600530
Smith     19651025
Smith     19700815

And the data would sort the way we would expect.

One trick that often trips up people is dealing with dates in a complex index that sort descending (most recent date first) if the rest of the data in the index is ascending. dBASE does not let you sort one field ascending and another descending in the same index tag -- it sorts the whole expression either ascending or descending. So ... if you have a situation, for example, using names and birthdates as shown above, where you want the most recent birthdates at the top, how would you do that? Let's move the data around so that the dates are first, followed by the names, just to make it easier to see.

If you created an index like:

               index on {12/31/9999}-birthday+:last name: tag datedescendname
Note that the year is 9999, a year that is way outside of most people's data range. Subtracting a date like that inside the expression should give data like:

2932670Smith
2934425Smith
2936399Jones

The numbers don't look like valid dates (they aren't ...), but the math allows us to sort the data differently.

And if we listed the data itself:

Smith     08/15/1970
Smith     10/25/1965
Jones     05/30/1960

Again, with only three records, this is not the best example. If, for example, we had several records with the same birthdate, the names would be sorted in ascending sequence ...

If we wanted to go the other direction, with the data sorted descending, with the dates being sorted ascending, again with the dates first, the index tag might look like:

               index on birthday-{12/31/9999}+:last name: descending tag dateascendname

The list would look like:

Jones     05/30/1960
Smith     10/25/1965
Smith     08/15/1970

Again, this is not the best example, it is sorting on the date first which is now in ascending sequence, and then on the name, which is in descending sequence, and with only three records in the table ... If we added a row with a last name of, say, "Aguilara" with the same birthday as "Jones", the list would place "Aguilara" under "Jones", as the same date, but the last name is in descending sequence, as expected. (For the record, I have been testing this as I write ...)

Numeric Field Types
Numbers can be interesting when concatenating them with character fields, and they can get really weird if you simply concatenate them. For example:

               index on salary+commission tag salarycommission
This would not sort on the salary field and then the commission field, but it would add the two values together and sort on the combined value. While this might give you the results you want, it most likely won't.

When combining numeric values, either with other numeric values or with other field types in an index, you should use the str() function which converts the number to a string:

               index on str(salary)+str(commission) tag salarycommission

For numeric values, to change the sequence so that part of the index expression is ascending and part is descending, you could do something like the following:

               index on TestChar+str( 1000000000-TestNum,10,0,"0") tag numdesc
Which would sort the data ascending by the last name field, and descending by the "NumericField".

Again, to go the other way, with the name descending, and the number ascending:

               index on TestChar+str( TestNum-1000000000,10,0,"0") descending tag numascend
This will show the names in descending sequence, and the numeric values in ascending sequence.

Other Field Types
In theory you cannot sort on other field types. However, as noted in several places, you can work with expressions. For example, if you wanted to use a logical field in an index expression, you could return a character or even a numeric value -- the example here uses the iif() function -- for "immediate if" -- the parameters are "condition", "what to do if true", "what to do if false"):

               index on iif( MyLogicaField, 1, 0 ) tag MyLogicalField
This would sort on the numeric value returned by the iif() function -- if the field is true, we get a number 1, if it is false (or null) we get a value of 0. You could of course reverse it and use 0 for true, etc. if you wanted the true values at the top.

You might need to use, say, part of a memo field. For that since a memo is text (by default, unless you use it to store binary values, which is not recommended), you could sort on the first 100 characters of a memo field:

               index on left( MyMemoField, 100 ) tag MyMemo

Examples of Complex Index Tags

The following are examples of some complex index tags from one of my databases, for various tables. This is meant to just give an idea of some of what can be done with these. The tables are for a tracking system used for a medieval re-creation group I belong to, and the index tags have a variety of uses:

Index Tag NameExpression
NameRDstr(namecode)+str(awardrank)+dtos(awarddate)+str(AwardNumber)
UniqueRankstr(rank) unique
Nameleft(upper(name),100)
RankDatestr(awardrank)+dtos(awarddate)+str(awardnumber)+left(upper(name),50)
RegionDatestr(at(trim(region),"West|Mists|Cynagua|Lochac|Other Kingdom|Barony"))+BaronyCode+dtos(reignstart)

The last is particularly complex, in that the use of the at() function returns a numeric value, which then needs to be converted to a string using str(), and so on.

Searching Data With Indexes

The last part of this discussion is going to go over some of the methods of working with indexes and searches. The discussions below will not get into every permutation of what is possible, or all the tricks, traps, etc. This is just to help you get started. The tutorial uses the seeker class that comes with dBASE, but we'll briefly look at that here as well.

Using an Index
The first thing is that with indexes, you need to be able to set the current sequence to one you want to use. In XDML this can be done in a couple of ways, the first is to open the table with the index tag:

               use MyTable order MyIndexTag
or, if the table is already open, you can use the set order to command:
               set order to MyIndexTag
               // optional -- go to the top of the table in this sequence
               go top
The second command go top ensures that you are at the first record in the current index sequence.

To do this with OODML, you use a rowset object's indexName property:

               queryname.rowset.indexName := "MyIndexTag"
               // optionall -- go to top of table in this sequence
               queryname.rowset.first()
In this example, go top is replaced with a call to the rowset object's first() method instead, which navigates to the first record in the current index sequence.

Clearing an Index / Natural Order
To turn off an index and set the table in "natural order" (the order the records were entered into the table) is sometimes useful. In XDML:

               set order to
               go top

In OODML, very similarly:

               queryname.rowset.indexName := null
               queryname.rowset.first()
Using the null operator ensures that there is nothing associated with the indexName property. You could have also used an empty string: "".

Finding a Record -- Locate
This can get complicated, so I'm only going to briefly discuss some of what can be done. For further information, see The dBASE Book, the Knowledgebase for dBASE, and online help. The Locate command does not require the use of an index tag, although if the field (or fields) you are searching for are in an index tag and in sequence, for a large table the use of the index tag can speed up the locate command.

It should be noted that the OODML applyLocate() and findKey() options shown here are affected by the canNavigate() event handler (if the code is set to not allow navigation, these methods cannot function ...), and they do navigate in a rowset. This latter is important, because of various other functionality with the rowset class (if you navigate and were editing a record, any locks will be released, the record is automatically saved, etc.) The XDML commands locate and seek also navigate in a table.

The XDML locate command can be very useful to find data in a table, as you can provide pretty specific conditions for what you are looking for. For example:

               locate for :last name: = "Smith"
This looks for a match on the first five characters (since you told it to look for five characters). If you used:
               locate for :last name: = "SMITH"
You might into problems as you told it to search for the last name in all capital letters, and if the data is not stored in the table in all capital letters, there will be no match.

Probably the best way to handle this is:

               locate for upper( :last name:) = "SMITH"
This converts the contents of the field to upper case, so that it matches the string you're looking for.

The locate command will attempt to find the closest match, if nothing is found it navigates to the end of the table. You can check by looking at:

               if not eof()
                  ? "Found: " + :last name:
               else
                  ? "Did not find SMITH"
               endif

One last useful aspect of the locate command, is a related command. If you are looking for a record and there are several records in the table that have the same value in one field, you can find the first occurance, if it is not what you're looking for, use the continue command to continue looking:

               bFound = false // set a flag
               locate for upper( :last name: ) = "SMITH"
               do while not eof() // loop until end of set
                  if msgbox( "Is this the record you're looking for? "+chr(13)+;
                             trim( :last name: )+" - "+birthday,;
                             "Found a match", 36 ) == 6 // Yes
                     bFound = true
                     exit
                  endif
                  continue // continue looking
               enddo
               if not bFound
                  ? "No Match"
               else
                  ? "Found: " + trim(:last name:)+" - " + birthday
               endif

That's the XDML version of locate. There is an OODML version as well. This one can be confusing, because a) it relies on the use of local SQL -- as defined by the Borland Database Engine -- meaning it is not complete (not all SQL functionality is there); b) some of the options are designed specifically for use with forms ... We'll start with the basics, to do what we've been talking about above:

            // basic setup
            oQuery = new query()
            oQuery.sql    := "select * from MyTable"
            oQuery.active := true
            
            // to perform the locate:
            oQuery.rowset.first()
            oQuery.rowset.applyLocate( " :last name: = 'Smith' " )
            if oQuery.rowset.endOfSet
               ? "Not Found"
            else
               ? "Found: " + oQuery.rowset.fields["Last Name"].value.rightTrim() + " - " +;
                             oQuery.rowset.fields["Birthday"].value
            endif

There is a property of the rowset called locateOptions which allows you to get very specific, one that might be useful here would be to try to go for case-insensitive searches.

            // to perform the locate:
            oQuery.rowset.locateOptions := 3 // Match partial length and ignore case
            oQuery.rowset.first()
            oQuery.rowset.applyLocate( " :last name: = 'SMITH' " )
            if oQuery.rowset.endOfSet
               ? "Not Found"
            else
               ? "Found: " + oQuery.rowset.fields["Last Name"].value.rightTrim() + " - " +;
                             oQuery.rowset.fields["Birthday"].value
            endif

The OODML equivalent of continue is the rowset method locateNext():

            oQuery.rowset.locateOptions := 3 // Match partial length and ignore case
            oQuery.rowset.first()
            bFound = false // set a flag
            oQuery.rowset.applyLocate( " :last name: = 'SMITH' " )
            do while not oQuery.rowset.endOfSet // loop until end of set
               if msgbox( "Is this the record you're looking for? "+chr(13)+;
                          oQuery.rowset.fields["last name"].value.rightTrim() + " - "+;
                          oQuery.rowset.fields["birthday"].value,;
                          "Found a match", 36 ) == 6 // Yes
                  bFound = true
                  exit
               endif
               oQuery.rowset.locateNext() // continue looking
            enddo
            if not bFound
               ? "No Match"
            else
               ? "Found: " + oQuery.rowset.fields["last name"].value.rightTrim() + " - "+;
                             oQuery.rowset.fields["birthday"].value
            endif

The OODML method beginLocate() is designed to allow you to specify multiple fields and search conditions, but was really originally created to work with forms. The idea is that you can design a form with the dataLinks for the entryfields set to the fields in the table, click a button that puts you into "begin" mode, which clears out the entryfields. You then type the values to search for in any fields, click a button to start a locate. It then looks for maches (assuming equal) for the fields that have values and returns the first record that matches, if any. There are examples in various places, including the Knowledgebase and The dBASE Book.

Finding a Record -- Seek / findKey()
Unlike the locate command and the OODML equivalent, the seek command and OODML findKey() method require the use of an index tag to perform a search. However, with a large table, these tend to be much faster than locate.

The XDML method is to use the seek command:

               use MyTable order MyIndexTag
               seek <some value>
The seek command is case-sensitive, and the expression or value you are searching for must exist in the table, or you will navigate to the end of the table. There is a handy function you can use: found() that returns a true or false value as well.

The following is a simple example:

               use MyTable order Last
               seek "Smith"
               ? iif( found(), "Found it!", "No Match!" )
If the index is ordered using the upper() function, we would not find the match. We would need to be more specific:
               use MyTable order Last
               seek "SMITH"
               ? iif( found(), "Found it!", "No Match!" )
And so on. There is a lot more that you can do with this, take a look at the various sources mentioned in this document. You might want to familarize yourself with the set near command, for example, as this can affect the way the seek command works.

The OODML method uses the rowset's findKey() method:

               queryname.rowset.findKey( <value< )
The method returns a value of true if a match is found, or false if no match is found. In addition, this method navigates like the seek command -- however, if no match is found, unlike the seek command, findKey() will return you to the current row before you issued the command.
               if oQuery.rowset.findKey( "Smith" ) // if we find a match
                  ? "Match found"
               else
                  ? "No match found"
               endif

In addition, the findKey() method will automatically do a partial match, so if your expression to find was "Smi", it would find the first record that started with those three lettters. findKey() is case-sensitive, however, so if your index is set to use the upper() function, you would want to search for the upper case version of the value.

There is a similar method findKeyNearest() which acts very similar to findKey(), but it looks for the nearest possible match. For example, if you used this:

            if oQuery.rowset.findKeyNearest( "Smythe" )
The software would find the closest match if possible, however if there is no record after "Smith" in the data, it would navigate to the end of set, and return false. If you had a record that started with "T" in the last name field, it would stop there as that was a close as it could get. As with findKey(), the findKeyNearest() method is also case sensitive.

Seeker
This is the last topic in this addition to the tutorial, it is a basic discussion of the seeker class that ships with dBASE ™. This control was created years ago by Ken Chan when he worked at Borland, and has been included in dBASE for a long time. It is a particularly useful custom class, and is used in the main tutorial project as well, but I thought I'd go over its basic use here, and show a modification that you might find handy as well.

The Seeker class is designed to work with both XDML and OODML. It assumes that your index uses the upper() function on the field(s) used in the expression.

To use a seeker object on a form (it is a form control -- a custom entryfield class), in the Command Window:

               set procedure to :formcontrols:seeker.cc
You can then drop an instance of the seeker control onto the form. It is recommended that you use this with a grid, so that you can see the records as they come up. The tutorial uses this on several different forms (you will see that in Phase 5). One of the nice things is that the seeker class performs what is called an "incremental search", in that if you type "S", it immediately jumps to the first record in the index tag that starts with the letter "S", if you then type "m" it goes to the first record that starts with "Sm", and so on.

The seeker class uses, when working with OODML, the rowset's findKeyNearest() method, so it seldom ends up at the end of the rowset.

Seeker and Numeric Values
One issue that comes up a lot is the need to use a seeker control on a field that is numeric. For example, you might be looking for a specific invoice number in a large table. The seeker class assumes a character value, and if you enter numbers you can end up with errors occurring, which is frustrating.

The nice thing is that it is easy to create your very own subclass of the seeker control. You could add to your own custom class file in dBASE something like the following:

               class NumSeeker( fArg ) of Seeker( fArg ) from :FormControls:seeker.cc custom
                  function NormalizedValue
                  return val( this.Value )
               endclass
And this is all that would be needed. If you then used this instead, with the appropriate index (it would need to use a numeric expression), you can search for numeric values, and it would interestingly enough work just like seeker does, as an incremental search.

And Done!

This part of the tutorial was added as an after-thought, as explained in the beginning of the page, because, well, seriously, there is a lot of assumed knowledge out there, and I felt like putting a lot of things that were related in one place. Some of what is here is really basic, some of it skirts around the edges of more advanced topics. If you want ot know more, check the various sources available, and as always, use the newsgroups. The odds are good in most cases that if you can't figure out how to do something specific, that someone in the newsgroups has already worked through the issue and can point you in the right direction.


  Back to the tutorial menu     Back to previous part of tutorial: Databases   Proceed to the next part of the tutorial: Creating Data Modules