%SQL.Statement
class %SQL.Statement extends %Library.RegisteredObject [ Final ]
%SQL.Statement implements an interface to prepare and execute dynamic SQL statements.Dynamic SQL
Dynamic SQL
Overview
The %SQL.Statement class implements an interface for managing dynamic SQL statements. The interface consists of %Prepare, %Execute, %ExecDirect methods and the %Dialect, %Metadata, %SchemaPath, and %Selectmode properties.
To prepare and execute a dynamic SQL statement, use an instance of %SQL.Statement, such as:
set tStatement = ##class(%SQL.Statement).%New(2,"Sample")
%New() accepts three optional parameters that allow you to specify the value of the %SelectMode, %SchemaPath and %Dialect properties. You can explicitly set these properties at any time; the value of each affects subsequent statement preparation and execution.
Next, prepare the dynamic statement by invoking the %Prepare() method. %Prepare() accepts an SQL statement in the form of a simple value or in an array passed by reference. For an array, the value of the base node is the number of lines in the array and each node’s subscript specifies its line number; the value of each subnode is a portion of the SQL statement. An example of preparing an array is:
set tSQL = 3 set tSQL(1) = "select %ID as id, Name, DOB, Home_State" set tSQL(2) = "from Person where Age > 80" set tSQL(3) = "order by 2" set tStatus = tStatement.%Prepare(.tSQL)
%Prepare() returns a status value that indicates success or failure. If the statement is successfully prepared, the next step is to execute it by invoking the %Execute() method.
set tResult = tStatement.%Execute()
The %Execute() method returns an instance of the %SQL.StatementResult class. Refer to that class for more information on the result object interface. All result objects support a %Display() method that is useful for testing.
The following example demonstrates the use of the %Prepare(), %Execute, and %Display() methods:
SAMPLES>s tStatus = tStatement.%Prepare(.tSQL) SAMPLES>w tStatus 1 SAMPLES>s tResult = tStatement.%Execute() SAMPLES>d tResult.%Display() id Name DOB Home_State 99 Finn,George V. 03/23/1928 MA 140 Hanson,James K. 04/02/1928 VT 14 Klein,Michael X. 06/17/1923 WV 159 Klingman,Brenda U. 07/09/1924 WA 49 Paladino,Rhonda H. 06/29/1923 AR 63 Vonnegut,Nellie K. 03/24/1926 HI 146 Zimmerman,Martin K. 05/25/1924 OH 7 Rows(s) Affected
Once an SQL statement has been successfully prepared, its %Metadata property is available. %Metadata is an instance of the %SQL.StatementMetadata class. %SQL.Metadata implements a %Display() method that is useful for examining the metadata interactively, such as:
SAMPLES>do tStatement.%Metadata.%Display() Columns (property 'columns'): Column Name Type Prec Scale Null ----------- ---- ---- ----- ---- id 4 10 0 0 id Person Sample 0 Y N N Y0 1 Name 12 50 0 0 Name Person Sample 0 N N N N0 0 DOB 9 10 0 1 DOB Person Sample 0 N N N N0 0 Home_State 12 2 0 1 Home_State Person Sample 0 N N N N0 0 Statement Parameters (property 'parameters'): Nbr. Type precision scale nullable colName columntype ---- ---- --------- ----- -------- ------------ ---------- 1 4 10 0 1 %parm(1) 1 2 12 2 0 1 %parm(2) 1 Formal Parameters (property 'formalParameters'): Nbr. Type precision scale nullable colName columntype ---- ---- --------- ----- -------- ------------ ---------- 2 4 10 0 1 %parm(1) 1 2 12 2 0 1 %parm(2) 1 Objects: Column Name Extent ExportCall ----------- ------ ---------- %%ID Sample.Person %QuickLoad^Sample.Person.T1(%rowid,%nolock,0,0,1) SAMPLES>
You can execute a successfully prepared statement repeatedly. This is most useful when the statement includes parameters, where a parameter is defined in the SQL statement source by a question mark (“?”). A statement may include an arbitrary number of parameters. (There are system limitations that limit the number of dynamic parameters; this limit varies but up to 200 should work with most Caché versions.)
The %Execute() method accepts parameter values in the order in which they appear in the source statement. For example:
SAMPLES>set tSQL=3 SAMPLES>set tSQL(1)="select %ID as id,Name,DOB,Home_State" SAMPLES>set tSQL(2)="from Person where Age > ? and Home_State = ?" SAMPLES>set tSQL(3)="order by 2" SAMPLES>set tStatus = tStatement.%Prepare(.tSQL) SAMPLES>set tResult = tStatement.%Execute(80,"VT") SAMPLES>do tResult.%Display() id Name DOB Home_State 140 Hanson,James K. 04/02/1928 VT 1 Rows(s) Affected SAMPLES>set tResult = tStatement.%Execute(50,"VT") SAMPLES>do tResult.%Display() id Name DOB Home_State 3 Eagleman,Emilio N. 09/01/1946 VT 140 Hanson,James K. 04/02/1928 VT 167 Hertz,Keith O. 01/01/1952 VT 3 Rows(s) Affected SAMPLES>
You can also use the same statement object for many different statements by simply invoking %Prepare() with the new statement. This is because %Prepare() initializes the statement’s execution plan and metadata. Also, an arbitrary number of statement objects can coexist in the same process.
You can also prepare and execute dynamic SQL statements by calling %ExecDirect(). This method prepares the statement and, if it prepares the statement successfully, executes it with the supplied argument values. An optional by-reference parameter returns an instance of %SQL.Statement; this instance contains the currently prepared statement. You can then execute that statement instance just as if it had been instantiated by invoking %New() followed by a call to %Prepare(). You can also use that statement instance to prepare other dynamic statements just as if it were instantiated by %New().
If an error occurs during either statement preparation or execution, then the error is described in the result object properties of %SQLCODE and %Message. Always check the result property %SQLCODE for an error following %Execute() and %ExecDirect().
SAMPLES>set tResult = ##class(%SQL.Statement).%ExecDirect(.tStatement,"select name,age from Sample.Person where age > ? and home_state = ?",50,"VT") SAMPLES>write tResult.%SQLCODE 0 SAMPLES>do tResult.%Display() Name Age Eagleman,Emilio N. 62 Hanson,James K. 81 Hertz,Keith O. 57 3 Rows(s) Affected SAMPLES>write tStatement 1@%SQL.Statement SAMPLES>set tResult = tStatement.%Execute(40,"AK") SAMPLES>do tResult.%Display() Name Age Finn,Quentin O. 66 1 Rows(s) Affected SAMPLES>
Property Inventory
Method Inventory
- %ClassPath()
- %DialectSet()
- %Display()
- %ExecDirect()
- %Execute()
- %GetImplementationDetails()
- %Prepare()
- %PrepareClassQuery()
- %PreparedStatementSet()
- execute()
- preparse()
Properties
%SchemaPath provides a list of schema names for resolving unqualified names during statement preparation. By default, its value is null; to set its value, use a comma-delimited list of schema names:
Set %SQL.Statement.%SchemaPath = "My_Schema,Your_Schema,DEFAULT_SCHEMA"
This is equivalent to the macro-preprocessor directive for embedded SQL:
#sqlcompile PATH = My_Schema,Your_Schema,DEFAULT_SCHEMA
PATH is a search path: if the unqualified name is found in a schema in the PATH, then this schema is used to qualify the name and no further searching is performed.
You can set the value of %SchemaPath at any time, but it is only used by the %Prepare method.
%SchemaPath can contain special schema name tokens that are resolved by the SQL compiler. Special tokens are:
- CURRENT_SCHEMA is the current default schema. If the %SQL.Statement call is defined in a class method, CURRENT_SCHEMA is the schema mapped to the current class package. If the statement is defined in a .MAC routine, CURRENT_SCHEMA is the configuration default schema.
- CURRENT_PATH is the currently defined schema search path. You can use this to add an additional schema to the search path while retaining the current search path.
- DEFAULT_SCHEMA specifies the use of the system-defined default schema.
You can also set %SchemaPath to a PATH value that is constructed from a given class definition; simply set %SchemaPath to ##class(%SQL.Statement).%ClassPath(classname).
Possible values are:
- 0 for LOGICAL mode.
- 1 for ODBC mode.
- 2 for DISPLAY mode.
Methods
Execute the current statement and return the result. The result of the execute is always the return value. Success/Failure information is reported in the result object as %SQLCODE, %Message, %ROWCOUNT, and/or %ROWID.
The %Execute() method accepts parameter values in the order in which they appear in the source statement. For example:
SAMPLES>set tSQL=3 SAMPLES>set tSQL(1)="select %ID as id,Name,DOB,Home_State" SAMPLES>set tSQL(2)="from Person where Age > ? and Home_State = ?" SAMPLES>set tSQL(3)="order by 2" SAMPLES>set tStatus = tStatement.%Prepare(.tSQL) SAMPLES>set tResult = tStatement.%Execute(80,"VT") SAMPLES>do tResult.%Display() id Name DOB Home_State 140 Hanson,James K. 04/02/1928 VT 1 Rows(s) Affected SAMPLES>set tResult = tStatement.%Execute(50,"VT") SAMPLES>do tResult.%Display() id Name DOB Home_State 3 Eagleman,Emilio N. 09/01/1946 VT 140 Hanson,James K. 04/02/1928 VT 167 Hertz,Keith O. 01/01/1952 VT 3 Rows(s) Affected SAMPLES>
You can also use the same statement object for many different statements by simply invoking %Prepare() with the new statement. This is because %Prepare() initializes the statement’s execution plan and metadata. Also, an arbitrary number of statement objects can coexist in the same process.
| Parameters | ||
|---|---|---|
| %parm... | Input |
Variable number of arguments that represent the values to be bound to parameters contained in pStatementText. only parameters with input direction (input or input-output) are actually used but the position of each '?' contained in the source statement must be maintained. An example of calling %Execute for a prepared statement "? = call Sample.PersonSets(?,?)" is:
set tStatement = ##class(%SQL.Statement).%New()
set tStatus = tStatement.%Prepare("?=call Sample.PersonSets(?,?)")
set tResult = tStatement.%Execute(,"A","NY")
In this example, the return value is allocated a position in the %parms list (the ','), "A" is bound to the first argument passed to Sample.PersonSets and "NY" is bound to the second. |
Return value: This method returns a %SQL.StatementResult object.
%GetImplementationDetails() - returns the details of the currently prepared statement. This method returns the implementation class, the statement text and the actual arguments bound to formal statement arguments.
This method returns 1 for success and 0 for failure. This method fails when no statement has been prepared.
Inherited Members
Inherited Methods
- %AddToSaveSet()
- %ClassIsLatestVersion()
- %ClassName()
- %ConstructClone()
- %DispatchClassMethod()
- %DispatchGetModified()
- %DispatchGetProperty()
- %DispatchMethod()
- %DispatchSetModified()
- %DispatchSetMultidimProperty()
- %DispatchSetProperty()
- %Extends()
- %GetParameter()
- %IsA()
- %IsModified()
- %New()
- %NormalizeObject()
- %ObjectModified()
- %OriginalNamespace()
- %PackageName()
- %RemoveFromSaveSet()
- %SerializeObject()
- %SetModified()
- %ValidateObject()