Using Dynamic SQL
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.StatementOpens in a new tab 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.StatementOpens in a new tab implementation, unless otherwise indicated. You can also create Dynamic SQL programs using the older %ResultSet.SQLOpens in a new tab class or the %Library.ResultSetOpens in a new tab 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 an 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 queries are prepared at program execution time, not compilation time. This means that the compiler cannot check for errors at compilation time and preprocessor macros cannot be used within Dynamic SQL. It also means that executing programs can create specialized Dynamic SQL queries in response to user or other input.
-
Dynamic SQL can issue a CREATE TABLE or CREATE VIEW and perform an INSERT or SELECT on that table or view in the same routine. Embedded SQL, because it is compiled, cannot do this.
-
Dynamic SQL executes slightly less efficiently than Embedded SQL, because it does not generate in-line code for queries. However, re-execution of a Dynamic SQL query is substantially faster than the first execution of the query because Dynamic SQL supports cached queries.
-
Dynamic SQL can accept a literal value input to a query in two ways: input parameters specified using the “?” character, and input host variables (for example, :var). Embedded SQL uses input and output host variables (for example, :var).
-
Dynamic SQL output values are retrieved using the API of the result set object (that is, the Data property). Embedded SQL uses host variables (for example, :var) with the INTO clause of a SELECT statement to output values.
-
Dynamic SQL sets the %SQLCODE, %Message, %ROWCOUNT, and %ROWID object properties. Embedded SQL sets the corresponding SQLCODE, %msg, %ROWCOUNT, and %ROWID local variables. Dynamic SQL does not set %ROWID for a SELECT query; Embedded SQL sets %ROWID for a cursor-based SELECT query.
-
Dynamic SQL can be invoked from either ObjectScript or Caché Basic. Embedded SQL can only be invoked from ObjectScript.
-
Dynamic SQL provides an easy way to find query metadata (such as quantity and names of columns).
-
Queries prepared by Dynamic SQL are maintained within the query cache so that subsequent calls to prepare the same query can reuse previously generated code. Embedded SQL generates in-line code at compilation time and does not need to use the query cache. Note that Dynamic SQL does not cache most non-query SQL statements, because these statements are commonly only used once. Refer to the “Cached Queries” chapter of the Caché SQL Optimization Guide for further details.
-
Dynamic SQL performs SQL privilege checking; you must have the appropriate privileges to access or modify a table, field, etc. Embedded SQL does not perform SQL privilege checking. Refer to the SQL %CHECKPRIV statement for further details.
-
Dynamic SQL cannot access a private class method. To access an existing class method, the method must be made public. This is a general SQL limitation. However, Embedded SQL gets around this limitation because the Embedded SQL operation itself is a method of the same class.
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.StatementOpens in a new tab class. To prepare and execute Dynamic SQL statements, use an instance of %SQL.StatementOpens in a new tab. The result of executing a Dynamic SQL statement is an SQL statement result object that is an instance of the %SQL.StatementResultOpens in a new tab 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.IResultSetOpens in a new tab) and supports the expected result set functionality.
The following ObjectScript code prepares and executes a Dynamic SQL query:
/* Simple %SQL.Statement example */
SET $NAMESPACE="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.StatementOpens in a new tab and %SQL.StatementResultOpens in a new tab classes.
Creating an Object Instance
You can create an instance of the %SQL.StatementOpens in a new tab class using the %New()Opens in a new tab class method in 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.StatementOpens in a new tab 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:
-
%SelectMode, which specifies the mode used for data input and data display.
-
%SchemaPath, which specifies the search path used to supply the schema name for an unqualified table name.
-
%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 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 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 %SelectModeOpens in a new tab property specifies one of the following modes: 0=Logical (the default), 1=ODBC, 2=Display. These modes specify how a data value is input and displayed. A mode is most commonly used for date and time values and for displaying %List data (a string containing an encoded list). Data is stored in Logical mode.
A SELECT query uses the %SelectMode value to determine the format used to display data.
An INSERT or UPDATE operation uses the %SelectMode value to determine the permitted format(s) for data input.
%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. SQL functions use 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.
-
For a SELECT query, %SelectMode specifies the format used for displaying the data. Setting %SelectMode to ODBC or Display also affects the data format used for specifying comparison predicate values. Some predicate values must be specified in the %SelectMode format, other predicate values must be specified in Logical format, regardless of the %SelectMode. For details, refer to Overview of Predicates in the Caché SQL Reference.
-
Time data type data in %SelectMode=1 (ODBC) can display fractional seconds, which is not the same as actual ODBC time. The Caché Time data type supports fractional seconds. The corresponding ODBC TIME data type (TIME_STRUCT standard header definition) does not support fractional seconds. The ODBC TIME data type truncates a supplied time value to whole seconds. ADO DotNet and JDBC do not have this restriction.
-
%List data type data in %SelectMode=0 (Logical) does not display the internal storage value, because %List data is encoded using non-printing characters. Instead, Dynamic SQL displays a %List data value as a $LISTBUILD statement, such as the following: $lb("White","Green"). See %Print() Method for an example. %List data type data in %SelectMode=1 (ODBC) displays list elements separated by commas; this elements separator is specified as the CollectionOdbcDelimiterOpens in a new tab parameter. %List data type data in %SelectMode=2 (Display) displays list elements separated by $CHAR(10,13) (Line Feed, Carriage Return); this elements separator is specified as the CollectionDisplayDelimiterOpens in a new tab parameter.
-
-
For an INSERT or UPDATE operation, %SelectMode specifies the format for input data that will be converted to Logical storage format. For this data conversion to occur, the SQL code must have been compiled with a select mode of RUNTIME (the default) so that a Display or ODBC %SelectMode is used when the INSERT or UPDATE is executed. For permitted input values for dates and times, refer to the date and time data types. For further details, refer to the INSERT or UPDATE statement in the Caché SQL Reference.
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()Opens in a new tab method. You can change the SelectMode default setting for the current process using the $SYSTEM.SQL.SetSelectMode(n)Opens in a new tab 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 %SchemaPathOpens in a new tab property specifies the search path used to supply the schema name for an unqualified table name, view name, or stored procedure name. A schema search path is used for data management operations such as SELECT, CALL, INSERT, and TRUNCATE TABLE; it is ignored by data definition operations such as DROP TABLE.
The 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.
-
CURRENT_PATH specifies the current schema search path, as defined in a prior %SchemaPath property. This is commonly used to add schemas to the beginning or end of an existing schema search path.
-
CURRENT_SCHEMA specifies the current schema container class name if the %SQL.Statement call is made from within a class method. If a #sqlcompile path macro directive is defined in a class method, the CURRENT_SCHEMA is the schema mapped to the current class package. Otherwise, CURRENT_SCHEMA is the same as DEFAULT_SCHEMA.
-
DEFAULT_SCHEMA specifies the system-wide default schema. This keyword enables you to search the system-wide default schema as a item within the schema search path, before searching other listed schemas. The system-wide default schema is always searched after searching the schema search path if all the schemas specified in the path have been searched without a match.
The %SchemaPath is the first place Caché searches schemas for a matching table name. If %SchemaPath is not specified, or does not list a schema that contains a matching table name, Caché uses the system-wide default schema.
You can specify a schema search path either by specifying the %SchemaPath property, or by specifying the second parameter of the %New() class method, 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()Opens in a new tab method to set %SchemaPath to the search path defined for the specified class name:
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SchemaPath=tStatement.%ClassPath("Sample.Person")
WRITE tStatement.%SchemaPath
%Dialect Property
The %DialectOpens in a new tab 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()Opens in a new tab 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 %ObjectSelectModeOpens in a new tab 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:
SET $NAMESPACE="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:
-
%Prepare(), which prepares an SQL statement (a query, for example) for a subsequent %Execute().
-
%PrepareClassQuery(), which prepares a call statement to an existing query. Once prepared, this query can be executed using a subsequent %Execute().
-
%ExecDirect(), which both prepares and executes an SQL statement. %ExecDirect() is described in “Executing an SQL 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()Opens in a new tab instance method of the %SQL.StatementOpens in a new tab 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 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. Note however, that if this code is called as a method, the minage variable must be made Public. By default, methods are ProcedureBlocks; this means that a method (such as %Prepare()) cannot see variables defined by its caller. You can either override this default by specifying the class as [ Not ProcedureBlock ], specifying the method as [ ProcedureBlock = 0], or by specifying [ PublicList = minage ].
It is good program practice to always confirm that an input variable contains an appropriate value before inserting it into SQL code.
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.
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()Opens in a new tab instance method, as shown in the following example:
SET $NAMESPACE="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()Opens in a new tab instance method of the %SQL.StatementOpens in a new tab 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 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:
SET $NAMESPACE="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:
SET $NAMESPACE="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.
Results of a Successful Prepare
Following a successful prepare (%Prepare(), %PrepareClassQuery(), or %ExecDirect()) you can invoke the %SQL.StatementOpens in a new tab %Display()Opens in a new tab instance method or %GetImplementationDetails()Opens in a new tab instance method to return the details of the currently prepared statement. For example:
%Display():
SET myquery = "SELECT TOP 5 Name,Age FROM Sample.Person WHERE Age > 21"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
DO tStatement.%Display()
SET rset = tStatement.%Execute()
%GetImplementationDetails():
SET myquery = "SELECT TOP ? Name,Age FROM Sample.Person WHERE Age > 21 AND Name=:fname"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET bool = tStatement.%GetImplementationDetails(.pclassname,.ptext,.pargs)
IF bool=1 {WRITE "Implementation class= ",pclassname,!
WRITE "Statement text= ",ptext,!
WRITE "Arguments= ",$LISTTOSTRING(pargs),! } // returns "?,?,c,21,v,fname
ELSE {WRITE "%GetImplementationDetails() failed",!}
SET rset = tStatement.%Execute()
These methods provide the following information:
-
Implementation class: the class name corresponding to the cached query. For example: %sqlcq.SAMPLES.cls49.
-
Arguments: A list of the query arguments in the order specified. If an argument is enclosed in double parentheses to suppress literal substitution the argument is not included in the argument list.
%Display() displays a comma-separated list of the query arguments. Each argument can be a literal value, the name of an input host variables (without the colon), or a question mark (?) for an input parameter. If there are no arguments, this item displays <<none>>. A predicate that specifies multiple values, such as IN or %INLIST lists each value as a separate argument.
%GetImplementationDetails() returns the query arguments as a %List structure. Each argument is represented by a pair of elements, a type and a value: Type c (constant) is followed by a literal value; Type v (variable) is followed by the name of an input host variable (without the colon); Type ? is an input parameter, and is followed by a second question mark. If there are no arguments, the arguments list is an empty string. A predicate that specifies multiple values, such as IN or %INLIST lists each value as a separate type and value pair.
-
Statement Text: the query text, exactly as specified. Comments and letter case are preserved, host variables and input parameters are shown as written, the default schema is not shown. For %Prepare() for example, SELECT TOP :n Name FROM Clients. For %PrepareClassQuery() for example, call Sample.SP_Sample_By_Name(?).
For other metadata information generated for a prepared query, refer to SQL Metadata.
The preparse() Method
You can use the preparse()Opens in a new tab method to return a %List structure of the query arguments without having to prepare the SQL query. The query arguments are returned in the same format as %GetImplementationDetails().
The preparse() method also returns the query text. However, unlike %Display() and %GetImplementationDetails() which return the query text exactly as specified, the preparse() method replaces each query argument with a ? character, removes comments, and normalizes whitespace. It does not supply a default schema name. The preparse() method in the following example returns a parsed version of the query text and a %List structure of the query arguments:
SET myq=2
SET myq(1)="SELECT TOP ? Name /* first name */, Age "
SET myq(2)="FROM Sample.MyTable WHERE Name='Fred' AND Age > :years -- end of query"
DO ##class(%SQL.Statement).preparse(.myq,.stripped,.args)
WRITE "preparsed query text: ",stripped,!
WRITE "arguments list: ",$LISTTOSTRING(args)
Executing an SQL Statement
There are two ways to execute an SQL statement using the %SQL.StatementOpens in a new tab class:
-
%Execute(), which executes an SQL statement previous prepared using %Prepare() or %PrepareClassQuery().
-
%ExecDirect(), which both prepares and executes an SQL statement.
You can also execute an SQL statement without creating an object instance by using the %SYSTEM.SQL.Execute()Opens in a new tab method. This method both prepares and executes the SQL statement. It creates a cached query. The Execute() method is shown in the following Terminal example:
USER>SET topnum=5
USER>SET rset=$SYSTEM.SQL.Execute("SELECT TOP :topnum Name,Age FROM Sample.Person")
USER>DO rset.%Display()
%Execute()
After preparing a query, you can execute it by calling the %Execute()Opens in a new tab instance method of the %SQL.StatementOpens in a new tab 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 ObjectScript:
SET rset = tStatement.%Execute()
Or in Caché Basic:
rset = tStatement.%Execute()
The %Execute() method sets the %SQL.StatementResultOpens in a new tab class properties %SQLCODEOpens in a new tab and %MessageOpens in a new tab for all SQL statements. %Execute() sets other %SQL.StatementResultOpens in a new tab properties as follows:
-
INSERT, UPDATE, INSERT OR UPDATE, DELETE, and TRUNCATE TABLE statements set %ROWCOUNTOpens in a new tab to the number of rows affected by the operation, and %ROWIDOpens in a new tab to the Id of the last record inserted, updated, or deleted.
-
A SELECT statement sets the %ROWCOUNTOpens in a new tab property to 0 when it creates the result set. %ROWCOUNT is incremented when the program iterates through the contents of the result set, for example by using the %Next() method. %Next() returns 1 to indicate that it is positioned on a row or 0 to indicate that it is positioned after the last row (at the end of the result set). If the cursor is positioned after the last row, the value of %ROWCOUNT indicates the number of rows contained in the result set.
If a SELECT query returns only aggregate functions, every %Next() sets %ROWCOUNT=1. The first %Next() always sets %SQLCODE=0, even when there is no data in the table; any subsequent %Next() sets %SQLCODE=100 and sets %ROWCOUNT=1.
A SELECT also sets the %CurrentResultOpens in a new tab and the %ResultColumnCountOpens in a new tab. SELECT does not set %ROWID.
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.
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.
The maximum number of input parameters when specified as explicit %Execute() parameters is 255. The maximum number of input parameters when specified using a variable length array %Execute(vals...) is 380.
Following a Prepare, you can use Prepare arguments metadata to return the count and required data types for ? input parameters. You can use the %GetImplementationDetails() method to return a list of ? input parameters in a prepared query and the query text with the ? input parameters shown in context.
The following ObjectScript example executes a query with two input parameters. It specifies the input parameter values (21 and 26) in the %Execute() method.
SET $NAMESPACE="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 ObjectScript example executes the same query. The %Execute() method formal parameter list uses a variable length array (dynd...) 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.
SET $NAMESPACE="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:
SET $NAMESPACE="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.SQLOpens in a new tab 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 {
SET $NAMESPACE="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.StatementOpens in a new tab class provides the %ExecDirect()Opens in a new tab class method, that both prepares and executes a query in a single operation. It can prepare either a specified query (like %Prepare()) or an existing class query (like %PrepareClassQuery()).
%ExecDirect() prepares and executes a specified query:
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
%ExecDirect() prepares and executes an existing class query:
SET mycallq = "?=CALL Sample.PersonSets('A','NH')"
SET rset = ##class(%SQL.Statement).%ExecDirect(,mycallq)
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:
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:
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"
%ExecDirect() can invoke the %SQL.StatementOpens in a new tab %Display()Opens in a new tab instance method or %GetImplementationDetails()Opens in a new tab instance method to return the details of the currently prepared statement. Because %ExecDirect() can prepare and execute either a specified query or an existing class query, you can use the %GetImplementationDetails() pStatementType parameter to determine which kind of query was prepared:
SET mycall = "?=CALL Sample.PersonSets('A',?)"
SET rset = ##class(%SQL.Statement).%ExecDirect(tStatement,mycall,,"NH")
IF rset.%SQLCODE'=0 {WRITE !,"ExecDirect SQLCODE=",rset.%SQLCODE,!,rset.%Message QUIT}
SET bool = tStatement.%GetImplementationDetails(.pclassname,.ptext,.pargs,.pStatementType)
IF bool=1 {IF pStatementType=1 {WRITE "Type= specified query",!}
ELSEIF pStatementType=45 {WRITE "Type= existing class query",!}
WRITE "Implementation class= ",pclassname,!
WRITE "Statement text= ",ptext,!
WRITE "Arguments= ",$LISTTOSTRING(pargs),!! }
ELSE {WRITE "%GetImplementationDetails() failed"}
DO rset.%Display()
WRITE !,"End of data"
For further details, see Results of a Successful Prepare.
Returning the Full Result Set
Executing a statement with either %Execute() or %ExecDirect() returns an object that implements the %SQL.StatementResultOpens in a new tab 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()Opens in a new tab instance method of the %SQL.StatementResultOpens in a new tab class.
For example, in 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()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). The following formats are available: XML (integer code 0), HTML (integer code 1), PDF (integer code 2), TXT (integer code 99), or CSV (integer code 100). (Note that CSV format is not implemented as a true comma-separated value output; instead, it uses tabs to separate the columns.) 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. Caché generates a file of the specified type, appending the appropriate file name extension.
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\Cache\mgr\user\myresults.txt. If the specified file with that suffix already exists, Caché 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\Cache\mgr\Temp\w4FR2gM7tX2Fjs.txt. Each time a query is run a new destination file is generated.
These examples show Windows filenames; Caché 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\Cache\mgr\samples\. It creates the mess result set for messages, then uses %Display() to display messages to the Terminal:
SET $NAMESPACE="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",.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 $NAMESPACE="SAMPLES"
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()
}
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()Opens in a new tab 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()Opens in a new tab 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.
If a SELECT query returns only aggregate functions, every %Next() sets %ROWCOUNT=1. The first %Next() returns 1 and sets %SQLCODE=0 and %ROWCOUNT=1, even when there is no data in the table; any subsequent %Next() returns 0 and sets %SQLCODE=100 and %ROWCOUNT=1.
After fetching a row from the result set, you can display data from that row using any of the following:
-
rset.%Print() to return all of the data values for the current row from a query result set.
-
rset.name to return a data value by property name, field name, alias property name, or alias field name from a query result set.
-
rset.%Get("fieldname") to return a data value by field name or alias field name from either a query result set or a stored query.
-
rset.%GetData(n) to return a data value by column number from either a query result set or a stored query.
%Print() Method
The %Print()Opens in a new tab instance method retrieves the current record from the result set. By default, %Print() inserts a blank space delimiter between data field values. %Print() does not insert a blank space before the first field value or after the last field value in a record; it issues a line return at the end of the record. If a data field value already contains a blank space, that field value is enclosed in quotation marks to differentiate it from the delimiter. For example, if %Print() is returning city names, it would return them as follows: Chicago "New York" Boston Atlanta "Los Angeles" "Salt Lake City" Washington. %Print() quotes field values that contain the delimiter as part of the data value even when the %Print() delimiter is never used; for example if there is only one field in the result set.
You can optionally specify a %Print() parameter that provides a different delimiter to be placed between the field values. Specifying a different delimiter overrides the quoting of data strings that contain blank spaces. This %Print() delimiter can be one or more characters. It is specified as a quoted string. It is generally preferable that the %Print() delimiter be a character or string not found in the result set data. However, if a field value in the result set contains the %Print() delimiter character (or string), that field value is returned enclosed in quotation marks to differentiate it from the delimiter.
If a field value in the result set contains a line feed character, that field value is returned delimited by quotation marks.
The following ObjectScript example iterates through the query result set using %Print() to display each result set record, separating values with a "^|^" delimiter. Note how %Print() displays data from the FavoriteColors field which is an encoded list of elements:
SET $NAMESPACE="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.
SET $NAMESPACE="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.
-
Property Name: If no field alias is defined, specify the field property name as rset.PropName. The result set field property name is taken from the corresponding property name in the table definition class.
-
Field Name: If no field alias is defined, specify the field name (or the property name) as rset."fieldname". This is the SqlFieldName specified in the table definition. Caché uses this field name to locate the corresponding property name. In many cases, the property name and the field name (SqlFieldName) are identical.
-
Alias Property Name: If a field alias is defined, specify the alias property name as rset.AliasProp. An alias property name is generated from the column name alias in the SELECT statement. You cannot specify a field property name for a field with a defined alias.
-
Alias Name: If a field alias is defined, specify this alias name (or the alias property name) as rset."alias". This is the column name alias in the SELECT statement. You cannot specify a field name for a field with a defined alias.
-
Aggregate, Expression, or Subquery: Caché assigns these select-items a field name of Aggregate_n, Expression_n, or Subquery_n (where the integer n corresponds to the sequence of the select-item list specified in the query). You can retrieve these select-item values using the field name (rset."SubQuery_7" not case-sensitive), the corresponding property name (rset.Subquery7 case-sensitive), or by a user-defined field name alias. You can also just specify the select-item sequence number using rset.%GetData(n).
When specifying a property name, you must use correct letter case; when specifying a field name, correct letter case is not required.
This invocation of rset.name using the property name has the following consequences:
-
Letter Case: Property names are case-sensitive. Field names are not case-sensitive. Dynamic SQL can automatically resolve differences in letter case between a specified field or alias name and the corresponding property name. However, letter case resolution takes time. To maximize performance, you should specify the exact letter case of the property name or the alias.
-
Non-alphanumeric Characters: A property name can only contain alphanumeric characters (except for an initial % character). If the corresponding SQL field name or field name alias contains non-alphanumeric characters (for example, Last_Name) you can do either of the following:
-
Specify the field name delimited with quotation marks. For example, rset."Last_Name"). This use of delimiters does not require that delimited identifiers be enabled. Letter case resolution is performed.
-
Specify the corresponding property name, eliminating the non-alphanumeric characters. For example, rset.LastName (or rset."LastName"). You must specify the correct letter case for the property name.
-
-
% Property Names: Generally, property names beginning with a % character are reserved for system use. If a field property name or alias begins with a % character and that name conflicts with a system-defined property, the system-defined property is returned. For example, for SELECT Notes AS %Message, invoking rset.%Message will not return the Notes field values; it returns the %MessageOpens in a new tab property defined for the statement result class. You can use rset.%Get("%Message") to return the field value.
-
Column Alias: If an alias is specified, Dynamic SQL always matches the alias rather than matching the field name or field property name. For example, for SELECT Name AS Last_Name, the data can only be retrieved using rset.LastName or rset."Last_Name", not by using rset.Name.
-
Duplicate Names: Names are duplicate if they resolve to the same property name. Duplicate names can be multiple references to the same field in a table, alias references to different fields in a table, or references to fields in different tables. For example SELECT p.DOB,e.DOB specifies two duplicate names, even though those names refer to fields in different tables.
If the SELECT statement contains multiple instances of the same field name or field name alias, rset.PropName or rset."fieldname" always return the first one specified in the SELECT statement. For example, for SELECT c.Name,p.Name FROM Sample.Person AS p,Sample.Company AS c using rset.Name retrieves the company name field data; SELECT c.Name,p.Name AS Name FROM Sample.Person AS p,Sample.Company AS c using rset."name" also retrieves the company name field data. If there are duplicate Name fields in the query the last character of the field name (Name) is replaced by a character (or characters) to create a unique property name. Thus a duplicate Name field name in a query has a corresponding unique property name, beginning with Nam0 (for the first duplicate) through Nam9 and continuing with capital letters NamA through NamZ.
For a user-specified query prepared using %Prepare()Opens in a new tab you can use the property name by itself. For a stored query prepared using %PrepareClassQuery()Opens in a new tab, you must use the %Get("fieldname")Opens in a new tab method.
The following example returns the values of three fields specified by property names: two field values by property name and the third field value by alias property name. In these cases, the specified property name is identical to the field name or field alias:
SET $NAMESPACE="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:
SET $NAMESPACE="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:
SET $NAMESPACE="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:
SET $NAMESPACE="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
If configured, the system generates a <SWIZZLE FAIL> error if the swizzled property is defined but cannot be referenced. This can occur if the referenced property has been unexpectedly deleted from disk or is locked by another process. To determine the cause of the swizzle failure look in %objlasterror immediately after the <SWIZZLE FAIL> error and decode this %Status value.
By default, <SWIZZLE FAIL> is not configured. You can set this behavior globally by setting SET ^%SYS("ThrowSwizzleError")=1, or by using the Caché Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then Objects. On this screen you can set the <SWIZZLE FAIL> option.
%Get("fieldname") Method
You can use the %Get("fieldname")Opens in a new tab instance method to return a data value by field name or field name alias. Dynamic SQL resolves letter case as needed. If the specified field name or field name alias does not exist, the system generates a <PROPERTY DOES NOT EXIST> error.
The following example returns values for the Home_State field and the Last_Name alias from the query result set.
SET $NAMESPACE="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")Opens in a new tab instance method to retrieve individual data items by field property name from an existing query prepared using %PrepareClassQuery()Opens in a new tab. If the field property name does not exist, the system generates a <PROPERTY DOES NOT EXIST> error.
The following example returns the Nsp (namespace) field values by field property name from a built-in query. Because this query is an existing stored query, this field retrieval requires the use of the %Get("fieldname") method. Note that because "Nsp" is a property name, it is case-sensitive:
SET 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:
SET $NAMESPACE="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)Opens in a new tab instance method returns data for the current row indexed by the integer count column number of the result set. You can use %GetData(n) with either a specified query prepared using %Prepare() or a stored query prepared using %PrepareClassQuery().
The integer n corresponds to the sequence of the select-item list specified in the query. The 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 ObjectScript:
SET $NAMESPACE="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()Opens in a new tab method to return multiple result sets separately:
SET $NAMESPACE="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
Dynamic SQL provides the following types of metadata:
-
After a Prepare, metadata describing the type of query.
-
After a Prepare, metadata describing the select-items in the query (Columns and Extended Column Info).
-
After a Prepare, metadata describing the query arguments: ? parameters, :var parameters, and constants. (Statement Parameters, Formal Parameters, and Objects)
-
After an Execute, metadata describing the query result set.
%SQL.StatementMetadataOpens in a new tab property values are available following a Prepare operation (%Prepare(), %PrepareClassQuery(), or %ExecDirect()).
-
You can return %SQL.StatementMetadataOpens in a new tab properties directly for the most recent %Prepare().
-
You can return the %SQL.StatementOpens in a new tab %Metadata property containing the oref for the %SQL.StatementMetadataOpens in a new tab properties. This enables you to return metadata for multiple Prepare operations.
A SELECT or CALL statement returns all of this metadata. An INSERT, UPDATE, or DELETE returns Statement Type Metadata and the Formal Parameters.
Statement Type Metadata
Following a Prepare using the %SQL.StatementOpens in a new tab class, you can use the %SQL.StatementMetadataOpens in a new tab statementType property to determine what type of SQL statement was prepared, as shown in the following example. This example uses the %SQL.StatementOpens in a new tab %Metadata property to preserve and compare the metadata for two Prepare operations:
SET tStatement = ##class(%SQL.Statement).%New()
SET myquery1 = "SELECT TOP ? Name,Age,AVG(Age),CURRENT_DATE FROM Sample.Person"
SET myquery2 = "CALL Sample.SP_Sample_By_Name(?)"
SET qStatus = tStatement.%Prepare(myquery1)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET meta1 = tStatement.%Metadata
SET qStatus = tStatement.%Prepare(myquery2)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET meta2 = tStatement.%Metadata
WRITE "Statement type query 1: ",meta1.statementType,!
WRITE "Statement type query 2: ",meta2.statementType,!
WRITE "End of metadata"
The Class Reference entry for the statementTypeOpens in a new tab property lists the statement type integer codes. The most common codes are 1 (a SELECT query) and 45 (a CALL to a stored query).
You can return the same information using the %GetImplementationDetails()Opens in a new tab instance method, as described in Results of a Successful Prepare.
After executing a query, you can return the statement type name (for example, SELECT) from the result set.
Select-item Metadata
Following a Prepare of a SELECT or CALL statement using the %SQL.StatementOpens in a new tab class, you can return metadata about each select-item column specified in 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 InterSystems Objects property origins and class type information.
The following example returns the number of columns specified in the most recently prepared query:
SET myquery = "SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,Home_State 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}
WRITE "Number of columns=",tStatement.%Metadata.columnCount,!
WRITE "End of metadata"
The following example returns the column name (or column alias), ODBC data type, maximum data length (precision), and scale for each select-item field:
SET $NAMESPACE="SAMPLES"
SET myquery=2
SET myquery(1)="SELECT Name AS VendorName,LastPayDate,MinPayment,NetDays,"
SET myquery(2)="AVG(MinPayment),$HOROLOG,%TABLENAME FROM Sample.Vendor"
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()
SET x=1
WHILE rset.%Metadata.columns.GetAt(x) {
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"
The following example displays all of the column metadata using the %SQL.StatementMetadataOpens in a new tab %Display()Opens in a new tab instance method:
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare("SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,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:
Display Header | %SQL.StatementColumnOpens in a new tab Property | Description |
---|---|---|
Column Name | colName |
The SQL name of the column. If the column is given an alias, the column alias, not the field name, is listed here. Names and aliases are truncated to 12 characters. For an expression, aggregate, literal, host variable, or subquery, the assigned “Expression_n”, “Aggregate_n”, “Literal_n”, “HostVar_n”, or “Subquery_n” label is listed (with n being the SELECT item sequence number). If you have assigned an alias to an expression, aggregate, literal, host variable, or subquery, the alias is listed here. |
Type | ODBCType | 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 InterSystems SQL Reference. Note that these ODBC data type codes are not the same as the CType data type codes. |
Prec | precision | The precision or maximum length, in characters. |
Scale | scale | The maximum number of fractional decimal digits. Returns 0 for integer or non-numeric values. |
Null | isNullable | An integer value that indicates whether the column is defined as Non-NULL (0), or if NULL is permitted (1). The RowID returns 0. If the SELECT item is an aggregate or subquery that could result in NULL, or if it specifies the NULL literal, this item is set to 1. If the SELECT item is an expression or host variable, this item is set to 2 (cannot be determined). |
Label | label | The column name or column alias (same as Column Name). |
Table | tableName | The SQL 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. If the SELECT item is a subquery, the subquery table name is listed. |
Schema | schemaName | The table’s schema name. If no schema name was specified, returns the system-wide default schema. If the SELECT item is an expression or an aggregate no schema name is listed. If the SELECT item is a subquery no schema name is listed. |
CType | clientType | The integer code for the client data type. See the %SQL.StatementColumnOpens in a new tab clientTypeOpens in a new tab property for a list of values. |
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):
Boolean Flag | %SQL.StatementColumnOpens in a new tab Property | Description |
---|---|---|
1: AutoIncrement | isAutoIncrement | The RowID and IDENTITY fields returns Y. |
2: CaseSensitive | isCaseSensitive |
A string data type field with %EXACT collation returns Y. A property that references a %SerialObject embedded object returns Y. |
3: Currency | isCurrency | A field defined with a data type of %Library.CurrencyOpens in a new tab, such as the MONEY data type. |
4: ReadOnly | isReadOnly | An Expression, Aggregate, Literal, HostVar, or Subquery returns Y. The RowID, IDENTITY, and RowVersion fields returns Y. |
5: RowVersion | isRowVersion | The RowVersion field returns Y. |
6: Unique | isUnique | A field defined as having a unique value constraint. The RowID and IDENTITY fields returns Y. |
7: Aliased | isAliased | The system supplies an alias to a non-field select-item. Therefore, an Expression, Aggregate, Literal, HostVar, or Subquery returns Y, whether or not the user replaced the system alias by specifying a column alias. This flag is not affected by user-specified column aliases. |
8: Expression | isExpression | An Expression returns Y. |
9: Hidden | isHidden | If the table is defined with %PUBLICROWID or SqlRowIdPrivate=0 (the default), the RowID field returns N. Otherwise, the RowID field returns Y. A property that references a %SerialObject embedded object returns Y. |
10: Identity | isIdentity | The RowID field returns Y. |
11: KeyColumn | isKeyColumn | A field defined as a primary key field or the target of a foreign key constraint. The RowID field returns Y. |
12: RowID | isRowId | The RowID and IDENTITY fields returns Y. |
The Extended Column Info metadata table lists the Column Name (the SQL name or column alias), the Linked Prop (linked persistent class property) and Type Class (data type class) for each of the selected fields. Note that the Linked Prop lists the persistent class name (not the SQL table name) and the property name (not the column alias).
-
For an ordinary table field (SELECT Name FROM Sample.Person): Linked Prop=Sample.Person.Name, Type Class=%Library.String.
-
For the table’s RowID (SELECT %ID FROM Sample.Person): Linked Prop= [none], Type Class=Sample.Person.
-
For an Expression, Aggregate, Literal, HostVar, or Subquery (SELECT COUNT(Name) FROM Sample.Person): Linked Prop= [none], Type Class=%Library.BigInt.
-
For a referenced %SerialObject embedded object property (SELECT Home_State FROM Sample.Person). Linked Prop=Sample.Address.State, Type Class=%Library.String.
-
For a field referencing a %SerialObject embedded object (SELECT Home FROM Sample.Person). Linked Prop=Sample.Person.Home, Type Class=Sample.Address.
In this example, the Home_State field in Sample.Person references the State property of the %SerialObject class Sample.Address.
The following example returns the metadata for a called stored procedure with one formal parameter, which is also a statement parameter:
SET $NAMESPACE="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 with three formal parameters. One of these three parameters is designated with a question mark (?) making it a statement parameter:
SET $NAMESPACE="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"
Note that this metadata returns no column information, but the Statement Parameters, Formal Parameters lists contain the column names and data types.
Query Arguments Metadata
Following a Prepare using the %SQL.StatementOpens in a new tab class, you can return metadata about query arguments: input parameters (specified as a question mark (?)), input host variables (specified as :varname), and constants (literal values). The following metadata can be returned:
-
Count of ? parameters: parameterCountOpens in a new tab property
-
ODBC data types of ? parameters: %SQL.StatementMetadataOpens in a new tab %Display()Opens in a new tab instance method Statement Parameters list.
-
List of ?, v (:var), and c (constant) parameters: %GetImplementationDetails()Opens in a new tab instance method, as described in Results of a Successful Prepare.
-
ODBC data types of ?, v (:var), and c (constant) parameters: formalParametersOpens in a new tab property.
%SQL.StatementMetadataOpens in a new tab %Display()Opens in a new tab instance method Formal Parameters list.
-
Text of query showing these arguments: %GetImplementationDetails()Opens in a new tab instance method, as described in Results of a Successful Prepare.
The statement metadata %Display() method lists the Statement Parameters and Formal parameters. For each 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.
Note that some ODBC data types are returned as negative integers. For a table of ODBC data type integer codes, see the Data Types reference page in the InterSystems SQL Reference.
The following example returns the ODBC data types of each of the query arguments (?, :var, and constants) in order. Note that the TOP argument is returned as data type 12 (VARCHAR) rather than 4 (INTEGER) because it is possible to specify TOP ALL:
SET myquery = 4
SET myquery(1) = "SELECT TOP ? Name,DOB,Age+10 "
SET myquery(2) = "FROM Sample.Person"
SET myquery(3) = "WHERE %ID BETWEEN :startid :endid AND DOB=?"
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 prepmeta = tStatement.%Metadata
WRITE "Number of ? parameters=",prepmeta.parameterCount,!
SET formalobj = prepmeta.formalParameters
SET i=1
WHILE formalobj.GetAt(i) {
SET prop=formalobj.GetAt(i)
WRITE prop.colName," type= ",prop.ODBCType,!
SET i=i+1 }
WRITE "End of metadata"
Following an Execute, arguments metadata is not available from the query result set metadata. In a result set all parameters are resolved. Therefore parameterCount = 0, and formalParameters contains no data.
Query Result Set Metadata
Following an Execute using the %SQL.StatementOpens in a new tab class, you can return result set metadata by invoking:
-
%SQL.StatementResultOpens in a new tab class properties.
-
%SQL.StatementResultOpens in a new tab %GetMetadata()Opens in a new tab method, accessing %SQL.StatementMetadataOpens in a new tab class properties.
%SQL.StatementResult Properties
Following an Execute query operation, %SQL.StatementResultOpens in a new tab returns:
-
The %StatementTypeOpens in a new tab property returns an integer code that corresponds to the SQL statement most recently executed. The following is a partial list of these integer codes: 1 = SELECT; 2 = INSERT; 3 = UPDATE; 4 = DELETE or TRUNCATE TABLE; 9 = CREATE TABLE; 15 = CREATE INDEX; 45 = CALL. For a complete list of these values, refer to %SQL.StatementResultOpens in a new tab in the InterSystems Class Reference.
-
The %StatementTypeNameOpens in a new tab calculated property returns the command name of the SQL statement most recently executed, based on the %StatementType. This name is returned in uppercase letters. Note that a TRUNCATE TABLE operation is returned as DELETE. An INSERT OR UPDATE is returned as INSERT, even when it performed an update operation.
-
The %ResultColumnCountOpens in a new tab property returns the number of columns in the result set rows.
The following example shows these properties:
SET myquery = "SELECT TOP ? Name,DOB,Age FROM Sample.Person WHERE Age > ?"
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,55)
IF rset.%SQLCODE=0 {
WRITE "Statement type=",rset.%StatementType,!
WRITE "Statement name=",rset.%StatementTypeName,!
WRITE "Column count=",rset.%ResultColumnCount,!
WRITE "End of metadata" }
ELSE { WRITE !,"SQLCODE=",rset.%SQLCODE," ",rset.%Message }
%SQL.StatementResult %GetMetadata()
Following an Execute, you can use the %SQL.StatementResultOpens in a new tab %GetMetadata()Opens in a new tab method to access the %SQL.StatementMetadataOpens in a new tab class properties. These are the same properties accessed by the %SQL.StatementOpens in a new tab %Metadata property following a Prepare.
The following example shows the properties:
SET myquery=2
SET myquery(1)="SELECT Name AS VendorName,LastPayDate,MinPayment,NetDays,"
SET myquery(2)="AVG(MinPayment),$HOROLOG,%TABLENAME FROM Sample.Vendor"
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 {
SET rsmeta=rset.%GetMetadata()
SET x=rsmeta.columns.Count()
SET x=1
WHILE rsmeta.columns.GetAt(x) {
SET column=rsmeta.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 }
}
ELSE { WRITE !,"SQLCODE=",rset.%SQLCODE," ",rset.%Message }
WRITE !,"End of metadata"
Note that the result set metadata does not provide arguments metadata. This is because the Execute operation resolves all parameters. Therefore, in a result set, parameterCount = 0, and formalParameters contains no data.
Auditing Dynamic SQL
Caché supports optional auditing of Dynamic SQL statements. Dynamic SQL auditing is performed when the %System/%SQL/DynamicStatement system audit event is enabled. By default, this system audit event is not enabled.
If you enable %System/%SQL/DynamicStatement, the system automatically audits every %SQL.StatementOpens in a new tab dynamic statement that is executed system-wide. Auditing records information in the Audit Database.
To view the Audit Database, go to the Management Portal, System Administration, select Security, then Auditing, then View Audit Database. You can set the Event Name filter to DynamicStatement to limit the View Audit Database to Dynamic SQL statements. The Audit Database lists Time (a local timestamp), User, PID (process ID), and the Description of the event. The Description specifies the type of Dynamic SQL statement. For example, SQL SELECT Statement (%SQL.Statement) or SQL CREATE VIEW Statement (%SQL.Statement).
By selecting the Details link for an event you can list additional information, including the Event Data. The Event Data includes the SQL statement executed and the values of any arguments to the statement. For example:
SELECT TOP ? Name , Age FROM Sample . MyTest WHERE Name %STARTSWITH ? /*#OPTIONS {"DynamicSQLTypeList":",1"} */ Parameter values: %CallArgs(1)=5 %CallArgs(2)="Fred"
The total length of Event Data, which includes the statement and parameters, is 3,632,952 characters. If the statement and parameters are longer than 3632952, the Event Data will be truncated.
Caché also supports auditing of ODBC and JDBC statements (Event Name=XDBCStatement), and auditing of Embedded SQL statements (Event Name=EmbeddedStatement).