Skip to main content

Dynamic SQL Using Older Result Set Classes

The %SQL.StatementOpens in a new tab class is the preferred way to perform Dynamic SQL. Dynamic SQL using this class is described in the previous chapter Using Dynamic SQL.

You can also use the older %ResultSet.SQLOpens in a new tab class or the %Library.ResultSetOpens in a new tab class to query the database. In most cases, the %SQL.StatementOpens in a new tab class is preferable for new Dynamic SQL code. The %ResultSet.SQLOpens in a new tab and %Library.ResultSetOpens in a new tab classes are described here for compatibility with existing code.

Dynamic SQL Using %ResultSet.SQL

The following ObjectScript example prepares and executes a Dynamic SQL query using the %ResultSet.SQLOpens in a new tab class:

  /* %ResultSet.SQL example */
  ZNSPACE "SAMPLES"
  SET myquery="SELECT TOP 5 Name,SSN FROM Sample.Person ORDER BY Name"
  SET rset=##class(%ResultSet.SQL).%Prepare(myquery,.err,"")
    WHILE rset.%Next() {
    WRITE rset.Name,", ",rset.SSN,! 
    }
  WRITE "End of data"

The following %ResultSet.SQLOpens in a new tab class example uses the %Print() instance method to print the current row data for all selected columns in the column order specified in the query:

  ZNSPACE "SAMPLES"
  SET myquery="SELECT TOP 10 Name,SSN FROM Sample.Person ORDER BY Name"
  SET rset=##class(%ResultSet.SQL).%Prepare(myquery,.err,"")
    WHILE rset.%Next() {
    DO rset.%Print("^")
    }
  WRITE "End of data"

This example uses the ^ character as a delimiter between column values. This use of a specified delimiter character is optional.

Dynamic SQL Using %Library.ResultSet

The following ObjectScript code prepares and executes a Dynamic SQL query using the %Library.ResultSetOpens in a new tab class and its Prepare()Opens in a new tab and Execute()Opens in a new tab methods:

  /* %Library.ResultSet example */
  ZNSPACE "SAMPLES"
  SET myquery="SELECT TOP 5 Name,SSN FROM Sample.Person ORDER BY Name"
  SET rset=##class(%ResultSet).%New()
  SET qStatus=rset.Prepare(myquery)
    IF qStatus'=1 {WRITE "Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET sc=rset.Execute()
     WHILE rset.Next() {
     WRITE rset.Data("Name"),", ",rset.Data("SSN"),!
     }
   WRITE "End of data",!
   WRITE "Row count=",%ROWCOUNT
Note:

The %ResultSet.%New() class method originally required an argument of "%DynamicQuery:SQL" to create a new result set. You can now call it either with no argument at all, as in the previous example, or with the "%DynamicQuery:SQL" argument, as in the following example.

The following %Library.ResultSetOpens in a new tab example shows the use of column name aliases. The column name is specified by the SQL query. If you have two columns with the same name, you cannot retrieve them both via the DataOpens in a new tab property. You can provide unique column names by using aliases within your SQL statement:

  ZNSPACE "SAMPLES"
  SET q1="SELECT TOP 10 P.Name AS pn,E.Name AS en"
  SET q2=" FROM Sample.Person AS P,Sample.Employee AS E"
  SET myquery=q1_q2
  SET rset=##class(%ResultSet).%New("%DynamicQuery:SQL")
  SET qStatus=rset.Prepare(myquery)
    IF qStatus'=1 {WRITE "Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET sc=rset.Execute()
     WHILE rset.Next() {
     WRITE rset.Data("pn"),", ",rset.Data("en"),!
     }
   WRITE "End of data",!
   WRITE "Row count=",%ROWCOUNT

%Library.ResultSet Supports SQL Result Properties

%Library.ResultSetOpens in a new tab supports the properties %SQLCODE, %ROWCOUNT, %ROWID and %Message. It sets %SQLCODE and %Message from status values returned by the query. The Execute() method populates %ROWID and %ROWCOUNT from the public variables %ROWID and %ROWCOUNT. Execute() initializes %ROWCOUNT to zero if the query is not dynamic. The Next() method also populates %ROWCOUNT.

%Library.ResultSet Does Not Support CALL

%Library.ResultSetOpens in a new tab does not support the CALL statement for invoking a dynamic query. If the SQL statement is a CALL, an “Invalid Statement Type” message is returned by the Prepare() method. The %SQL.Statement class supports the CALL statement.

If the called routine is a function, %Library.ResultSetOpens in a new tab can use SELECT to invoke it, as shown in the following example:

  ZNSPACE "SAMPLES"
  SET rs=##class(%ResultSet).%New()
  DO $SYSTEM.OBJ.DisplayError(rs.Prepare("SELECT Sample.Stored_Procedure_Test(?,?)"))
  WRITE rs.%Execute("Doe,John",""),!
  DO rs.%Display()
  WRITE !,"End of display"

Input Parameters

Input parameters are specified in a query using a question mark (?). Values are supplied to these input parameters by a method.

  • %ResultSet.SQLOpens in a new tab specifies the input parameter values in the %Prepare() method as the 4th and subsequent arguments. There is no limit on the number of input parameters. You can use input parameters to supply values to the TOP clause and the WHERE clause; you cannot use input parameters to supply values to the SELECT list.

  • %Library.ResultSetOpens in a new tab specifies the input parameter values in the Execute()Opens in a new tab method as arguments. There is a limit of 16 input parameters. You can use input parameters to supply values to the TOP clause and the WHERE clause; you cannot use input parameters to supply values to the SELECT list.

  • %SQL.Statement specifies the input parameter values in the %Execute() method as arguments. There is no limit on the number of input parameters. You can use input parameters to supply values to the TOP clause, the WHERE clause, and to supply expressions to the SELECT list; you cannot use input parameters to supply column names to the SELECT list.

The two following ObjectScript examples both execute the same query with two input parameters. The first uses %ResultSet.SQLOpens in a new tab and specifies the input parameter values (21 and 26) as the 4th and 5th arguments of the Prepare() method. The second uses %Library.ResultSetOpens in a new tab, and specifies the input parameter values (21 and 26) in the Execute() method.

  /* %ResultSet.SQL example */
  ZNSPACE "SAMPLES"
  SET myquery="SELECT Name,Age FROM Sample.Person WHERE Age > ? AND Age < ?"
  SET rset=##class(%ResultSet.SQL).%Prepare(myquery,.err,"",21,26)
    WHILE rset.%Next() {
    WRITE rset.Name,", ",rset.Age,! 
    }
  WRITE "End of data"
  /* %Library.ResultSet example */
  ZNSPACE "SAMPLES"
  SET myquery="SELECT Name,Age FROM Sample.Person WHERE Age > ? AND Age < ?"
  SET rset=##class(%ResultSet).%New("%DynamicQuery:SQL")
  SET qStatus=rset.Prepare(myquery)
    IF qStatus'=1 {WRITE "Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET sc=rset.Execute(21,26)
     WHILE rset.Next() {
     WRITE rset.Data("Name"),", ",rset.Data("Age"),!
     }
   WRITE "End of data",!
   WRITE "Row count=",%ROWCOUNT

The following Caché Basic example uses %Library.ResultSetOpens in a new tab to execute a Dynamic SQL query with two input parameters:

myquery="SELECT Name,Age FROM Sample.Person WHERE Age > ? AND Age < ?"
result = New %Library.ResultSet()
' prepare the query
result.Prepare(myquery)
' find everyone with ages within the range specified below
result.Execute(21,26)
While (result.Next())
    PrintLn result.Data("Name") & ", " & result.Data("Age")
Wend
    PrintLn "End of data"
    PrintLn "Row count=",%ROWCOUNT

Note that public variables, such as SQLCODE, are not supported by Caché Basic subroutines. All variables in a Caché Basic subroutine are private variables.

Closing a Query

When you are done with a Dynamic SQL query you can close it (release any resources used by the query) in two different ways:

  • By destroying the result set object (such as letting it go out of scope).

  • By explicitly calling the Close()Opens in a new tab instance method of the %Library.ResultSetOpens in a new tab class. Calling the Close() method closes the current result set cursor, This allows you to execute and fetch from the same query without having to re-prepare it.

The following %Library.ResultSetOpens in a new tab example shows how using Close() enables you to start a new result set cursor:

  ZNSPACE "SAMPLES"
  SET myquery="SELECT Name,SSN FROM Sample.Person WHERE Name %STARTSWITH ?"
  SET rset=##class(%ResultSet).%New("%DynamicQuery:SQL")
  SET qStatus=rset.Prepare(myquery)
    IF qStatus'=1 {WRITE "Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET sc=rset.Execute("A")
     WHILE rset.Next() {
     WRITE rset.Data("Name"),", ",rset.Data("SSN"),!
     }
   WRITE "End of 'A' data",!!
   SET sc=rset.Close()
   SET sc=rset.Execute("B")
     WHILE rset.Next() {
     WRITE rset.Data("Name"),", ",rset.Data("SSN"),!
     }
   WRITE "End of 'B' data"

%Library.ResultSet Metadata

%Library.ResultSetOpens in a new tab supports static metadata; %SQL.Statement supports dynamic metadata. ZEN Reports programming requires the use of the %Library.ResultSetOpens in a new tab class, because it requires static metadata.

After preparing a query, you can return metadata about that query. You can either return individual metadata items by using methods of the %Library.ResultSetOpens in a new tab class, or you can return a table of metadata by using the %GetMetadata()Opens in a new tab method.

To return a table of query metadata values, use %GetMetadata() with its %Display() method, as shown in the following example:

  ZNSPACE "SAMPLES"
  SET q1="SELECT Name,SSN AS GovtNum,Age"
  SET q2=" FROM Sample.Person WHERE Name %STARTSWITH ?"
  SET myquery=q1_q2
  SET rset=##class(%ResultSet).%New("%DynamicQuery:SQL")
  SET qStatus=rset.Prepare(myquery)
    IF qStatus'=1 {WRITE "Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  WRITE !,rset.%GetMetadata().%Display() 

The resulting metadata table lists the following items:

Column Name The name of the column. If the column is given an alias, the alias is listed here. If the SELECT item is an expression or an aggregate, the assigned “Expression_n” or “Aggregate_n” label is listed (with n being the SELECT item sequence number). If you have assigned an alias to an expression or aggregate, the alias is listed here.
Type The integer code for the ODBC data type. These codes are listed in the Integer Codes for Data Types section of the Data Types reference page in the Caché SQL Reference. Note that these ODBC data type codes are not the same as the client data type codes returned by %Library.ResultSetOpens in a new tab class method GetColumnType(n)Opens in a new tab.
Precision The maximum data size, in characters.
Scale The maximum number of fractional decimal digits.
Null A boolean value that indicates whether the column is defined as Non-NULL (0), or if NULL is permitted (1). If the SELECT item is an expression or aggregate that could result in NULL, this item is set to 1. If the SELECT item is an expression with a system-supplied value (such as a system variable or a function that returns the current date, or returns Pi), this item is set to 2.
Label The column name or alias.
Table The table name. The actual table name is always listed here, even if you have given the table an alias. If the SELECT item is an expression or an aggregate no table name is listed.
Schema The table’s schema name. If the SELECT item is an expression or an aggregate no schema name is listed.

Each column is then listed with twelve Extended Column Info (SQLRESULTCOL) boolean flags, specified as Y (Yes) or N (No): 1:AutoIncrement, 2:CaseSensitive, 3:Currency, 4:ReadOnly, 5:RowVersion, 6:Unique, 7:Aliased, 8:Expression, 9:Hidden, 10:Identity, 11:KeyColumn, 12:RowId.

You can either return individual metadata items by using methods of the %Library.ResultSetOpens in a new tab class. These metadata item methods include:

Method Description
GetColumnCount()Opens in a new tab Returns the number of columns selected in the query.
GetColumnName(n)Opens in a new tab Returns the name (or name alias) of a column. The n integer specifies the desired column by column sequence number in the query.
GetColumnType(n)Opens in a new tab

Returns an integer code for the client data type of a column specified in the query. The n integer specifies the desired column by column sequence number in the query.

A table of these client data type integer codes is found in the %Library.ResultSetOpens in a new tab class documentation. Note that these client data type codes are not the same as the more widely used ODBC data type integer codes (described below). Also note that a column that contains list structured data (such as FavoriteColors in Sample.Person) returns a column data type of 10 (VARCHAR).

GetParamCount()Opens in a new tab Returns the number of input parameters (question marks) specified in the query.
GetStatementType()Opens in a new tab Returns an integer code for the SQL statement type of the query. For example, a 1=SELECT, 2=INSERT, etc. A table of these integer codes is found in the %Library.ResultSetOpens in a new tab class documentation.

The following ObjectScript example shows the use of these query metadata methods:

  ZNSPACE "SAMPLES"
  SET q1="SELECT Name,SSN AS GovtNum,Age"
  SET q2=" FROM Sample.Person WHERE Name %STARTSWITH ?"
  SET myquery=q1_q2
  SET rset=##class(%ResultSet).%New("%DynamicQuery:SQL")
  SET qStatus=rset.Prepare(myquery)
    IF qStatus'=1 {WRITE "Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  WRITE !,rset.GetStatementType() /* returns 1 (SELECT) */
  WRITE !,rset.GetColumnCount()  /* returns 3 */
  WRITE !,rset.GetColumnName(1)  /* returns Name */
  WRITE !,rset.GetColumnName(2)  /* returns GovtNum */
  WRITE !,rset.GetColumnType(1)  /* returns 10 (VARCHAR)  */  
  WRITE !,rset.GetColumnType(3)  /* returns 5 (INTEGER)  */
  WRITE !,rset.GetParamCount()  /* returns 1 */

%ResultSet.SQL Metadata

To return a table of query metadata values from %ResultSet.SQLOpens in a new tab, use the %PrepareMetaData()Opens in a new tab class method, as shown in the following example:

  ZNSPACE "SAMPLES"
  SET q1="SELECT ID,Name,CURRENT_DATE AS Now,DOB,Age,AVG(Age) AS AvgAge,SSN"
  SET q2=" FROM Sample.Person"
  SET myquery=q1_q2
  SET rset=##class(%ResultSet.SQL).%PrepareMetaData(myquery)
  DO rset.%Display()
FeedbackOpens in a new tab