SQL

SQL

Chapter 13 Using Dynamic SQL (3)

Execute SQL statement

There are two uses

%SQL.Statement
Class method to execute SQL statement:

  • %Execute()
    , It executes previously used
    %Prepare()
    or
    %PrepareClassQuery()
    Prepared SQL statement.
  • %ExecDirect()
    , It prepares and executes a SQL statement at the same time.

You can also use

$SYSTEM.SQL.Execute()
The method executes the SQL statement without creating an object instance. This method both prepares and executes SQL statements. It creates a cached query. The following terminal example shows
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 code

%Execute()

After preparing the query, you can call

%SQL.Statement
Category
%Execute()
Instance method to execute the query. For non
SELECT
Statement,
%Execute()
Invoke the desired operation (e.g. perform
INSERT
). for
SELECT
Inquire,
%Execute()
Generate a result set for subsequent traversal and data retrieval. E.g:

SET rset = tStatement.% Execute ( ) to copy the code

%Execute()
Method is set for all SQL statements
%SQL.StatementResult
Class attribute
%SQLCODE
with
%Message
.
%Execute()
Set other
%SQL.StatementResult
The properties are as follows:

  • INSERT
    ,
    UPDATE
    ,
    INSERT
    or
    UPDATE
    ,
    DELETE
    with
    TRUNCATE TABLE
    The statement will
    %ROWCOUNT
    Set to the number of rows affected by the operation.
    TRUNCATE TABLE
    Unable to determine the actual number of rows deleted, so change
    %ROWCOUNT
    Set to -1.

INSERT
,
UPDATE
,
INSERT OR UPDATE
with
DELETE
will
%ROWID
Set as the last record inserted, updated or deleted
RowID
value. If the operation does not insert, update or delete any records, then
%ROWID
Is undefined or remains set to its previous value.
TRUNCATE TABLE
no setting
%ROWID
.

  • SELECT
    When the statement creates the result set, it will
    %ROWCOUNT
    The attribute is set to 0. When the program traverses the contents of the result set (for example, using
    %Next()
    Method),
    %ROWCOUNT
    Will increase.
    %Next()
    Return 1 means it is on a row, return 0 means it is after the last row (at the end of the result set). If the cursor is after the last line, then
    %ROWCOUNT
    The value of indicates the number of rows contained in the result set.

in case

SELECT
The query only returns aggregate functions, and each
%Next()
Will be set
%ROWCOUNT = 1
. Even if there is no data in the table, the first
%Next()
Always set
SQLCODE = 0
Next
SQLCODE = 100
ROWCOUNT = 1

SELECT
CurrentResult
ResultColumnCount
SELECT
ROWID

ZWRITE
SQL.StatementResult

Execute

Execute
SQL
Execute
SQL
Execute
Execute
Execute
SQLCODE
SQLCODE -400

SELECT
TOP
WHERE
SELECT

Execute
255
Execute vals ...
380

Prepare
Prepare
GetImplementationDetails

ObjectScript

Execute
21 26

///d ##class(PHA.TEST.SQL).PrepareClassQuery7() ClassMethod PrepareClassQuery7() { 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 }
DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery7() 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

ObjectScript

Execute
dynd ...
dynd
dynd
2

///d ##class(PHA.TEST.SQL).PrepareClassQuery8() ClassMethod PrepareClassQuery8() { 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 }
DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery8() 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

Execute
Execute

///d ##class(PHA.TEST.SQL).PrepareClassQuery9() ClassMethod PrepareClassQuery9() { 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" }
DHC-APP>d ##class(PHA.TEST.SQL).PrepareClassQuery9() Name SSN Age Alton,Martin S. 624-25-8488 47 Ahmed,Elmo X. 950-40-6135 77 Anderson,Mario L. 604-10-9256 77 Adams,Diane F. 640-77-5933 9 Anderson,Valery N. 882-50-4971 27 Alton,Phil T. 785-37-8519 68 Adams,Susan E. 947-66-8684 52 7 Rows(s) Affected End of A data Name SSN Age Bukowski,Mario V. 683-32-4214 85 Bachman,Susan O. 102-59-3932 88 Bush,Jules K. 547-97-7915 13 Basile,Filomena X. 888-66-1725 86 Browne,Robert X. 308-58-1444 82 Burroughs,Barbara H. 627-56-2213 86 Beatty,Molly Z. 794-64-5615 54 7 Rows(s) Affected End of B data

TRY/CATCH Execute

TRY
Dynamic SQL
CATCH
Execute
Exception.SQL
CATCH

Execute
SQL 1
Execute
3
SQLCODE
Message
Code
Data
CATCH
IsA
Execute

///d ##class(PHA.TEST.SQL).SQLTRY() ClassMethod SQLTRY() { 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 } }
DHC-APP>d ##class(PHA.TEST.SQL).SQLTRY() In the CATCH block SQLCODE: -400 Message: Dynamic SQL Execute, more parameter values passed than are specified in the dynamic statement

%ExecDirect()

SQL.Statement
ExecDirect
Prepare
PrepareClassQuery

ExecDirect

///d ##class(PHA.TEST.SQL).ExecDirect() ClassMethod ExecDirect() { 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 }
DHC-APP> d ##class(PHA.TEST.SQL).ExecDirect() ExecDirect OK Name Age Van De Griek,Dick U. 22 Peterson,Kirsten R. 23 Van De Griek,Phil S. 24 Wijnschenk,Lydia G. 24 Xiang,Kirsten U. 24 Schaefer,Usha G. 25 Peterson,Sophia A. 25 Petersburg,Bill O. 25 Ng,Josephine Z. 26 Munt,Valery W. 26 Ingleman,Martin T. 26 Eno,Diane U. 26 Pascal,Kim P. 27 Ipsen,Jane A. 27 Anderson,Valery N. 27 Gomez,Mo Q. 27 Xerxes,Angelo P. 28 Young,Barbara N. 29 18 Rows(s) Affected End of data: SQLCODE=100

ExecDirect

///d ##class(PHA.TEST.SQL).ExecDirect1() ClassMethod ExecDirect1() { 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 }

ExecDirect

///d ##class(PHA.TEST.SQL).ExecDirect2() ClassMethod ExecDirect2() { 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" }
DHC-APP> d ##class(PHA.TEST.SQL).ExecDirect2() Name Age Bush,Jules K. 13 ... Eastman,Howard K. 18 9 Rows(s) Affected End of teen data Name Age Ingrahm,Susan N. 20 ... Young,Barbara N. 29 20 Rows(s) Affected End of twenties data

ExecDirect
SQL
ExecDirect

ExecDirect
ExecDirect
Sample.PersonSets 'MA'

///d ##class(PHA.TEST.SQL).ExecDirect3() ClassMethod ExecDirect3() { 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" }
DHC-APP>d ##class(PHA.TEST.SQL).ExecDirect3() Output Values: 0. 1 Dumping result #1 Name DOB Spouse ... 1 Rows(s) Affected End of B people data

ExecDirect
SQL.Statement Display
GetImplementationDetails
ExecDirect
GetImplementationDetails
pStatementType

///d ##class(PHA.TEST.SQL).ExecDirect4() ClassMethod ExecDirect4() { 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" }