Recipe 21.2 Reading Recordsets

21.2.1 Problem

You want to read the data from a recordset.

21.2.2 Solution

Use the RecordSet.getItemAt( ) method to get a record at a particular index. Use RecordSet.getColumnNames( ) to get an array of the column names.

21.2.3 Discussion

You can think of recordsets as being composed of rows and columns, like a grid. Each row represents a single record, and each column represents a field within each record. All recordsets have a getItemAt( ) method that returns a record object at a given row index.

#include "NetServices.as"

// Create a recordset and populate it using addItem(  ) (see Recipe 21.1).
rs = new RecordSet(["COL0", "COL1", "COL2"]);
rs.addItem({COL0: "a", COL1: "b", COL2: "c"});
rs.addItem({COL0: "d", COL1: "e", COL2: "f"});
rs.addItem({COL0: "g", COL1: "h", COL2: "i"});

// Get a single record from the recordset using getItemAt(  ).
record1 = rs.getItemAt(1);

// Output the values from the record object. Displays: d e f
trace(record1.COL0 + " " + record1.COL1 + " " + record1.COL2);

The getItemAt( ) method is all you need, as long as you know the column names. However, if you do not already know the column names (remember, recordsets are often retrieved from the server), use the getColumnNames( ) method to get an array of the recordset's column names. You can then use that array to loop through all the columns of a given recordset.

// Get a record from the recordset.
record1 = rs.getItemAt(1);

// Retrieve the column names.
columnNames = rs.getColumnNames(  );

/* Loop through all the columns and output the values for the record. Outputs:
   COL0: d
   COL1: e
   COL2: f
*/
for (var i = 0; i < columnNames.length; i++) {
  trace(columnNames[i] + ": " + record1[columnNames[i]]);
}

You can also use the RecordSet.getLength( ) method to determine the number of records that a recordset contains. Using this value, you can loop through all the records.

// Get the column names.
columnNames = rs.getColumnNames(  );

// Loop through each record in the recordset from 0 to the length, as returned by
// getLength(  ).
for (var i = 0; i < rs.getLength(  ); i++) {

  // Display the record number.
  trace("record " + i);

  /* Loop through each column for each record and output the values. Displays:
     record 0
       COL0: a
       COL1: b
       COL2: c
     record 1
       COL0: d
       COL1: e
       COL2: f
     record 2
       COL0: g
       COL1: h
       COL2: i
  */
  for (var j = 0; j < columnNames.length; j++) {
    trace("  " + columnNames[j] + ": " + rs.getItemAt(i)[columnNames[j]]);
  }
}

You can create a custom method that allows you to write the recordset data to the Output window during testing. The RecordSet.trace( ) method, as shown in the following code block, uses the same logic as the preceding code:

RecordSet.prototype.trace = function (  ) {
  var columnNames = this.getColumnNames(  );
  for (var i = 0; i < this.getLength(  ); i++) {
    trace("record " + i);
    for (var j = 0; j < columnNames.length; j++) {
     trace("  " + columnNames[j] + ": " + this.getItemAt(i)[columnNames[j]]);
    }
  }
};
// Example usage (if rs contains a recordset):
rs.trace(  );