*-----Scaling for C/S----------------------by Paul Mahar----------- Ed: Paul Mahar is a test engineer on the Visual dBASE development team, but he's probably better known as the author of one of the best books to date on Visual dBASE, "Visual dBASE 5.5 Unleashed" (See "Resources" later in this issue).... This is the first in a planned series of articles on writing your VdB apps so that they can be converted to .db tables or scaled with the least effort possible to Client/Server (SQL). Scalable Deleting For the most part, deleting records from a .db (Paradox) or SQL table is like deleting records from a .dbf table when "Deleted" is set ON. However it is not exactly the same and if you switch table formats you've got to accomodate the difference. Pointers in the .dbf... When you delete a record from a .dbf table, the record pointer stays on that record. Visual dBASE does not hide the record until you move the pointer. The only exception is when you delete records from the Table Records Window or a Browse control. In that case, the deleted record disappears immediately and you move to the next record. Deletes on .db and SQL tables work the same way as Deletes in a Browse. Consider the following SCAN loop. It moves through a table processing some records and deleting others. It works fine on .dbf tables. Use orders.dbf Scan If amt_paid < total ***** process invoice Else Delete Endif Endscan Pointers in .db or SQL... If you use the same logic on a .db or SQL table, you'll end up skipping over every record that follows a Delete. Delete will move the record pointer forward once and Scan will move it once again. You can compensate for this auto-movement with an old-style "Do While .not. eof()" logic: Use orders.db Go Top Do While .not. Eof() If :amount paid: < :total invoice: *** process invoice skip Else Delete && implied skip Endif Enddo The behavior of Delete is not the only "gotcha" that you have to be aware of when migrating to DB or SQL tables. In future articles I plan to provide you with more information on how to avoid common pitfalls as you design your applications to work with .db and SQL tables. *------Unleashed-------------------------------by-Paul Mahar------- Ed: Paul Mahar is a test engineer on the Visual dBASE development team, but he's probably better known as the author of one of the best books to date on Visual dBASE, "Visual dBASE 5.5 Unleashed" (See "Resources" later in this issue).... This is the another in a series of articles on writing your VdB apps so that they can be converted to .db tables or scaled with the least effort possible to Client/Server (SQL). Migrating Indexes to DB and SQL Tables One of the major differences between .DBF tables and other types is the way indexes work. DB indexes are similar to SQL indexes. The dBASE data manipulation commands are identical when using indexes with DB and SQL tables. One of the unique characteristics of DBF tables is expression indexes. Although the most common use of expression indexes is to combine one or more fields into a single key, you can include any valid dBASE expression as part of a DBF index key. DB index keys, on the other hand, can be a single field or a combination of fields but not an expression. Expressions and Lists... The first index of a DB table is the primary key. This determines the default order for the table. Once a primary key exists, you can no longer view the table in a natural order. Once you've established the primary key, you can create and use additional index keys. If the additional key includes a single field, the syntax for DB tables is the same as DBF tables. Once you go beyond a single field, the syntax changes from an expression to a list. For instance, if you have a table with Firstname and Lastname, you can create an index on a DBF using: INDEX ON Lastname + Firstname TAG Fullname To create the same type of index on DB or SQL tables the command is: INDEX ON Lastname, Firstname TAG Fullname For a DB or SQL table, the INDEX command accepts a comma delimited field list. One advantage of using a list is that you do not have to worry about combining fields of different types. Lists also work with the SEEK command. To lookup Bob Smith, you can use: SEEK "Smith", "Bob" If Found() * do something Endif dBASE uses only as many values as appear in the SEEK list to find the record. That is, if you only include the "Smith", you will locate any record with a Lastname of Smith. This works even when the current index key contains both Lastname and Firstname. Note that, unlike the SEEK command, you can only specify a single column in the SEEK() function. If you try to enter a second value, it acts as a table alias. Same View, Different Results... If you change your table type in an existing application pay special attention to EXACT and to lookups that use multiple fields. If EXACT is OFF, SEEK("Smith") locates Bob Smith with any table type containing a Fullname index. When it is ON, SEEK("Smith") does not find the record in a DBF, but it does in DB and SQL tables. When using a DBF, the SEEK() looks at the entire index expression to find a match. One unexpected consequence of moving to the list approach can be seen when linking tables with the Query Designer. Here is some code that creates a different result set depending on the table type. This is a simplified version of a QBE file. SET EXACT ON && Always in QBE code USE customer && Contains Firstname and Lastname USE address ORDER TAG Fullname IN 2 SET RELATION TO Lastname INTO child SET FILTER TO FOUND(2) && Similar to SEEK(Lastname,2) COUNT && Always 0 for DBF tables If you are using DBF tables, the result is an empty set. If you're using DB or SQL, data will be found. Change the SET EXACT ON to SET EXACT OFF and the result set is the same for any table type. Concluding Tip For maximum scalability, avoid DBF-specific syntax as you design and refine your applications. For instance, only use expression indexes for combining fields. Complex expressions such as User Defined Functions will not convert to other table types.