Home > Class Reference > ENSLIB namespace > %Library.ResultSet

%Library.ResultSet

class %Library.ResultSet extends %Library.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.

Property Inventory

Method Inventory

Properties

property %Metadata as %String [ MultiDimensional ];
xDBC metadata - for compatibility with new cached result sets.
Property methods: %MetadataDisplayToLogical(), %MetadataGet(), %MetadataIsValid(), %MetadataLogicalToDisplay(), %MetadataLogicalToOdbc(), %MetadataNormalize(), %MetadataSet()
property ClassName as %CacheString;
The name of the class containing the query to run.
Property methods: ClassNameGet(), ClassNameIsValid(), ClassNameSet()
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 methods: DataDisplayToLogical(), DataGet(), DataIsValid(), DataLogicalToDisplay(), DataLogicalToOdbc(), DataNormalize(), DataSet()
property QueryName as %CacheString;
The name of the query to run.
Property methods: QueryNameGet(), QueryNameIsValid(), QueryNameSet()