%Library.ResultSet
Class %Library.ResultSet Extends %AbstractResultSet [ ClientName = ResultSetOld, Deprecated, OdbcType = RESULTSET, System = 2 ]
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 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 InterSystems IRIS 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 SQL Manager.
Properties
%Metadata
Property %Metadata As %String [ MultiDimensional ];
xDBC metadata - for compatibility with new cached result sets.
ClassName
Property ClassName As %RawString;
The name of the class containing the query to run.
QueryName
Property QueryName As %RawString;
The name of the query to run.
RuntimeMode
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.
QHandle
Property QHandle As %String [ MultiDimensional, Private ];
HasInfo
Property HasInfo As %String [ InitialExpression = 0, Private ];
HasExtInfo
Property HasExtInfo As %String [ InitialExpression = 0, Private ];
ParamInfo
Property ParamInfo As %String [ Private ];
ColInfo
Property ColInfo As %String [ Private ];
ColExtInfo
Property ColExtInfo As %String [ Private ];
IdInfo
Property IdInfo As %String [ Private ];
AtEnd
Property AtEnd As %String [ InitialExpression = 0, Private ];
Row
Property Row As %String [ MultiDimensional, Private ];
StatementType
Property StatementType As %String [ Private ];
IsOpened
Property IsOpened As %String [ InitialExpression = 0, Private ];
ColIndex
Property ColIndex As %String [ MultiDimensional, Private ];
Data
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.
Column
Property Column As %String [ Internal, MultiDimensional ];
Duplicate of Data but subscripted by column number instead of name. This is used only by %SendOdbc to maintain values of columns orphaned by a full buffer. This data is not maintained for use for any other purpose and is not part of the public interface.
NextColumn
Property NextColumn As %Integer [ InitialExpression = 0, Private ];
This is the pointer to the next Column to be processed by %SendODBC. When %SendODBC is next called, this will be the first column to be sent. This value is zero if no data is unprocessed, non zero if there is something to process It is set to 1 on every Next() call that returns a valid row.
Methods
%MetadataGet
Method %MetadataGet() As %String
%ObjectsGet
Method %ObjectsGet() As %String [ Internal ]
%Objects is a serialized collection of object specifications that include the name of the column whose type class is a subclass of %SwizzleObject, the name of the class and the call to export the object directly to the wire. This property is not meant for public use. It is used by %SendObjects.
RuntimeModeSet
Method RuntimeModeSet(mode As %String) As %Status
RuntimeModeGet
Method RuntimeModeGet() As %String [ CodeMode = expression ]
%DispatchGetProperty
Method %DispatchGetProperty(pProperty As %String(MAXLEN=255))
ClassNameSet
Method ClassNameSet(class As %String) As %Status
%OnClose
Method %OnClose() As %Status [ Private ]
%OnNew
Method %OnNew(initvalue As %String) As %Status [ Private ]
%ResultColumnCountGet
Method %ResultColumnCountGet() As %Integer [ CodeMode = call ]
Prepare
Method Prepare(args...) 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"),! }
Execute
Method Execute(args...) As %Status [ PublicList = (qHandle, args) ]
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.
%Execute
Method %Execute(args...) As %Library.Status [ CodeMode = call ]
alias for new result set interface
Execute16
Method Execute16(arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9, arg10, arg11, arg12, arg13, arg14, arg15, arg16) As %Status [ Internal ]
Version of Execute() that takes up to 16 arguments instead of a variable number of arguments. Provides compatibility with C++ binding and CacheActiveX, which do not fully support methods with a variable number of arguments. For internal use only.
%SendODBC
Method %SendODBC() As %Integer
Fetch and send a series of rows for the ODBC/JDBC server. For internal use only.
%SendDelimitedRows
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.
Next
Method Next(ByRef sc As %Status) As %Integer [ PublicList = (Row, AtEnd, SQLCODE) ]
Advance the result set cursor to the next row. Returns 0 if the cursor is at the end of the result set.
%Next
Method %Next(ByRef sc As %Library.Status) As %Library.Integer [ CodeMode = call ]
alias for new result set interface
GetCachedQueryName
Method GetCachedQueryName() As %Library.String [ Internal ]
Get the name of the cached query routine for the current result set. For internal use only. This is called from the SMP page %CSP.Util.SQLQueryPane to display the cached query name on the Execute Query page
GetData
Method GetData(n As %Integer) As %String [ CodeMode = expression ]
Returns the value of column n in the current row of the result set.
%GetData
Method %GetData(colnbr As %Integer = 0) As %Library.String
alias for new result set interface
Get
Method Get(name As %String) As %String [ CodeMode = expression, ProcedureBlock = 1 ]
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.
%Get
Method %Get(name As %String) As %String
GetDataByName
Method GetDataByName(name As %String) As %String [ CodeMode = expression ]
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.
GetObject
Method GetObject() As %RegisteredObject [ ProcedureBlock = 1 ]
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.
Close
Method Close() As %Status [ PublicList = (Row, SQLCODE) ]
Closes the current result set cursor.
%CloseCursor
Method %CloseCursor() As %Status [ CodeMode = call ]
alias for new result set interface
GetInfo
Method GetInfo(extoption) As %Status [ Private ]
GetODBCInfo
Method GetODBCInfo(ByRef colinfo As %List, ByRef parminfo As %List) As %Status
SetIdInfo
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.
GetColumnCount
Method GetColumnCount() As %Integer [ CodeMode = expression ]
Returns the number of columns in the result set.
GetColumnName
Method GetColumnName(n As %Integer) As %String [ CodeMode = expression ]
Returns the name of column n in the result set.
GetColumnExtInfo
Method GetColumnExtInfo(n As %Integer) As %String [ CodeMode = expression ]
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.
GetColumnHeader
Method GetColumnHeader(n As %Integer) As %String
Returns the column header for column n in the result set.
GetColumnType
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 |
GetParamCount
Method GetParamCount() As %Integer [ CodeMode = expression ]
Returns the number of input parameters for the current query.
GetParamName
Method GetParamName(n As %Integer) As %String [ CodeMode = expression, ProcedureBlock = 1 ]
Returns the name of input parameter n for the current query.
GetStatementType
Method GetStatementType() As %String [ CodeMode = expression ]
Returns the SQL statement type of the query if available. Statement type numbers are defined as:
| 1 | SELECT |
|---|---|
| 2 | INSERT (also 'INSERT OR UPDATE') |
| 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 (Not supported) |
| 17 | DROP INDEX |
| 18 | CREATE ROLE |
| 19 | DROP ROLE |
| 20 | SET TRANSACTION |
| 21 | START TRANSACTION |
| 22 | %INTRANSACTION |
| 23 | %BEGTRANS (Alias for START TRANSACTION) |
| 24 | %INTRANS (Alias for %INTRANSACTION) |
| 25 | GET (Not supported) |
| 26 | SET OPTION |
| 27 | STATISTICS (UPDATE STATISTICS, not supported)) |
| 28 | %CHECKPRIV |
| 29 | CREATE USER |
| 30 | ALTER USER |
| 31 | DROP USER |
| 32 | %CHECKPRIV (SQL Admin Privilege) |
| 33 | GRANT (SQL Admin Privilege) |
| 34 | REVOKE (SQL Admin Privilege) |
| 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 | CALL |
| 46 | SAVEPOINT |
| 47 | LOCK TABLE |
| 48 | UNLOCK TABLE |
| 49 | CREATE DATABASE |
| 50 | DROP DATABASE |
| 51 | USE DATABASE |
| "" | Anything not list above |
QueryIsValid
Method QueryIsValid() As %Integer [ CodeMode = expression ]
Returns true (1) if the ClassName and QueryName properties of this %ResultSet object refer to a valid class query. Otherwise it returns false (0).
ContainsId
Method ContainsId() As %Integer [ CodeMode = expression ]
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.
GetExtent
Method GetExtent() As %String [ CodeMode = expression ]
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.
Test
ClassMethod Test(ClassName As %String, QueryName As %String, args...)
This method is deprecated; use RunQuery.
%PrepareMetaData
ClassMethod %PrepareMetaData(pQueryId As %String(MAXLEN=""), ByRef returnError As %SYSTEM.Error = "", importPackages As %RawString = "") 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.