XBase to dBASE's
Object Oriented Database Manipulation Language (OODML)

NOTE: This document was originally written for Visual dBASE 7.0/7.01, it has been updated for dB2K (release 1) to include information about new properties, events, etc., and any new controls since the document was first written. Any changes for later versions of dBASE should be noted as well. The text may state "dB2K", but should work for dBASE Plus, etc. If a control is new it will be noted. In updating this document, the images were left "as is" unless it was felt to be absolutely necessary to change them ...

In addition, this document refers to dB2K a lot, but unless it is about a dB2K specific aspect, the text can be used for Visual dBASE 7.0 through Visual dBASE 7.5.

One more note, the programming language of dB2K is a mixture of XDML, OODML, and other commands that don't deal with the data, all of the language is called dBL.

The Purpose of this Document

While Xbase DML is being replaced by the more capable OODML, there are times when it makes sense to use the former. Most of the XDML commands are internally mapped to their OODML equivalents and are slower than OODML, however some are not and will still outperform the OODML method of accomplishing the same task. Good candidates for XDML are some batch processes, table creation and index creation. OODML is nearly a requirement for some of the interactive and visual elements of VdB, namely the grid object and the report class.

---

DISCLAIMER: Please keep in mind that not every single possible variation or combination of XBase commands could be covered in one document. An attempt is being made to cover the basic commands themselves, and to show examples of their OODML equivalents (if any). The online help is actually very good at showing equivalents as well, and some of that information is being used here. Some of the information contained in this document is being "lifted" from "Code Tips and Tricks" HOW TO, another HOW TO document created by the author ... there didn't seem to be a need to re-write things that already were written ...

---

• Table of OODML/dB2K Equivalents for XBase DML Commands
• XBase Command Equivalents in the OODML, in Detail
  • APPEND
  • APPEND BLANK
  • APPEND AUTOMEM
  • BLANK
  • BOF()
  • CHANGE()
  • CONTINUE
  • CONVERT
  • COPY
  • COPY BINARY
  • COPY MEMO
  • COPY STRUCTURE
  • COPY TABLE
  • COPY TO ARRAY
  • COUNT
  • DELETE
  • DELETE TABLE
  • DELETE TAG
  • DELETED()
  • EDIT
  • EOF()
  • FIELD()
  • FLDCOUNT()
  • FLDLIST()
  • FLENGTH()
  • FLOCK()
  • FLUSH
  • FOR()
  • FOUND()
  • GO/GOTO
  • GO TOP
  • GO BOTTOM
  • INDEX
  • ISTABLE()
  • KEY()
  • KEYMATCH()
  • LDRIVER()
  • LKSYS()
  • LOCATE
  • LOCK()
  • LOOKUP()
  • LUPDATE()
  • ON ERROR
  • OPEN DATABASE
  • ORDER()
  • PACK
  • RECALL
  • RECCOUNT()
  • RECNO()
  • RECSIZE()
  • REFRESH
  • REINDEX
  • RELATION()
  • REPLACE
  • REPLACE BINARY
  • RLOCK()
  • SCAN loop
  • SEEK/SEEK()
  • SET CARRY
  • SET DELETED
  • SET EXACT
  • SET EXCLUSIVE
  • SET FIELDS
  • SET FILTER
  • SET INDEX
  • SET KEY
  • SET NEAR
  • SET ORDER
  • SET RELATION
  • SET SAFETY
  • SET SKIP
  • SKIP
  • TAG()
  • TAGCOUNT()
  • TAGNO()
  • TOTAL
  • UNIQUE()
  • UNLOCK
  • USE
  • ZAP

Table of OODML/dBASE Equivalents for XBase DML Commands

Many, but not all, of the items in the table below have "links" to a section of this document that discusses that item in more detail. In addition, there may be a few sections on "how to do" something in particular in more depth ... If a command does not have a link, there is not much that the author felt needed to be said, and your best bet is to check online help for that command.

Finally, while there are examples in many of the discussions of equivalents, most of the examples do not include a lot of error checking (for example, looping through the fields in a rowset and assigning a NULL value, under the BLANK command, I do not add checks for read-only fields ...). I leave this to the developer to determine their needs ...

Back to the menu

XBase Command Equivalents in the OODML, in Detail

Note that the examples below, that while the examples show using these properties, events and methods in a form, they can also be set up (coded) in a Datamodule, a program, a report, and so on, in the same ways ...

APPEND
The OODML equivalent of APPEND is to use the rowset object's beginAppend() method.

Once you do this, any control on a form that is datalinked to fields in the table will be emptied (or default values will appear), and the user can add data to the new row.

Items to consider:

• Navigation of any sort (moving the row pointer) will cause an automatic save of the new row.
• The rowset has a save method which can be used to save the changes.
• The rowset has an abandon method which can be used to abandon changes and the new row.
• The rowset's canAppend event can be used to override or set default values for fields in the table (for example, you could set today's date for a date field with:

  
         form.rowset.fields["Date Transact"].value := date()

  and so on for fields you wish to set defaults for) -- make sure that this method returns a logical (TRUE) value, or you will not be able to continue ...

It is possible to create default values (see the Developer's Guide that ships with dB2K) for fields in the table designer (for DBF7 table format tables). This is very useful if you always wish to set specific defaults. You should note that in the case of most (or all) field types in dB2K the default value is NULL unless specifically set otherwise.

   form.rowset.beginAppend()

Back to the menu

APPEND BLANK
The Xbase DML APPEND BLANK command is used to add a blank row to a table.

There is no completely direct method of doing this in the OODML, but you can simulate it simply enough with the following:

   form.rowset.beginAppend()
   // you may need to put spaces in a field or something
   form.rowset.save() 

Back to the menu

APPEND AUTOMEM
The "AutoMem" abilities of dBASE are sort of redundant with the use of the OODML. The purpose of APPEND AUTOMEM and it's related REPLACE AUTOMEM and so on, is to create variables in memory on the fly, that are used to create a record buffer. The record buffering is automatic in dB2K. If you really wanted to, you could emulate automem by looping through the fields array, and creating memory variables based on those, and so on. The amount of work involved is a bit silly, and I will not attempt to create something here. You are better off using the beginAppend() method noted under APPEND above.

Note: If your table uses a read-only field (AutoInc or _dBASELOCK), the APPEND AUTOMEM and REPLACE AUTOMEM commands may give you some trouble, as they will not write to the table -- they will return an error.

Back to the menu

BLANK
The XDML BLANK command is used to blank a field or fields in a row. There is no direct OODML equivalent. If you only want to do this for a specific field, or fields, you can simply assign NULL as the value:

   form.rowset.fields["A Field"].value := NULL
   form.rowset.save()

Note that if you wish to save this change, you should use the rowset's save() method as shown.

If needed, you could loop through the fields in the current row and set their values to NULL (see also APPEND BLANK above):

   for i = 1 to form.rowset.fields.size
       form.rowset.fields[ i ].value := NULL
   next
   form.rowset.save()

Note that if you do not issue the 'save()' method call, that the state of the rowset will be left in edit mode, and may actually cause problems elsewhere in your form (or other code).

The code sample above does not test for "AutoInc" field types, or for "_dBASELOCK" fields ... trying to write to those will return an error as they are read-only.

Back to the menu

BOF()/EOF()
In the XBase DML, there are two checks -- one at either end of the table to see if you are at the "Beginning of File" or the "End of File". There are three checks in the OODML.

The first of these is the endOfSet property. This property is assigned a logical value, and is true if you are either at the beginning or the end of the rowset. If you are on a valid row, this property is assigned a value of false. You can tell if you are at the beginning of the rowset if you are navigating (see SKIP ...) toward the beginning of the rowset, and the end if you are navigating toward the end of the rowset.

However, some folk aren't satisfied with that, and the rowset includes to methods that check to see if you are at the first or last rows of the table: atFirst() and atLast(). Both of these return logical values (true or false), depending on whether or not you are on the first or last rows of the rowset.

Use of endOfSet

   if form.rowset.endOfSet
      // do something
   endif

Note that if the table is empty, endOfSet is going to be true by default.

Use of atFirst() or atLast()

   if not form.rowset.atFirst()
      form.rowset.next( -1 ) // navigate toward the top of the rowset
   endif

Back to the menu

CHANGE()
This really ties into CONVERT, and the _DBASELOCK field that is generated with the CONVERT command. The direct equivalent to the CHANGE() function is the method associated with the _DBASELOCK field (if it exists for a table) -- the method is called changed(). In addition, the lockfield object has properties that you would use the LKSYS() function in the XBase DML to obtain.

If a table has not been "CONVERTED", the _DBASELOCK field does not exist in the table. If your application is designed in such a way that you do not know if the table in use has this field, you should check to see if it exists first, as the normal field object does not have this property (this is a lockfield, not a normal field -- see lockfield in online help ...) -- note that this field, if it exists, is always the last field in the rowset:

   nLast = form.rowset.fields.size 
   if form.rowset.fields[ nLast ].className == "LOCKFIELD"
      ? form.rowset.fields[ nLast ].lock
      ? form.rowset.fields[ nLast ].update
      ? form.rowset.fields[ nLast ].user
   endif

If you know that the field exists in the table, you can make the code more simple. Remove the first two lines and the last line of the code shown above, and change "nLast" to "_DBASELOCK":

   ? form.rowset.fields[ "_DBASELOCK" ].lock
   ? form.rowset.fields[ "_DBASELOCK" ].update
   ? form.rowset.fields[ "_DBASELOCK" ].user

Some of this information is information that you would use the LKSYS() function in the XBase DML to find.

Back to the menu

CONVERT
There is no OODML equivalent to the CONVERT command. Keep in mind that this command requires EXCLUSIVE use of the table you need to convert.

In addition, the OODML has a special field type for the _DBASELOCK field that is created. See CHANGE above.

Back to the menu

COPY
The XBase DML command COPY is used to copy the rows of a table to another table. There are a variety of options to this command as well, which allow limiting the data copied.

The OODML method of handling this is to use the updateSet object, and the copy() method of that object.

Let's take a look at some examples. The first is copying one rowset or table to another. To do this, we must have a table or rowset that is the source -- i.e., what we are copying, and we must name a table or rowset as the destination, or the new table.

At it's simplest, we can do:

   u = new UpdateSet()
   u.source      := "FISH" 
   u.destination := "COPYFISH"
   u.copy()

This will copy the rows in the "FISH" table in the samples directory to "COPYFISH", creating a new table.

Not very exciting. We could do this as well with:

   COPY TABLE FISH TO COPYFISH

So, what's the big deal? Well ... among other things, we can create tables of different types. If you were using a local table, and wanted to copy that table to a BDE Aliased SQL server, you could:

   u = new UpdateSet()
   u.source      := "FISH"
   u.destination := ":myalias:Fish"
   u.copy()

This should create a new table in "myalias" (as it is defined in the BDE), and convert the table to that appropriate type. This is one way to get your table into an Interbase database, for example.

However, another, and potentially even more useful thing to note is that we can create a rowset from a query control, and copy just the rows that match whatever conditions we need. The example shown below uses the customer table in the MUGS sample, and assumes that you have the MUGS BDE Alias set in the BDE (which was installed there, but you may need to re-create it). It will copy only those rows that have the "STATE ID" containing a value of "CA" (California).

   d = new database()
   d.databaseName     := "MUGS"
   d.active           := true
   q = new query()
   q.database         := d // assign mugs database ...
   q.sql              := "select * from CUSTOMER"
   q.active           := true
   q.rowset.filter    := [:state id: = 'CA']
   q.rowset.indexName := "Last Name"

   u = new UpdateSet()
   u.source           := q.rowset
   u.destination      := "CACUST"
   u.copy()

   // cleanup
   q.active           := false
   d.active           := false

In this example, the new table will be created "locally" -- i.e., wherever you were currently working, as opposed to in the MUGS database. If you wanted to copy this to the MUGS database, you would have set the destination as ":MUGS:CACUST.DBF".

---

NOTE: You can use a query object's rowset for either or both of the source and destination rowsets. I.e.,

      q1 = new query()
      q1.sql = "select * from fish"
      q1.active = true

      q2. = new query()
      q2.sql = "select * from copyfish"
      q2.active = true

      u = new updateSet()
      u.source := q2.rowset
      u.destination := q1.rowset
      u.copy()

---

In the case of the copy method, you cannot create a rowset that does not exist, i.e.:

   q2 = new query()
   q2.sql = "select * from sometable" 

   [...skipped code...]
   u.destination := q2.rowset

Since this rowset does not exist, you will get an invalid reference error.

---

NOTE: Using the copy method will NOT copy the index tags of the original table. This is a serious drawback, and while I believe it is working "as designed", is a bit annoying. (See COPY TABLE if you need to copy the indexes as well ...)

---

You can also copy the fields from the current rowset by looping through the fields array -- the code below assumes two tables with the same structure:

   q1 = new query()
   q1.sql = "Select * from table1" 
   q1.active

   q2 = new query()
   q2.sql = "Select * from table1" 
   q2.active

   q2.rowset.beginAppend() // start a new row
   for i = 1 to q1.rowset.fields.size
       q2.rowset.fields[ i ].value := ;
          q1.rowset.fields[ i ].value
   next
   q2.rowset.save()

Note that the code above does not take into account autoincrement or _dBASELOCK fields, which will give some trouble (they are read-only -- you can simply test for them, there are examples of this elsewhere in this document -- and not do anything). In addition, assigning the value of a binary field cannot be done this way, you will need to use the field object's copyToFile() and replaceFromFile() methods.

Back to the menu

COPY BINARY
The XBase DML commands COPY BINARY and COPY MEMO are used to copy the contents of a binary field in a table to a file.

The OODML equivalent is to use the field object's copyToFile() method. The following example is taken from the online help:

   function exportMemoButton_onClick

     local cFile
     cFile = putfile( "Export memo", "*.txt" )
     if "" # cFile
       form.rowset.fields[ "Notes" ].copyToFile( cFile )
     endif

Back to the menu

COPY STRUCTURE
The COPY STRUCTURE command is useful to copy the field structure of a table to another table. There is no direct equivalent in the OODML for this, however, do not despair. There are ways to do this.

The simplest (and only one discussed here) is to use the database object's copyTable() method:

   _app.databases[1].copyTable( "OLDTABLE", "NEWTABLE" )
   _app.databases[1].emptyTable( "NEWTABLE" )   

The only problem with using the copyTable method shown above is that you cannot copy the table to a different table type. The advantage to this method over others, is that you do not have to worry about copying the index tags for the table, as this is handled automatically for you.

Back to the menu

COPY TABLE
The XBase DML COPY TABLE command has a direct equivalent in the OODML, which is a method of the database class -- copyTable().

Use of copyTable()

   _app.databases[1].copyTable( "OLDTABLE", "NEWTABLE" )

This method automatically copies .DBT files and index tags (.MDX files) if the table is a .DBF. The drawback to this is that you cannot change the table's type in the process of copying it.

Back to the menu

COPY TO ARRAY
This XBase DML command can copy a whole table to an array, except for memo fields. You can add a fields list, you can limit the rows to copy, etc.

To do the same sort of processing, the work is a bit larger, since the OODML does not include this functionality. However, it can be done.

One simple method (this would be hardcoded -- if you want to create your own copy2array routine that included all the XBase DML options, you are more than welcome -- let me know ...) is to simply create a small routine with a couple of for loops. The following example copy all rows and fields in a table to an array:

    nCols = form.rowset.fields.size
    nRows = form.rowset.count()
    aTable = new array( nRows, nCols )
    for i = 1 to nRows
       for j = 1 to nCols
           if form.rowset.fields.type $ "MEMO, OLE"
              aTable[i,j] = form.rowset.fields.type
           endif
           aTable[ i, j ] = form.rowset.fields[ j ].value
       next
    next

Note that if you then wanted to store that array's data back to the table, you would need to do something sort of opposite.

Back to the menu

COUNT
The XBase DML command COUNT is used to count the records in a table. You can add qualifiers (COUNT FOR ...) and so on. And you can use a COUNT ... TO ... to save the value to a memory variable.

The OODML equivalent is the rowset's count() or rowCount() methods.

For this to work with a filter you must set the filter first, rather than adding some sort of qualifier to the count method.

   form.rowset.count()

This will count all rows in the rowset. If you want to filter the rowset:

   form.rowset.filter = "some filter"
   form.rowset.count()
   form.rowset.filter = ""

The difference between count() and rowCount() is that rowCount() will take into account deleted records -- this may return values that are confusing ...

Back to the menu

DELETE
The XBase DML uses a "soft delete". The only advantage to using this is that it means that the records are left in the table, in case you need to recall them. Some developers have come to rely on using this, because all that really happens is a hidden logical field is used in the table, and some developers have written code to do a filter using the deleted flag. NOTE that this only occurs for .DBFs -- this is the only table format that uses the soft delete -- all other table formats actually delete a row when you specify that you really want to delete it.

The OODML does not recognize the soft delete, per se. When you issue a call to the delete() method of the rowset, the logical field does get set on a .DBF table, but the row disappears, no matter what the setting for SET DELETED.

There is no OODML equivalent for recall, and it is not possible to query the deleted flag using the deleted() function.

---

If you wish to use the BDE's API, you can actually work with the soft delete abilities of the XDML as if they were built-in to the OODML. Gary White has provided a form (SDQUERY.WFM) that does this, and the custom query is in the file SDQUERY.CC. Both of these files are in the source code .ZIP mentioned at the beginning of this document.

---

Usage

   form.rowset.delete()

Back to the menu

DELETE TABLE
The XBase DML command DELETE TABLE is used to remove a table and all it's associated files (.MDX, .DBT in the case of a .DBF, in the case of a .DB table, all the myriad assorted files that may be created).

The OODML equivalent is the database object's dropTable() method.

   _app.databases[1].dropTable( "tableName" )

Back to the menu

DELETE TAG
The XBase DML command DELETE Tag is used to remove an index tag from a table. Note that this is really a local SQL command, as opposed to strict XBase.

The OODML equivalent of this is the database object's dropIndex() method.

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

Back to the menu

EDIT
The XBase DML command EDIT is used to modify a record, but it is really just an IDE command, and not meant to be used in a deployed application. When used, it creates a quick form "on-the-fly" based on the layout of the table, and allows you to edit the record.

There is no direct OODML equivalent for this command.

However, when creating your own form, there is an XBase DML method that you can use. This is to use the rowset's beginEdit() method. However, if you do not have the rowset's autoEdit property set to "false", the rowset is always editable.

Use of beginEdit()

form.rowset.beginEdit()

  This command will enable any datalinked controls that are datalinked to the specific rowset (if you are using multiple rowsets on the same form, only the controls that are datalinked to this specific rowset will be enabled).

Items to consider:

• Navigation of any sort (moving the row pointer) will cause an automatic save of the new row.
• The rowset has a save() method which can be used to save the changes.
• The rowset has an abandon() method which can be used to abandon changes and the new row.

Notes:
You can use the canEdit() event to check specific situations or execute code before allowing the user to edit the row or to prevent the user from editing (this event fires before after you call beginEdit() and before the user is allowed to edit ...). You can use the onEdit() event which fires after the beginEdit() method is called.

Back to the menu

FIELD()
The XBase DML function FIELD() returns the name of the field in question (you pass a field number).

The field object in the OODML has a property: fieldName that is used for the same purpose:

   ? form.rowset.fields[ 1 ].fieldName

The fieldName property is read-only, unless you are creating a calculated field (at which time you assign the fieldname yourself).

Back to the menu

FLDCOUNT()
The FLDCOUNT() function returns the number of fields in a table.

To get the same functionality, use the size property of the fields array in the OODML.

   ? form.rowset.fields.size

Back to the menu

FLDLIST()
The FLDLIST() function returns the field names and/or calculated expressions for calculated fields by passing a field number.

To get the same functionality, you use the fieldName property of the specific field.

   ? form.rowset.fields[ 1 ].fieldName   

Back to the menu

FLENGTH()
The FLENGTH() function returns the width or size of the field.

To get the same functionality, you use the length property of the specific field.

   ? form.rowset.fields[ 1 ].length

Back to the menu

FLOCK(), LOCK(), RLOCK()
Locking rows and tables can be done in the XBase DML using the FLOCK() function (File Lock -- locks the table), or LOCK()/RLOCK() functions (Lock record or specified records in table).

The OODML equivalents are in the rowset object, and are called: lockSet() and lockRow(). These methods are, however, not normally necessary. Locking is normally automatic, and so these methods are seldom really necessary. In most cases you only want to ever lock a table or rowset for the duration of a specific action (such as editing a row, or packing a table) -- in those cases, you do not need to use these methods - dBASE will handle the locks for you.

However, if you have some scenario where you need to lock a table or row for more than just a short amount of time, you can use these methods ...

New to dB2K: isRowLocked() and isSetLocked() methods. These return a logical (true/false) value, but do not actually attempt to perform the locks, which the lockSet() and lockRow() methods do.

Back to the menu

FLUSH
The FLUSH command in XBase DML is used to force a write of all changes to the table(s) that are open.

You can flush changes to a table in the OODML with the rowset's flush() method:

   form.rowset.flush()

Note that if a disk cache is in use, the data is written to the disk cash, rather than to disk. The disk cache decides when to write to the phsyical hard disk in that case.

There is a related method of the rowset: refresh(). The refresh() method is really used to refresh() a rowset in memory, but any cached rows are purged, and the data is read directly from disk.

Back to the menu

FOR()
The FOR() function in XBase DML is used to return the "FOR" expression in an index tag.

There is no direct equivalent in the OODML language, but there is a forExpression property of the index. Getting to this information takes a bit of work, because the way to get there is through an undocumented feature -- the tableDef object (this is being documented for a future release of the online help).

A quick example of this is:

   t = new TableDef()
   t.tableName := "SomeTable"
   t.load() 

   ? t.indexes[ "indexname" ].forExpression
   // or
   ? t.indexes[ 1 ].forExpression

Back to the menu

GO/GOTO
The XBase DML navigation command GOTO does not truly have a direct equivalent in the OODML. I say this knowing that this is a direct contradiction to what is stated in the online help and Language Reference.

The reason I say this is that the OODML does not recognize Record Numbers (see RECNO()).

That said, you can use the rowset's bookMark() method to return a binary value that can be used to get to a specific row in the table, using the rowset's goto() method.

You could save the current row location with the bookMark, move to some other row in the table, do some processing, and return back to where you were, using code like:

   bSave = form.rowset.bookMark() // save location
   form.rowset.findKey( "some value" )
   // do some processing
   form.rowset.goto( bSave )      // back to original location

Problems with BookMarks()
BookMarks() are not very persistant -- if you change the rowset's indexName property, the bookmark you may have saved before doing so is now useless. This is due to the internal mechanics of bookmarks. You are better off using a unique value in the table if you must change indexes on the fly, and then use findKey() to find the row.

GO TOP or GO BOTTOM
After all that, there are direct equivalents to the XBase DML commands GO TOP or GO BOTTOM. These are the rowset object's first() and last() methods.

These are pretty straight-forward -- they navigate to the first or last rows in the table.

Note:
Navigation will automatically save any changes to the current row in the buffer, unless you issue a call to the abandon() method of the rowset.

Back to the menu

INDEX
The XBase DML command INDEX, with it's various options, is used to create indexes for your tables. With .DBF tables, it is used to create either .NDX files or .MDX tags.

There is no direct method of replacing this command in the OODML, however, there are INDEX objects, and the database object's createIndex() method. When used on a local table, the createIndex() method requires that the table be used exclusively. See SET EXCLUSIVE in this document ...

To Create an Index Tag for a .DBF
First, you must create an instance of the DBFINDEX object, assign the appropriate properties, and then use the database object's createIndex() method. To do this, follow these steps:

   i = new DBFIndex()
   i.indexName  := "name of tag" // or .NDX file
   i.expression := "expression to index on" 
   _app.databases[1].createIndex( "tableName", i )

This does nothing more than create an index tag that uses a standard expression, an index tag name, and then creates the index. "Tablename" must be the name of the table. If you are using a BDE Alias, and there is a database object for that, you can use the database object for that alias, otherwise you should use the default "current directory" alias as shown above. The letter 'i' refers to the index object. You could have named it anything.

To get more complicated, the DBFIndex object has descending, forExpression, and unique properties which match the appropriate INDEX ON command options.

To Create a .NDX File for a .DBF
This works almost exactly the same as the previous example, except that you add the type property:

   i = new DBFIndex()
   i.indexName  := "name of tag" // or .NDX file
   i.expression := "expression to index on" 
   i.type       := 1 // 0 = MDX, 1 = NDX
   _app.databases[1].createIndex( "tableName", i )

To Create an Index For Any Other Table Type
Besides the DBFIndex, there is also the index object, which is basically for use with any other type of table.

   i = new Index()
   i.indexName  := "name of tag" 
   i.fields     := "fields to index on" 
   _app.databases[1].createIndex( "tableName", i )

The index object has the following properties as well: caseSensitive, descending and unique.

Back to the menu

ISTABLE()
This function is used to determine if a table exists in a database. The OODML equivalent is the database object's tableExists() method.

   ? _app.databases[1].tableExists( "SomeTable" )

If you wanted to look for the CUSTOMER table in the MUGS alias, you could instantiate a database object that uses the MUGS BDE Alias, and then call tableExists():

   d = new Database()
   d.databaseName = "MUGS"
   d.active = true
   ? d.tableExists( "CUSTOMER" )
   d.active = false

Back to the menu

KEY()
The XBase DML function KEY() is used to show the index tag's expression. There is no direct equivalent, but if you use the undocumented TableDef object (see below) you can get this information. Note that the tableDef object is being documented for dB2K's Online Help ... so you will be able to find information on this useful object soon.

   t = new TableDef()
   t.tableName := "SomeTable"
   t.load() 

   ? t.indexes[ "indexname" ].expression
   // or
   ? t.indexes[ 1 ].expression

Back to the menu

KEYMATCH()
The XBase DML function KeyMatch() is used to check the contents of an index expression to see if a value is contained in that expression.

Gary White created some code that can emulate this functionality, but there is no direct OODML equivalent:

   function keyExists
      parameters cTable, cIndex, cKey
      local q, lFound
      q=new query()
      q.sql="select * from &cTable."
      q.active = true
      q.rowset.indexName = cIndex
      lFound = q.rowset.findKey( cKey )
      q.active = false
      release object q
      q=null
   return lFound

This function opens a table and searches for the expression, using the OODML objects, and returns a logical value if the key is found. To use it, you call the function like:

   ? keyExists( "MyTable", "IndexTagName", "Keytolookfor" )

Back to the menu

LDRIVER()
The XBase DML LDRIVER() function returns the name of the language driver that was used to create a table. There is no direct OODML method of handling this, but it is possible to find this information using the undocumented feature (Note that the tableDef object is being documented for dB2K's Online Help ... so you will be able to find information on this useful object soon.): tableDef.

   t = new TableDef()
   t.tableName := "MyTable"
   t.load()
   ? t.Language

New to dB2K: The rowset object now has a new property: languageDriver -- this is read-only, but can be used to query the language driver used to create the table:

   ? q.rowset.languageDriver

LOCATE
The XBase LOCATE and CONTINUE commands can be emulated with the OODML rowset object's beginLocate(), applyLocate(), and locateNearest() methods. In addition there is a property of the rowset called locateOptions that can be used to determine if the match must be case sensitive, and more.

There are a couple of ways you can use this feature. The first is to set up some code along the lines of:

   form.rowset.filterOptions := 3 // partial length and ignore case
   form.rowset.beginLocate()
   form.rowset.fields["first name"].value == "Smith"
   form.rowset.fields["end date"].value <= date()
   form.rowset.applyLocate()
   // this will find the first match, if any

There are some difficulties in using the locate options of the rowset. The most difficult one is the inability to use a fairly standard search option -- to find a string contained in a string. Another difficulty is that all conditions must be met ...you cannot say "if the first condition or the second condition is met, call that a match".

Another method of using these options are to create (rather easily) a "Locate By Form", which is done by setting a pushbutton's onClick to call:

   form.rowset.beginLocate()

And another to call the applyLocate(). When the beginLocate() method is activated, all datalinked controls on the form will clear, and the user is allowed to enter the options they wish to affect the locate. When the user clicks the applyLocate() button, the search will begin.

You could add a "locate next" pushbutton which would allow the user to decide that the first match found was not what they were looking for, and that the search should continue from where it is at to find the next possible match. This pushbutton's code would simply call the locateNext() method of the rowset.

There is more information on the locate options in the document Misc. Code Tips and Tricks HOW TO in the Knowledgebase.

Back to the menu

LOOKUP()
The LOOKUP() function in the XBase DML is used to lookup a key value in a lookup table and return the value of another field in the same table.

The online help is a bit misleading, in that it states that there is no direct equivalent. Actually, there is an equivalent (but it's not exact) -- the field object's lookupSQL() property.

There are some differences in functionality. The lookupSQL property actually returns a rowset (the field's lookupRowset) based on the criteria you give in the lookupSQL, and if you are using a form, it will automatically set the datasource of a combobox to the first non-keyfield field of the lookupRowset.

For this to work properly, you must select the code field in the lookup table as the first field (the type must match that of the field you will store it into), and then the field you want to display. If your lookup table is designed that way in the first place, you could simply use:

   select * from lookuptable

To see this in action, you may want to follow along with the following. (This example uses the Visual dBASE 7.x samples which are not installed for dB2K, so unless you still have 7.x on your computer, the example will not work exactly as given, but the concept should work for any BDE Alias/set of tables ...)

• Create a new form in the designer
• Go to the navigator and select the 'Tables' tab
• Use the combobox and select the MUGS database
• Drag the "Customer" table to the form
• Click on the query (SQL Icon), and go to the inspector
• In the inspector, select the rowset object and click the "I" (Inspect) button.
• You are now inspecting the rowset, you want to go to Fields, and click "I" again (you are now inspecting the fields array).
• At the top of the inspector should be your fields list. Click on "STATE ID" and click the "I" button one more time. You are now inspecting the Field object for that specific field.
• Select "lookupSQL" and enter:
  

  
          select * from state

  and press the Enter key.

If everything worked properly, you have just set up the lookup. To see this, drag several fields from the customer table over to the design surface of the form. Make sure you grab the state id field. When you drag the "STATE ID" field to the design surface -- notice that you get a COMBOBOX! This is automatic -- you do not have to do anything to make this happen.

---

WARNING: Do NOT set the dataSource for a combobox that uses the lookupSQL -- this is generated automatically for you by the lookupSQL property (and the underlying code for the lookupSQL), and you may have unpredictable results if you attempt it ...

---

If you run the form now, and scroll through the data, you will see the value in the combobox automatically update. This is done because of the lookupSQL property that was set for the STATE ID field.

There is one interesting side-effect. If you need to query the value of the STATE ID combobox (the one that appears on screen) or the field itself, you will get the value that appears in the combobox, NOT the value that is stored in the table!

What if you need to extract that value? Luckily, there is a way to get to it. The lookupSQL property automatically generates a lookupRowset for that field. The syntax to get to the real value of the field may appear strange, but it works:

   form.rowset.fields["STATE ID"].lookupRowset.fields[2].value

If you want to see what's really stored in the state id field while the form is running, this is how you would do it.

If your lookup table had more than two fields, and you wanted to look at an associated field that wasn't the one stored in the field, and wasn't the one displayed in the combobox, you can get to that by changing the '2' in the above statement to the appropriate field number, OR you can substitute the field name in quotes.

NOTE: There is more detail on this ability in the HOW TO Document on Misc. Code Tips and Tricks in the Knowledgebase.

Back to the menu

LUPDATE()
The LUPDATE() function tells you the last date a table was modified (updated). While there is no direct OODML equivalent, you can, using the file class (or low-level file functions), get to this information. You can also get the modified date using the array class:

   aTables = new array()
   aTables.dir( "*.DBF" )
   nRow = aTables.subscript( aTables.Scan( "SomeTableName" ), 1 )
   ? aTables[ nRow, 3 ]

Back to the menu

ON ERROR
While the ON ERROR command is not strictly speaking an XBase DML command, it is useful to note that there is another way of trapping errors -- the dB2K try/catch. Even more interesting is that this set of commands can be set specifically to catch data errors (DBEXCEPTION).

While this is being mentioned here, you should spend a bit of time in the online help examining the examples and the code there.

The basic syntax of TRY/CATCH is:

   TRY
      // some command or commands here
   CATCH( DBEXCEPTION D ) // catch a data exception
      msgbox( d.error )
   CATCH( EXCEPTION E )   // catch other exceptions
      msgbox( e.error )
   ENDTRY

There are other options, such as a FINALLY clause, and the ability to "THROW" an error yourself. See online help for details.

Back to the menu

OPEN DATABASE
This XBase DML command is superceded by the database object in dB2K.

The purpose is to connect to a database or to a BDE Alias, allowing access to the tables associated with it.

At it's most basic, you can do:

   d = new Database()
   d.databaseName := "MUGS"
   d.active       := true

If the database requires a login, you can set a loginString to send the userid and password through code, and there are tons of methods that can be called, some of which are discussed elsewhere in this document.

If you are using a BDE Alias for local tables, you will probably want to use a database object, and if you are accessing a SQL Server, you will be required to use a database object.

There is some more detail on this topic in the Misc. Code Tips and Tricks HOW TO in the Kowledgebase, as well as in the Developer's Guide that ships with dB2K and the online help system.

Back to the menu

ORDER()
The XBase DML function ORDER() returns the name of the current active index tag. This is easily reproduced in the OODML by using the rowset's indexName property:

   ? form.rowset.indexName

Back to the menu

PACK
The Xbase DML command "PACK" is used to remove deleted records in a .DBF table. This is based on the "soft delete".

While the OODML does not directly support "soft deletes", the PACK command does have an equivalent, the database object's packTable() method. This method works exactly like the XBase PACK command, including requiring EXCLUSIVE use of the table to perform the compression. See SET EXCLUSIVE in this document for details ...

The command is simply:

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

If you are using a BDE Alias, use the database object associated with that alias. If you use the above, the packTable method will attempt to find the table in the local directory.

Back to the menu

RECSIZE()
This XBase DML function is used to determine the size of the record in a table (in characters used).

While there is no direct OODML method of emulating this, it is simple enough to loop through the fields of a rowset and total the length property:

   nLength = 0
   for i = 1 to form.rowset.fields.size
       nLength += form.rowset.fields[ i ].length
   next
   ? nLength

Back to the menu

REFRESH
This XBase DML command updates the record buffers from the table. The OODML replacement works the same -- note that if you have not "flushed" the table, you could wipe out any changes if you issue this ...

The OODML version of this command is the rowset object's refresh() method.

   form.rowset.refresh()

This will re-read the data from the table on disk, and update the datalinked controls on the form. If you change the SQL for your query, you will want to use the requery() method of the query object, rather than the refresh() method of the rowset.

Back to the menu

REINDEX
The XBase DML command REINDEX was designed in the dBASE/DOS product to update index tags if necessary. In dB2K, due to the use of the BDE, this really wasn't necessary.

In dB2K the REINDEX command, and the OODML equivalent both actually fix broken index tags, as long as the definition of the index tags still exists in the table header.

The OODML equivalent to the reindex command is the database object's reindex() method.

_app.databases[1].reindex( "TableName" )

As with the XBase command, this method requires exclusive use of the table. See SET EXCLUSIVE elsewhere in this document for details.

Back to the menu

RELATION()
This XBase DML function returns the linking expression used with SET RELATION for related tables.

There is no direct OODML relationship, but if you are using local tables, the rowset object's masterFields and masterRowset property are what is used to link the two tables.

   ? form.child1.rowset.masterFields

See Code Tips and Tricks HOW TO in the KB for a bit more on this.

New to dB2K: the rowset has a new property masterChild which is used to determine if the child rowset is constrained (by default it is) or not. This means that when navigating the child rowset, is the endOfSet reached when you try to go past all rows that match the parent rowset? If constrained, this is what should occur. If unconstrained, then you can navigate from the top to the end of the child table, ignoring the parent/child link.

Back to the menu

REPLACE
The REPLACE command is the XBase DML method of programmatically assigning a value to a field, or by using the "ALL" option, of replacing the data for a specific field (or fields) in all rows of a table.

The OODML method of performing a replace is:

   form.rowset.fields["fieldName"].value := somevalue

If you do not save this, it will be stored in a record buffer, until either: an explicit call to form.rowset.save() is made; a navigation occurs in the table (next(), first(), last() ...), or the form (and/or query) is closed (deactivated).

There is no direct OODML method of performing a REPLACE ALL.

One solution, albeit a slow one, is to use a loop and loop through all the rows in a table.

A much faster solution is to use the local SQL UPDATE statement. The syntax is:

   UPDATE tablename ;
      SET fieldname=somevalue, fieldname2=someothervalue, etc.;
      WHERE somecondition

The "WHERE" clause is optional (just like the REPLACE statement in the XBase DML has an optional FOR clause).

An example might be a need to replace a logical field for all rows in a rowset with "false":

   UPDATE MyTable SET myLogicalField = false

If you needed to have a condition, let's say a date field based on some value:

   UPDATE MyTable SET myLogicalField = false;
          WHERE myDateField <= date()

Back to the menu

REPLACE BINARY
This command is related to COPY BINARY and COPY MEMO, but is used to put data into a binary field.

The OODML equivalent is the field object's replaceFromFile().

The following example is from the online help:

   function importImageButton_onClick

     local cFile
     cFile = getfile( "*.bmp", "Import mugshot image" )
     if "" # cFile
       form.rowset.fields[ "Mugshot" ].replaceFromFile( cFile )
     endif

Back to the menu

SCAN Loop
The XBase DML SCAN loop is a rather useful loop, in that it automatically moves the row pointer through a table from either the top or the current row toward the bottom.

There is no direct OODML equivalent to the SCAN loop. However, you can do this kind of processing easily enough with the rowset's properties and methods:

   form.rowset.first() // move to top of table
   do while not form.rowset.endOfSet
      // insert your processing here
      form.rowset.next() // next row in the rowset
   enddo

It's rather important part to remember the "form.rowset.next()" statement (endless loops are so annoying).

Back to the menu

SEEK/SEEK()
The SEEK command and SEEK() functions in the XBase DML have a single direct equivalent in the OODML: The rowset object's findKey() method.

To use this method, you need to have an indexName property set, and then you can use findKey to attempt to find the value. For example, if your user enters the last name you could use findKey to see if that last name exists in the table:

   cName = trim( upper( form.entryfield1.value ) )
   if form.rowset.findKey( cName )
      // do something
   else
      msgbox( "Could not find name " + cName )
   endif

---

Warning: findKey() and findKeyNearest() are navigation commands and will trigger the canNavigate event (if set), and will cause an implicit save. If they fail, you are left on the same record you started on, but any changes made to the record will have been saved.

---

   cName = trim( upper( form.entryfield1.value ) )
   form.rowset.findKeyNearest( cName )

If you have a grid displaying the table, what would happen is if there is an exact match, that will be displayed, otherwise the closest match will be found and displayed.

This particular method may have problems unless you can guarantee that you are working with a unique value in the field you are searching on.

Back to the menu

SET DELETED
Soft deletes are not directly supported in the OODML. Even if SET DELETED is OFF (the default is ON), a form will not display deleted rows. See DELETE above.

Back to the menu

SET CARRY
The XBase DML SET CARRY command is useful in that it allows the developer to specify which fields to "carry" from the current row to a new row (append command).

There is no direct OODML equivalent for this, but at the request of another developer some time ago, the author of this document created a routine that is in the dBASE Users' Function Library Project (dUFLP) in the Knowledgebase. The code is attached to a pushbutton in the file CUSTBUTT.CC, the pushbutton is called kmCarryPushButton. The code is shown below, but there are no guarantees it will work if you simply copy and paste it from here to another pushbutton's onClick event (this is some fairly complex code). The code below was updated in the dUFLP to have more functionality, so if you want the full code, you should check out the dUFLP version.

   function onClick
     // is there a rowset on the form?
     rType = this.form.rowset
     if ( TYPE( "rType" ) == "U" )
        msgbox( 'Cannot copy rows on a form without a rowset!',;
                "Can't do it!", 16 )
        return

     else
        // if so, check the rowset state -- if we
        // are editing/adding a row, we don't want 
        // to just close the form ...
        nState = this.form.rowset.state
        do case
           case nState == 2 or nState == 3
                msgbox( "Save edits/new row first", ;
                        "Can't copy row!", 16 )
           case nState == 4
                msgbox( "You are in filter mode",;
                        "Can't Abandon row!", 16 )
              otherwise

                // deal with field list (optional)
                if type( "this.Carry" ) == "C"
                   lList = true
                else
                   lList = false
                endif

                // array used to copy current record
                aCarry = new Array()
                // shorten the form.rowset.fields references:
                fFields = form.rowset.fields

                // copy current record
                for i = 1 to fFields.size
                    lAdd = true
                    /*
                       except for readonly, autoincrement or lock 
                       (_dbaseLock) fields 
                        NOTE: _dbaseLock fields don't have
                             type or readOnly properties,
                             so we have to make sure that they
                             exist before we check to see what
                             is in them ...
                    */
                    if ( type( "fFields[i].readOnly" ) # "U" and ;
                               fFields[i].readOnly ) or ;
                       ( type ( "fFields[i].type" ) # "U" and ;
                         "AUTOINC" $ fFields[i].type ) or ;
                       fFields[i].className == "LOCKFIELD"
                       lAdd = false
                    endif

                    // fields not in this.Carry if provided ...
                    if lList AND ;
                       NOT upper( fFields[i].fieldName ) $ ;
                           upper( this.Carry )
                       lAdd = false
                    endif

                    if not lAdd
                       aCarry.add( "* Skip This Field *" )
                    else
                       /*
                          if we have a lookupRowset, then
                          we have a lookupSQL statement, and
                          we need to ensure that we store the 
                          value that needs to be stored, NOT 
                          the value that will be returned by 
                          the lookupSQL ...
                       */
                       if fFields[i].lookupRowset # NULL
                          aCarry.add( fFields[i].lookupRowset.fields[2].value )
                       else
                          aCarry.add( fFields[i].value )
                       endif
                    endif // form.rowset.fields ...
                next

                // append a new record
                form.rowset.beginAppend()
            
                // -------------------------------
                // copy data from previous record:
                for i = 1 to fFields.size
                    if aCarry[i] == "* Skip This Field *"
                       loop
                    else
                       fFields[i].value := aCarry[i]
                    endif  // skip ...
                next

                // redisplay
                form.rowset.refreshControls()

                // let user know:
                msgbox( "Row is copied -- you are in append mode."+;
                        "Make changes and save or abandon new row.",;
                        "Copy complete", 64 )
        endcase
     endif
   return

A quick summary of the code shown above: there is a field list property that can be used to specify the fields, which is a custom property of the pushbutton. If this is empty, we copy all fields except for read-only, lock fields, and such. These are copied to an array, the rowset's beginAppend is called, and then the values in the array are stuffed into the appropriate fields in the new row in the rowset's buffer.

Back to the menu

SET EXACT
The XBase DML SET EXACT option is used to determine equality between two strings. This affects the SEEK and FILTER and LOCATE commands and options.

In the OODML, there is no direct equivalent, but if you are using filters and locates, there are rowset properties that can affect how your filters and locates act. These are the filterOptions and locateOptions properties of the rowset, which are defined more under SET FILTER and LOCATE.

New to dB2K: the rowset now has an exactMatch property, which is a logical (true/false) property. It defaults to true so that older code is not broken.

Back to the menu

SET EXCLUSIVE
The help file says that EXCLUSIVE is always OFF. However, in the OODML, database object methods such as packTable(), reindex(), and such require that the table be able to be accessed exlusively. If the table is already open in another query (the active property of the query is true), then these methods will fail.

The real difficulty is that in dB2K, while this is "as designed", if you attempt to open a table that is already opened exclusively, there are no trappable errors, and even more fun is that "USE tablename EXCLUSIVE" does not return an error either. Gary White has put together some code that will check to see if a table was successfully opened exclusively:

   function useExclusive
      // will tell you if a table CAN be opened exclusively
      // there are no guarantees that it will remain so if you do not act quickly
      parameter cTableName
      local bSuccess, bContinue

      bSuccess = true
      bContinue = true

      do
         try
            use &cTableName excl in select()
         catch ( exception e )
            // most likely error here is "File does not exist"
            // failure to get exclusive use will NOT be trapped here
            MsgBox( e.message, "Error: " + ltrim( str(e.code) ), 16 )
            return false
         endtry
         try
            // this one WILL generate an exception
            delete tag "myDummyXYZ"
            exit
         catch ( exception e )
            // error 53 is "Tag does not exist"
            bSuccess := e.code == 53
            if bSuccess
               exit
            endif
            if e.code == 110	// in use by another
               bContinue := MsgBox( "File is in use by another", "Alert", 48 + 5 ) == 4
            else              // some other error
               bContinue := MsgBox( e.message, "Error: " + ltrim( str(e.code) ), 16 + 5 ) == 4
            endif
            use
         endtry
      until bSuccess or (not bContinue)
   return bSuccess

This function actually uses the XBase DML in combination with a try/catch block to attempt to open the table exclusively. If it fails, then it returns an error ... (read the code for more details)

To use this to, say, pack a table using the OODML, you would call the function, and if it returned 'true', close the table (which is now open, exclusively) and then call the packTable method:

   if useExclusive( cTableName )
      use in &cTableName // close the table
      _app.databases.packTable( cTableName )
   endif

Back to the menu

SET FIELDS
The XBase DML SET FIELDS command is used to limit the fields displayed or accessed in a table.

The equivalent to this is not directly an OODML equivalent, but actually an SQL equivalent.

By default, when you select the fields in a table using a SQL select, you use the wildcard character (*) to specify all fields. You can limit the fields displayed by using a field list instead. For example, if you wish to use a query object, you could specify the sql property as shown below:

   q = new Query()
   q.sql = "SELECT field1, field2 FROM mytable"
   q.active = true

Back to the menu

SET FILTER
The XBase DML SET FILTER statement was how a lot of filtering got done. In the OODML, setting up filters has gotten interesting. And even more interesting is that if you examine the options, you will see, in the rowset object, several different ways to work with filters.

The biggest problem is that people often end up trying to combine these, and make their lives a lot more difficult. This may be because if you look up "SET FILTER" in the online help, it suggests that you use "applyFilter()/beginFilter()" which is not necessarily the way you want to do this.

To put it bluntly, it is not necessary, and actually sometimes will cause problems, to try to use the filter parameter with the beginFilter and/or applyFilter methods. Seriously ... If nothing else you will fry your own synapses.

So, what are these options, and how do they work?

The Filter Property
This is the one that I recommend you use when just programmatically setting your own filters. There are some drawbacks, which we'll examine, but this is really the best way to go in most cases.

The filter property takes a SQL filter -- i.e., something you might use in a WHERE clause, and more importantly, it MUST BE A STRING. (And should not contain dBASE functions!!!) Note that you can use the SQL "LIKE" clause in the filter property, but that the filter property is not complete in that you cannot use the SQL wildcard characters using the LIKE clause.

How, pray tell, do you end up filtering your table on dates, numerics, and logicals, then?

Take a look at the following:

Dates:

      form.rowset.filter = [MYDATEFIELD = '] + date() +[']

What is with the use of the square bracket characters? These are delimiters in dB2K -- ones that most people don't use, because the single and double quotes have always been "good enough". However, if you want to design a string that has quotes inside it, using the double and single quotes can get very difficult to read. Using the brackets makes it much easier to read.

What does this do? If today's date is: March 21, 1999 (which it is at the time I am writing this), your filter string would be converted to look like:

      [MYDATEFIELD ='March 21, 1999']

The BDE can understand this just fine, and when your table is examined, all should be well.

PROBLEM: SQL itself does not understand non-US date formats. This is not a BDE issue, nor is it a Visual dBASE issue ... This means you need to use the US/American date format of MM/DD/YYYY.

Numerics:
Numerics are just the same. You must convert them to strings (but don't panic -- dB2K does what is called "automatic casting" when working with character strings -- if you "add" a character string and a numeric, date, or logical you will not get errors, like you used to in earlier versions of dBASE -- instead, dB2K will convert that numeric, date, or logical value to a character string ...

      [MYNUMERICFIELD = '] + somenumber +[']

Should work fine.

Logicals:
Logicals must use the words TRUE or FALSE. That's the only big deal.

      [MYLOGICALFIELD = 'TRUE']

Filter By Form
This is a little-known ability to most dBASE developers, and most people haven't quite gotten their minds around it.

The idea is that you can create a form with all the datalinks you need, and allow the user to enter what they want to search for in those fields. (There is one drawback -- you cannot do "or" type filters this way -- ALL fields that have values in them must "match" or the filter does not work ...)

This is done by using two methods: beginFilter() and applyFilter(). The first method (beginFilter() ) clears out the controls on the form, allowing the user to enter what they need to do their filter. The applyFilter() method actually executes this filter.

The applyFilter() method returns a logical value if it found any matches or not -- true means it did, and false means it did not. So you could code something like:

   if form.rowset.applyFilter()
      // do something
   else
      msgbox( "No Match Found" )
   endif

Another method you should be aware, that was designed to work with these other two methods is clearFilter() -- which clears out the current settings.

As a side-note: clearFilter() sometimes works with the filter property, but it is not reliable there. To clear out the filter property, you should simply assign a blank string ("") to it ...

Using beginFilter()/applyFilter() Programmatically
It is possible to use these methods programmatically as well as in the Filter by Form option noted above.

For simple search, you can use applyFilter() with the appropriate criteria in the parens:

   form.rowset.applyFilter( form.rowset.fields["fieldname"].value = "something" )

The applyFilter method actually will store the necessary information into the filter property as a string:

   ? form.rowset.filter
     // should show:
   fieldname = "something"

You can augment the filter's property at this point by using:

   form.rowset.filter += [ AND Color = "Green"]

Do not forget to put a space after the open bracket and before the word "AND". (You can use the 'OR' operator here as well ...)

This can be unwieldy and if you wanted to do a more complex filter, this would not be the way to go.

You could do something like:

   form.rowset.beginFilter()
   form.rowset.fields["fieldname1"].value = "something"
   form.rowset.fields["fieldname2"].value => "something else"
   form.rowset.fields["somedate"].value   <= date()
   form.rowset.applyFilter()

Note however that all three of the conditions given above would have to evaluate to true.

The filterOptions Property
The filter in the rowset, whether you use the filter property, or the beginFilter/applyFilter methods, uses the filterOptions property of the rowset. This allows you to determine if the filter is case sensitive, length sensitive or a combination. There are four options:

   0 -- Match length and case // this is the default!
   1 -- Match partial length
   2 -- Ignore case
   3 -- Match partial length and ignore case

One thing to note or remind you of -- the filterOption property defaults to 0 - "Match length and case", and you may want to be sure you change that for your filter to be more flexible. It means an exact match must be found for the filter to return a true value. See online help for more details. These are rather useful.

Filtering on Substrings and Complex Filters
You cannot do a locate on a string contained in another string. The same problem exists in filters. In addition, creating complex filters, or filters that use dBASE functions are not easy with the OODML filter property and methods.

Luckily, the developers at Inprise realized this and came up with the canGetRow event of the rowset.

This event is used to evaluate each row in a rowset based on whatever criteria you give it, and will return a row only if it matches said criteria.

The canGetRow event belongs to the rowset, and can be used either with a codeblock:

   form.rowset.canGetRow = {|| return SomeLogicalExpression }

or it can be used as a function that returns a logical value (the example below assumes a date field where you want dates that fall between a specific range, perhaps determined by the user):

   function rowset_canGetRow
      // set your conditions here:
      lReturn = this.fields["DateField"].value => form.beginDate
      lReturn = lReturn and;
                this.fields["DateField"].value <= form.endDate
   return lReturn

SQL Select/WHERE
Before we leave the topic of filters, it is possible to do substring filters using the SQL Select statement used in your query's SQL property.

Using the WHERE clause of the SQL SELECT statement, you can use options such as "LIKE" -- which uses wildcard characters. An example might be to find all the names in a table that contain the letters "as" in any specific location. To do this, you would set your sql property to read:

   queryname.sql = [select * from customer where upper(customer."Last Name") like "%AS%"]

Notice that SQL can use its own UPPER() function. The "where" clause here is comparing the uppercase value of the "Last Name" field in the "customer" table (note the syntax -- if the field name does not have a space, you do not need the quotes -- but it is probably a good idea to use them anyway) against the value 'like "%AS%"'. What the "LIKE" option does is use wildcards. The "%" character is a wildcard, and when used at the beginning of a string, it says to match anything up to the literal value (in this case "AS"). When used at the end of a string, it means that the match must start with the literal value ("AS") and end with anything. When used in combination (as shown) it means any string that contains the literal value anywhere in it. A good book on SQL will show you all the permutations of wildcards.

More importantly, you may, as a developer, need to pass a filter value -- some string you need to limit the rowset to, usually as a parameter of some sort. To do this, you need to concatenate the filter string into the same sort of statement as above:

   queryname.sql = [select * from customer where upper(customer."Last Name") ]+;
                   [like "%]+cFilter+[%"]

One special caveat, Gary White, while testing this, found that while in a normal SELECT statement, you can (and sometimes should) place the table extension into the SELECT statement (i.e., "select * from customer.dbf"), when using the WHERE clause as we are here, for whatever reason, the statement will not work properly -- but if you leave off the extension, the exact same statement will function properly.

Back to the menu

SET INDEX/SET ORDER
The XBase DML has SET INDEX and SET ORDER, which have similar uses. However, while SET INDEX can open .NDX files, the OODML does not use these at all, and frankly doesn't know what they are.

The SET ORDER command's OODML equivalent is to use the rowset's indexName property. This is described elsewhere in this document ...

Back to the menu

SET KEY
In the XBase DML we have SET KEY, which is a very fast method of limiting the values shown in a table to those where a field fits within a range of values.

The OODML version of this is the rowset's setRange() method. This can be used to limit a rowset to a range, with a lower value and an optional upper value. The fun part is that you can specify any combination of lower value and upper value.

The important thing here is that setRange (like SET KEY) works off the current index expression. If the range you set does not match the expression, you will either receive an error, or it will not work ...

If you want to check for a date range where the dates must all match a specific date:

   form.rowset.setRange( {01/01/1998} )

This will limit your rowset to only the rows where the date field used in your index expression exactly match that date.

More useful, however, is the ability to provide a lower, upper or lower and upper range. The following examples show each of these. To start, let's provide a lower range:

   form.rowset.setRange( {01/01/1998}, NULL )

The use of "NULL" in the "upper" position is all that is necessary to tell dBASE to limit the rowset to only rows where the value in the date field are equal or greater than the date shown.

   form.rowset.setRange( NULL, {01/01/1998} )

In this example, only the rows where the value in the date field are less than or equal to the date shown will be displayed.

Finally, a range:

   form.rowset.setRange( {01/01/1998}, {10/31/1998} )

This will limit the dates to ones that are greater than or equal to the lower value, and less than or equal to the upper value.

You can use numeric, character, date, datetime, whatever values that provide a range, and that you use in your index.

One thing some developers have found to be very useful is using setRange() to do one part of a filter (it's very quick), and then using the filter property of the rowset to further limit the data. The combination can be quite flexible, fast and powerful.

Once you have a range set, it is useful to be able to clear it out. This is done simply by using the clearRange() method:

   form.rowset.clearRange()

Back to the menu

SET RELATION
The XBase DML uses SET RELATION to determine relationships between tables. The OODML in dB2K allows this, but it looks rather different (which is often where a lot of the confusion using this comes from).

If you are working with local tables, there are two things you need to know: masterRowset and masterFields. That's it. If you are working with SQL Server tables (Interbase, Oracle, etc.), you only need to know: masterSource.

An Example
If you wish to design a datamodule or a form (or a report) that uses multiple tables that are related, you need to know the relationship. A prime example is perhaps in an Invoice system -- you might want to have a form for entering Invoices. The invoice table would be the parent or primary table. A customer table would be linked in, but more importantly, a line-item table (each item for the invoice) would be necessary, and this would be a child or detail table.

The following is a step-by-step using the tables in the MUGS database alias that is set up with the BDE. If you do not have this alias set up, you may wish to go back and do this. This example uses tables that already exist, and actually there's a working datamodule already set up -- we want to do a step-by-step to do it ourselves ...

In the Navigator, click on the "Tables" tab. In the "Look In" combobox, select the alias for "MUGS". You should now see several tables.

Now click on the "Datamodule" tab. Double click the "Untitled" icon to create a new datamodule.

When the design surface appears, go back to the navigator and click on the "Tables" tab again.

Drag the "Invoice" and "LineItem" tables to the design surface (notice that a database object is brought over with the first table -- this is supposed to happen, since we are using a BDE Alias ...).

Click on the query for the "Invoice" (the Parent) table, and select "rowset" in the inspector. Click on the "I" button (Inspect), and you will see rowset properties. Click on the indexName property, and in the combobox select "Invoice ID" for the indexName.

Click on the query for the "LineItem" table, and select "rowset" in the inspector. Click on the "I" button. Click on the masterRowset property, and in the combobox select "invoice1". This has now told the designer that the master rowset (or parent) for this relationship is the Invoice rowset. We need to set the linking field, so select masterFields, and in the combobox select "Invoice ID".

At this point, we have a working one to many relationship. (In the datamodule it may not look like much, but it's true!) Save the datamodule and exit the designer (CTRL+W works for this -- give it a name like "TESTING" or something). We'll come back to this in a moment.

What about constraints? Well, believe it or not, it's all automatic. There is no need to tell dBASE that you cannot go past the last child row for a specific parent -- it will not allow you to. (See discussion on SET SKIP elsewhere in this document, if you need to do something like that.)

What about using SQL Server tables (Interbase, Oracle, etc.)? These are actually easier, if you can believe it. Most SQL Server tables handle relationships easier than dBASE tables. Rather than using the masterRowset and masterFields properties of the rowset for the child (LineItem) table, you would use the query object of the child table, and the masterSource property. When you point the masterSource at the parent table, everything else is done for you. However, that only works properly for SQL Server tables. If you are using local tables, you need to use the method described above.

Now, to see this datamodule in action, we'll create a very quick form:

Click on "Forms" in the navigator, and double click the "Untitled" icon to create a new form (if asked about the Wizard, select the Design button). With a new form on the screen, select the navigator, and select the "Datamodule" tab and drag the new datamodule to the design surface.

Notice that the field palette automatically has both tables showing, and the fields from both tables.

Drag some fields for the Invoice table to the design surface. However, let's use a grid for the line items. To do this, drag the grid onto the form, and set the datalink to the LineItem table. (You will need to play with the size of the form and the grid a bit.) You may want to set the columns that are displayed, but for the moment, leave the Invoice ID.

Save and run the form. When you navigate in the parent table, notice that the rows displayed in the grid change, and that they all have the correct Invoice ID. This is automatic ... If you were to add a new row to the Line Item table, it would automatically be assigned the Invoice ID for you.

Hopefully this will get you over any major hurdles you have getting started with this kind of relational tables scenario ...

Back to the menu

SET SAFETY
This command in the XBase DML is used, if set to "ON", to force a verification before causing dBASE to do some command that might cause irreparable damage to your table.

According to the online help, "SET SAFETY" is always OFF when using the OODML. However, there are situations apparently this is not true. The author has not run across any of these, but one of his editors has ...

Back to the menu

SET SKIP
The SET SKIP command in the XBase DML is used to tell dBASE which table is the controlling table in a situation where SET RELATION has been used.

As has been pointed out in the dB2K newsgroups, The XBase DML command SET SKIP is not completely covered in the OODML in dB2K. If you use the masterRowset/masterFields (for local tables -- for SQL Server tables the equivalent is the query's masterSource property), you will get the "standard" skip -- as you navigate through the parent table, the child table(s) will keep up.

New to dB2K: the rowset has a new property masterChild which is used to determine if the child rowset is constrained (by default it is) or not. This means that when navigating the child rowset, is the endOfSet reached when you try to go past all rows that match the parent rowset? If constrained, this is what should occur. If unconstrained, then you can navigate from the top to the end of the child table, ignoring the parent/child link.

However, some developers need to be able to do other things that SET SKIP allows (like navigating through the child table, and having the parent keep up, and/or being able to see records that have no matching parent or child ...) The OODML does not currently have any way to do these things. So, in some cases, you may be stuck using the XBase DML if you have a need to use this capability.

One solution to "skip" in the child rowset and have the parent keep up is to use the following code -- it assumes a "next" button on the form, and also assumes you have your masterRowset/masterFields (or masterSource) set properly:

   function nextButton_onClick
      if form.childquery.rowset.atLast() or;
         form.childquery.rowset.endOfSet

         form.rowset.next // move the parent row
      else
         form.childquery.rowset.next()
      endif

This would solve the problem explicitly mentioned above (code by Gary White).

Another solution is given in the online help for dB2K:

Override the next() method of the detail table. For example:

   function next( nArg )

     if argcount() < 1
        nArg := 1                           // Skip one row forward by default
     endif
     if not rowset::next( nArg )            // Navigate as far as specified, but
                                            //   if end of detail rowset
       this.masterRowset.next( sign( nArg ) ) // Move forward or backward in master
       if nArg < 0                          // If navigating backwards
         this.last()                        // Go to last matching detail row

       endif
     endif
   

Then navigate by calling next() in the detail rowset not the master rowset, as you would with SET SKIP.

There was a discussion (as noted earlier under this topic) in the dB2K newsgroups. One of the authors of the online help posted the following additional information on the topic:

"With SET SKIP, you SKIP in the parent. When you override the child's next(), you call next() in the child:

      // code to setup SET SKIP [XDML]
      select PARENT
      scan
         // do whatever
         // SCAN causes implicit SKIP in parent
      endscan

      // versus:

      // code to setup masterRowset, next() in child overriden
      // [OODML]
      parent.rowset.first()
      do 
         // whatever
      until not child.rowset.next() // navigate through child
   

"The overridden next() in the child (similar to the SET SKIP OODML example, but beefed up a bit) handles the navigation in the parent when it runs out of children for each parent."

[The following is a summary of a query by one of the developers in the newsgroup.] There is no method to browse through each work area [there is no concept of a "work area" using the OODML -- KM] and check to see if a match is working.

[Back to the quote from the "help author"] "If you set up a masterRowset relationship and navigate through the parent, the corresponding row in the child is chosen automatically, and you can check it -- the same as with SET RELATION -- although not with a BROWSE (becuase the BROWSE doesn't work on the query/rowset). You could check endOfSet in the child, or display some of its fields."

There was a datamodule in the newsgroup message that this was discussed in, but without a taking up even more space in this already very large document to try to put the datamodule code and an explanation together, it's not really worth it. A bit of experimentation can get you a long way with the OODML in dB2K ...

Back to the menu

SKIP
The XBase DML SKIP command is how we programmaticaly move through a table. The OODML equivalent is the rowset's next() method. This accepts a numeric parameter, which can be a positive or negative value.

   form.rowset.next()     // forward one row
   form.rowset.next( 5 )  // forward five rows
   form.rowset.next( -1 ) // back one row
   form.rowset.next( -5 ) // back five rows

You should note that no check is made automatically to see that you are attempting to move beyond the "endOfSet" -- you should check for this in your code ... (see BOF() elsewhere in this document).

In addition, as noted elsewhere, navigation of any sort will cause an automatic save to a buffered row -- i.e., if the user has made any changes in a form to datalinked controls, and you navigate, then the changes made will automatically be saved ...

Back to the menu

TAG(), TAGCOUNT(), TAGNO()
The XBase DML has a few functions used to return information about index tags. There is no direct equivalent shown in the online help files in the OODML for TAG(), TAGCOUNT() and TAGNO().

However, the tableDef object in dB2K allows you to view this information with very little effort.

   t = new TableDef()
   t.tableName := "mytable"
   t.load()

   ? t.indexes.size         // TAGCOUNT() equivalent
   ? t.indexes[1].indexName // TAG() equivalent

   // TAGNO() is a bit more work
   cTagName = "TAGNAME" //Tag Name to look for
   nTagNo = 0         
   for i = 1 to t.indexes.size
       if t.indexes[ i ].indexName == cTagName
          nTagNo = i
          exit
       endif
   next
   ? nTagNo

Back to the menu

TOTAL
The XBase DML TOTAL command creates a new table that contains totals for numeric fields in the original table you are totalling from.

There is no OODML equivalent, but you may wish to examine the local SQL SUM() (and other local SQL aggregate functions) which can be used with the SQL SELECT statement.

Back to the menu

UNIQUE()
The XBase DML UNIQUE() function determines if an index tag uses the UNIQUE option to ignore duplicate records for that specific index tag.

There is no direct OODML equivalent, but as with TAG() and other functions used to poll an index tag, it is possible to replicate it using the tableDef object.

   t = new TableDef()
   t.tableName := "mytable"
   t.load()

   ? t.indexes["indexName"].unique

Back to the menu

UNLOCK
The XBase DML UNLOCK command is designed to unlock any locks that have been explicitly set by the developer (LOCK(), FLOCK(), RLOCK() ).

The OODML equivalent is the rowset's unlock() method. It will release locks created either with the rowset.lock() or the rowset.lockSet() methods.

   form.rowset.unlock()

Back to the menu

USE
In XBASE DML, to open a table, you use the "USE" command.

In the OODML, things have changed rather dramatically. To "use" a table, you actually create a query object, and then set the appropriate information in the sql property of the query. This sounds like you need to know a lot about SQL -- you do not. Here is the simplest method of opening a table using the OODML:

   q = new Query()
   q.sql = "select * from tableName"
   q.active = true

The SQL property uses the SQL select command, to select, in this case, all fields (the wildcard "*" character is where you could limit the fields), and you must supply the name of the table.

If you are using the design surfaces, this gets easier. When you create a datamodule, a form, or a report, all you have to do is drag the table from the navigator to the design surface -- dBASE will set up the query for you, assuming you wish to use all fields in the table.

If you wish to set the sequence the table is displayed in (the index), you can use the SQL "ORDER BY" clause, but be warned that this usually will generate a read-only query, which except for reports is not desireable (this is a BDE issue, not a dB2K issue). Instead, simply use the rowset's indexName property to open an index tag.

   q = new Query()
   q.sql = "select * from tableName"
   q.active = true
   q.rowset.indexName = "myindextag"

Note that the last statement must come after the query is set to active. If you do not set it there, there is no active rowset, and while no error occurs, no indexName is set, either.

There are a lot of variations that can be done with the SQL property here, but these are covered in other parts of this document, and in other HOW TO documents.

Back to the menu

ZAP
The XBase DML ZAP command is used to empty out all records in a table.

The OODML equivalent is the database object's emptyTable() method. Note that just like the ZAP command, this method requires that the table be used exclusively. See SET EXCLUSIVE for details ...

Back to the menu

Summary

If you need help with the database classes, I recommend that you read Alan Katz' OODML.HOW, and the HOW TO document on working with the OODML objects ... these can be found in the Knowledgebase.

Special thanks to Gary White for helping out, both with readability and with some code samples.

---

DISCLAIMER: the author is an employee of dBASE, Inc., but has written this on his own time. If you have questions regarding this .HOW document, or about dB2K you can communicate directly with the author and dBVIPS in the appropriate newsgroups on the internet.

.HOW files are created as a free service by members of dBVIPS and dBASE, Inc. employees to help users learn to use dB2K more effectively. They are edited by both dBVIPS members and dBASE, Inc. Technical Support (to ensure quality). This .HOW file MAY NOT BE POSTED ELSEWHERE without the explicit permission of the author, who retains all rights to the document.

Copyright 2001, Kenneth J. Mayer. All rights reserved.

Information about dBASE, Inc. can be found at:

       http://www.dbase.com
    

---

EoHT: X2OODML.HTM -- January 31, 2001 -- KJM