Creating an Object Instance

%SelectMode Property

The %SelectModeOpens in a new tab property specifies one of the following modes: 0=Logical (the default), 1=ODBC, 2=Display. These modes specify how a data value is input and displayed. A mode is most commonly used for date and time values and for displaying %List data (a string containing an encoded list). Data is stored in Logical mode.

A SELECT query uses the %SelectMode value to determine the format used to display data.

An INSERT or UPDATE operation uses the %SelectMode value to determine the permitted format(s) for data input.

%SelectMode is used for data display. SQL statements run internally in Logical mode. For example, an ORDER BY clause orders records based on their Logical values, regardless of the %SelectMode setting. SQL functions use Logical values, regardless of the %SelectMode setting. Methods projected as SQLPROC also run in Logical mode. SQL routines called as functions in an SQL statement need to return the function value in Logical format.

• For a SELECT query, %SelectMode specifies the format used for displaying the data. Setting %SelectMode to ODBC or Display also affects the data format used for specifying comparison predicate values. Some predicate values must be specified in the %SelectMode format, other predicate values must be specified in Logical format, regardless of the %SelectMode. For details, refer to Overview of SQL Predicates.

  • Time data type data in %SelectMode=1 (ODBC) can display fractional seconds, which is not the same as actual ODBC time. The InterSystems IRIS Time data type supports fractional seconds. The corresponding ODBC TIME data type (TIME_STRUCT standard header definition) does not support fractional seconds. The ODBC TIME data type truncates a supplied time value to whole seconds. ADO DotNet and JDBC do not have this restriction.

  • %List data type data in %SelectMode=0 (Logical) does not display the internal storage value, because %List data is encoded using non-printing characters. Instead, Dynamic SQL displays a %List data value as a $LISTBUILD statement, such as the following: $lb("White","Green"). See %Print() Method for an example. %List data type data in %SelectMode=1 (ODBC) displays list elements separated by commas; this elements separator is specified as the CollectionOdbcDelimiter parameter. %List data type data in %SelectMode=2 (Display) displays list elements separated by $CHAR(10,13) (Line Feed, Carriage Return); this elements separator is specified as the CollectionDisplayDelimiter parameter.

• For an INSERT or UPDATE operation, %SelectMode specifies the format for input data that will be converted to Logical storage format. For this data conversion to occur, the SQL code must have been compiled with a select mode of RUNTIME (the default) so that a Display or ODBC %SelectMode is used when the INSERT or UPDATE is executed. For permitted input values for dates and times, refer to the date and time data types. For further details, see INSERT or UPDATE.

You can specify %SelectMode either as the first parameter of the %New() class method, or set it directly, as shown in the following two examples:

  set tStatement = ##class(%SQL.Statement).%New(2)

  set tStatement = ##class(%SQL.Statement).%New()
  set tStatement.%SelectMode=2

The following example returns the current value of %SelectMode:

  set tStatement = ##class(%SQL.Statement).%New()
  write !,"default select mode=",tStatement.%SelectMode
  set tStatement.%SelectMode=2
  write !,"set select mode=",tStatement.%SelectMode

You can determine the SelectMode default setting for the current process using the $SYSTEM.SQL.Util.GetOption("SelectMode")Opens in a new tab method. You can change the SelectMode default setting for the current process using the using the $SYSTEM.SQL.Util.SetOption("SelectMode",n)Opens in a new tab method, when n can be 0=Logical, 1=ODBC, or 2=Display. Setting %SelectMode overrides this default for the current object instance; it does not change the SelectMode process default.

For further details on SelectMode options, see Data Display Options.

%SchemaPath Property

The %SchemaPathOpens in a new tab property specifies the search path used to supply the schema name for an unqualified table name, view name, or stored procedure name. A schema search path is used for data management operations such as SELECT, CALL, INSERT, and TRUNCATE TABLE; it is ignored by data definition operations such as DROP TABLE.

The search path is specified as a quoted string containing a schema name or a comma-separated series of schema names. InterSystems IRIS searches the listed schemas in left-to-right order. InterSystems IRIS searches each specified schema until it locates the first matching table, view, or stored procedure name. Because schemas are searched in the specified order, there is no detection of ambiguous table names. Only schema names in the current namespace are searched.

The schema search path can contain both literal schema names and the CURRENT_PATH, CURRENT_SCHEMA, and DEFAULT_SCHEMA keywords.

• CURRENT_PATH specifies the current schema search path, as defined in a prior %SchemaPath property. This is commonly used to add schemas to the beginning or end of an existing schema search path.

• CURRENT_SCHEMA specifies the current schema container class name if the %SQL.Statement call is made from within a class method. If a #sqlcompile path macro directive is defined in a class method, the CURRENT_SCHEMA is the schema mapped to the current class package. Otherwise, CURRENT_SCHEMA is the same as DEFAULT_SCHEMA.

• DEFAULT_SCHEMA specifies the system-wide default schema. This keyword enables you to search the system-wide default schema as a item within the schema search path, before searching other listed schemas. The system-wide default schema is always searched after searching the schema search path if all the schemas specified in the path have been searched without a match.

The %SchemaPath is the first place InterSystems IRIS searches schemas for a matching table name. If %SchemaPath is not specified, or does not list a schema that contains a matching table name, InterSystems IRIS uses the system-wide default schema.

You can specify a schema search path either by specifying the %SchemaPath property, or by specifying the second parameter of the %New() class method, as shown in the following two examples:

  set path="MyTests,Sample,Cinema"
  set tStatement = ##class(%SQL.Statement).%New(,path)

  set tStatement = ##class(%SQL.Statement).%New()
  set tStatement.%SchemaPath="MyTests,Sample,Cinema"

You can set %SchemaPath at any point prior to the %Prepare() method which uses it.

The following example returns the current value of %SchemaPath:

  set tStatement = ##class(%SQL.Statement).%New()
  write !,"default path=",tStatement.%SchemaPath
  set tStatement.%SchemaPath="MyTests,Sample,Cinema"
   write !,"set path=",tStatement.%SchemaPath

You can use the %ClassPath()Opens in a new tab method to set %SchemaPath to the search path defined for the specified class name:

  set tStatement = ##class(%SQL.Statement).%New()
  set tStatement.%SchemaPath=tStatement.%ClassPath("Sample.Person")
  write tStatement.%SchemaPath

%Dialect Property

The %DialectOpens in a new tab property specifies the SQL statement dialect. You can specify Sybase, MSSQL, or IRIS (InterSystems SQL). The Sybase or MSSQL setting causes the SQL statement to be processed using the specified Transact-SQL dialect.

The Sybase and MSSQL dialects support a limited subset of SQL statements in these dialects. They support the SELECT, INSERT, UPDATE, DELETE, and EXECUTE statements. They support the CREATE TABLE statement for permanent tables, but not for temporary tables. CREATE VIEW is supported. CREATE TRIGGER and DROP TRIGGER are supported. However, this implementation does not support transaction rollback should the CREATE TRIGGER statement partially succeed but then fail on class compile. CREATE PROCEDURE and CREATE FUNCTION are supported.

The Sybase and MSSQL dialects support the IF flow-of-control statement. This command is not supported in the IRIS (InterSystems SQL) dialect.

The default is InterSystems SQL, represented by an empty string (""), or specified as "IRIS"

You can specify %Dialect either as the third parameter of the %New() class method, or set it directly as a property, or set it using a method, as shown in the following three examples:

Setting %Dialect in %New() class method:

  set tStatement = ##class(%SQL.Statement).%New(,,"Sybase")
  write "language mode set to=",tStatement.%Dialect

Setting the %Dialect property directly:

  set tStatement = ##class(%SQL.Statement).%New()
  set defaultdialect=tStatement.%Dialect
  write "default language mode=",defaultdialect,!
  set tStatement.%Dialect="Sybase"
  write "language mode set to=",tStatement.%Dialect,!
  set tStatement.%Dialect="IRIS"
   write "language mode reset to default=",tStatement.%Dialect,!

Setting the %Dialect property using the %DialectSet()Opens in a new tab instance method, which returns an error status:

  set tStatement = ##class(%SQL.Statement).%New()
  set tStatus = tStatement.%DialectSet("Sybase")
    if tStatus'=1 {write "%DialectSet failed:" do $System.Status.DisplayError(tStatus) quit}
  write "language mode set to=",tStatement.%Dialect

The %DialectSet() method returns a %Status value: Success returns a status of 1. Failure returns an object expression that begins with 0, followed by encoded error information. For this reason, you cannot perform a tStatus=0 test for failure; you can perform a $$$ISOK(tStatus)=0 macro test for failure.

%ObjectSelectMode Property

The %ObjectSelectModeOpens in a new tab property is a boolean value. If %ObjectSelectMode=0 (the default) all columns in the SELECT list are bound to properties with literal types in the result set. If %ObjectSelectMode=1 then columns in the SELECT list are bound to properties with the type defined in the associated property definition.

%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=0 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=1 the property will be defined with the column’s declared type. That means that accessing the result set property will trigger swizzling.

%ObjectSelectMode cannot be set as a parameter of %New().

The following example returns the %ObjectSelectMode default value, sets %ObjectSelectMode, then returns the new %ObjectSelectMode value:

  set myquery = "SELECT TOP 5 %ID AS MyID,Name,Age FROM Sample.Person"
  set tStatement = ##class(%SQL.Statement).%New()
  write !,"default ObjectSelectMode=",tStatement.%ObjectSelectMode
  set tStatement.%ObjectSelectMode=1
  write !,"set ObjectSelectMode=",tStatement.%ObjectSelectMode

%ObjectSelectMode=1 is principally used when returning values from a result set using the field name property. This is further described with examples in Fieldname Property.

%ObjectSelectMode=1 can be used when a field in the SELECT list is linked to a collection property. %ObjectSelectMode will swizzle the collection. If %SelectMode = 1 or 2, the system converts the collection serial value into Logical mode form before swizzling. The resulting oref supports the full collection interface.