ChartDirector 6.0 (ASP/COM/VB Edition)

Reading Data from Databases


This section provides some code templates on how to read database tables into array variables. Once the data is in array variables, they can be used with the ChartDirector API.

The examples assume RecordSet objects are used to represent the result of database queries. The RecordSet object is part of the Microsoft Advanced Data Objects (ADO) framework.

This examples may be useful even if you are not using ADO for database queries, as they can be readily modified to use other kind of database objects.

Number of Rows Known In Advance

The simplest case is that the number of rows returned from the database query is known. For example, you are querying a database for the monthly rainfall and temperature in a year, and it is known that the database will return 12 rows for the 12 months of the year.

The code will be something like the followings:

'
'Two columns are read from the table into array variables col0 and col1.
'rs is the ADO RecordSet object
'noOfRows is the number of rows expected
'
Dim col0(noOfRows - 1)
Dim col1(noOfRows - 1)
i = 0
Do While Not rs.EOF
    col0(i) = rs.fields(0).Value
    col1(i) = rs.fields(1).Value
    i = i + 1
    If i >= noOfRows Then Exit Do
    rs.MoveNext
Loop

Note: If the database returns less than the expected number of records (eg. only the first few months for the current year), the missing months will have zero values. For certain chart applications, you may want to replace those zero values with NoValue by initializing the col0 and col1 with NoValue first.

Number of Rows Unknown

If the number of rows is not known, one common method is to declare bigger arrays that can handle the worst case, then use ReDim to trim the arrays after reading the rows.

'
'Two columns are read from the table into array variables col0 and col1.
'rs is the ADO RecordSet object
'maxNoOfRows is the maximum number of rows expected
'
ReDim col0(maxNoOfRows - 1)
ReDim col1(maxNoOfRows - 1)
i = 0
Do While Not rs.EOF
    col0(i) = rs.fields(0).Value
    col1(i) = rs.fields(1).Value
    i = i + 1
    If i >= maxNoOfRows Then Exit Do
    rs.MoveNext
Loop
ReDim Preserve col0(i - 1)
ReDim Preserve col1(i - 1)

Note that the maxNoOfRows should be a reasonable number and not a very large number (not one million). If you database query can return one million rows, you may consider to modify your query to avoid your query consuming too much CPU and memory resources.

If you do not know the worst case, it is a good programming practice to set a reasonable worst case to avoid runaway queries (queries that produces millions of rows). A good suggestion is 10000, as this allows much more than enough data points for a chart, and the memory it consumes is small in a modern computer (only around 100K for 0.1 second only).

Using an Index Column

Suppose we want to query a database for the monthly rainfall and temperature in a year. In theory, the database query should return 12 rows for the 12 months of the year, except for the current year which may not have full 12 months of data. The sample code in the earlier sections can handle this nicely.

What happens if the database is incomplete and may miss some records, or have some duplicated records? For example, what if the row for April of a particular year is missing, while the remaining 11 rows are there? What if there are two rows for April for the same year?

One simply way to made the code more robust and can handle this sort of database errors is to use an index column when reading the rows into the arrays. The index column specifies the position in the array where a row should be read into.

For example, suppose a database contains 3 columns - TimeStamp, RainFall, Temperature. We can construct a query like the followings:

Select Month(TimeStamp) - 1, RainFall, Temperature from MyDB Where Year(TimeStamp) = 2000

Note that in addition to RainFall and Temperature, we also retrieve an extra column "Month(TimeStamp) - 1". The value of this column will be 0 for January, 1 for February, 2 for March, etc. If we use this column as the array index, then the January row will be put in array element 0, February in element 1, March in element 2, etc, which is correct.

'
'Three columns are read from the table. The 1st column is used as the index
'column. The 2nd and 3rd columns are read into array variables col0 and col1.
'
'rs is the ADO RecordSet object
'noOfRows is the number of rows expected
'
Dim col0(noOfRows - 1)
Dim col1(noOfRows - 1)
Do While Not rs.EOF
    col0(rs.fields(0).Value) = rs.fields(1).Value
    col1(rs.fields(0).Value) = rs.fields(2).Value
    rs.MoveNext
Loop

The key features of the index column approach are:

Using the DBTable utility

In the previous sections, we illustrates several approaches to reading database queries into arrays. They demonstrates the principles involved. ChartDirector actually comes with a utility class DBTable that can turn the RecordSet into arrays in one step.

For example, the following code reads data from the record set to arrays. The array length will equal to the actual number of rows in the record set.

'
'rs is the ADO RecordSet object
'cd is the ChartDirector.API object
'
Set dbTable = cd.DBTable(rs)
col0 = dbTable.getCol(0)
col1 = dbTable.getCol(1)

The following code reads data from the record set to arrays. The array length will equal to the given noOfRows regardless of the actual number of rows in the record set.

'
'rs is the RecordSet object
'cd is the ChartDirector.API object
'noOfRows is the number of rows expected
'
Set dbTable = cd.DBTable(rs, -1, noOfRows)
col0 = dbTable.getCol(0)
col1 = dbTable.getCol(1)

The following code reads data from the record set to arrays using the first column as the index column. The array length will equal to the given noOfRows regardless of the actual number of rows in the record set.

'
'rs is the ADO RecordSet object
'cd is the ChartDirector.API object
'noOfRows is the number of rows expected
'
Set dbTable = cd.DBTable(rs, 0, noOfRows)
col0 = dbTable.getCol(1)
col1 = dbTable.getCol(2)