Opening tables and views Professional

Tables are opened in traditional Xbase programming style with the USE command. This is also possible when tables are managed by a SQL DBMS and are accessed via the ODBCDBE. However, there is a major difference between Xbase tables and SQL tables that require some kind of paradigm shift in database programming. The reason for this is given by the fact that Xbase tables are file-based data sources (DBF files) while SQL database tables are non-file-based data sources. For example:

USE Customer ALIAS Cust NEW   // assume: VIA "DBFNTX" 

USE Customer ALIAS Cust NEW   // assume: VIA "ODBCDBE" 

The first USE command would open a file CUSTOMER.DBF, it would require a file handle, and customer data would become accessible in a work area.

The second USE command does not differ from the first one, except that it is addressed to the ODBCDBE. This DatabaseEngine does not open a file, but instructs the SQL DBMS it is connected to via a DacSession object to prepare a resulting data set including all records of the Customer table. This leads to an important difference to the traditional Xbase programming approach: USE requests the server to transfer data to the client. The effect is the same as with a DBF table: data becomes accessible in a work area on the client side of an application and a work area is used.

When data is managed by a SQL DBMS, the USE command submits a request to the server. In its simpliest form, USE can be programmed in the traditional Xbase way by specifying the table name. However, it has an extended functionality in that SQL SELECT statements can be submitted. This instructs the server to prepare a subset of records and/or table columns (fields). For example, this is legal code when using the ODBCDBE:

cSQL := "SELECT LastName, FirstName, Phone FROM Customer;" 
USE (cSQL) ALIAS Cust NEW 
Browse() 

This SQL SELECT statement requests the server to prepare a data set of the Customer table including only three columns, or fields. It is coded as a character string which is passed to USE. Note the semicolon at the end of the string: it must be included as the last character and marks the end of the SQL SELECT statement.

The result of this code is that only a limited amount of customer data is transferred to the client application where it becomes accessible in a work area. As a matter of fact, the function FCount() would return 3 (since only 3 table columns are requested to be available in the work area), although the Customer table, as it is defined on the server side, may have multiple other columns storing data for State, Zip, City, Address etc. In turn, the Browse() function, which displays all fields (columns) of a used work area, would list only three columns.

The nature of SQL DBMS allows a server to expose tabular data not only in form of a single database table but also in form of a View. A view may span across several individual tables but can be displayed like a single database table maintained by a DBMS. As a result, the symbolic name of a view can be passed to the USE command in order to display the data defined as a view on the server side (DBMS).

Conclusion

The USE command has an extended functionality when it is processed by the ODBCDBE:

USE "single table name" 
USE "SQL SELECT statement;" 
USE "View defined on DBMS" 

The result is alway the same: a request is submitted from the client application to the DBMS (server side). The DBMS transfers requested data to the client where tabular data becomes accessible in a used work area.