Chapter 13 Using Dynamic SQL (3)
Execute SQL statement
There are two ways to execute an SQL Statement using the % sql. Statement class:
% the Execute (), which it performs before use% Prepare ()or% PrepareClassQuery ()Prepared SQL statements.% ExecDirect (), which simultaneously prepares and executes an SQL statement.
You can also Execute SQL statements without creating object instances by using the $system.sqL.execute () method. This method prepares and executes SQL statements. It creates a cached query. The following terminal example shows the Execute () method:
USER>SET topnum=5
USER>SET rset=$SYSTEM.SQL.Execute("SELECT TOP :topnum Name,Age FROM Sample.Person")
USER>DO rset.%Display()
Copy the code%Execute()
Once the query is ready, it can be executed by calling the % Execute () instance method of the % SQL.Statement class. For non-SELECT statements, % Execute () invokes the desired action (such as performing an INSERT). For SELECT queries, % Execute () generates a result set for subsequent traversal and data retrieval. Such as:
SET rset = tStatement.%Execute()
Copy the codeThe % Execute () method sets the % SQL.statementResult class attributes % SQLCODE and % Message for all SQL statements. % Execute () sets the other % sqL.statementResult attributes as follows:
INSERT.UPDATE.INSERTorUPDATE.DELETEandTRUNCATE TABLEStatement will% ROWCOUNTSet to the number of rows affected by the operation.TRUNCATE TABLEThe actual number of rows deleted cannot be determined, so will% ROWCOUNTSet this parameter to -1.
INSERT, UPDATE, INSERT OR UPDATE and DELETE sets % ROWID to the ROWID value of the last record that was inserted, updated, OR deleted. If this operation does not insert, update, or delete any records, % ROWID is undefined or remains set to its previous value. TRUNCATE TABLE does not set % ROWID.
SELECTStatement when creating the result set% ROWCOUNTProperty set to 0. When the program iterates through the contents of the result set (e.g% Next ()Method),% ROWCOUNTWill increase.% Next ()Returning 1 means it is on one line, and returning 0 means it is after the last line (at the end of the result set). If the cursor is after the last line% ROWCOUNTIndicates the number of rows contained in the result set.
If the SELECT query returns only aggregate functions, each % Next () will set % ROWCOUNT = 1. Even if there is no data in the table, the first % Next () always sets % SQLCODE = 0. Any subsequent % Next () sets % SQLCODE = 100 and % ROWCOUNT = 1.
SELECT also sets % CurrentResult and % ResultColumnCount. SELECT % ROWID not set.
You can use ZWRITE to return the values of all the % SQL.statementResult class attributes.
% Execute () with input parameters
The % Execute () method can take one or more input parameters associated with the prepared SQL statement (with “? Represents the corresponding parameter of). The % Execute () argument corresponds to? The sequential character of “? “appears in SQL statements: the first argument is used for the first”?” , the second argument is used for the second “?” And so on. Multiple % Execute () arguments are separated by commas. Parameter values can be omitted by specifying a placeholder comma. The % Execute () parameter must have the same number as? Corresponds to the input parameter. If the % Execute () parameter is less than or greater than the corresponding? Execution failed with % SQLCODE property set to SQLCODE -400 error.
Input parameters can be used to provide literal values or expressions for SELECT lists and other query clauses, including the TOP and WHERE clauses. You cannot use input parameters to provide column names or column name aliases for SELECT lists or other query clauses.
When specified as explicit% the Execute ()Parameter, the maximum number of input parameters is 255. Use a variable length array% Execute (vals...)When specified, the maximum number of input parameters is 380.
After you execute Prepare, you can use the Prepare parameter metadata to return? Count and the required data type. Input parameters. You can use the % GetImplementationDetails () method to return? In the list. Enter parameters in the prepared query and use? In the query text. The input parameters are displayed in the context.
The following ObjectScript example performs a query with two input parameters. It specifies input parameter values (21 and 26) in the % Execute () method.
/// d ##class(PHA.TEST.SQL).PrepareClassQuery7()
ClassMethod PrepareClassQuery7(a)
{
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. The Status. The DisplayError (qStatus) QUIT} SET rset = tStatement. % the Execute (21, 26) WRITE ! ,"Execute OK: SQLCODE=",rset.%SQLCODE,!! DO rset.%Display() WRITE ! ,"End of data: SQLCODE=",rset.%SQLCODE }Copy the codeDHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery7(a)Execute OK: SQLCODE=0
Name DOB Age
Van De Griek,Dick U. 1998-12-21 22
Peterson,Kirsten R. 1997-12-13 23
Van De Griek,Phil S. 1996- 09 -26 24
Wijnschenk,Lydia G. 1997-01-17 24
Xiang,Kirsten U. 1996- 08 -06 24
Schaefer,Usha G. 1995- 09 -16 25
Peterson,Sophia A. 1995-12-05 25
Petersburg,Bill O. 1995-10-23 25
8 Rows(s) Affected
End of data: SQLCODE=100
Copy the codeThe following ObjectScript example performs the same query. The % Execute () method forms the argument list using a variable length array (dynd…). Specifies an indefinite number of input parameter values. In this case, the subscript of the DYnd array. The dynd variable is set to 2 to indicate two subscripts.
/// d ##class(PHA.TEST.SQL).PrepareClassQuery8()
ClassMethod PrepareClassQuery8(a)
{
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 }Copy the codeDHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery8(a)Execute OK: SQLCODE=0
Name DOB Age
Van De Griek,Dick U. 1998-12-21 22
Peterson,Kirsten R. 1997-12-13 23
Van De Griek,Phil S. 1996- 09 -26 24
Wijnschenk,Lydia G. 1997-01-17 24
Xiang,Kirsten U. 1996- 08 -06 24
Schaefer,Usha G. 1995- 09 -16 25
Peterson,Sophia A. 1995-12-05 25
Petersburg,Bill O. 1995-10-23 25
8 Rows(s) Affected
End of data: SQLCODE=100
Copy the codeMultiple % Execute () operations can be performed on the prepared result set. This makes it possible to run the query multiple times and provide different input parameter values. You do not have to close the result set between % Execute () operations, as shown in the following example:
/// d ##class(PHA.TEST.SQL).PrepareClassQuery9()
ClassMethod PrepareClassQuery9(a)
{
SET myquery="SELECT Name,SSN,Age FROM Sample.Person WHERE Name %STARTSWITH ?"
SET tStatement = ##class(%SQL.Statement%).New(a)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" }Copy the codeDHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery9(a)Name SSN Age
Alton.Martin S47. 624-25-8488Ahmed.Elmo X950-40-6135-77Anderson.Mario LThe 604-10-9, 256, 77Adams.Diane F9. 640-77-5933Anderson.Valery N882-50-4971-27Alton.Phil T785-37-8519, 68Adams.Susan E52 7. 947-66-8684Rows(s) Affected
End of A data
Name SSN Age
Bukowski.Mario V683-32-4214-85Bachman.Susan O102-59-3932, 88Bush.Jules K13. 547-97-7915Basile.Filomena X. 888-66-1725, 86Browne.Robert X82. The 308-58-1444Burroughs.Barbara H86. 627-56-2213Beatty.Molly Z54 7. 794-64-5615Rows(s) Affected
End of B data
Copy the codeUse TRY/CATCH to handle % Execute errors
Dynamic SQL can be executed within the TRY block structure, passing run-time errors to the associated CATCH block exception handler. For % Execute () errors, you can use the % exception.sql class to create an Exception instance and then throw it into a CATCH Exception handler.
The following example creates an SQL exception instance when the % Execute () error occurs. In this case, the error is the cardinality mismatch between the quantities. Enter arguments (1) and the number of % Execute () arguments (3). It throws the % SQLCODE and % Message attribute values (as Code and Data) into the CATCH exception handler. The exception handler tests the exception type using the % IsA () instance method and displays the % Execute () error:
/// d ##class(PHA.TEST.SQL).SQLTRY()
ClassMethod SQLTRY(a)
{
TRY {
SET myquery = "SELECT TOP ? Name,DOB FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'= 1 {WRITE "% Prepare failed:" DO $System. The Status. The DisplayError (qStatus) QUIT} SET rset = tStatement. % the Execute the IF (7,9,4) 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 } }Copy the codeDHC-APP>d ##class(PHA.TEST.SQL).SQLTRY(a)In the CATCH block
SQLCODE: - 400.Message: Dynamic SQL Execute.more parameter values passed than are specified in the dynamic statement
Copy the code%ExecDirect()
The % sqL.Statement class provides the % ExecDirect () class method, which prepares and executes queries in a single operation. It can Prepare a specified query (such as % Prepare ()) or an existing class query (such as % PrepareClassQuery ()).
% ExecDirect () prepares and executes the specified query:
/// d ##class(PHA.TEST.SQL).ExecDirect()
ClassMethod ExecDirect(a)
{
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
}
Copy the codeDHC-APP> d ##class(PHA.TEST.SQL).ExecDirect(a)ExecDirect OK
Name Age
Van De Griek.Dick U22.Peterson.Kirsten R23.Van De Griek.Phil S24.Wijnschenk.Lydia G24.Xiang.Kirsten U24.Schaefer.Usha G25.Peterson.Sophia A25.Petersburg.Bill O25.Ng.Josephine Z26.Munt.Valery W26.Ingleman.Martin T26.Eno.Diane U26.Pascal.Kim P27.Ipsen.Jane A27.Anderson.Valery N27.Gomez.Mo Q27.Xerxes.Angelo P28.Young.Barbara N29 18.Rows(s) Affected
End of data: SQLCODE=100
Copy the code% ExecDirect () prepares and executes an existing class query:
/// d ##class(PHA.TEST.SQL).ExecDirect1()
ClassMethod ExecDirect1(a)
{
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
}
Copy the codeThe input parameter value can be specified as the third and subsequent parameter of the % ExecDirect () class method, as shown in the following example:
/// d ##class(PHA.TEST.SQL).ExecDirect2()
ClassMethod ExecDirect2(a)
{
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(,.myquery19, 30),IF rset2%.SQLCODE'=0{ WRITE ! ."2nd ExecDirect SQLCODE=",rset2.%SQLCODE,! ,rset2.%Message QUIT } DO rset2.%Display() WRITE ! ."End of twenties data"
}
Copy the codeDHC-APP> d ##class(PHA.TEST.SQL).ExecDirect2(a)Name Age
Bush.Jules K. 13...Eastman.Howard K18 September.Rows(s) Affected
End of teen data
Name Age
Ingrahm.Susan NThe 20...Young.Barbara N29 20.Rows(s) Affected
End of twenties data
Copy the codeThe % ExecDirect () input parameter corresponds to the? The sequential character of “? “appears in SQL statements: the third argument is used for the first”?” , and the fourth argument is used for the second? And so on. Parameter values can be omitted by specifying a placeholder comma. If the % ExecDirect () input parameter is less than the corresponding ‘? The default value, if present, is used for input parameters.
In the following example, the first % ExecDirect () specifies all three ‘? Enter the argument, the second % ExecDirect () specifies only the second? Enter arguments and omit the first and third. It uses the default sample.Personsets () (‘MA’) for the third input argument:
/// d ##class(PHA.TEST.SQL).ExecDirect3()
ClassMethod ExecDirect3(a)
{
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"
}
Copy the codeDHC-APP>d ##class(PHA.TEST.SQL).ExecDirect3(a)Output Values:
0. 1
Dumping result# 1Name DOB Spouse. 1Rows(s) Affected
End of B people data
Copy the code% ExecDirect () can call the % SQL.Statement % Display () instance method or the % GetImplementationDetails () instance method to return details of the currently prepared Statement. Because % ExecDirect () can prepare and execute a specified query or an existing class query, you can use the % GetImplementationDetails () pStatementType parameter to determine which query to prepare:
/// d ##class(PHA.TEST.SQL).ExecDirect4()
ClassMethod ExecDirect4(a)
{
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"
}
Copy the code