Returning the Full Result Set

%Display() Method

You can display the entire result set (the contents of the result object) by calling the %Display()Opens in a new tab instance method of the %SQL.StatementResultOpens in a new tab class, as shown in the following example:

  do rset.%Display()

Note that the %Display() method does not return a %Status value.

When displaying a query result set, %Display() concludes by displaying the row count: “5 Rows(s) Affected”. (This is the %ROWCOUNT value after %Display() has iterated through the result set.) Note that %Display() does not issue a line return following this row count statement.

%Display() has two optional arguments:

• Delimiter: a string inserted between data columns and data headers. It appears between resultset columns, immediately before the header or data value. The default is no delimiter. If omitted, specify a placeholder comma before the Column Alignment flag.

• Column Alignment: an integer flag that specifies how whitespace is calculated between data columns and data headers. The available options are:

  • 0: Resultset header/data columns will be aligned based on the standard delimiter (tab). This is the default.

  • 1: Resultset header/data columns will be aligned based on the length of the column header and the standard delimiter (tab).

  • 2: Resultset header/data columns will be aligned based on the precision/length of the column data property and the standard delimiter (tab).

%DisplayFormatted() Method

You can reformat and redirect the result set contents to a generated file by calling the %DisplayFormatted()Opens in a new tab instance method of the %SQL.StatementResultOpens in a new tab class, rather than calling %Display().

You can specify the result set format either by specifying the string option %DisplayFormatted("HTML") or the corresponding integer code %DisplayFormatted(1). InterSystems IRIS generates a file of the specified type, appending the appropriate file name extension. This table shows the options you can specify and the files you can generate.

Note that the values in the generated CSV file are separated by tabs, not commas.

If you specify any other number or string, then %DisplayFormatted() generates a text (.txt) file. Text files conclude with the row count (for example “5 Rows(s) Affected”). The other formats do not include a row count.

You can specify or omit a result set file name:

• If you specify a destination file (for example, %DisplayFormatted(99,"myresults")) a file with that name and the appropriate suffix (file name extension) is generated in the mgr directory in the subdirectory for the current namespace. For example, C:\InterSystems\IRIS\mgr\user\myresults.txt. If the specified file with that suffix already exists, InterSystems IRIS overwrites it with new data.

• If you do not specify a destination file (for example, %DisplayFormatted(99)) a file with a randomly-generated name and the appropriate suffix (file name extension) is generated in the mgr directory in the Temp subdirectory. For example, C:\InterSystems\IRIS\mgr\Temp\w4FR2gM7tX2Fjs.txt. Each time a query is run a new destination file is generated.

These examples show Windows filenames; InterSystems IRIS supports equivalent locations on other operating systems.

If the specified file cannot be opened, this operation times out after 30 seconds with an error message; this commonly occurs when the user does not have write privileges to the specified directory (file folder).

If data cannot be rendered in the specified format, the destination file is created but no result set data is written to it. Instead, an appropriate message is written to the destination file. For example, a stream field OID contains characters that conflict with XML and HTML special formatting characters. This XML and HTML stream field issue can be resolved by using the XMLELEMENT function on stream fields; for example, SELECT Name,XMLELEMENT("Para",Notes).

You can optionally supply the name of a translate table that %DisplayFormatted() will use when performing the specified format conversion.

In the case of multiple result sets in a result set sequence, the content of each result set is written to its own file.

The optional third %DisplayFormatted() argument specifies that messages are stored in a separate result set. Upon successful completion a message like the following is returned:

Message
21 row(s) affected.

The following Windows example creates two PDF (integer code 2) result set files in C:\InterSystems\IRIS\mgr\user\. It creates the mess result set for messages, then uses %Display() to display messages to the Terminal:

  set $NAMESPACE="USER"
  set myquery=2
  set myquery(1)="SELECT Name,Age FROM Sample.Person"
  set myquery(2)="WHERE Age > ? AND Age < ? ORDER BY Age"
  set rset = ##class(%SQL.Statement).%ExecDirect(,.myquery,12,20)
    if rset.%SQLCODE'=0 {write !,"1st ExecDirect SQLCODE=",rset.%SQLCODE,!,rset.%Message  quit}
  do rset.%DisplayFormatted(2,"Teenagers",.mess)
  do mess.%Display()
  write !,"End of teen data",!!
  set rset2 = ##class(%SQL.Statement).%ExecDirect(,.myquery,19,30)
    if rset2.%SQLCODE'=0 {write !,"2nd ExecDirect SQLCODE=",rset2.%SQLCODE,!,rset2.%Message  quit}
  do rset2.%DisplayFormatted(2,"Twenties",.mess)
  do mess.%Display()
  write !,"End of twenties data"

Paginating a Result Set

You can use a view ID (%VID) to paginate a result set. The following example returns pages from the result set, each page containing 5 rows:

  set q1="SELECT %VID AS RSRow,* FROM "
  set q2="(SELECT Name,Home_State FROM Sample.Person WHERE Home_State %STARTSWITH 'M') "
  set q3="WHERE %VID BETWEEN ? AND ?"
  set myquery = q1_q2_q3
  set tStatement = ##class(%SQL.Statement).%New()
  set qStatus=tStatement.%Prepare(myquery)
      if qStatus'=1 {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
  for i=1:5:25 {
      write !!,"Next Page",!
      set rset=tStatement.%Execute(i,i+4)
      do rset.%Display()
      }

Refer to %GetRows() for another way to return groups of rows (records) from a result set.