Class %Library.ResultSet

class %Library.ResultSet extends %AbstractResultSet

Cache Dynamic SQL is the preferred mechanism for preparing and executing dynamic SQL statements. Please refer to %SQL.Statement for more information. %Library.ResultSet continues to be used and supported.

The %ResultSet class provides a way to use the results of class queries from within a Caché ObjectScript application. It is similar in operation to the ResultSet objects provided with the ActiveX and Java bindings.

You can use a %ResultSet object as follows:

Note you can bind a %ResultSet object to a query by either a) setting the ClassName and QueryName properties or b) passing a string containing the class name and query name (separated by a :) to the %New method:

	; Display the results of the Person class' ByName query to the console.
		set rs=##class(%ResultSet).%New()
		set rs.ClassName="Sample.Person"
		set rs.QueryName="ByName"
		; Alternatively, you can bind the result set object to a query
	; set rs=##class(%ResultSet).%New("Sample.Person:ByName")
		set sc=rs.Execute("a")  If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
		while rs.%Next() { do rs.%Print() }

Dynamic SQL: You can use the %ResultSet class to execute dynamic SQL queries using the system-provided %DynamicQuery:SQL query. In this case, use the Prepare method to supply the text of the query. For example:

	Set result=##class(%ResultSet).%New("%DynamicQuery:SQL")
	Set sc=result.Prepare("SELECT %ID, Name, Salary FROM Sample.Employee WHERE Salary > ?")
	If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
	Set sc=result.Execute(10000) If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
	While result.Next(.sc) {
		If $$$ISERR(sc) Quit
		Write result.Data("Name"),result.Data("Salary"),!
	}
	If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit

Dynamic SQL queries are cached in the same query cache as used by Caché ODBC and JDBC. This means that repeated calls to the same dynamic SQL query do not incur any additional query preparation and optimization overhead. You can view and manage this cache using the Caché SQL Manager.

Inventory

Parameters	Properties	Methods	Queries	Indices	ForeignKeys	Triggers
	19	42

Summary

Properties
%Message	%Metadata	%PrivateTables	%ROWCOUNT
%ROWID	%ResultColumnCount	%SQLCODE	ClassName
Data	QueryName	RuntimeMode

Methods
%%OIDGet	%AddToSaveSet	%ClassIsLatestVersion	%ClassName
%CloseCursor	%ConstructClone	%CreateSnapshot	%DispatchClassMethod
%DispatchGetModified	%DispatchGetProperty	%DispatchMethod	%DispatchSetModified
%DispatchSetMultidimProperty	%Display	%DisplayFormatted	%Execute
%Extends	%Get	%GetData	%GetMetadata
%GetParameter	%IsA	%IsModified	%MetadataGet
%New	%Next	%NormalizeObject	%ObjectModified
%ObjectsGet	%OriginalNamespace	%PackageName	%Prepare
%PrepareMetaData	%Print	%RemoveFromSaveSet	%ResultColumnCountGet
%SendDelimitedRows	%SendODBC	%SerializeObject	%SetModified
%ValidateObject	ClassNameSet	Close	ContainsId
Execute	Get	GetColumnCount	GetColumnExtInfo
GetColumnHeader	GetColumnName	GetColumnType	GetData
GetDataByName	GetExtent	GetODBCInfo	GetObject
GetParamCount	GetParamName	GetStatementType	Next
Prepare	QueryIsValid	RunQuery	RuntimeModeGet
RuntimeModeSet	SetIdInfo	Test

Subclasses
%Library.ScrollableResultSet	%TSQL.ResultSet	%XML.DataSet
%ZEN.Report.ResultSet

Properties

• property %Metadata as %String [ MultiDimensional ];

xDBC metadata - for compatibility with new cached result sets.

• property ClassName as %CacheString;

The name of the class containing the query to run.

• property Data as %String [ MultiDimensional ];

Used to store the data returned from the resultset by column name. This can be accessed directly for better performance than the Get and GetDataByName methods. For example:
	; This slower code
	While result.Next() {
		Write result.Get("Name"),result.Get("Salary"),!
	}
	; Becomes this faster code
	While result.Next() {
		Write $get(result.Data("Name")),$get(result.Data("Salary")),!
	}
Note that because this 'Data' property is multidimensional, if there is no such column name as 'Salary' you will get an UNDEFINED error without the $get around it. If there are two columns with the same name in the result set then the second one will be the one referenced by the 'Data' property. If you need to refer to both of them, use the GetData and give the position of the column you want.

• property QueryName as %CacheString;

The name of the query to run.

• property RuntimeMode as %String;

Use this property to set the SQL runtime mode for the query to be executed. Setting the runtime mode for this ResultSet does not permanently change the $zu(115,5) value. Possible values mode are:

0 for LOGICAL mode.

1 for ODBC mode.

2 for DISPLAY mode.

"" to use the process wide $zu(115,5) value.

Methods

• method %CloseCursor() as %Status

alias for new result set interface

• method %DispatchGetProperty(pProperty As %String(MAXLEN=255))

Is used to get the value of an unknown property.

• method %Execute(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16) as %Library.Status

alias for new result set interface

• method %Get(name As %String) as %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.

• method %GetData(colnbr As %Integer = 0) as %Library.String

alias for new result set interface

• method %MetadataGet() as %String

This is a Get accessor method for the %Metadata property.

• method %Next(ByRef sc As %Library.Status) as %Library.Integer

alias for new result set interface

• classmethod %PrepareMetaData(pQueryId As %String(MAXLEN=""), ByRef returnError As %SYSTEM.Error = "", importPackages As %CacheString = "") as %ResultSet.MetaData

%PrepareMetaData is similar to %Prepare except that no result set is instantiated. This method returns a %ResultSet.MetaData object. Be aware that the implementation of this method might cause the result set to be instantiated but that is up to the result set class itself and no result set is actually returned by this method - only metadata. queryId This can be the concrete subclass of %IResultSet that implements the desired query. For dynamic cached results this value is generally the query text that can be parsed, a hashed value computed and the cached result implementation class looked up in the query cache index. can also be a or . If it is a then the result set returned is an instance of %Library.ResultSet that is bound to that query. If it is the name of a method Then that method is called, passing %args... to the method, and the first result set found in the context object (%sqlcontext) is returned. returnError If an error occurs this is returned by reference and is a %SYSTEM.Error object with properties set to describe information about the error. %returnError.Status contains the %Status value. importPackages This is a comma delimited list of packages to import. This is only relevant if the result set implementation is generated and the query source might include unqualified names. The import packages list is used to resolve package/schema names for unqualified names.

• method %ResultColumnCountGet() as %Integer

This is a Get accessor method for the %ResultColumnCount property.

• method %SendDelimitedRows(pMaxRows As %Integer = 0, pDelimiter As %String = "", ByRef pReturnCount As %Integer) as %Status

Fetch and send a series of rows for continuous query client. For internal use only.

• method %SendODBC() as %Integer

Fetch and send a series of rows for the ODBC/JDBC server. For internal use only.

• method ClassNameSet(class As %String) as %Status

This is a Set accessor method for the ClassName property.

• method Close() as %Status

Closes the current result set cursor.

• method ContainsId() as %Integer

If the current query contains an object Id (based on the CONTAINSID parameter being set), return the column position of the object Id. Otherwise return 0.

• method Execute(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16) as %Status

Executes the current query.
The arguments p1... supply the value of any parameters the query may have.

This method sets the %SQLCODE property if an error is reported. The value of %SQLCODE is computed from the status value and is not set to the value of the public SQLCODE variable. The %Message, %ROWCOUNT and %ROWID properties are set to the values of the SQL public variables %msg, %ROWCOUNT and %ROWID. If those public variables were not set by the query's Execute method and those variables were defined prior to calling Execute then the properties will contain the prior values of those variables. It is the caller's responsibility to initialize and manage these public variables.

• method Get(name As %String) as %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.
Using the Data multidimensional property to access the fields is faster than using this method call.

• method GetColumnCount() as %Integer

Returns the number of columns in the result set.

• method GetColumnExtInfo(n As %Integer) as %String

This method accepts the column number as an input parameter and will return a $list containing the class name and property name of the property corresponding to the column in the query. If the column is not based on a property then the returned information will be an empty $list.

• method GetColumnHeader(n As %Integer) as %String

Returns the column header for column n in the result set.

• method GetColumnName(n As %Integer) as %String

Returns the name of column n in the result set.

• method GetColumnType(n As %Integer) as %Integer

Returns the type of column n in the result set where type is an integer associated with the client datatype. If the type cannot be determined, 0 (zero) is returned.

1 BINARY

2 DATE

3 DOUBLE

4 HANDLE

5 INTEGER

6 LIST

7 LONGVARCHAR

8 TIME

9 TIMESTAMP

10 VARCHAR

11 STATUS

12 BINARYSTREAM

13 CHARACTERSTREAM

14 NUMERIC

15 CURRENCY

16 BOOLEAN

17 OID

18 BIGINT

19 FDATE

20 FTIMESTAMP

• method GetData(n As %Integer) as %String

Returns the value of column n in the current row of the result set.

• method GetDataByName(name As %String) as %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.
Note: this method has been superceded by the equivalent Get method.

• method GetExtent() as %String

The name of the extent that this query will return Id values from (based on the EXTENT parameter being set). Only returns a value if the query contains Id values.

• method GetODBCInfo(ByRef colinfo As %List, ByRef parminfo As %List) as %Status

• method GetObject() as %RegisteredObject

If this query contains an object Id then this method opens an object with this Id and returns the object reference. Otherwise it returns a null object reference.

• method GetParamCount() as %Integer

Returns the number of input parameters for the current query.

• method GetParamName(n As %Integer) as %String

Returns the name of input parameter n for the current query.

• method GetStatementType() as %String

Returns the SQL statement type of the query if available. Statement type numbers are defined as:

Type Statement

1 SELECT

2 INSERT

3 UPDATE

4 DELETE

5 COMMIT

6 ROLLBACK

7 GRANT

8 REVOKE

9 CREATE TABLE

10 ALTER TABLE

11 DROP TABLE

12 CREATE VIEW

13 ALTER VIEW

14 DROP VIEW

15 CREATE INDEX

16 ALTER INDEX

17 DROP INDEX

18 CREATE ROLL

19 DROP ROLL

20 SET TRANSACTION

21 START TRANSACTION

22 %IN_TRANSACTION

23 %BEGTRANS

24 %INTRANS

25 GET

26 SET OPTION

27 STATISTICS

28 %CHECKPRIV

29 CREATE USER

30 ALTER USER

31 DROP USER

32 %CHECKPRIV

33 GRANT

34 REVOKE

35 CREATE FUNCTION

36 CREATE METHOD

37 CREATE PROCEDURE

38 CREATE QUERY

39 DROP FUNCTION

40 DROP METHOD

41 DROP PROCEDURE

42 DROP QUERY

43 CREATE TRIGGER

44 DROP TRIGGER

45 SAVEPOINT

• method Next(ByRef sc As %Status) as %Integer

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

• method Prepare(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16) as %Status

Use this method with dynamic queries to provide the query to be executed. In the case of the %DynamicQuery:SQL query, p1 is a string containing an SQL query. The query may contain parameters represented by ? characters within the query. The values of any parameters are supplied via the Execute method. For example:
	Set result=##class(%ResultSet).%New("%DynamicQuery:SQL")
	Do result.Prepare("SELECT Name,City FROM Person WHERE Name %STARTSWITH ? AND City = ?")
	Do result.Execute("A","Boston")
	While result.Next() {
	Write result.Data("Name"),result.Data("City"),!
	}

• method QueryIsValid() as %Integer

Returns true (1) if the ClassName and QueryName properties of this %ResultSet object refer to a valid class query. Otherwise it returns false (0).

• classmethod RunQuery(ClassName As %String, QueryName As %String, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16)

This is a diagnostic function; it runs the specified query and prints the output to the console.

• method RuntimeModeGet() as %String

This is a Get accessor method for the RuntimeMode property.

• method RuntimeModeSet(mode As %String) as %Status

This is a Set accessor method for the RuntimeMode property.

• method SetIdInfo(idnumber As %Integer, extent As %String) as %Status

Set the location of the ID in this query and the extent. The idnumber is the column number of the ID and the extent is the class name the id is from. Note, you must call this after the Execute method is called or the data will be overwritten.

• classmethod Test(ClassName As %String, QueryName As %String, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16)

This method is deprecated; use RunQuery.

1		BINARY
2		DATE
3		DOUBLE
4		HANDLE
5		INTEGER
6		LIST
7		LONGVARCHAR
8		TIME
9		TIMESTAMP
10		VARCHAR
11		STATUS
12		BINARYSTREAM
13		CHARACTERSTREAM
14		NUMERIC
15		CURRENCY
16		BOOLEAN
17		OID
18		BIGINT
19		FDATE
20		FTIMESTAMP

Type	Statement
1	SELECT
2	INSERT
3	UPDATE
4	DELETE
5	COMMIT
6	ROLLBACK
7	GRANT
8	REVOKE
9	CREATE TABLE
10	ALTER TABLE
11	DROP TABLE
12	CREATE VIEW
13	ALTER VIEW
14	DROP VIEW
15	CREATE INDEX
16	ALTER INDEX
17	DROP INDEX
18	CREATE ROLL
19	DROP ROLL
20	SET TRANSACTION
21	START TRANSACTION
22	%IN_TRANSACTION
23	%BEGTRANS
24	%INTRANS
25	GET
26	SET OPTION
27	STATISTICS
28	%CHECKPRIV
29	CREATE USER
30	ALTER USER
31	DROP USER
32	%CHECKPRIV
33	GRANT
34	REVOKE
35	CREATE FUNCTION
36	CREATE METHOD
37	CREATE PROCEDURE
38	CREATE QUERY
39	DROP FUNCTION
40	DROP METHOD
41	DROP PROCEDURE
42	DROP QUERY
43	CREATE TRIGGER
44	DROP TRIGGER
45	SAVEPOINT


[BASEXML] > [%Library] > [ResultSet]		Private Storage