Tip: How To Deal with A Lot of Columns in Database Tables

Most of the time, the access to our databases tables involves a small amount of columns (fields), so the use of the Fieldmethod of the RecordSetclass is the most practical approach to retrieve the selected columns values. But what can we do when the query returns, for example, dozens of columns we need to get values from?

In these cases, the use of the Fieldmethod to access every column is not the most practical. Imagine having to write as many lines of code as the number of fields you need to get the values from! Imagine having to do this on every query again and again. The better solution is in the Xojo framework itself: iterating the record fields with IdxField.

Unlike when using Field, where we have to use as a parameter the column name whose value we want to retrieve, when using IdxFieldwe use as a parameter the Index number of the column whose value we want to retrieve (or set a new value to). This is an important difference when compared with other framework classes relying on indexes, IdxFielduses the value one (1) for the first column of the table and not zero. Of course, the maximum value will be the total amount of columns on the record from the queryed table.

However, our table schemes probably have more columns that the ones containing the real data we are interested in.

Some examples of this could be the idcolumn and others whose values are only there for internaluse and have no meaning to the user of our solution.

So, when designing this kind of database table, with a large amount of columns, it is always a good idea to spend a bit of time organizing, so these columns get grouped at the beginning and / or the end of the table definition. This way, all the columns we are interested in iterating will be grouped in a sequential order, without gaps or jumpsin between. For example:

id			// Column 0; we won't include this one in our iteration
control_fld		// column 1; we aren't interested in this one, either
data1_fld		// first of the columns we want to iterate over
data2_fld		// second column we want to iterate
.
.
.
dataN_fld		// last of the columns we want to include in our iteration
date			// last column of the table; we are not interested in this one

Thus, if the total number of columns in our hypothetical example table is 100, then the valid indexes would be in the range 2-99.

With this in mind, how can we know how many columns a table has (specially if this one has been created based on runtime conditions)? That’s very easy: using the FieldCountmethod against a valid returned RecordSetfrom the query.

This way, and supposing we have a valid database instance named db, and a table named test, the following snippet of code would allow us to access all the columns to get their values as Strings:

Dim rc as RecordSet = db.SqlSelect("Select * from test")
If rc <> Nil and rc.RecordCount <> 0 Then
  Dim s as String
  While not rc.EOF
    For n as Integer = 2 to rc.FieldCount - 1
      s = rc.IdxField(n).StringValue
    Next
    rc.MoveNext
  Wend
End If

However, in other more practical cases you probably will want to get the column names too, so you can display them in a ListBox or just take different actions based on the name (field) for every record of the RecordSet.

If this is the case, you can access the Nameproperty on the RecordSetwe got, then iterate with IdxField. For example, this code snippet will store the name of every iterated field into a variable declared as String:

Dim rc as RecordSet = db.SqlSelect("Select * from test")
If rc <> Nil and rc.RecordCount <> 0 Then
  Dim s, s1 as String
  While not rc.EOF
    For n as Integer = 2 to rc.FieldCount - 1
      s = rc.IdxField(n).StringValue
      s1 = rc.IdxField(n).Name  
    Next
    rc.MoveNext
  Wend
End If

As you can see, this is a very practical approach to retrieve the values of every field in every RecordSet record and that you can use with all kind of Xojo projects and targets.