The ResultSet Class

Caché provides interfaces for performing both pre-defined or dynamically-defined queries. For access to this interface from an ActiveX client, use an ActiveX ResultSet object. A ResultSet object allows to you to set up a query, execute it, and parse its results.

As part of an ActiveX client, the ResultSet object exists independently; on the Caché server, a ResultSet is a class member — just as an object or a property is a class member. Each ActiveX ResultSet object is tied to a particular query — one defined either dynamically or in a specific Caché class.

The Caché ResultSet object supports the following members:

Close, closes a ResultSet object after it has been executed.
ContainsID, determines whether a query contains an ID.
Execute, executes the query referenced by the ResultSet object.
Get, uses the name of the column to return the data value for the specified column.
GetColumnCount, returns the number of columns in the ResultSet object.
GetColumnHeader, returns the description of the column in the specified position.
GetColumnName, returns the name of a specified column.
GetData, returns the data value for the specified column.
GetDataAsString, returns the value of a specified column in string format.
GetDataByName, uses the name of the column to return the data value for the specified column.
GetParamCount, returns the number of possible parameters for the query.
GetParamName, returns the name of a specified parameter.
IsDataNull, determines if the specified column has been defined.
Next, advances to the next row of data.
Prepare, specifies a dynamic SQL query to use instead of a class query.
SetParam, sets the value of input parameters required by the query.
SetRunTimeMode, sets the runtime mode of the query.

Important:

The default SQL runtime mode for CacheActiveX.dll is ODBC. If your application uses the older CacheObject.dll, the runtime mode would be Logical (see Upgrading from CacheObject.dll and the SetRunTimeMode() method).

Close()

Closes a result set after you have executed the query that created it.

Boolean Close()

This method does not free the result set's memory. If you wish to execute a statement multiple times, you must close the ResultSet object between pair of calls to the Execute() method, making the sequence of calls something like:

Prepare 'prior to the first Execute call
Execute
Close
Execute
Close

Returns:

a Boolean true if the result set was successfully closed, or false otherwise.

Example:

res.Close
' res no longer contains the data returned by the query.
' If the ResultSet object is no longer to be used, 
' its memory can be freed with:
Set res = Nothing

ContainsID()

Determines whether the current query returns an ID and, if so, which column contains it.

Integer ContainsID()

The method returns the number of the ID column or 0 if the ResultSet does not contain an ID column; since column numbers start at 1, a returned non-zero value is that of the column number exactly. For information on how a pre-defined query holds information on its ID column, see the section About CONTAINID in Using Caché Objects.

Note:

It is possible to manually specify a non-existent or incorrect column as the value of the column containing the ID and this method will retrieve that value.

Returns:

an Integer

Example:

' This puts a message up saying whether or not
' the query contains an ID field.
MsgBox "ContainsID = " & res.ContainsID

Execute()

Executes the query defined by the ResultSet object. It passes any arguments specified to the query.

Boolean Execute([param0, param1, ... , paramN])

Parameters:

param1,...,paramN — up to 16 optional parameters passed to the query on the server in the order specified. These parameters are identified as param0 (zero) through param15, inclusive

Returns:

a Boolean true if the query executed successfully, or false otherwise.

Example:

' First, declare and prepare the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")

' Now run the ByName query. This query returns all 
' people whose names begin with the letter specified.
res.Execute("A")

Get()

Returns the value of the data stored in a column of the current row of the ResultSet, where that column is specified by its name.

Variant Get(String ColumnName)

While certain situations require the use of the Get() method, the GetData() method — which uses the column number rather than column name — is faster.

Note:

This is the equivalent of the GetDataByName() method and supersedes it.

Parameter:

ColumnName — the name of the column from the ResultSet.

Returns:

a Variant containing the value of the data.

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

While res.Next()
  ' places all the matching names in a listbox
  lstMyList.AddItem res.Get("Name")
Wend

GetColumnCount()

Returns the number of columns in the result set.

Integer GetColumnCount()

Returns:

an Integer indicating the number of columns in the result set.

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Get the number of columns in the result set.
For i = 1 To res.GetColumnCount
    ' For each column, display its name in a listbox.
    lstColumns.AddItem res.GetColumnName(i)
Next

GetColumnHeader()

Returns a description of the data contained in the specified column of a pre-defined query.

String GetColumnHeader(Integer ColumnNumber)

Note:

Not implemented in CacheActiveX

This method is only implemented in the older CacheObject.dll (see “Upgrading from CacheObject.dll”), not in CacheActiveX.dll. CacheActiveX will always return the column (property) name, ignoring what has been defined in the ROWSPEC optionalDescription for the column. For example, if a class query has a ROWSPEC that defines a new column header like:

"prop1:%String:MyHeader,prop2,prop3",

then CacheActiveX will return "prop1" instead of "MyHeader".

If no description exists (and for dynamically defined queries, they never exist), GetColumnHeader() returns the name of the specified column. For information on setting up a pre-defined query's column headers, see the About ROWSPEC section of the queries chapter of Using Caché Objects.

Parameter:

ColumnNumber — the numeric position of the data in the ResultSet. Column numbers start with 1.

Returns:

a String containing a description of the data.

Example:

Warning: this example only works when using the older CacheObject.dll (see note above).

' Declare, prepare, and run the query.
Dim res As CacheObject.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Get the number of columns in the result set.
For i = 1 To res.GetColumnCount
    ' For each column, display its name and header
    ' in a listbox as "Name - Header".
    lstColumns.AddItem res.GetColumnName(i) & " - " & res.GetColumnHeader(i)
Next

GetColumnName()

Returns an identifier associated with a specified column.

String GetColumnName(Integer ColumnNumber)

The behavior of this method depends on whether the ResultSet object was generated from a pre-defined query (using Factory.ResultSet()) or a dynamically generated query (using Factory.DynamicSQL()). For pre-defined queries, it returns the name of the property stored in the column; for dynamically defined queries, it returns either the name of the property stored in the column or, if defined, the alias of the property, as specified in an AS clause of the query's SELECT statement.

Parameter:

ColumnNumber — the numeric position of the data in the ResultSet. Column numbers start with 1.

Returns:

a String

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Get the number of columns in the result set.
For i = 1 To res.GetColumnCount
    ' For each column, display its name in a listbox.
    lstColumns.AddItem res.GetColumnName(i)
Next

GetData()

Returns the value of the data stored in a column (specified by its number) from the current row of the ResultSet.

Variant GetData(Integer ColumnNumber)

Parameter:

ColumnNumber — the numeric position of the data in the ResultSet. Column numbers start with 1.

Returns:

a Variant containing the value of the data.

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Retrieve the data from the specified column
While res.Next
    lstMyList.AddItem res.GetData(4)
Wend

GetDataAsString()

Returns the value of the data stored in a column (specified by name) from the current row of the ResultSet, converted to a string.

String GetDataAsString(Integer ColumnNumber)

It is similar to the GetData() method, except that it performs a type conversion.

Parameter:

ColumnNumber — the numeric position of the data in the ResultSet. Column numbers start with 1.

Returns:

a String

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Retrieve the data from the specified column
While res.Next
    lstMyList.AddItem res.GetDataAsString(4)
Wend

GetDataByName()

Note:

The GetDataByName() method has been superseded by the Get() method.

Returns the value of the data stored in a column of the current row of the ResultSet, where that column is specified by its name.

Variant GetDataByName(String ColumnName)

While certain situations require the use of the GetDataByName() (or Get()) methods, the GetData() method — which uses the column number rather than column name — is faster.

Parameter:

ColumnName — name of the column.

Returns:

a Variant containing the value of the data.

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Retrieve the data from the specified column
While res.Next
    lstMyList.AddItem res.GetDataByName("Name")
Wend

GetParamCount()

Returns the maximum number of arguments a specified query expects to be passed.

Integer GetParamCount()

Returns:

an Integer

Example:

' Declare and prepare the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")

' Find out how many parameters the query uses and display it in a textbox.
txtParamCount.Text = res.GetParamCount

GetParamName()

Returns the name of the argument specified.

String GetParamName(Integer ParamNumber)

For example, if you want to know the name of the first argument, pass 1 as an argument to GetParamName().

Parameter:

ParamNumber — number of the parameter to be returned.

Returns:

a String

Example:

' Declare and prepare the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")

' Display each query parameter in a listbox.
For i = 1 To res.GetParamCount
    lstParams.AddItem res.GetParamName(i)
Next

IsDataNull()

Checks if a particular column in the current row of the ResultSet contains a SQL NULL value.

Boolean IsDataNull(Integer ColumnNumber)

Parameter:

ColumnNumber — the numeric position of the data in the ResultSet, where column numbers start at 1.

Returns:

a Boolean true if:

There is no data in the column. That is, the column is non-existent or the data in the column is the empty string (specified by ObjectScript code on the server such as Set person.DOB="" ).
The data in the column is $c(0) and its datatype is not String.

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Check if each row of the ResultSet has NULL value in column 4.
While res.Next
    lstMyList.AddItem res.IsDataNull(4)
Wend

Next()

Advances the ResultSet cursor to the next row of data.

Boolean Next()

Next() must be called to move to the first row of data before calling Get() or GetData().

Returns:

a Boolean true if the move succeeds and the now-current row of data exists, or false if no more data exists.

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Iterate through the ResultSet.
While res.Next
    ' All data processing occurs here; 
    ' see Get, all GetData..., and IsDataNull methods for examples.
Wend

Prepare()

Specifies a dynamic SQL statement (or statements) to use as the query, instead of specifying a class query.

This method returns a status, which should be checked before proceeding.

The queries can contain parameters represented by ? characters within the query. The values of any parameters are supplied via the Execute() method.

SetParam()

Sets the value of input parameters for the query.

Sub SetParam(Integer Index, Variant Value)

Parameters:

Index — represents the position of the parameter in the list of parameters. Parameter index numbers start with 1.
Value — the value to pass through to the query for that parameter

Example:

' Declare and prepare the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")

Call res.SetParam(1, "F")

' Run the query, since it already has the necessary parameter(s).
res.Execute

SetRunTimeMode()

Sets the SQL runtime mode for the query to be executed.

Sub SetRunTimeMode(Integer mode)

This method is typically used with GetDataAsString(), also of the ResultSet class, because this influences how the string conversion happens.

For more information on the various modes, see the section Data Formats and Translation Methods in Using Caché Objects.

Parameter:

mode — an Integer that represents one of the available modes:
- 0 for LOGICAL mode
- 1 for ODBC mode (similar to the system datatypes' LogicalToODBC() method)
- 2 for DISPLAY mode (similar to the system datatypes' LogicalToDisplay() method)

Example:

' Declare, prepare, and run the query.
Dim res As CacheActiveX.ResultSet
Set res = factory.ResultSet("Person","ByName")
res.Execute("A")

' Iterate through the ResultSet.
While res.Next
    res.SetRunTimeMode 0
    
    ' processing based on having data in logical mode
Wend