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

This chapter discusses Dynamic SQL, queries and other SQL statements that are prepared and executed at runtime. It includes the following topics:

This chapter describes Dynamic SQL programming using the %SQL.Statement class, which is the preferred implementation of Dynamic SQL. All statements about Dynamic SQL in this chapter, and throughout our documentation, refer specifically to the %SQL.Statement implementation, unless otherwise indicated. You can also create Dynamic SQL programs using the older %ResultSet.SQL class or the %Library.ResultSet class, as described in the Dynamic SQL Using the Older %ResultSet.SQL and %Library.ResultSet Classes chapter of this manual.
Introduction to Dynamic SQL
Dynamic SQL refers to SQL statements that are prepared and executed at runtime. Dynamic SQL lets you program within Caché in a manner similar to an ODBC or JDBC application (except that you are executing the SQL statement within the same process context as the database engine).
Dynamic SQL can be invoked from either a Caché ObjectScript program or a Caché Basic program.
Dynamic SQL can be used to perform an SQL query. It can also be used to issue other SQL statements. The examples in this chapter perform a SELECT query. For Dynamic SQL program examples invoking CREATE TABLE, INSERT, UPDATE, DELETE, or CALL, refer to these commands in the Caché SQL Reference.
Dynamic SQL is used in the execution of the Caché SQL Shell, the Caché Management Portal Execute Query interface, the SQL Code Import methods, and the Data Import and Export Utilities. The maximum size of a row in Dynamic SQL (and applications that use it) is 32,767 characters. This limitation can be greatly expanded by configuring long string operations.
Dynamic SQL versus Embedded SQL
Dynamic SQL differs from Embedded SQL in the following ways:
Dynamic SQL and Embedded SQL use the same data representation (logical mode by default, but this can be changed) and NULL handling.
The %SQL.Statement Class
The preferred interface for Dynamic SQL is the %SQL.Statement class. To prepare and execute Dynamic SQL statements, use an instance of %SQL.Statement. The result of executing a Dynamic SQL statement is an SQL statement result object that is an instance of the %SQL.StatementResult class. An SQL statement result object is either a unitary value, a result set, or a context object. In all cases, the result object supports a standard interface. Each result object initializes the %SQLCODE, %Message and other result object properties; The values these properties are set to depends on the SQL statement issued. For a successfully executed SELECT statement, the object is a result set (specifically, an instance of %SQL.IResultSet) and supports the expected result set functionality.
The following Caché ObjectScript code prepares and executes a Dynamic SQL query:
  /* Simple %SQL.Statement example */
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 5 Name,DOB 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()
  DO rset.%Display()
  WRITE !,"End of data"
 
The following is the same Dynamic SQL query in Caché Basic:
myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
tStatement = New %SQL.Statement()
qStatus = tStatement.%Prepare(myquery)
If qStatus<>1 Then 
   PrintLn "%Prepare failed: "
   PrintLn Piece(qStatus," ",3,10)
Else
  rset = tStatement.%Execute()
  CALL rset.%Display()
  PrintLn
  PrintLn "End of data"
End If
 
The examples in this chapter use methods associated with the %SQL.Statement and %SQL.StatementResult classes.
Creating an Object Instance
You can create an instance of the %SQL.Statement class using the %New() class method in Caché ObjectScript:
  SET tStatement = ##class(%SQL.Statement).%New()
Or in Caché Basic:
tStatement = New %SQL.Statement()
At this point the result set object is ready to prepare an SQL statement. Once you have created an instance of the %SQL.Statement class, you can use that instance to issue multiple Dynamic SQL queries and/or INSERT, UPDATE, or DELETE operations.
%New() accepts three optional comma-separated parameters in the following order:
  1. %SelectMode, which specifies the data display mode.
  2. %SchemaPath, which specifies the search path used to supply the schema name for an unqualified table name.
  3. %Dialect, which specifies the Transact-SQL (TSQL) Sybase or MSSQL dialect. The default is Caché SQL.
There is also an %ObjectSelectMode property, which cannot be set as a %New() parameter. %ObjectSelectMode specifies the data type binding of fields to their related object properties.
In the following Caché ObjectScript example, the %SelectMode is 2 (Display mode), and the %SchemaPath specifies “Sample” as the default schema:
  SET tStatement = ##class(%SQL.Statement).%New(2,"Sample")
In the following Caché ObjectScript example, a %SelectMode is not specified (note the placeholder comma), and the %SchemaPath specifies a schema search path containing three schema names:
  SET tStatement = ##class(%SQL.Statement).%New(,"MyTests,Sample,Cinema")
%SelectMode Property
The %SelectMode property specifies the data display mode. 0=Logical (the default), 1=ODBC, 2=Display.
The %SelectMode property is used for SELECT query operations and for INSERT and UPDATE operations. This mode is most commonly used for date and time values and for displaying %List data.
%SelectMode is used for data display. SQL statements run internally in Logical mode. For example, an ORDER BY clause orders records based on their Logical values, regardless of the %SelectMode setting. Methods projected as SQLPROC also run in Logical mode. SQL routines called as functions in an SQL statement need to return the function value in Logical format.
You can specify %SelectMode either as the first parameter of the %New() class method, or set it directly, as shown in the following two examples:
  SET tStatement = ##class(%SQL.Statement).%New(2)
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=2
The following example returns the current value of %SelectMode:
  SET tStatement = ##class(%SQL.Statement).%New()
  WRITE !,"default select mode=",tStatement.%SelectMode
  SET tStatement.%SelectMode=2
  WRITE !,"set select mode=",tStatement.%SelectMode
 
You can determine the SelectMode default setting for the current process using the $SYSTEM.SQL.GetSelectMode() method. You can change the SelectMode default setting for the current process using the $SYSTEM.SQL.SetSelectMode(n) method, when n can be 0=Logical, 1=ODBC, or 2=Display. Setting %SelectMode overrides this default for the current object instance; it does not change the SelectMode process default.
For further details on SelectMode options, refer to Data Display Options in the “Caché SQL Basics” chapter of this book.
%SchemaPath Property
The %SchemaPath property specifies the search path used to supply the schema name for an unqualified table name, view name, or stored procedure name. A search path is specified as a quoted string containing a schema name or a comma-separated series of schema names. Caché searches the listed schemas in left-to-right order. Caché searches each specified schema until it locates the first matching table, view, or stored procedure name. Because schemas are searched in the specified order, there is no detection of ambiguous table names. Only schema names in the current namespace are searched.
The schema search path can contain both literal schema names and the CURRENT_PATH, CURRENT_SCHEMA, and DEFAULT_SCHEMA keywords.
The %SchemaPath is the first place Caché searches for a schema name. If %SchemaPath is not specified, or does not contain a matching schema name, Caché uses the system-wide Default SQL Schema Name, as defined in the Management Portal System Administration, Configuration, SQL and Object Settings, General SQL Settings ([Home] > [Configuration] > [General SQL Settings]). Refer to system default schema for details.
You can specify %SchemaPath either as the second parameter of the %New() class method, or set it directly, as shown in the following two examples:
  SET path="MyTests,Sample,Cinema"
  SET tStatement = ##class(%SQL.Statement).%New(,path)
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SchemaPath="MyTests,Sample,Cinema"
You can set %SchemaPath at any point prior to the %Prepare() method which uses it.
The following example returns the current value of %SchemaPath:
  SET tStatement = ##class(%SQL.Statement).%New()
  WRITE !,"default path=",tStatement.%SchemaPath
  SET tStatement.%SchemaPath="MyTests,Sample,Cinema"
   WRITE !,"set path=",tStatement.%SchemaPath
 
You can use the %ClassPath() method to set %SchemaPath to the search path defined for the specified class name:
  ZNSPACE "SAMPLES"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SchemaPath=tStatement.%ClassPath("Sample.Person")
  WRITE tStatement.%SchemaPath
 
%Dialect Property
The %Dialect property specifies the SQL statement dialect. You can specify either Sybase or MSSQL. This setting causes the SQL statement to be processed using the specified Transact-SQL dialect.
The Sybase and MSSQL dialects support a limited subset of SQL statements in these dialects. They support the SELECT, INSERT, UPDATE, DELETE, and EXECUTE statements. They support the CREATE TABLE statement for permanent tables, but not for temporary tables. CREATE VIEW is supported. CREATE TRIGGER and DROP TRIGGER are supported. However, this implementation does not support transaction rollback should the CREATE TRIGGER statement partially succeed but then fail on class compile. CREATE PROCEDURE and CREATE FUNCTION are supported.
The Sybase and MSSQL dialects support the IF flow-of-control statement. This command is not supported in the Caché SQL dialect.
The default is Caché SQL, which can be represented either by a null or “CACHE”.
You can specify %Dialect either as the third parameter of the %New() class method, or set it directly as a property, or set it using a method, as shown in the following three examples:
Setting %Dialect in %New() class method:
  SET tStatement = ##class(%SQL.Statement).%New(,,"Sybase")
  WRITE "language mode set to=",tStatement.%Dialect
 
Setting the %Dialect property directly:
  SET tStatement = ##class(%SQL.Statement).%New()
  SET defaultdialect=tStatement.%Dialect
  WRITE "default language mode=",defaultdialect,!
  SET tStatement.%Dialect="Sybase"
  WRITE "language mode set to=",tStatement.%Dialect,!
  SET tStatement.%Dialect="Cache"
   WRITE "language mode reset to default=",tStatement.%Dialect,!
 
Setting the %Dialect property using the %DialectSet() instance method, which returns an error status:
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatus = tStatement.%DialectSet("Sybase")
    IF tStatus'=1 {WRITE "%DialectSet failed:" DO $System.Status.DisplayError(tStatus) QUIT}
  WRITE "language mode set to=",tStatement.%Dialect
 
The %DialectSet() method returns a %Status value: Success returns a status of 1. Failure returns an object expression that begins with 0, followed by encoded error information. For this reason, you cannot perform a tStatus=0 test for failure; you can perform a $$$ISOK(tStatus)=0 macro test for failure.
%ObjectSelectMode Property
The %ObjectSelectMode property is a boolean value. If %ObjectSelectMode=0 (the default) all columns in the SELECT list are bound to properties with literal types in the result set. If %ObjectSelectMode=1 then columns in the SELECT list are bound to properties with the type defined in the associated property definition.
%ObjectSelectMode allows you to specify how columns whose type class is a swizzleable class will be defined in the result set class generated from a SELECT statement. If %ObjectSelectMode=0 the property corresponding to the swizzleable column will be defined in result sets as a simple literal type corresponding to the SQL table's ROWID type. If %ObjectSelectMode=1 the property will be defined with the column’s declared type. That means that accessing the result set property will trigger swizzling.
%ObjectSelectMode cannot be set as a parameter of %New().
The following example returns the %ObjectSelectMode default value, sets %ObjectSelectMode, then returns the new %ObjectSelectMode value:
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 5 %ID AS MyID,Name,Age FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  WRITE !,"default ObjectSelectMode=",tStatement.%ObjectSelectMode
  SET tStatement.%ObjectSelectMode=1
  WRITE !,"set ObjectSelectMode=",tStatement.%ObjectSelectMode
 
%ObjectSelectMode=1 is principally used when returning values from a result set using the field name property. This is further described with examples in Fieldname Property in the “Returning Specific Values from the Result Set” section of this chapter.
Preparing an SQL Statement
Preparing an SQL statement validates the statement, prepares it for subsequent execution, and generates metadata about the SQL statement.
There are three ways to prepare a statement:
Preparing an SQL statement places the statement in the query cache. This allows the same query to be executed multiple times without the need to re-prepare the SQL statement. A cached query can be executed one or more times by any process; it can be executed with different input parameter values.
Each time you prepare an SQL statement, Caché searches the query cache to determine if the same SQL statement has already been prepared and cached. If not, it places the prepared statement in the query cache. If the prepared statement already exists in the query cache, no new cached query is created. For this reason, it is important not to code a prepare statement within a loop structure.
%Prepare()
You can prepare an SQL statement using the %Prepare() instance method of the %SQL.Statement class. The %Prepare() method takes, as its first parameter, the SQL statement. This can be specified as a quoted string or a variable that resolves to a quoted string.
For example, in Caché ObjectScript:
  SET qStatus = tStatement.%Prepare("SELECT Name,Age FROM Sample.Person")
Or in Caché Basic:
qStatus = tStatement.%Prepare("SELECT Name,Age FROM Sample.Person")
More complex queries can be specified using a subscripted array passed by reference, as shown in the following example:
  SET myquery = 3
  SET myquery(1) = "SELECT %ID AS id, Name, DOB, Home_State"
  SET myquery(2) = "FROM Person WHERE Age > 80"
  SET myquery(3) = "ORDER BY 2"
  SET qStatus = tStatement.%Prepare(.myquery)
A query can contain duplicate field names and field name aliases.
A query supplied to %Prepare() can contain input host variables, as shown in the following example:
  SET minage = 80
  SET myquery = 3
  SET myquery(1) = "SELECT %ID AS id, Name, DOB, Home_State"
  SET myquery(2) = "FROM Person WHERE Age > :minage"
  SET myquery(3) = "ORDER BY 2"
  SET qStatus = tStatement.%Prepare(.myquery)
Caché substitutes the defined literal value for each input host variable when the SQL statement is executed. You can also supply literal values to a query using ? input parameters. Caché substitutes a literal value for each ? input parameter using the corresponding parameter value you supply to the %Execute() method. Following a %Prepare(), you can use the %GetImplementationDetails() method to list the input host variables and the ? input parameters in the query.
The %Prepare() method returns a %Status value: Success returns a status of 1 (the query string is valid; referenced tables exist in the current namespace). Failure returns an object expression that begins with 0, followed by encoded error information. For this reason, you cannot perform a status=0 test for failure; you can perform a $$$ISOK(status)=0 macro test for failure.
The %Prepare() method uses the %SchemaPath property defined earlier to resolve unqualified names.
Note:
Dynamic SQL performance can be significantly improved by using fully qualified names whenever possible.
You can specify input parameters in the SQL statement by using the “? ” character:
  SET myquery="SELECT TOP ? Name,Age FROM Sample.Person WHERE Age > ?"
  SET qStatus = tStatement.%Prepare(myquery)
You specify the value for each ? input parameter in the %Execute() instance method when you execute the query. An input parameter must take a literal value or an expression that resolves to a literal value. An input parameter cannot take a field name value or a field name alias. An input parameter must be declared PUBLIC for a SELECT statement to reference it directly.
A query can contain field aliases. In this case, the Data property accesses the data using the alias, not the field name.
You are not limited to SELECT statements within Dynamic SQL: you can use the %Prepare() instance method to prepare other SQL statements, including the CALL, INSERT, UPDATE, and DELETE statements.
You can display information about the currently prepared statement using the %Display() instance method, as shown in the following example:
  ZNSPACE "SAMPLES"
  SET tStatement = ##class(%SQL.Statement).%New(,"Sample")
  SET myquery = 3
  SET myquery(1) = "SELECT TOP ? Name,DOB,Home_State"
  SET myquery(2) = "FROM Person"
  SET myquery(3) = "WHERE Age > 60 AND Age < 65"
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  DO tStatement.%Display()
  WRITE !,"End of %Prepare display"
 
This information consists of the Implementation Class, the Arguments (a comma-separated list of the actual arguments, either literal values or ? input parameters), and the Statement Text.
%PrepareClassQuery()
You can prepare an existing SQL query using the %PrepareClassQuery() instance method of the %SQL.Statement class. The %PrepareClassQuery() method takes two parameters: the class name of the existing query, and the query name. Both are specified as a quoted string or a variable that resolves to a quoted string.
For example, in Caché ObjectScript:
  SET qStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
Or in Caché Basic:
qStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
The %PrepareClassQuery() method returns a %Status value: Success returns a status of 1. Failure returns an object expression that begins with 0, followed by encoded error information. For this reason, you cannot perform a qStatus=0 test for failure; you can perform a $$$ISOK(qStatus)=0 macro test for failure.
The %PrepareClassQuery() method uses the %SchemaPath property defined earlier to resolve unqualified names.
%PrepareClassQuery() executes using a CALL statement. Because of this, the executed class query must have an SqlProc parameter.
The following example shows %PrepareClassQuery() invoking the ByName query defined in the Sample.Person class, passing a string to limit the names returned to those that start with that string value:
  SET statemt=##class(%SQL.Statement).%New()
  SET cqStatus=statemt.%PrepareClassQuery("Sample.Person","ByName")
    IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
  SET rset=statemt.%Execute("L")
  DO rset.%Display()
 
The following example shows %PrepareClassQuery() invoking an existing query:
   SET tStatement=##class(%SQL.Statement).%New()
   SET cqStatus=tStatement.%PrepareClassQuery("%SYS.GlobalQuery","Size")
     IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
   SET install=$SYSTEM.Util.InstallDirectory()
   SET rset=tStatement.%Execute(install_"mgr\Samples")
   DO rset.%Display()  
 
The following example shows %Prepare() preparing a CREATE QUERY statement, and then %PrepareClassQuery() invoking this class query:
  ZNSPACE "SAMPLES"
  /* Creating the Query */
  SET myquery=4
    SET myquery(1)="CREATE QUERY DocTest() SELECTMODE RUNTIME PROCEDURE "
    SET myquery(2)="BEGIN "
    SET myquery(3)="SELECT TOP 5 Name,Home_State FROM Sample.Person ; "
    SET myquery(4)="END"
  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()
  IF rset.%SQLCODE=0 { WRITE !,"Created a query",! }
  ELSEIF rset.%SQLCODE=-361 { WRITE !,"Query exists: ",rset.%Message,! }
  ELSE { WRITE !,"CREATE QUERY error: ",rset.%SQLCODE," ",rset.%Message   QUIT}
  /* Calling the Query */
  WRITE !,"Calling a class query"
  SET cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
    IF cqStatus'=1 {WRITE !,"%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
  SET rset = tStatement.%Execute()
  WRITE "Query data",!,!
  WHILE rset.%Next() {
     DO rset.%Print() } 
  WRITE !,"End of data"
  /* Deleting the Query */
  &sql(DROP QUERY DocTest)
  IF SQLCODE=0 { WRITE !,"Deleted the query" } 
 
To display a row of data retrieved by a stored query you can use the %Print() method, as shown in this example. To display specific column data that was retrieved by a stored query you must use either the %Get("fieldname") or the %GetData(colnum) method. See Iterating through a Result Set.
If the query is defined to accept arguments, you can specify input parameters in the SQL statement by using the “? ” character. You specify the value for each ? input parameter in the %Execute() method when you execute the query. An input parameter must be declared PUBLIC for a SELECT statement to reference it directly.
You can display information about the currently prepared query using the %Display() method, as shown in the following example:
  ZNSPACE "SAMPLES"
  /* Creating the Query */
  SET myquery=4
    SET myquery(1)="CREATE QUERY DocTest() SELECTMODE RUNTIME PROCEDURE "
    SET myquery(2)="BEGIN "
    SET myquery(3)="SELECT TOP 5 Name,Home_State FROM Sample.Person ; "
    SET myquery(4)="END"
  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()
  IF rset.%SQLCODE=0 { WRITE !,"Created a query",! }
  ELSEIF rset.%SQLCODE=-361 { WRITE !,"Query exists: ",rset.%Message }
  ELSE { WRITE !,"CREATE QUERY error: ",rset.%SQLCODE," ",rset.%Message   QUIT}
  /* Preparing and Displying Info about the Query */
  WRITE !,"Preparing a class query"
  SET cqStatus = tStatement.%PrepareClassQuery("User.queryDocTest","DocTest")
    IF cqStatus'=1 {WRITE !,"%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
  DO tStatement.%Display()
  WRITE !,"End of %Prepare display"
  /* Deleting the Query */
  &sql(DROP QUERY DocTest)
  IF SQLCODE=0 { WRITE !,"Deleted the query" }
 
This information consists of the Implementation Class, the Arguments (a comma-separated list of the actual arguments, either literal values or ? input parameters), and the Statement Text.
For further details, refer to Defining and Using Class Queries in Using Caché Objects.
Executing an SQL Statement
You can execute an SQL statement using either of the following methods:
%Execute()
After preparing a query, you can execute it by calling the %Execute() instance method of the %SQL.Statement class. In the case of a non-SELECT statement, %Execute() invokes the desired operation (such as performing an INSERT). In the case of a SELECT query, %Execute() generates a result set for subsequent traversal and data retrieval.
For example, in Caché ObjectScript:
  SET rset = tStatement.%Execute()
Or in Caché Basic:
rset = tStatement.%Execute()
The %Execute() method sets the %SQL.StatementResult class properties %SQLCODE and %Message for all SQL statements. %Execute() sets other %SQL.StatementResult properties as follows:
For further details, refer to the corresponding SQL System Variables in the “Using Embedded SQL” chapter of this manual. If you are executing TSQL code with %Dialect set to Sybase or MSSQL, errors are reported both in the standard protocols for that SQL dialect and in the Caché %SQLCODE and %Message properties.
%Execute() with Input Parameters
The %Execute() method can take one or more parameters that correspond to the input parameters (indicated by “?”) within the prepared SQL statement. The %Execute() parameters correspond to the sequence in which the “?” characters appear within the SQL statement: the first parameter is used for the first “?”, the second parameter for the second “?”, and so on. Multiple %Execute() parameters are separated by commas. You can omit a parameter value by specifying the placeholder comma. The number of %Execute() parameters must correspond to the “?” input parameters. If there are fewer or more %Execute() parameters than corresponding “?” input parameters, execution fails with the %SQLCODE property set to an SQLCODE -400 error.
There is no limit on the number of input parameters. You can use an input parameter to supply a literal value or an expression to the SELECT list and to the other query clauses, including the TOP clause and the WHERE clause. You cannot use an input parameter to supply a column name or a column name alias to the SELECT list or to the other query clauses.
You can use the %GetImplementationDetails() method to return a list of ? input parameters in a prepared query.
The following Caché ObjectScript example executes a query with two input parameters. It specifies the input parameter values (21 and 26) in the %Execute() method.
  ZNSPACE "SAMPLES"
  SET tStatement = ##class(%SQL.Statement).%New(1)
  SET tStatement.%SchemaPath = "MyTests,Sample,Cinema"
  SET myquery=2
  SET myquery(1)="SELECT Name,DOB,Age FROM Person"
  SET myquery(2)="WHERE Age > ? AND Age < ? ORDER BY Age"
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(21,26)
  WRITE !,"Execute OK: SQLCODE=",rset.%SQLCODE,!!
  DO rset.%Display()
  WRITE !,"End of data: SQLCODE=",rset.%SQLCODE
 
The following Caché ObjectScript example executes the same query. The %Execute() method uses dynamic dispatch (...) to specify an indefinite number of input parameter values; in this case, the subscripts of the dynd array. The dynd variable is set to 2 to indicate two subscript values.
  ZNSPACE "SAMPLES"
  SET tStatement = ##class(%SQL.Statement).%New(1)
  SET tStatement.%SchemaPath = "MyTests,Sample,Cinema"
  SET myquery=2
  SET myquery(1)="SELECT Name,DOB,Age FROM Person"
  SET myquery(2)="WHERE Age > ? AND Age < ? ORDER BY Age"
  SET dynd=2,dynd(1)=21,dynd(2)=26
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(dynd...)
  WRITE !,"Execute OK: SQLCODE=",rset.%SQLCODE,!!
  DO rset.%Display()
  WRITE !,"End of data: SQLCODE=",rset.%SQLCODE
 
You can issue multiple %Execute() operations on a prepared result set. This enables you to run a query multiple times, supplying different input parameter values. It is not necessary to close the result set between %Execute() operations, as shown in the following example:
  ZNSPACE "SAMPLES"
  SET myquery="SELECT Name,SSN,Age FROM Sample.Person WHERE Name %STARTSWITH ?"
  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("A")
  DO rset.%Display()
  WRITE !,"End of A data",!!
  SET rset = tStatement.%Execute("B")
  DO rset.%Display()
  WRITE !,"End of B data"
 
Handling %Execute Errors Using TRY/CATCH
You can execute Dynamic SQL within a TRY block structure, passing runtime errors to the associated CATCH block exception handler. For %Execute() errors, you can use the %Exception.SQL class to create an exception instance, which you can then THROW to the CATCH exception handler.
The following example creates an SQL exception instance when an %Execute() error occurs. In this case, the error is a cardinality mismatch between the number of ? input parameters (1) and the number of %Execute() parameters (3). It throws the %SQLCODE and %Message property values (as Code and Data) to the CATCH exception handler. The exception handler uses the %IsA() instance method to test the exception type, then displays the %Execute() error:
  TRY {
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP ? Name,DOB 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(7,9,4)
     IF rset.%SQLCODE=0 { WRITE !,"Executed query",! }
     ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)
            THROW badSQL }
  DO rset.%Display()
  WRITE !,"End of data"
  RETURN
  }
  CATCH exp { WRITE "In the CATCH block",!
              IF 1=exp.%IsA("%Exception.SQL") {
                WRITE "SQLCODE: ",exp.Code,!
                WRITE "Message: ",exp.Data,! }
              ELSE { WRITE "Not an SQL exception",! }
              RETURN
  }
 
%ExecDirect()
The %SQL.Statement class provides the %ExecDirect() class method, that both prepares and executes a query in a single operation. The following is an example of %ExecDirect():
  ZNSPACE "SAMPLES"
  SET myquery=2
  SET myquery(1)="SELECT Name,Age FROM Sample.Person"
  SET myquery(2)="WHERE Age > 21 AND Age < 30 ORDER BY Age"
  SET rset = ##class(%SQL.Statement).%ExecDirect(,.myquery)
    IF rset.%SQLCODE=0 { WRITE !,"ExecDirect OK",!! }
    ELSE { WRITE !,"ExecDirect SQLCODE=",rset.%SQLCODE,!,rset.%Message  QUIT}
  DO rset.%Display()
  WRITE !,"End of data: SQLCODE=",rset.%SQLCODE
 
You can specify input parameter values as the third and subsequent parameters of the %ExecDirect() class method, as shown in the following example:
  ZNSPACE "SAMPLES"
  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.%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.%Display()
  WRITE !,"End of twenties data"
 
The %ExecDirect() input parameters correspond to the sequence in which the “?” characters appear within the SQL statement: the third parameter is used for the first “?”, the fourth parameter for the second “?”, and so on. You can omit a parameter value by specifying a placeholder comma. If there are fewer %ExecDirect() input parameters than corresponding “?” input parameters, the default value (if one exists) is used.
In the following example, the first %ExecDirect() specifies all three “?” input parameters, the second %ExecDirect() specifies only the second ? input parameter, and omits the first and third. It takes the Sample.PersonSets() default ('MA') for the third input parameter:
  ZNSPACE "SAMPLES"
  SET mycall = "?=CALL Sample.PersonSets(?,?)"
  SET rset = ##class(%SQL.Statement).%ExecDirect(,mycall,"","A","NH")
    IF rset.%SQLCODE'=0 {WRITE !,"1st ExecDirect SQLCODE=",rset.%SQLCODE,!,rset.%Message  QUIT}
  DO rset.%Display()
  WRITE !,"End of A people data",!!
  SET rset2 = ##class(%SQL.Statement).%ExecDirect(,mycall,,"B")
    IF rset2.%SQLCODE'=0 {WRITE !,"2nd ExecDirect SQLCODE=",rset2.%SQLCODE,!,rset2.%Message  QUIT}
  DO rset2.%Display()
  WRITE !,"End of B people data"
 
Returning the Full Result Set
Executing a statement with either %Execute() or %ExecDirect() returns an object that implements the %SQL.StatementResult interface. This object can be a unitary value, a result set, or a context object that is returned from a CALL statement.
%Display() Method
You can display the entire result set (the contents of the result object) by calling the %Display() instance method of the %SQL.StatementResult class.
For example, in Caché ObjectScript:
  DO rset.%Display()
Or in Caché Basic:
CALL 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.
%DisplayFormatted() Method
You can reformat and redirect the result set contents to a generated file by calling the %DisplayFormatted() instance method of the %SQL.StatementResult class, rather than calling %Display(). You can specify XML (integer code 0), HTML (integer code 1), PDF (integer code 2), TXT (integer code 99), or CSV (comma-separated values, integer code 100) format. Caché generates a file of that type, appending the appropriate file name extension. If the specified file already exists, Caché overwrites it with new data.
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. Messages are written to a separate file.
The following are Windows filenames; Caché supports equivalent locations on other operating systems:
TXT formatting (integer code 99) concludes with the row count (for example “5 Rows(s) Affected”); the other formats do not include a row count.
The following Windows example creates two PDF (integer code 2) result set files in C:\InterSystems\Cache\mgr\samples\:
  ZNSPACE "SAMPLES"
  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")
  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")
  WRITE !,"End of twenties data"
 
Returning Specific Values from the Result Set
To return specific values from a query result set, you must iterate through the result set one row at a time. To iterate through a result set, use the %Next() instance method. (For unitary values, there are no rows in the result object, so %Next() returns 0 — and not an error.) You can then either display the results of the whole current row using the %Print() method, or retrieve the value of a specified column in the current row.
The %Next() method fetches the data for the next row within the query results and places this data in the Data property of the result set object. %Next() returns 1 to indicate that it is positioned on a row in the query result. %Next() returns 0 to indicate that it is positioned after the last row (at the end of the result set). Each invocation of %Next() that returns 1 increments %ROWCOUNT; if the cursor is positioned after the last row (%Next() returns 0), the %ROWCOUNT indicates the number of rows in the result set.
After fetching a row from the result set, you can display data from that row using any of the following:
%Print() Method
The %Print() instance method retrieves the current record from the result set. By default, %Print() inserts a blank space between data field values. %Print() does not insert a blank space 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 delimited by quotation marks. 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.
You can optionally specify a %Print() parameter that provides a delimiter to be placed between the field values. This delimiter can be one or more characters. It is specified as a quoted string. It is generally preferable that the delimiter be a character or string not found in the result set data. However, if a field value in the result set contains the delimiter character (or string), that field value is returned delimited by quotation marks to differentiate it from the delimiter immediately following it.
If a field value in the result set contains a line feed character, that field value is returned delimited by quotation marks.
The following Caché 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:
  ZNSPACE "SAMPLES"
  SET q1="SELECT TOP 5 Name,DOB,Home_State,FavoriteColors "
  SET q2="FROM Sample.Person WHERE FavoriteColors IS NOT NULL"
  SET myquery = q1_q2
  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()
  WHILE rset.%Next() {
     WRITE "Row count ",rset.%ROWCOUNT,!
     DO rset.%Print("^|^")
     }
  WRITE !,"End of data"
  WRITE !,"Total row count=",rset.%ROWCOUNT
 
In Caché Basic:
q1 = "SELECT TOP 5 Name,DOB,Home_State,FavoriteColors "
q2 = "FROM Sample.Person WHERE FavoriteColors IS NOT NULL"
myquery = q1 & q2
tStatement = New %SQL.Statement()
qStatus = tStatement.%Prepare(myquery)
If qStatus<>1 Then 
   PrintLn "%Prepare failed:"
   PrintLn Piece(qStatus," ",3,10)
Else
 rset = tStatement.%Execute()
 While (rset.%Next())
    PrintLn "Row count ",rset.%ROWCOUNT
    rtn=rset.%Print("^|^")
 Wend
 PrintLn "End of data"
 PrintLn "Total row count=",rset.%ROWCOUNT
End If
 
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.
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 25 Name,Home_Street,Home_State,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()
  WHILE rset.%Next() {
     DO rset.%Print("A")
     }
  WRITE !,"End of data"
  WRITE !,"Total row count=",rset.%ROWCOUNT
 
rset.name Property
When Caché 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.
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:
For a user-specified query prepared using %Prepare() you can use the property name by itself. For a stored query prepared using %PrepareClassQuery(), you must use the %Get("fieldname") 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:
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 5 Name,DOB AS bdate,FavoriteColors FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New(1)
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
     WRITE "Row count ",rset.%ROWCOUNT,!
     WRITE rset.Name
     WRITE " prefers ",rset.FavoriteColors
     WRITE " birth date ",rset.bdate,!!
     }
  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:
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 5 Name,Home_State FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New(2)
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
     WRITE "Row count ",rset.%ROWCOUNT,!
     WRITE rset.Name
     WRITE " lives in ",rset."Home_State",!
       }
  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). Caché 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:
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 5 Name,Home FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New(0)
  SET tStatement.%ObjectSelectMode=1
  WRITE !,"set ObjectSelectMode=",tStatement.%ObjectSelectMode,!
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
     WRITE "Row count ",rset.%ROWCOUNT,!
     WRITE rset.Name
     WRITE " ",rset.Home,!
     WRITE rset.%GetData(1)
     WRITE " ",$LISTTOSTRING(rset.%GetData(2)),!!
       }
  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:
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 5 %ID AS MyID,Name,Age FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%ObjectSelectMode=1
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
     WRITE rset.Name
     WRITE " Home State:",rset.MyID.Home.State,!
     }
  WRITE !,"End of data"
  WRITE !,"Total row count=",rset.%ROWCOUNT
 
Caché 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.
You can turn this behavior off globally by setting: SET ^%SYS("ThrowSwizzleError")=0, or by using the Caché Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General Object Settings ([Home] > [Configuration] > [General Object Settings]). On this screen you can set the <SWIZZLE FAIL> option.
%Get("fieldname") Method
You can use the %Get("fieldname") 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, Caché 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.
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 5 Home_State,Name AS Last_Name FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New(2)
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
    WRITE rset.%Get("Home_State")," : ",rset.%Get("Last_Name"),!
    }
  WRITE !,"End of data"
  WRITE !,"Total row count=",rset.%ROWCOUNT
 
You must use the %Get("fieldname") instance method to retrieve individual data items by field property name from an existing query prepared using %PrepareClassQuery(). If the field property name does not exist, Caché 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 tStatement = ##class(%SQL.Statement).%New(2)
  SET qStatus = tStatement.%PrepareClassQuery("%SYS.Namespace","List")
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
     WRITE "Namespace: ",rset.%Get("Nsp"),!
     }
  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:
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT c.Name,p.Name FROM Sample.Person AS p,Sample.Company AS c"
  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()
  WHILE rset.%Next() {
  WRITE "Prop=",rset.Name," Get=",rset.%Get("Name"),! }
  WRITE !,rset.%ROWCOUNT," End of data"
 
%GetData(n) Method
The %GetData(n) 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 ID 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.
In Caché ObjectScript:
  ZNSPACE "SAMPLES"
  SET myquery="SELECT TOP 5 Name,SSN,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()
  WHILE rset.%Next() {
     WRITE "Years:",rset.%GetData(3)," Name:",rset.%GetData(1),!
     }
   WRITE "End of data"
   WRITE !,"Total row count=",rset.%ROWCOUNT
 
In Caché Basic:
myquery = "SELECT TOP 5 Name,SSN,Age FROM Sample.Person"
tStatement = New %SQL.Statement()
qStatus = tStatement.%Prepare(myquery)
If qStatus<>1 Then 
   PrintLn "%Prepare failed:"
   PrintLn Piece(qStatus," ",3,10)
Else
 rset = tStatement.%Execute()
 While (rset.%Next())
    Print "Years:",rset.%GetData(3)
    PrintLn " Name:",rset.%GetData(1)
 Wend
 PrintLn "End of data"
 PrintLn "Total row count=",rset.%ROWCOUNT
End If
 
Returning Multiple Result Sets
A CALL statement can return multiple dynamic result sets as a collection referred to as a result set sequence (RSS).
The following example uses the %NextResult() method to return multiple result sets separately:
  ZNSPACE "SAMPLES"
  SET mycall = "CALL Sample.CustomSets()"
  SET rset = ##class(%SQL.Statement).%ExecDirect(,mycall)
     IF rset.%SQLCODE'=0 {WRITE !,"ExecDirect SQLCODE=",rset.%SQLCODE,!,rset.%Message  QUIT}
  SET rset1=rset.%NextResult()
  DO rset1.%Display()
  WRITE !,"End of 1st Result Set data",!!
  SET rset2=rset.%NextResult()
  DO rset2.%Display()
  WRITE !,"End of 2nd Result Set data"
 
SQL Metadata
The %SQL.StatementMetadata class provide properties that return SQL metadata. This metadata includes:
SQL Statement Metadata
Some SQL statement metadata can be returned after preparing an SQL statement, using properties of the %SQL.StatementMetadata class. Some SQL statement metadata can be returned after executing an SQL statement, using properties of the %SQL.StatementResult class. The following is a partial list of these metadata items.
Statement Metadata Examples
%SQL.StatementMetadata property values are available following the %Prepare() operation, as shown in the following example. These include the statement type (but not the statement name), the column count, and the input parameter count:
  ZNSPACE "SAMPLES"
  SET myquery = 4
  SET myquery(1) = "SELECT TOP ? %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE"
  SET myquery(2) = "FROM Sample.Person"
  SET myquery(3) = "WHERE Age > ?"
  SET myquery(4) = "ORDER BY $PIECE(Name,',',?)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  WRITE "Statement type code=",tStatement.%Metadata.statementType,!
  WRITE "Number of columns=",tStatement.%Metadata.columnCount,!
  WRITE "Number of ? parameters=",tStatement.%Metadata.parameterCount,!
  WRITE "End of metadata"
 
%SQL.StatementResult property values are available following the %Execute() operation, as shown in the following example. These include the statement type, the statement type name, and the column count (but not the input parameter count).
  ZNSPACE "SAMPLES"
  SET myquery = 4
  SET myquery(1) = "SELECT TOP ? %ID AS id,Name,DOB,Age,"
  SET myquery(2) = "AVG(Age) AS AvgAge,CURRENT_DATE AS Today"
  SET myquery(3) = "FROM Sample.Person WHERE Age > ?"
  SET myquery(4) = "ORDER BY $PIECE(Name,',',?)"
  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(10,60,2)
  WRITE "%Execute SQLCODE=",rset.%SQLCODE,!,!
  WRITE "Statement type=",rset.%StatementType,!
  WRITE "Statement name=",rset.%StatementTypeName,!
  WRITE "Column count=",rset.%ResultColumnCount,!
  WRITE "End of metadata",!!
  DO rset.%Display()
  WRITE !,"%Display SQLCODE=",rset.%SQLCODE
 
%GetImplementationDetails() Method
Implementation details metadata can be returned after preparing an SQL statement, by invoking the %GetImplementationDetails() method, as follows:
WRITE statement.%GetImplementationDetails(.class,.text,.args)
This method returns 1 if an SQL statement has been prepared, and 0 if no SQL statement has been prepared.
The values assigned to the method parameters can be displayed as shown in the following example:
  ZNSPACE "SAMPLES"
  SET retireage = 65
  SET myquery = 4
  SET myquery(1) = "SELECT TOP 7 %ID AS id,Name,Age||'years',"
  SET myquery(2) = ":retireage-Age AS retire,CURRENT_DATE AS Today"
  SET myquery(3) = "FROM Sample.Person WHERE Age > ?"
  SET myquery(4) = "ORDER BY $PIECE(Name,',',2)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET x = tStatement.%GetImplementationDetails(.class,.text,.args)
  IF x=1 { WRITE "class is: ",class,!
           ZWRITE text
           ZWRITE args }
  ELSE { WRITE "SQL statement not prepared" }
 
The class is the class name of the prepared cached query.
The args are listed in the order that they appear in the query text. The args value for this example is $lb("c",7,"c","years","v","retireage","?","?"), where the TOP clause literal is represented by the pair "c",7 (“c” meaning constant), the concatenated string constant by the pair "c","years", the retire arithmetic expression using an input host variable (“v”) by the pair "v","retireage", and the WHERE clause input parameter by the pair "?","?".
SQL Metadata for Columns
After preparing a query using the %SQL.Statement class, you can find metadata about each column returned by the query, either by displaying all of the metadata, or by specifying individual metadata items. This column metadata includes ODBC data type information, as well as client type and Caché Objects property origins and class type information.
The following example displays all of the metadata using the %Metadata.%Display() instance method:
  ZNSPACE "SAMPLES"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare("SELECT TOP ? Name,DOB,Home_State FROM Sample.Person")
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  DO tStatement.%Metadata.%Display()
  WRITE !,"End of metadata"
 
This returns two table listings of the selected fields. The first columns metadata table lists column definition information:
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 the %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.
The second columns metadata table lists extended column information. The Extended Column Info table lists each column with twelve boolean flags (SQLRESULTCOL), 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.
The Extended Column Info metadata table also lists the object class projection of the selected fields. Note in this example that the Name and DOB fields project to the linked property Sample.Person.Name and Sample.Person.DOB. However, the Home_State field projects to the linked property Sample.Address.State. This is because this field accesses a linked container property. Home_State in Sample.Person is projected from the State property of the Home property’s type class of Sample.Address.
The statement metadata display then lists the Statement Parameters and Formal parameters. For each input parameter (? parameter) it lists the sequential parameter number, ODBC data type, precision, scale, whether it is nullable (2 means that a value is always supplied), and its corresponding property name (colName), and column type.
The following example returns specific metadata items: the column name and the ODBC data type for the selected fields:
  ZNSPACE "SAMPLES"
  SET myquery="SELECT Name,DOB FROM Sample.Person"
  SET rset = ##class(%SQL.Statement).%New()
  SET qStatus = rset.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET x=rset.%Metadata.columns.Count()
  WHILE x>0 {
    SET column=rset.%Metadata.columns.GetAt(x)
  WRITE !,x," ",column.colName," is data type ",column.ODBCType
  SET x=x-1 }
  WRITE !,"End of metadata"
 
The following example returns the column name, ODBC data type code, maximum data length (precision), and scale for every column in Sample.Employee:
  ZNSPACE "SAMPLES"
  SET myquery="SELECT * FROM Sample.Employee"
  SET rset = ##class(%SQL.Statement).%New()
  SET qStatus = rset.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET x=rset.%Metadata.columns.Count()
  WHILE x>0 {
    SET column=rset.%Metadata.columns.GetAt(x)
    WRITE !,x," ",column.colName," is data type ",column.ODBCType
    WRITE " with a size of ",column.precision," and scale = ",column.scale
    SET x=x-1 }
  WRITE !,"End of metadata"
 
Note that some ODBC data types are returned as negative numbers. For a table of ODBC data type codes, see the Data Types reference page in the Caché SQL Reference.
Dynamic SQL metadata includes extended metadata for each column. This extended metadata includes the ID of a property definition in the dictionary if the column can be linked back to a column in a table. This linked property ID when the column is projected from a container property is set to the ID of the property in the container property's type class. For example, Home_City in Sample.Person is projected from the City property of the Home property's type class of Sample.Address. The linked property ID is therefore "Sample.Address||City".
Metadata for Query Stored Procedures
The following example returns the metadata for a called stored procedure with one formal parameter, which is also a statement parameter:
  ZNSPACE "SAMPLES"
  SET mysql = "CALL Sample.SP_Sample_By_Name(?)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.mysql)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  DO tStatement.%Metadata.%Display()
  WRITE !,"End of metadata"
 
It returns not only column (field) information, but also values for Statement Parameters, Formal Parameters, and Objects.
The following example returns the metadata for a called stored procedure with three formal parameters. One of these three parameters is designated with a question mark (?) making it a statement parameter:
  ZNSPACE "SAMPLES"
  SET mycall = "CALL personsets(?,'MA')"
  SET tStatement = ##class(%SQL.Statement).%New(0,"sample")
  SET qStatus = tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  DO tStatement.%Metadata.%Display()
  WRITE !,"End of metadata"