Returning Specific Values from the Result Set

%Print() Method

The %Print()Opens in a new tab instance method retrieves the current record from the result set. By default, %Print() inserts a blank space delimiter between data field values. %Print() does not insert a blank space before the first field value or after the last field value in a record; it issues a line return at the end of the record. If a data field value already contains a blank space, that field value is enclosed in quotation marks to differentiate it from the delimiter. For example, if %Print() is returning city names, it would return them as follows: Chicago "New York" Boston Atlanta "Los Angeles" "Salt Lake City" Washington. %Print() quotes field values that contain the delimiter as part of the data value even when the %Print() delimiter is never used; for example if there is only one field in the result set.

You can optionally specify a %Print() parameter that provides a different delimiter to be placed between the field values. Specifying a different delimiter overrides the quoting of data strings that contain blank spaces. This %Print() delimiter can be one or more characters. It is specified as a quoted string. It is generally preferable that the %Print() delimiter be a character or string not found in the result set data. However, if a field value in the result set contains the %Print() delimiter character (or string), that field value is returned enclosed in quotation marks to differentiate it from the delimiter.

If a field value in the result set contains a line feed character, that field value is returned delimited by quotation marks.

The following ObjectScript example iterates through the query result set using %Print() to display each result set record, separating values with a "^|^" delimiter. Note how %Print() displays data from the FavoriteColors field which is an encoded list of elements:

  set q1="SELECT TOP 5 Name,DOB,Home_State,FavoriteColors "
  set q2="FROM Sample.Person WHERE FavoriteColors IS NOT NULL"
  set query = q1_q2
  set statement = ##class(%SQL.Statement).%New()

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}

  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write "Row count ",rset.%ROWCOUNT,!
    do rset.%Print("^|^")
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

The following example shows how field values that contain the delimiter are returned enclosed in quotation marks. In this example, the capital letter A is used as the field delimiter; therefore, any field value (name, street address, or state abbreviation) that contains a capital A literal is returned delimited by quotation marks.

  set query = "SELECT TOP 25 Name,Home_Street,Home_State,Age FROM Sample.Person"
  set statement = ##class(%SQL.Statement).%New()

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}
  
  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
     do rset.%Print("A")
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

%GetRow() and %GetRows() Methods

The %GetRow()Opens in a new tab instance method retrieves the current row (record) from the result set as an encoded list of field value elements:

  set myquery = "SELECT TOP 17 %ID,Name,Age FROM Sample.Person"
  set tStatement = ##class(%SQL.Statement).%New()
  set qStatus = tStatement.%Prepare(myquery)
    if qStatus'=1 {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
  set rset = tStatement.%Execute()
  for {set x=rset.%GetRow(.row,.status)
        if x=1 {write $listtostring(row," | "),! }
        else {write !,"End of data"
              write !,"Total row count=",rset.%ROWCOUNT
              return }
      }

The %GetRows()Opens in a new tab instance method retrieves a group of rows (records) of a specified size from the result set. Each row is returned as an encoded list of field value elements.

The following example returns the 1st, 6th, and 11th row in the result set. In this example, the %GetRows() first parameter (5) specifies that %GetRows() should retrieve successive groups of five rows. %GetRows() returns 1 if it successfully retrieves a group of five rows. The .rows parameter passes by reference a subscripted array of these five rows, so rows(1) returns the first row from each set of five: rows 1, 6, and 11. Specifying rows(2) would return rows 2, 7, and 12.

  set myquery = "SELECT TOP 17 %ID,Name,Age FROM Sample.Person"
  set tStatement = ##class(%SQL.Statement).%New()
  set qStatus = tStatement.%Prepare(myquery)
    if qStatus'=1 {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
  set rset = tStatement.%Execute()
  for {set x=rset.%GetRows(5,.rows,.status)
        if x=1 {write $listtostring(rows(1)," | "),! }
        else {write !,"End of data"
              write !,"Total row count=",rset.%ROWCOUNT
              return }
       }

Rather than retrieving individual rows by subscript, you can use the ZWRITE rows command to return all of the subscripts in the retrieved array. Note that above example ZWRITE rows does not return the 16th and 17th row in the result set, because these rows are remainders after the last group of five rows was retrieved.

rset.name Property

When InterSystems IRIS generates a result set, it creates a result set class that contains a unique property corresponding to each field name and field name alias in the result set.

You can use the rset.name property to return a data value by property name, field name, property name alias, or field name alias.

• Property Name: If no field alias is defined, specify the field property name as rset.PropName. The result set field property name is taken from the corresponding property name in the table definition class.

• Field Name: If no field alias is defined, specify the field name (or the property name) as rset."fieldname". This is the SqlFieldName specified in the table definition. InterSystems IRIS uses this field name to locate the corresponding property name. In many cases, the property name and the field name (SqlFieldName) are identical.

• Alias Property Name: If a field alias is defined, specify the alias property name as rset.AliasProp. An alias property name is generated from the column name alias in the SELECT statement. You cannot specify a field property name for a field with a defined alias.

• Alias Name: If a field alias is defined, specify this alias name (or the alias property name) as rset."alias". This is the column name alias in the SELECT statement. You cannot specify a field name for a field with a defined alias.

• Aggregate, Expression, or Subquery: InterSystems IRIS assigns these select-items a field name of Aggregate_n, Expression_n, or Subquery_n (where the integer n corresponds to the sequence of the select-item list specified in the query). You can retrieve these select-item values using the field name (rset."SubQuery_7" not case-sensitive), the corresponding property name (rset.Subquery7 case-sensitive), or by a user-defined field name alias. You can also just specify the select-item sequence number using rset.%GetData(n).

When specifying a property name, you must use correct letter case; when specifying a field name, correct letter case is not required.

This invocation of rset.name using the property name has the following consequences:

• Letter Case: Property names are case-sensitive. Field names are not case-sensitive. Dynamic SQL can automatically resolve differences in letter case between a specified field or alias name and the corresponding property name. However, letter case resolution takes time. To maximize performance, you should specify the exact letter case of the property name or the alias.

• Non-alphanumeric Characters: A property name can only contain alphanumeric characters (except for an initial % character). If the corresponding SQL field name or field name alias contains non-alphanumeric characters (for example, Last_Name) you can do either of the following:

  • Specify the field name delimited with quotation marks. For example, rset."Last_Name"). This use of delimiters does not require that delimited identifiers be enabled. Letter case resolution is performed.

  • Specify the corresponding property name, eliminating the non-alphanumeric characters. For example, rset.LastName (or rset."LastName"). You must specify the correct letter case for the property name.

• % Property Names: Generally, property names beginning with a % character are reserved for system use. If a field property name or alias begins with a % character and that name conflicts with a system-defined property, the system-defined property is returned. For example, for SELECT Notes AS %Message, invoking rset.%Message will not return the Notes field values; it returns the %MessageOpens in a new tab property defined for the statement result class. You can use rset.%Get("%Message") to return the field value.

• Column Alias: If an alias is specified, Dynamic SQL always matches the alias rather than matching the field name or field property name. For example, for SELECT Name AS Last_Name, the data can only be retrieved using rset.LastName or rset."Last_Name", not by using rset.Name.

• Duplicate Names: Names are duplicate if they resolve to the same property name. Duplicate names can be multiple references to the same field in a table, alias references to different fields in a table, or references to fields in different tables. For example SELECT p.DOB,e.DOB specifies two duplicate names, even though those names refer to fields in different tables.

  If the SELECT statement contains multiple instances of the same field name or field name alias, rset.PropName or rset."fieldname" always return the first one specified in the SELECT statement. For example, for SELECT c.Name,p.Name FROM Sample.Person AS p,Sample.Company AS c using rset.Name retrieves the company name field data; SELECT c.Name,p.Name AS Name FROM Sample.Person AS p,Sample.Company AS c using rset."name" also retrieves the company name field data. If there are duplicate Name fields in the query the last character of the field name (Name) is replaced by a character (or characters) to create a unique property name. Thus a duplicate Name field name in a query has a corresponding unique property name, beginning with Nam0 (for the first duplicate) through Nam9 and continuing with capital letters NamA through NamZ.

For a user-specified query prepared using %Prepare()Opens in a new tab you can use the property name by itself. For a stored query prepared using %PrepareClassQuery()Opens in a new tab, you must use the %Get("fieldname")Opens in a new tab method.

The following example returns the values of three fields specified by property names: two field values by property name and the third field value by alias property name. In these cases, the specified property name is identical to the field name or field alias:

  set query = "SELECT TOP 5 Name,DOB AS bdate,FavoriteColors FROM Sample.Person"
  set statement = ##class(%SQL.Statement).%New(1)

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}
  
  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write "Row count ",rset.%ROWCOUNT,!
    write rset.Name
    write " prefers ",rset.FavoriteColors
    write ", Birth date ",rset.bdate,!!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

In the above example, one of the fields returned is the FavoriteColors field, which contains %List data. To display this data, the %New(1) class method sets the %SelectMode property parameter to 1 (ODBC), causing this program to display %List data as a comma-separated string and the birth date in ODBC format:

The following example returns the Home_State field. Because a property name cannot contain an underscore character, this example specifies the field name (the SqlFieldName) delimited with quotation marks ("Home_State"). You could also specify the corresponding generated property name without quotation marks (HomeState). Note that the delimited field name ("Home_State") is not case-sensitive, but the generated property name (HomeState) is case-sensitive:

  set query = "SELECT TOP 5 Name,Home_State FROM Sample.Person"
  set statement = ##class(%SQL.Statement).%New(2)

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}
  
  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
     write "Row count ",rset.%ROWCOUNT,!
     write rset.Name
     write " lives in ",rset."Home_State",!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

Swizzling a Fieldname Property with %ObjectSelectMode=1

The following example is prepared with %ObjectSelectMode=1, which causes fields whose type class is a swizzleable type (a persistent class, a serial class, or a stream class) to automatically swizzle when returning a value using the field name property. The result of swizzling a field value is the corresponding object reference (OREF). InterSystems IRIS does not perform this swizzling operation when accessing a field using the %Get() or %GetData() methods. In this example, rset.Home is swizzled, while rset.%GetData(2), which refers to the same field, is not swizzled:

  set query = "SELECT TOP 5 Name,Home FROM Sample.Person"
  set statement = ##class(%SQL.Statement).%New(0)
  set statement.%ObjectSelectMode = 1
  write !,"set ObjectSelectMode=",statement.%ObjectSelectMode,!

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}

  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
     write "Row count: ",rset.%ROWCOUNT,!
     write rset.Name,!
     write " ",rset.Home,!
     write rset.%GetData(1)
     write " ",$listtostring(rset.%GetData(2)),!!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

The following example uses %ObjectSelectMode=1 to derive Home_State values for the selected records from the unique record ID (%ID). Note that the Home_State field is not selected in the original query:

  set query = "SELECT TOP 5 %ID AS MyID,Name,Age FROM Sample.Person"
  set statement = ##class(%SQL.Statement).%New()
  set statement.%ObjectSelectMode=1

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}

  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
     write rset.Name
     write " Home State:",rset.MyID.Home.State,!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

If configured, the system generates a <SWIZZLE FAIL> error if the swizzled property is defined but cannot be referenced. This can occur if the referenced property has been unexpectedly deleted from disk or is locked by another process. To determine the cause of the swizzle failure look in %objlasterror immediately after the <SWIZZLE FAIL> error and decode this %Status value.

By default, <SWIZZLE FAIL> is not configured. You can set this behavior globally by setting set ^%SYS("ThrowSwizzleError")=1, or by using the InterSystems IRIS Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then Objects. On this screen you can set the <SWIZZLE FAIL> option.

%Get("fieldname") Method

You can use the %Get("fieldname")Opens in a new tab instance method to return a data value by field name or field name alias. Dynamic SQL resolves letter case as needed. If the specified field name or field name alias does not exist, the system generates a <PROPERTY DOES NOT EXIST> error.

The following example returns values for the Home_State field and the Last_Name alias from the query result set.

  set query = "SELECT TOP 5 Home_State,Name AS Last_Name FROM Sample.Person"
  set statement = ##class(%SQL.Statement).%New(2)

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}

  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write rset.%Get("Home_State")," : ",rset.%Get("Last_Name"),!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

You must use the %Get("fieldname")Opens in a new tab instance method to retrieve individual data items by field property name from an existing query prepared using %PrepareClassQuery()Opens in a new tab. If the field property name does not exist, the system generates a <PROPERTY DOES NOT EXIST> error.

The following example returns the Nsp (namespace) field values by field property name from a built-in query. Because this query is an existing stored query, this field retrieval requires the use of the %Get("fieldname") method. Note that because "Nsp" is a property name, it is case-sensitive:

  set statement = ##class(%SQL.Statement).%New(2)
  set status = statement.%PrepareClassQuery("%SYS.Namespace","List")
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}
  
  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
     write "Namespace: ",rset.%Get("Nsp"),!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,"End of data"
  write !,"Total row count=",rset.%ROWCOUNT

Duplicate Names: Names are duplicate if they resolve to the same property name. Duplicate names can be multiple references to the same field, references to different fields in a table, or references to fields in different tables. If the SELECT statement contains multiple instances of the same field name or field name alias, %Get("fieldname") always returns the last instance of a duplicate name as specified in the query. This is the opposite of rset.PropName, which returns the first instance of a duplicate name as specified in the query. This is shown in the following example:

  set query = "SELECT c.Name,p.Name FROM Sample.Person AS p,Sample.Company AS c"
  set statement = ##class(%SQL.Statement).%New()

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}

  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write "Prop=",rset.Name," Get=",rset.%Get("Name"),!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write !,rset.%ROWCOUNT," End of data"

%GetData(n) Method

The %GetData(n)Opens in a new tab instance method returns data for the current row indexed by the integer count column number of the result set. You can use %GetData(n) with either a specified query prepared using %Prepare() or a stored query prepared using %PrepareClassQuery().

The integer n corresponds to the sequence of the select-item list specified in the query. The RowID field is not given an integer n value, unless explicitly specified in the select-item list. If n is higher than the number of select-items in the query, or 0, or a negative number, Dynamic SQL returns no value and issues no error.

%GetData(n) is the only way to return a specific duplicate field name or duplicate alias; rset.Name returns the first duplicate, %Get("Name") returns the last duplicate.

  set query = "SELECT TOP 5 Name,SSN,Age FROM Sample.Person"
  set statement = ##class(%SQL.Statement).%New()

  set status = statement.%Prepare(query)
  if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}

  set rset = statement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write "Years:",rset.%GetData(3)," Name:",rset.%GetData(1),!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
  write "End of data"
  write !,"Total row count=",rset.%ROWCOUNT