%Dialect defines the SQL dialect used to prepare dynamic SQL statements. Valid values are IRIS, MSSQLSERVER, MSSQL, and SYBASE. The default is IRIS. Support for MSSQLSERVER and SYBASE dialects is limited to a subset of the TSQL grammar supported by the InterSystems IRIS TSQL language mode.

%Metadata is the statement descriptor. It is an instance of %SQL.StatementMetadata.

%ObjectSelectMode allows you 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 column's declared type. That means that accessing the result set property will trigger swizzling.

This property is used to keep track of RTPC runtime cached query name for the query that triggers RTPC

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

This property is set on instantiation to the current select mode as returned by $system.SQL.GetSelectMode(); see %SYSTEM.SQL. You can set this property directly. The statement object uses this property 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.

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.

This is a Set accessor method for the %Dialect property.

Displays the details of the currently prepared statement.

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 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 %SQL.StatementResult object.

This method returns a %SQL.StatementResult object. This is the same as %ExecDirect but no SQL privileges are checked.

Executes the current statement and returns the result as an instance of %SQL.StatementResult. 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.

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.

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

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

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 %Library.File class:

	set status = stmt.%PrepareClassQuery("%Library.File","FileSet")

If this method returns success, use %Execute 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.

%PreparedStatement