Let us write a very simple support procedure to handle the data access for one new ProDataSet. You will use this ProDataSet in other examples in this chapter.
To show something different from Orders and OrderLines, and to provide a basis for data caching and data sharing between procedures, the sample ProDataSet is a set of independent tables that have coded values of one kind or another. Within the limited confines of the Sports2000 database, likely candidates are the State table, the Department table, and the SalesRep table. These are all limited enough in scope that the tables can be completely populated when the ProDataSet is first filled.
Here is the dsCodeTT.i include file, with the temp-table definitions:
/* dsCodeTT.i -- caching object for various code tables */
DEFINE TEMP-TABLE ttSalesRep NO-UNDO
FIELD RepCode AS CHARACTER FORMAT "x(4)"
FIELD RepName AS CHARACTER FORMAT "x(20)"
FIELD Region AS CHARACTER FORMAT "x(12)"
FIELD AnnualQuota AS DECIMAL
FIELD TotalBalance AS DECIMAL
INDEX RepCode IS UNIQUE RepCode.
DEFINE TEMP-TABLE ttDept LIKE Department.
DEFINE TEMP-TABLE ttState LIKE State.
You can see that the ttSalesRep table is different from the database table it is derived from. It maps some database fields to different names in the temp-table and generates two calculated fields as well.
Here is the dsCode.i include file with the ProDataSet definition:
/* dsCode.i -- caching object for the temp-tables in dsCodeTT.i */
DEFINE DATASET dsCode FOR ttSalesRep, ttState, ttDept.
This could not be any simpler. There are no relationships between the tables, so the ProDataSet definition only needs to list them.
Now let us start to build the data access support procedure that defines the Data-Sources and handles the FILL logic. First, it defines the Data-Sources, which are also quite simple, as each one names just a single database table the ProDataSet temp-table is derived from. Thus, there is no need for query definitions. The AVM can generate the queries it needs for loading data automatically, as shown:
/* CodeSource.p -- Data-Source definitions and FILL logic for code tables */
DEFINE DATA-SOURCE srcRep FOR SalesRep.
DEFINE DATA-SOURCE srcDept FOR Department.
DEFINE DATA-SOURCE srcState FOR State.
The other thing to note about the top of the procedure is that it does not include the temp-table or ProDataSet definitions. The ProDataSet is always referenced by its handle alone, which means that anything about the ProDataSet definition can change without requiring even a recompile of this procedure. The only dependencies are the specific fields that the procedures must map or reference for calculations. Even in those cases, the code that maps or references them could be made conditional so that it would not fail if the fields were removed from the ProDataSet, or this procedure was used with a version of the ProDataSet that did not have them.
