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.
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.
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.
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:
%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 [ Language = objectscript ]
method %SendDelimitedRows(pMaxRows As %Integer = 0, pDelimiter As %String = "", ByRef pReturnCount As %Integer) as %Status [ Language = objectscript ]
Fetch and send a series of rows for continuous query client. For internal use only.
method %SendODBC() as %Integer [ Language = objectscript ]
Fetch and send a series of rows for the ODBC/JDBC server. For internal use only.
method ClassNameSet(class As %String) as %Status [ Language = objectscript ]
method Close() as %Status [ Language = objectscript ]
Closes the current result set cursor.
method ContainsId() as %Integer [ Language = objectscript ]
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(args...) as %Status [ Language = objectscript ]
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 [ Language = objectscript ]
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 [ Language = objectscript ]
Returns the number of columns in the result set.
method GetColumnExtInfo(n As %Integer) as %String [ Language = objectscript ]
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 [ Language = objectscript ]
Returns the column header for column n in the result set.
method GetColumnName(n As %Integer) as %String [ Language = objectscript ]
Returns the name of column n in the result set.
method GetColumnType(n As %Integer) as %Integer [ Language = objectscript ]
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 [ Language = objectscript ]
Returns the value of column n in the current row of the result set.
method GetDataByName(name As %String) as %String [ Language = objectscript ]
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 [ Language = objectscript ]
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 [ Language = objectscript ]
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 [ Language = objectscript ]
Returns the number of input parameters for the current query.
method GetParamName(n As %Integer) as %String [ Language = objectscript ]
Returns the name of input parameter n for the current query.
method GetStatementType() as %String [ Language = objectscript ]
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
method Next(ByRef sc As %Status) as %Integer [ Language = objectscript ]
Advance the result set cursor to the next row. Returns 0 if the cursor is at the end of the
result set.
method Prepare(args...) as %Status [ Language = objectscript ]
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 [ Language = objectscript ]
Returns true (1) if the ClassName and QueryName properties of this
%ResultSet object refer to a valid class query.
Otherwise it returns false (0).
method RuntimeModeGet() as %String [ Language = objectscript ]
method RuntimeModeSet(mode As %String) as %Status [ Language = objectscript ]
method SetIdInfo(idnumber As %Integer, extent As %String) as %Status [ Language = objectscript ]
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, args...) [ Language = objectscript ]