Class Reference
Cache for UNIX 2018.1.2
InterSystems: The power behind what matters   
Documentation  Search
Private  Storage  

class %SQL.Statement extends %Library.RegisteredObject

%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>
	

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
17 17


Summary

Properties
%CallArgs %Dialect %Metadata %ObjectSelectMode
%SchemaPath %SelectMode %msqlvar

Methods
%%OIDGet %AddToSaveSet %BindExport %BuildObjectGraph
%ClassIsLatestVersion %ClassName %ClassPath %Close
%ConstructClone %DialectSet %DispatchClassMethod %DispatchGetModified
%DispatchGetProperty %DispatchMethod %DispatchSetModified %DispatchSetMultidimProperty
%DispatchSetProperty %Display %ExecDirect %Execute
%Extends %GetImplementationDetails %GetParameter %IncrementCount
%IsA %IsModified %MetadataGet %New
%NormalizeObject %ObjectModified %OriginalNamespace %PackageName
%Prepare %PrepareClassQuery %PreparedStatementSet %RemoveFromSaveSet
%SerializeObject %SetModified %ValidateObject execute
prepare prepareForServer preparse


Properties

• property %CallArgs as %String(MAXLEN="") [ MultiDimensional ];
At prepare time, %CallArgs is initialized to the literals and host vars (argtype C, V) with gaps left for the ?-args. NOT USED ANYMORE (DPV4449)
• property %CallQArgs as %Integer [ Private,MultiDimensional ];
This is an array of ?-args - base is the number of qArgs, %CallQArgs(arg#) = argument position At execute time, this array is used to populate the ?-arg values. This work is done in bindArguments which returns an array of arguments suitable for passing using byref array syntax. This has two primary benefits - first of all, we can execute the statement directly instead of using $xecute. Second of all there is no limit to the number of arguments we pass.
• property %Dialect as %String;
%Dialect defines the SQL dialect used to prepare dynamic SQL statements. Valid values are CACHE, MSSQLSERVER, MSSQL, and SYBASE. The default is CACHE. Support for MSSQLSERVER and SYBASE dialects is limited to a subset of the TSQL grammar supported by the Cache TSQL language mode.
• property %HostVars as %Integer [ Private,MultiDimensional ];
• property %Metadata as %SQL.StatementMetadata;
%Metadata is the statement descriptor. It is an instance of %SQL.StatementMetadata.
• property %ObjectSelectMode as %Library.Boolean [ InitialExpression = 0 ];
%ObjectSelectMode allows the user to specify how columns whose type class is a swizzleable class will be defined in the result set class generated from a SELECT statement. If %ObjectSelectMode is false (the default) then the property corresponding to the swizzleable column will be defined in result sets as a simple literal type corresponding to the SQL table's ROWID type. If %ObjectSelectMode is true then the property will be defined with the columns declared type. That means that accessing the result set property will trigger swizzling.
• property %PreparedStatement as %ObjectHandle [ Private ];
• property %SchemaPath as %String(MAXLEN="");

%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).

• property %SelectMode as %Library.String [ InitialExpression = $zu(115,5) ];
This property is set on instantiation to the current select mode as returned by $system.SQL.GetSelectMode(). It can be set directly by the user. It is used by the statement object to establish the SQL SELECTMODE value used by dynamic statements.
Possible values are:
  • 0 for LOGICAL mode.
  • 1 for ODBC mode.
  • 2 for DISPLAY mode.
• property %StatementActualArgs as %String(MAXLEN="") [ Private ];
%StatementActualArgs are the actual values bound to formal statement arguments. This value is established during %Prepare from the %StatementArgs value. This value is used to form the actual arguments passed when executing the currently prepared statement. The value of %StatementActualArgs includes and literals and host variables replaced during preparsing as well as the local variables used to hold actual parameter values passed to %Execute. We use two types of locals - %parm(parameter_ordinal_position) for non-CALL statements and parm_parameter_ordinal_position (for example, parm1 for the first '?') for CALL statements. The reason for the difference is that we need to preserve 'undefinedness' of parameters passed to SQL invoked routines.
• property %StatementArgCount as %Integer [ Private,InitialExpression = 0 ];
• property %StatementArgs as %String(MAXLEN="") [ Private ];
statementArgs are literals, host variables (not supported here), and parameters (as ?) This value is established during preparsing and is a $list consisting of n*2 elements where 'n' is the number of arguments discovered by the preparser. For a non-empty list, the first element of the list is the argType and the second is the argValue for the first argument discovered. This structure repeats for each argument. Arguments are either constants, (argType = 'C', argValue is a literal), host variables (argType = 'V', argValue is a legal COS identifier), or replacable parameter (argType = '?', argValue = '?'). This value is processed during %Prepare to construct the %StatementActualArgs value. This value is retained for metadata reporting as the actual statement prepared might have a different formal argument specification.
• property %StatementGUID as %Binary [ Private ];
• property %StatementHash as %Binary [ Private ];
• property %StatementText as %String(MAXLEN="") [ Private,MultiDimensional ];
Internal property containing the actual SQL statement submitted to prepare().
• property %StatementType as %Integer [ Private ];

Integer value indicating the type of statement.

1SELECT
2INSERT (also 'INSERT OR UPDATE')
3UPDATE
4DELETE
5COMMIT
6ROLLBACK
7GRANT
8REVOKE
9CREATE TABLE
10ALTER TABLE
11DROP TABLE
12CREATE VIEW
13ALTER VIEW
14DROP VIEW
15CREATE INDEX
16ALTER INDEX (Not supported)
17DROP INDEX
18CREATE ROLE
19DROP ROLE
20SET TRANSACTION
21START TRANSACTION
22%INTRANSACTION
23%BEGTRANS (Alias for START TRANSACTION)
24%INTRANS (Alias for %INTRANSACTION)
25GET (Not supported)
26SET OPTION
27STATISTICS (UPDATE STATISTICS, not supported))
28%CHECKPRIV
29CREATE USER
30ALTER USER
31DROP USER
32%CHECKPRIV (SQL Admin Privilege)
33GRANT (SQL Admin Privilege)
34REVOKE (SQL Admin Privilege)
35CREATE FUNCTION
36CREATE METHOD
37CREATE PROCEDURE
38CREATE QUERY
39DROP FUNCTION
40DROP METHOD
41DROP PROCEDURE
42DROP QUERY
43CREATE TRIGGER
44DROP TRIGGER
45CALL
46SAVEPOINT
47LOCK TABLE
48UNLOCK TABLE
49CREATE DATABASE
50DROP DATABASE
51USE DATABASE
99Anything not list above
• property %msqlvar as %String;
This property is used to set %msqlvars during the compilation of the cached query. If defined, %msqlvars will be placed into the cached query methods as #EXECUTE set %msqlvar=<%msqlvar>

Methods

• classmethod %ClassPath(pClassName As %Library.String) as %Library.String
%ClassPath 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.
• method %DialectSet(pValue As %String = "") as %Status
This is a Set accessor method for the %Dialect property.
• method %Display()
%Display() - display the details of the currently prepared statement.
• classmethod %ExecDirect(ByRef pHStatement As %SQL.Statement = $$$NULLOREF, ByRef pStatementText As %CacheString = "", %parm...) as %SQL.StatementResult
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: pHStatement - OPTIONAL (byref) the statement handle. If an actual arg is passed by reference then it will be populated with an oref to a new statement object; pStatementText - (byref) SQL statement text. This can be an array of SQL statement lines with the base node set to the number of lines or - it can be a single string; %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 tResult = ##class(%SQL.Statement).%ExecDirect(.tStatement,"?=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 %SQL.StatementResult object.
• method %Execute(%parm...) as %SQL.StatementResult

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.

• method %GetImplementationDetails(Output pClassName As %Library.String(MAXLEN=300), Output pStatementText As %Library.String(MAXLEN=""), Output pArguments As %Library.List, Output pStatementType As %Integer) as %Integer

%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.

pClassName contains the name of the current statement's implementation class.
pStatementText Contains the statement text. This variable is either a simple string or an array of strings where pStatementText is the number of lines and pStatementText(line) is a line of statement text.
pArguments A $list containing argument types and argument value in the form $list(arg1type, arg1value[, arg2type, arg2value...argNtype, argNvalue]). There are three argument types - 'c' = constant, 'v' = host variable and '?' is a parameter. All three argument types are replaced by ? arguments during statement prepare to improve the efficiency of the statement cache. Only actual arguments of type '?' require a value to be supplied when executing the statement.
pStatementType An integer value corresponding to the internal type number for the type of statement. This type number is for internal use and subject to change.

This method returns 1 for success and 0 for failure. This method fails when no statement has been prepared.

• method %MetadataGet() as %SQL.StatementMetadata
This is a Get accessor method for the %Metadata property.
• private method %OnClose() as %Status
%OnClose will Purge the just used cached query for certain statement types, like DDL statements.
• private method %OnNew(pSelectMode As %CacheString = "", pPath As %CacheString = "", pDialect As %CacheString = "") as %Status
%OnNew accepts three optional parameters, the values of these three parameters are the initial values of %SelectMode, %SchemaPath and %Dialect.
• method %Prepare(ByRef pStatementText As %CacheString = "") as %Status
Prepare an SQL statement. Pass the statement as a simple string or an array of lines with the root containing the number of lines. A comma delimited list of packages can also be passed. This list of packages will be used to qualify any unqualified names referenced in the statement source. This method returns a %Status value.
• method %PrepareClassQuery(pClassName As %String = "", pQueryName As %String = "") as %Library.Status
%PrepareClassQuery will generate the appropriate CALL statement for invoking a class query. That generated statement is then %Prepare'd and, if successful, the statement can be executed by invoking %Execute. All parameters defined in the formal specification of the query are assigned place holder '?' 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 %PrepareClassQuery 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.
• method %PreparedStatementSet(pPS As %ObjectHandle) as %Status
%PreparedStatement
• private method bindArguments(ByRef pActual As %String(MAXLEN=""), ByRef %CallArgs As %String) as %Integer
• method execute(%parm...) as %SQL.StatementResult
• method prepare(ByRef pStatementText As %CacheString = "")
prepare() is an internal method that does the actual work of preparing a statement. This method throws an exception if any error is encountered.
• method prepareForServer(ByRef pServerStatement As %Binary, pStatementHandle As %Integer = 0, ByRef pMetadata As %Binary = "")
prepareForServer() is an internal method that is called by the xDBC server. It constructs metadata specific to the server and populates the server statement object. (%qsc(%qacn) is where the server "statement object" is kept). **NOTE** THIS METHOD IS INTERNAL, ISC USE ONLY!!
• classmethod preparse(ByRef pStatementText, ByRef pStatementPreparsed, ByRef pStatementArgs) as %Integer


Copyright © 1997-2019, InterSystems Corporation