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:
%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:
set 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. Successful execution of the statement sets %SQLCODE to 0. This does not mean that the statement successfully retrieved results. Similarly, %Execute() does not set %SQLCODE to 100 if the statement retrieves no results. The check for results, and subsequent setting of %SQLCODE to 0, 100, or a negative error value occurs as you fetch the results one row at a time, such as by using the %Next() method.
%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. TRUNCATE TABLE cannot determine the actual number of rows deleted, so it sets %ROWCOUNT to -1.
INSERT, UPDATE, INSERT OR UPDATE, and DELETE set %ROWIDOpens in a new tab to the RowID value of the last record inserted, updated, or deleted. If the operation did not insert, update, or delete any records, %ROWID is undefined, or remains set to its prior value. TRUNCATE TABLE does not set %ROWID.
-
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.
You can use ZWRITE to return the values for all of the %SQL.StatementResultOpens in a new tab class properties.
For further details, see SQL System Variables. 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 InterSystems IRIS %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.
When an input parameter is used in a greater than or less than comparison, such as in a WHERE clause, the parameter is normalized only if it is a valid number. If the input parameter is not a valid number, the comparison condition is checked using either the sorts after operator (]]) or the no sorts after operator (']]), depending on the comparison. Note that this operator orders all numbers before any nonnumeric values (such as strings).
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 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 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 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 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.
