Using Result Sets

The EnsLib.SQL.GatewayResultSetOpens in a new tab class represents a special-purpose result set for use in Ensemble. An initialized instance of this class has a live data connection to a data source. The class provides methods to examine the contents of the result set as well as a method to return a static snapshot.

This chapter describes how to use the EnsLib.SQL.GatewayResultSetOpens in a new tab class. It discusses the following:

How to create and initialize a result set
How to get basic information about the result set
How to navigate the result set, row by row
How to get information about the current row of the result set

Note that you can also get a snapshot that contains rows from the result set; see “Using Snapshots.”

Creating and Initializing a Result Set

To create and initialize an Ensemble SQL result set:

Within an SQL adapter (either EnsLib.SQL.InboundAdapterOpens in a new tab or EnsLib.SQL.OutboundAdapterOpens in a new tab), connect to a DSN.
Use the ExecuteQuery() or the ExecuteQueryParmArray() method of the adapter. You will receive, by reference, an instance of EnsLib.SQL.GatewayResultSetOpens in a new tab.

Note:

If you just use the %New() class method, you can create a result set, but it will not be initialized and cannot contain any data. To initialize the result set, use the procedure described here.

Getting Basic Information about the Result Set

The following properties of EnsLib.SQL.GatewayResultSetOpens in a new tab provide basic information about a result set:

The ColCount property indicates the number of columns in the result set.
The QueryStatement property indicates the query statement used by this result set.

Navigating the Result Set

A result set consists of rows of data. You can use the following methods to navigate through the rows:

Next()

method Next(ByRef pSC As %Status) returns %Integer

Advances the cursor to the next row and caches the row data. Returns 0 if the cursor is at the end of the result set.

SkipNext()

method SkipNext(ByRef pSC As %Status) returns %Integer

Advances the cursor to the next row. Returns 0 if the cursor is at the end of the result set.

Examining the Current Row of the Result Set

Use the following methods to examine the current row of the result set:

Get()

method Get(pName As %String) returns %String

Returns the value of the column that has the name pName, in the current row.

GetData()

method GetData(pColumn As %Integer) returns %String

Returns the value of the column whose position is specified by pColumn in the current row.

GetColumnName()

method GetColumnName(pColumn As %Integer = 0)

Returns the name of the column whose position is specified by pColumn.

Note:

If the source data contains any unnamed columns, the result set automatically provides names for these columns in the following form: xCol_n