Using Caché SQL
Dynamic SQL Using Older Result Set Classes
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

The %SQL.Statement 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.SQL class or the %Library.ResultSet class to query the database. In most cases, the %SQL.Statement class is preferable for new Dynamic SQL code. The %ResultSet.SQL and %Library.ResultSet classes are described here for compatibility with existing code.
Dynamic SQL Using %ResultSet.SQL
The following Caché ObjectScript example prepares and executes a Dynamic SQL query using the %ResultSet.SQL 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.SQL 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 Caché ObjectScript code prepares and executes a Dynamic SQL query using the %Library.ResultSet class and its Prepare() and Execute() 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 sc=rset.Prepare(myquery)
  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.ResultSet 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 Data 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 sc=rset.Prepare(myquery)
  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.ResultSet 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.ResultSet 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.ResultSet 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.
The two following Caché ObjectScript examples both execute the same query with two input parameters. The first uses %ResultSet.SQL and specifies the input parameter values (21 and 26) as the 4th and 5th arguments of the Prepare() method. The second uses %Library.ResultSet, 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 sc=rset.Prepare(myquery)
  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.ResultSet 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:
The following %Library.ResultSet 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 sc=rset.Prepare(myquery)
  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.ResultSet supports static metadata; %SQL.Statement supports dynamic metadata. ZEN Reports programming requires the use of the %Library.ResultSet 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.ResultSet class, or you can return a table of metadata by using the %GetMetadata() 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 sc=rset.Prepare(myquery)
  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.ResultSet class method GetColumnType(n).
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.ResultSet class. These metadata item methods include:
Method Description
GetColumnCount() Returns the number of columns selected in the query.
GetColumnName(n) 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)
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.ResultSet 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() Returns the number of input parameters (question marks) specified in the query.
GetStatementType() 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.ResultSet class documentation.
The following Caché 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 sc=rset.Prepare(myquery)
  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.SQL, use the %PrepareMetaData() 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()