Class Reference
%SQL.IResultSet
Server:basexml
Instance:SOAXML
User:UnknownUser
  
-
  [BASEXML] >  [%SQL] >  [IResultSet]
Private  Storage

abstract class %SQL.IResultSet extends %SQL.StatementResult

A result set can be returned by executing a SELECT statement or a CALL statement.

A result set is a type of statement result (%SQL.StatementResult). A result set is a collection of rows, all of the same ROWTYPE. The ROWTYPE is described by the result set's metadata. The result set metadata can be retrieved by calling %GetMetadata. The OREF returned by %GetMetadata references an instance of %SQL.StatementMetadata.

A result set has an iterator that is initially positioned before the first row. The iterator is repositioned by calling %Next. %Next returns one (1) if the iterator is positioned at the next row or zero (0) indicating that the iterator is positioned after the last row.

Column values from the current row can be accessed by column name by calling %Get or by column position by calling %GetData. 

	SAMPLES>set result = ##class(%SQL.Statement).%ExecDirect(,"select Name, SSN, DOB from Sample.Person where Age < 50")
	SAMPLES>write result.%SQLCODE
	0
	SAMPLES>write result.%Next()
	1
	SAMPLES>write result.%Get("Name")
	Zevon,Patricia L.
	SAMPLES>write result.%GetData(1)
	Zevon,Patricia L.
	SAMPLES>write result.Name
	Zevon,Patricia L.
Column values can also be accessed by property name. The name of the property corresponding to a column is based on the column name. If the column name is not a valid object identifier then it is transformed into a valid object identifier. The column can still be accessed as a property through dynamic dispatch. If there are questions about the property name that corresponds to a particular column then the statement can define an alias for the column that is a valid object identifier.

If the result set is the result of executing a statement prepared with %ObjectSelectMode = 1 then any column whose type class is a swizzleable class is bound to a swizzleable property.

		set tStatement = ##class(%SQL.Statement).%New()
		set tStatement.%ObjectSelectMode = 1
		set tSC = tStatement.%Prepare("select %ID as ID, Spouse as Spouse from Sample.Person where Spouse IS NOT NULL")
		set tResult = tStatement.%Execute()
		if 'tResult.%SQLCODE {
			if tResult.%Next() {
				write "Person ID: ",tResult.ID.%Id()," Name: ",tResult.ID.Name," Spouse Name: ",tResult.Spouse.Name,!
			}
		}

Executing this code in the SAMPLES namespace generates output similar to: 

	Person ID: 101 Name: Donaldson,Nataliya J. Spouse Name: Grabscheid,Dan L.

The number of rows returned from a result set is available in %ROWCOUNT.

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
10 13


Summary

Properties
%CurrentResult %Message %Metadata %OutputColumnCount
%PrivateTables %ROWCOUNT %ROWID %ResultColumnCount
%SQLCODE %StatementType %StatementTypeName

Methods
%%OIDGet %ClassIsLatestVersion %ClassName %ConstructClone
%DispatchClassMethod %DispatchGetModified %DispatchMethod %DispatchSetModified
%DispatchSetMultidimProperty %Display %DisplayFormatted %Extends
%Get %GetData %GetMetadata %GetParameter
%IsA %IsModified %MoreResults %New
%Next %NextOutputValue %NextResult %NormalizeObject
%ObjectModified %OriginalNamespace %PackageName %Print
%SerializeObject %SetModified %ValidateObject getSelectMode

Subclasses
%SQL.CSVResultSet %SQL.ClassQueryResultSet %SQL.CustomResultSet
%SQL.Gateway.JDBCResultSet %SQL.Gateway.ODBCResultSet %SQL.ISelectResult

Properties

• property %Metadata as %Library.String(MAXLEN="") [ MultiDimensional ];
The rowtype metadata. This is the metadata string serialized for transport to clients. Its structure is based on the value of the public variable '%protocol' at the time the result set is instantiated. Metadata can be swizzled by calling the %GetMetadata.
• property %PrivateTables as array of %XSQL.DS.TempTable;
%PrivateTables is an array of private temporary table orefs used by this result set subscripted by temp table name.

Methods

• method %DispatchMethod(pMethod As %String, Args...)
Is used to implement an unknown method call. It is also used to resolve an unknown multidimensional property reference (to get the value of a property) because that syntax is identical to a method call.
• method %Display(pDelimiter As %String = $Char(9))
Display the contents of this object on the current device
• method %DisplayFormatted(pFormat As %String = -1, ByRef pFileName As %String(MAXLEN="")="", ByRef pMessages As %SQL.Manager.Messages = "", ByRef pFilesUsed As %String(MAXLEN="")=0)

Display the contents of the result object. If formatted display is available then format the results using the requested format and, if appropriate, open the formatted results using the host OS. The output is directed to one or more files and messages are placed in a result set object. All file names used are returned in an array.


Parameters


NameDescription
pFormat

The format applied to the result content. This parameter is also used to determine the file name extension.

Supported formats are:
-1%Display() format
0XML
1HTML
2PDF (requires a renderer such as FOP)
99TXT

If pFormat is specified as any number not listed above then it will default to TXT.

pFormat can also be specified as XML, HTML, PDF or TXT.
pFileName

The base file name to be used to generate actual file names used for output. If no value is specified then a file name will be generated, using the TEMP folder defined for the Cache instance. This value is not expected to include an extension. An extension is added to this value to form the actual file used. Also, if nested results exist then a number is appended to the file name specified to produce a unique name for each result.

pMessages

Instance of a system result set class. If no value is passed then the system message result class is instantiated. This parameter is passed by reference. It is up to the caller to process the result set oref that is returned. pMessages.Count() returns the number of messages contained in the result set. pMessages.%Display() will display the messages on the current device. pMessages.%DisplayFormatted() is also implemented and can be used to display the messages using the selected format.

pFilesUsed

This pass-by-reference parameter will contain the number of files used to display the result content and the name of each file. pFilesUsed is the number of files and pFilesUsed(file_number) is the name of the file. The sequence of the files is the same sequence as the results are processed. For simple result objects, there is a single file. For context objects that can contain result set sequences, the results are output in the order they are returned and the files used are present in pFilesUsed in that same order.


• abstract method %Get(colname As %String = "") as %Library.String
Returns the value of the column with the name name in the current row of the result set.

If name is not a valid column name, this method returns an empty string.

• abstract method %GetData(colnbr As %Integer = 0) as %Library.String
Returns the value of column colnbr in the current row of the result set.
• method %GetMetadata() as %SQL.StatementMetadata
Return the result set metadata as a %SQL.StatementMetadata instance.
• method %Print(pDelimiter As %String = " ") as %Status
%Print()

Write the current row to the current device. An optional column delimiter can be specified. No formatting is applied other than writing pDelimiter between column values. This can be useful for exporting result set data to tab delimited files.

For example:

	set sql = "select name,home_street,home_city,home_state,home_zip from sample.person"
	set rset = ##class(%ResultSet.SQL).%Prepare(.sql,.err,"")
	if '$Isobject(err) {
		set file="c:\temp\names.asv"
		open file:"WNS"
		use file while rset.%Next() { do rset.%Print($char(9)) } 
		close file
	}

Example data:
Cornell,Ezra M.340 Franklin CourtSarasotaNY12866
Huff,Stephen A.548 Washington BlvdBrownfield CenterMD53436
Sands,Jeep Q.7298 Washington DriveXavierWY23685

• method getSelectMode() as %Library.String