Skip to main content

SelectMode

SelectMode

InterSystems SQL uses a SelectMode option to specify how data is to be displayed or stored. The available options are Logical, Display, and ODBC. Data is stored internally in Logical mode, and can be displayed in any of these modes. Every data type class can define transformations between internal Logical format and Display format or ODBC format by using the LogicalToDisplay(), LogicalToOdbc(), DisplayToLogical(), and OdbcToLogical() methods. When SQL SelectMode is Display, the LogicalToDisplay transformation is applied, and returned values are formatted for display. The default SQL SelectMode is Logical; thus by default returned values are displayed in their storage format.

SelectMode affects the format that in which query result set data is displayed, SelectMode also affects the format in which data values should be supplied, for example in the WHERE clause. InterSystems IRIS applies the appropriate transformation method based on the storage mode and the specified SelectMode. A mismatch between a supplied data value and the SelectMode can result in an error or in erroneous results. For example, if DOB is a date stored in $HOROLOG Logical format, and a WHERE clause specifies WHERE DOB > 2000–01–01 (ODBC format), SelectMode = ODBC returns the intended results. SelectMode = Display generates SQLCODE -146 Unable to convert date input to a valid logical date value. SelectMode = Logical attempts to parse 2000–01–01 as a Logical date value, and returns zero rows.

For most data types, the three SelectMode modes return the same results. The following data types are affected by the SelectMode option:

  • Date, Time, and Timestamp data types. InterSystems SQL supports numerous Date, Time, and Timestamp data types (%Library.DateOpens in a new tab, %Library.TimeOpens in a new tab, %Library.PosixTimeOpens in a new tab, %Library.TimeStampOpens in a new tab, and %MV.Date). With the exception of %Library.TimeStampOpens in a new tab, these data types use different representations for Logical, Display, and ODBC modes. In several of these data types InterSystems IRIS stores dates in $HOROLOG format. This Logical mode internal representation consists of an integer count of the number of days from an arbitrary starting date (December 31st, 1840), a comma separator, and an integer count of the number of seconds since midnight of the current day. InterSystems IRIS stores %PosixTime timestamps as an encoded 64-bit signed integer. In Display mode, dates and times commonly appear in the format specified by the data type’s FORMAT parameter or the date and time format defaults for the current locale in %SYS.NLS.FormatOpens in a new tab. The default for the American locale is DD/MM/YYYY hh:mm:ss. In ODBC mode, dates and times are always represented as YYYY-MM-DD hh:mm:ss.fff. The %Library.TimeStampOpens in a new tab data type also uses this ODBC format for Logical and Display modes.

  • %List data type. InterSystems IRIS Logical mode stores lists using two non-printing characters that appear before the first item in the list, and appear as a separator between list items. In ODBC SelectMode, list items are displayed with a comma separator between list items. In Display SelectMode, list items are displayed with a blank space separator between list items.

  • Data types that specify VALUELIST and DISPLAYLIST. For required fields, if you are in display mode and you insert a value into a table where the field has a DISPLAYLIST, the display value you enter must exactly match one of the items in the DISPLAYLIST. For non-required fields, non-matching values are converted to NULL values.

  • Empty strings, and empty BLOBs (stream fields). In Logical mode empty strings and BLOBs are represented by the non-display character $CHAR(0). In Display mode they are represented by an empty string ("").

The SQL SelectMode may be specified as follows:

Next sectionData Collation
Previous sectionPrivileges
FeedbackOpens in a new tab