%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