Most of the time, the access to our databases tables involves a small amount of columns (fields), so the use of the
Field method of the
RecordSet class 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
Field method 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
Unlike when using
Field, where we have to use as a parameter the column name whose value we want to retrieve, when using
IdxField we 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,
IdxField uses 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
id column and others whose values are only there for internal use 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 jumps in 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
FieldCount method against a valid returned
RecordSet from 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
Name property on the
RecordSet we 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.
Javier Rodriguez has been the Xojo Spanish Evangelist since 2008, he’s also a Developer, Consultant and Trainer who has be using Xojo since 1998. He manages AprendeXojo.com and is the developer behind the GuancheMOS plug-in for Xojo Developers, Markdown Parser for Xojo, HTMLColorizer for Xojo and the Snippery app, among others.