|
IRISLIB database
|
|
|
IRISLIB database
|
|
Private Member Functions | |
| Display () | |
| Displays the details of the currently prepared statement. | |
| _.SQL.StatementResult | Execute (_, parm) |
| _.Library.Integer | GetImplementationDetails (_.Library.String pClassName, _.Library.String pStatementText, _.Library.List pArguments, _.Library.Integer pStatementType, _.Library.String pRuntimeClassName) |
| _.Library.Status | Prepare (_.Library.RawString pStatementText, _.Library.Boolean checkPriv) |
| Prepares an SQL statement and returns a Status indicating success or failure. More... | |
| _.Library.Status | PrepareClassQuery (_.Library.String pClassName, _.Library.String pQueryName, _.Library.Boolean checkPriv) |
| Generates the appropriate CALL statement for invoking a class query, prepares the. More... | |
Static Private Member Functions | |
| _.Library.String | ClassPath (_.Library.String pClassName) |
| Returns the PATH string for a given class name. More... | |
| _.SQL.StatementResult | ExecDirect (_.SQL.Statement pHStatement, _.Library.RawString pStatementText, _, parm) |
| Prepare and execute an SQL statement. More... | |
| _.SQL.StatementResult | ExecDirectNoPriv (_.SQL.Statement pHStatement, _.Library.RawString pStatementText, _, parm) |
| This method returns a SQL.StatementResult object. More... | |
Additional Inherited Members | |
 Public Member Functions inherited from RegisteredObject | |
| _.Library.Status | OnAddToSaveSet (_.Library.Integer depth, _.Library.Integer insert, _.Library.Integer callcount) |
| This callback method is invoked when the current object is added to the SaveSet,. More... | |
| _.Library.Status | OnClose () |
| This callback method is invoked by the <METHOD>Close</METHOD> method to. More... | |
| _.Library.Status | OnConstructClone (_.Library.RegisteredObject object, _.Library.Boolean deep, _.Library.String cloned) |
| This callback method is invoked by the <METHOD>ConstructClone</METHOD> method to. More... | |
| _.Library.Status | OnNew () |
| This callback method is invoked by the <METHOD>New</METHOD> method to. More... | |
| _.Library.Status | OnValidateObject () |
| This callback method is invoked by the <METHOD>ValidateObject</METHOD> method to. More... | |
| Static Public Attributes inherited from RegisteredObject | |
| CAPTION = None | |
| Optional name used by the Form Wizard for a class when generating forms. More... | |
| JAVATYPE = None | |
| The Java type to be used when exported. | |
| PROPERTYVALIDATION = None | |
| This parameter controls the default validation behavior for the object. More... | |
For details on using this class, see Using Dynamic SQL.
The sample class mentioned here (Sample.Person) is part of https://github.com/intersystems/Samples-Data. See Downloading Samples.
<class>SQL.Statement</class> implements an interface to prepare and execute dynamic SQL statements.
The SQL.Statement class implements an interface for managing dynamic SQL statements. The interface consists of the <method>Prepare</method>, <method>Execute</method>, <method>ExecDirect</method> methods and the <property>Dialect</property>, <property>Metadata</property>, <property>SchemaPath</property>, and <property>SelectMode</property> 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")
<method>New</method> accepts three optional parameters as follows: New(selectmode,schemapath,dialect) where selectmode is one of the allowed values of the SelectMode property of this class, schemapath is one of the allowed values of the SchemaPath property of this class, and dialect is one of the allowed values of the Dialect property of this class. 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 <method>Prepare</method> 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)
<method>Prepare</method> 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 <method>Execute</method> method.
set tResult = tStatement.Execute()
The <method>Execute</method> method returns an instance of the <class>SQL.StatementResult</class> 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 <property>Metadata</property> property is available. Metadata is an instance of the <class>SQL.StatementMetadata</class> 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; up to 200 are always supported.)
The <method>Execute</method> 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 <method>Prepare</method> 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 <method>ExecDirect</method>. 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>
|
staticprivate |
Returns the PATH string for a given class name.
The PATH string is essentially the same
as a default schema that is determined from the compiled class. If no extra IMPORT or inheritance rules are present, the default schema within a class context is determined from the class's package. IMPORT and inheritance add additional items to the PATH.
|
staticprivate |
Prepare and execute an SQL statement.
If no statement handle is allocated in pHStatement then a new statement handle will be
allocated and returned by reference. That behavior is different from SQLPrepare. Formal Parameters:
parm... - 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 a position for each '?' contained in pStatementText needs to be represented. In the statement:
? = call Sample.PersonSets(?,?)
The parms... list must contain an empty position for the return value. An example of a call to ExecDirect for such a statement is:
set tRes = ##class(SQL.Statement).ExecDirect(.tStmt,"?=call Sample.PersonSets(?,?)",,"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. This method returns a <class>SQL.StatementResult</class> object.
|
staticprivate |
This method returns a SQL.StatementResult object.
This is the same as ExecDirect but no SQL privileges are checked.
|
private |
Executes the current statement and returns the result as an instance of <class>SQL.StatementResult</class>. 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 <method>Prepare</method> 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.
The parm... input parameter represents a 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.
|
private |
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.
|
private |
Prepares an SQL statement and returns a Status indicating success or failure.
You can pass the statement as a simple string or an array of lines with the root containing the number of lines. If this method returns success, use <method>Execute</method> to execute the class query and obtain the result set.
The checkPriv argument can be used to not perform SQL privilege checking on the statement if checkPriv=0. The default is that privileges will be checked.
|
private |
Generates the appropriate CALL statement for invoking a class query, prepares the.
generated statement, and returns a Status indicating success or failure. For example, the following prepares the FileSet query of the <class>Library.File</class> class:
set status = stmt.PrepareClassQuery("%Library.File","FileSet")
If this method returns success, use <method>Execute</method> to execute the class query and obtain the result set.
All parameters defined in the formal specification of the query are assigned placeholder '?' in the generated CALL statement. Actual values for those parameters can be passed in the Execute() call. Metadata is available after a successful prepare.
Because this method generates a CALL statement, the executed class query must have the SqlProc keyword set to True. Class queries defined with SqlProc = False cannot be called by PrepareClassQuery.
The checkPriv argument can be used to not perform SQL EXECUTE privilege checking on the class queryes procedure call if checkPriv=0. The default is that privileges will be checked.
1.9.1