DBTable(recordset[, indexCol[, noOfRows ]])

Creates a DBTable object.

The DBTable constructor accepts a Microsoft ADO RecordSet object. It will read all the rows from the RecordSet object into an internal table.

By default, the order of the rows in the internal table will be the same as the order of the rows read from the RecordSet object.

The order of the rows can be modified by specifying one of the columns as the index column. In this case, a row read from the RecordSet will be put in row n of the internal table, where n is the value of the index column of that row. (Note: the first row is n = 0). With an index column, there is no need to sort the records in the RecordSet.

By default, the number of rows in the internal table will be the same as the number of rows read from the RecordSet object. If an index column is used, the number of rows in the internal table will be determined by the largest value of the index.

The number of rows in the internal table can be set manually by using the noOfRows argument. In this case, if there are insufficient rows in the RecordSet, the extra rows will be set to empty. If there are too many rows, the extra rows will be discarded.

Using an index column and manually setting the number of rows are useful for applications where the rows in the database may not contain missing or duplicated rows.

For example, suppose we want to obtain the monthly data for the 12 months of a particular year to plot a bar chart. Ideally, the database query should retrieve 12 rows, representing Jan to Dec in that order.

In practice, abnormalities may happen. For example, the database may contain duplicated records for the same month, may have a missing month, or may not contain the full 12 months of data for the latest year.

In the above example, the SQL query can be modified to have an additional column containing the month number of the month (Jan = 0, Feb = 1, ... Dec = 11). This column can be obtained easily by applying SQL functions to the date/time column. (The exact syntax depends on the brand of database.) If this column is used as the index column, Jan will be row 0 and Dec will be row 11.

Furthermore, if the number of rows are set to 12, the table will always contain 12 rows for the 12 months. If there are missing months, corresponding rows will be empty. If there are two duplicated rows for the same month, the second row will simply overwrite the first row at the same row number.

So the result will be exact 12 rows with no duplication for the 12 months. Missing rows will be handled as empty rows (data value set to 0). This structure is ideal for plotting charts (e.g. bar charts).

The DBTable object created.