docs.intersystems.com
Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  SQL Reference Material  /  Data Types


InterSystems SQL Reference
Data Types
[Next] 
InterSystems: The power behind what matters   
Search:  


Specifies the kind of data that an SQL entity (such as a column) can contain.
Description
The following topics are described here:
A data type specifies the kind of value that a column can hold. You specify the data type when defining a field with CREATE TABLE. When defining SQL expressions, you can specify the DDL data types listed in the following table (left-hand column). When you specify one of these DDL data types, it maps to the InterSystems IRIS data type listed in the right-hand column.
To view the current system data type mappings, go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, System-defined DDL Mappings.
You can also define additional user data types. To create or view the user data type mappings, go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, User-defined DDL Mappings.
Table of DDL Data Types
DDL Data Type Corresponding InterSystems IRIS Class Property Data Type
BIGINT
%Library.BigInt (MAXVAL=9223372036854775807, MINVAL=-9223372036854775807)
If a BIGINT column can contain both NULLs and extremely small negative numbers, you may need to redefine the index null marker to support standard index collation. For further details refer to Indexing a NULL in the SQL Optimization Guide.
BIGINT(%1)
%Library.BigInt The %1 is ignored.
Equivalent to BIGINT. Provided for MySQL compatibility.
BINARY %Library.Binary(MAXLEN=1)
BINARY(%1) %Library.Binary(MAXLEN=%1)
BINARY VARYING %Library.Binary(MAXLEN=1)
BINARY VARYING(%1) %Library.Binary(MAXLEN=%1)
BIT %Library.Boolean
CHAR %Library.String(MAXLEN=1)
CHAR(%1) %Library.String(MAXLEN=%1)
CHAR VARYING %Library.String(MAXLEN=1)
CHAR VARYING(%1) %Library.String(MAXLEN=%1)
CHARACTER %Library.String(MAXLEN=1)
CHARACTER VARYING %Library.String(MAXLEN=1)
CHARACTER VARYING(%1) %Library.String(MAXLEN=%1)
CHARACTER(%1) %Library.String(MAXLEN=%1)
DATE %Library.Date
DATETIME %Library.DateTime
DATETIME2 %Library.DateTime
DEC %Library.Numeric MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0.
DEC(%1) %Library.Numeric A 64–bit signed integer. If %1 is less than 19, MAXVAL and MINVAL are the %1 number of digits. For example, DEC(8) MAXVAL=99999999, MINVAL=-99999999, SCALE=0. The largest meaningful value for %1 is 19; %1 values larger than 19 do not issue an error, but default to 19. If %1 is 19 or greater: MAXVAL=9223372036854775807, MINVAL=-9223372036854775808, SCALE=0.
DEC(%1,%2) %Library.Numeric (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2)
DECIMAL %Library.Numeric MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0.
DECIMAL(%1) %Library.Numeric A 64–bit signed integer. If %1 is less than 19, MAXVAL and MINVAL are the %1 number of digits. For example, DECIMAL(8) MAXVAL=99999999, MINVAL=-99999999, SCALE=0. The largest meaningful value for %1 is 19; %1 values larger than 19 do not issue an error, but default to 19. If %1 is 19 or greater: MAXVAL=9223372036854775807, MINVAL=-9223372036854775808, SCALE=0.
DECIMAL(%1,%2) %Library.Numeric (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2)
DOUBLE %Library.Double This is the IEEE floating point standard. An SQL column with this data type returns a default precision of 20. For further details (including important max/min value limits), refer to the $DOUBLE function in the ObjectScript Reference.
DOUBLE PRECISION %Library.Double This is the IEEE floating point standard. An SQL column with this data type returns a default precision of 20. For further details (including important max/min value limits), refer to the $DOUBLE function in the ObjectScript Reference.
IMAGE %Stream.GlobalBinary
INT %Library.Integer (MAXVAL=2147483647, MINVAL=-2147483648)
INT(%1)
%Library.Integer (MAXVAL=2147483647, MINVAL=-2147483648). The %1 is ignored.
Equivalent to INT. Provided for MySQL compatibility.
INTEGER %Library.Integer (MAXVAL=2147483647, MINVAL=-2147483648)
LONG %Stream.GlobalCharacter
LONG BINARY %Stream.GlobalBinary
LONG RAW %Stream.GlobalBinary
LONGTEXT
Equivalent to LONG. Provided for MySQL compatibility.
LONG VARCHAR %Stream.GlobalCharacter
LONG VARCHAR(%1) %Stream.GlobalCharacter
LONGVARBINARY %Stream.GlobalBinary
LONGVARBINARY(%1) %Stream.GlobalBinary
LONGVARCHAR %Stream.GlobalCharacter
LONGVARCHAR(%1) %Stream.GlobalCharacter
MEDIUMINT
%Library.Integer(MAXVAL=8388607,MINVAL=-8388608)
Provided for MySQL compatibility.
MEDIUMINT(%1)
%Library.Integer(MAXVAL=8388607,MINVAL=-8388608) The %1 is ignored.
Provided for MySQL compatibility.
MONEY %Library.Currency(MAXVAL=922337203685477.5807, MINVAL=-922337203685477.5808, SCALE=4)
NATIONAL CHAR %Library.String(MAXLEN=1)
NATIONAL CHAR(%1) %Library.String(MAXLEN=%1)
NATIONAL CHAR VARYING %Library.String(MAXLEN=1)
NATIONAL CHAR VARYING(%1) %Library.String(MAXLEN=%1)
NATIONAL CHARACTER %Library.String(MAXLEN=1)
NATIONAL CHARACTER(%1) %Library.String(MAXLEN=%1)
NATIONAL CHARACTER VARYING %Library.String(MAXLEN=1)
NATIONAL CHARACTER VARYING(%1) %Library.String(MAXLEN=%1)
NATIONAL VARCHAR %Library.String(MAXLEN=1)
NATIONAL VARCHAR(%1) %Library.String(MAXLEN=%1)
NCHAR %Library.String(MAXLEN=1)
NCHAR(%1) %Library.String(MAXLEN=%1)
NTEXT %Stream.GlobalCharacter
NUMBER %Library.Numeric A 64–bit signed integer. (MAXVAL=9223372036854775807, MINVAL=-9223372036854775808, SCALE=0)
NUMBER(%1) %Library.Numeric A 64–bit signed integer. If %1 is less than 19, MAXVAL and MINVAL are the %1 number of digits. For example, NUMBER(8) MAXVAL=99999999, MINVAL=-99999999, SCALE=0. The largest meaningful value for %1 is 19; %1 values larger than 19 do not issue an error, but default to 19. If %1 is 19 or greater: MAXVAL=9223372036854775807, MINVAL=-9223372036854775808, SCALE=0.
NUMBER(%1,%2) %Library.Numeric (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2)
NUMERIC %Library.Numeric MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0.
NUMERIC(%1) %Library.Numeric A 64–bit signed integer. If %1 is less than 19, MAXVAL and MINVAL are the %1 number of digits. For example, NUMERIC(8) MAXVAL=99999999, MINVAL=-99999999, SCALE=0. The largest meaningful value for %1 is 19; %1 values larger than 19 do not issue an error, but default to 19. If %1 is 19 or greater: MAXVAL=9223372036854775807, MINVAL=-9223372036854775808, SCALE=0.
NUMERIC(%1,%2) %Library.Numeric (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2)
NVARCHAR %Library.String(MAXLEN=1)
NVARCHAR(%1) %Library.String(MAXLEN=%1)
NVARCHAR(%1,%2) %Library.String(MAXLEN=%1)
NVARCHAR(MAX)
Equivalent to LONGVARCHAR. Provided for TSQL compatibility.
POSIXTIME %Library.PosixTime MAXVAL=1406323805406846975, MINVAL=-6979664624441081856, SCALE=0.
RAW(%1) %Library.Binary(MAXLEN=%1)
ROWVERSION
%Library.RowVersion(MAXVAL=9223372036854775807, MINVAL=1)
A system-assigned sequential integer. See ROWVERSION Data Type for details.
SERIAL %Library.Counter System-generated: (MAXVAL=2147483647, MINVAL=1). User-supplied: (MAXVAL=2147483647, MINVAL=-2147483648)
SMALLDATETIME %Library.DateTime MAXVAL=’2079-06- 06-23:59:59’; MINVAL=’1900-01-01 00:00:00’)
SMALLINT %Library.SmallInt (MAXVAL=32767, MINVAL=-32768)
SMALLINT(%1)
%Library.SmallInt The %1 is ignored.
Equivalent to SMALLINT. Provided for MySQL compatibility.
SMALLMONEY %Library.Currency SCALE=4
SYSNAME %Library.String(MAXLEN=128)
TEXT %Stream.GlobalCharacter
TIME %Library.Time
TIMESTAMP
You can re-map TIMESTAMP=%Library.PosixTime in the system configuration or CPF file. %Library.PosixTime is a more efficient timestamp representation than %Library.TimeStamp. For further details, refer to Date, Time, PosixTime, and TimeStamp Data Types.
TINYINT %Library.TinyInt (MAXVAL=127, MINVAL=-128)
TINYINT(%1)
%Library.TinyInt The %1 is ignored.
Equivalent to TINYINT. Provided for MySQL compatibility.
UNIQUEIDENTIFIER %Library.UniqueIdentifier
VARBINARY %Library.Binary(MAXLEN=1)
VARBINARY(%1) %Library.Binary(MAXLEN=%1)
VARCHAR %Library.String(MAXLEN=1)
VARCHAR(%1) %Library.String(MAXLEN=%1)
VARCHAR(%1,%2) %Library.String(MAXLEN=%1)
VARCHAR2(%1) %Library.String(MAXLEN=%1)
VARCHAR(MAX)
Equivalent to LONGVARCHAR. Provided for TSQL compatibility only.
Important:
Each of the DDL or InterSystems IRIS data type expressions shown above is actually one continuous string. These strings may contain space characters, but generally do not contain white space of any kind. Some white space appears in this table for readability.
Specifying MAXLEN
Precision and Scope
Numeric data types such as NUMERIC(6,2) have two integer values (p,s) precision and scope. These are mapped to ObjectScript %Library class data types, as described in “Understanding DDL Data Type Mappings”. When specified in an SQL data type, the following apply on Windows systems (maximums may differ on other systems):
The following example shows the maximum values for different combinations of precision and scope:
  FOR i=0:1:6 {
      WRITE "Max for (",i,",2)=",$$maxval^%apiSQL(i,2),!}
SQL System Data Type Mappings
The syntax shown for DDL and InterSystems IRIS data type expressions in the above table are the default mappings configured for the SQL.SystemDataTypes. There are separate mapping tables available for supplied system data types, and user data types.
To view and modify the current data type mappings, Go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, System-defined DDL Mappings.
Understanding DDL Data Type Mappings
When mapping data types from DDL to InterSystems IRIS, regular parameters and function parameters follow these rules:
Data Type Precedence
When an operation can return several different values, and these values may have different data types, InterSystems IRIS assigns the return value whichever data type has the highest precedence. For example, a NUMERIC data type can contain all possible INTEGER data type values, but an INTEGER data type cannot contain all possible NUMERIC data type values. Thus NUMERIC has the higher precedence (is more inclusive).
For example, if a CASE statement has a possible result value of data type INTEGER, and a possible result value of data type NUMERIC, the actual result is always of type NUMERIC, regardless of which of these two cases are taken.
The precedence for data types is as follows, from highest (most inclusive) to lowest:
LONGVARBINARY
LONGVARCHAR
VARBINARY
VARCHAR
GUID
TIMESTAMP
DOUBLE
NUMERIC
BIGINT
INTEGER
DATE
TIME
SMALLINT
TINYINT
BIT
Normalize and Validate
The %Library.DataType superclass contains classes for the specific data types. These data type classes provide a Normalize() method to normalize an input value to the data type format and an IsValid() method to determine if an input value is valid for that data type, as well as various mode conversion methods such as LogicalToDisplay() and DisplayToLogical().
The following examples show the Normalize() method for the %TimeStamp data type:
  SET indate=64701
  SET tsdate=##class(%Library.TimeStamp).Normalize(indate)
  WRITE "%TimeStamp date: ",tsdate
  SET indate="2018-2-22"
  SET tsdate=##class(%Library.TimeStamp).Normalize(indate)
  WRITE "%TimeStamp date: ",tsdate
The following examples show the IsValid() method for the %TimeStamp data type:
  SET datestr="July 4, 2018"
  SET stat=##class(%Library.TimeStamp).IsValid(datestr)
    IF stat=1 {WRITE datestr," is a valid %TimeStamp",! }
    ELSE {WRITE datestr," is not a valid %TimeStamp",!}
  SET leapdate="2016-02-29 00:00:00"
  SET noleap="2018-02-29 00:00:00"
  SET stat=##class(%Library.TimeStamp).IsValid(leapdate)
    IF stat=1 {WRITE leapdate," is a valid %TimeStamp",! }
    ELSE {WRITE leapdate," is not a valid %TimeStamp",!}
  SET stat=##class(%Library.TimeStamp).IsValid(noleap)
    IF stat=1 {WRITE noleap," is a valid %TimeStamp",! }
    ELSE {WRITE noleap," is not a valid %TimeStamp",!}
Date, Time, PosixTime, and TimeStamp Data Types
You can define date, time, and timestamp data types, and interconvert dates and timestamps through standard InterSystems SQL date and time functions. For example, you can use CURRENT_DATE or CURRENT_TIMESTAMP as input to a field defined with that data type, or use DATEADD, DATEDIFF, DATENAME, or DATEPART to manipulate date values stored with this data type.
The data type classes %Library.Date, %Library.Time, %Library.PosixTime, %Library.TimeStamp, and %MV.Date are treated as follows with regard to SqlCategory:
  1. %Library.Date classes, and any user-defined data type class that has a logical value of +$HOROLOG (the date portion of $HOROLOG) should use DATE as the SqlCategory.
  2. %Library.Time classes, and any user-defined data type class that has a logical value of $PIECE($HOROLOG,”,”,2) (the time portion of $HOROLOG) should use TIME as the SqlCategory. TIME supports fractional seconds, so this data type can also be used for HH:MI:SS.FF to a user-specified number of digits of precision. To support fractional seconds set the Precision parameter: a value of 0 rounds to the nearest second; a value of "" (the default) retains whatever precision is specified in the data value.
    A field using the %Time datatype reports precision and scale to xDBC as follows: If a precision argument is not specified, or specified as 0 or "", xDBC sets precision=8 and scale=0; fractional seconds are truncated. If a precision argument is specified, xDBC sets precision=8+precision and scale=precision.
  3. %Library.PosixTime classes, and any user-defined data type class that has an encoded signed 64-bit integer logical value should use POSIXTIME as the SqlCategory. %PosixTime is an encoded timestamp calculated from the number of seconds (and fractional seconds) since 1970–01–01 00:00:00. Timestamps after that date are represented by a positive %PosixTime value, timestamps before that date are represented by a negative %PosixTime value. %PosixTime supports a maximum of 6 digits of precision for fractional seconds. The earliest date supported by %PosixTime is 0001-01-01 00:00:00, which has a logical value of -6979664624441081856. The last date supported is 9999-12-31 23:59:59.999999, which has a logical value of 1406323805406846975.
    Because a %PosixTime value is always represented by a encoded 64-bit integer, it can always be unambiguously differentiated from a %Date or %TimeStamp value. For example, the %PosixTime value for 1970–01–01 00:00:00 is 1152921504606846976, the %PosixTime value for 2017–01–01 00:00:00 is 1154404733406846976, and the %PosixTime value for 1969–12–01 00:00:00 is -6917531706041081856.
    %PosixTime is preferable to %TimeStamp, because it takes up less disk space and memory than the %TimeStamp data type and provides better performance than %TimeStamp.
    You can integrate %PosixTime and %TimeStamp values by using the ODBC display mode:
    You can convert %TimeStamp values to %PosixTime using the TO_POSIXTIME function. You can use the IsValid() method to determine if a numeric value is a valid %PosixTime value.
  4. %Library.TimeStamp classes, and any user-defined data type class that has a logical value of YYYY-MM-DD HH:MI:SS.FF should use TIMESTAMP as the SqlCategory. Note that %Library.TimeStamp derives its maximum precision from the system platform’s precision, while %Library.PosixTime has a maximum precision of 6 digits. Therefore, %Library.TimeStamp may be more precise than %Library.PosixTime on some platforms.
  5. %Library.DateTime is a subclass of %Library.TimeStamp. It defines a type parameter named DATEFORMAT and it overrides the DisplayToLogical() and OdbcToLogical() methods to handle imprecise datetime input that TSQL applications are accustomed to.
  6. %MV.Date classes, or any user-defined data type class that has a logical date value of $HOROLOG-46385, should use MVDATE as the SqlCategory.
  7. A user-defined date data type that does not fit into any of the preceding logical values should define the SqlCategory of the data type as DATE and provide in the data type class a LogicalToDate() method to convert a user-defined logical date value to a %Library.Date logical value, and a DateToLogical() method to convert a %Library.Date logical value to the user-defined logical date value.
  8. A user-defined time data type that does not fit into any of the preceding logical values should define the SqlCategory of the data type as TIME and provide in the data type class a LogicalToTime() method to convert a user-defined logical time value to a %Library.Time logical value, and a TimeToLogical() method to convert a %Library.Time logical value to the user-defined logical time value.
  9. A user-defined timestamp data type that does not fit into any of the preceding logical values should define the SqlCategory of the data type as TIMESTAMP and provide in the data type class a LogicalToTimeStamp() method to convert a user-defined logical timestamp value to a %Library.TimeStamp logical value, and a TimeStampToLogical() method to convert a %Library.TimeStamp logical value to the user-defined logical timestamp value.
You can compare POSIXTIME to DATE or TIMESTAMP values using =, <>, >, or < operators.
When comparing FMTIMESTAMP category values with DATE category values, InterSystems IRIS does not strip the time from the FMTIMESTAMP value before comparing it to the DATE. This is identical behavior to comparing TIMESTAMP with DATE values, and comparing TIMESTAMP with MVDATE values. It is also compatible with how other SQL vendors compare timestamps and dates. This means a comparison of a FMTIMESTAMP 320110202.12 and DATE 62124 are equal when compared using the SQL equality (=) operator. Applications must convert the FMTIMESTAMP value to a DATE or FMDATE value to compare only the date portions of the values.
Dates Prior to December 31, 1840
A date is commonly represented by the DATE data type. This data type stores a date in $HOROLOG format, as a positive integer count of days from the arbitrary starting date of December 31, 1840.
By default, dates can only be represented by a positive integer (MINVAL=0). However, you can change the MINVAL type parameter to enable storage of dates prior to December 31, 1840. By setting MINVAL to a negative number, you can store dates prior to December 31, 1840 as negative integers. The earliest allowed MINVAL value is -672045. This corresponds to January 1 of Year 1 (CE). DATE data type cannot represent BCE (also known as BC) dates.
Note:
Be aware that these date counts do not take into account changes in date caused by the Gregorian calendar reform (enacted October 15, 1582, but not adopted in Britain and its colonies until 1752).
You can redefine the minimum date for your locale as follows:
  SET oldMinDate = ##class(%SYS.NLS.Format).GetFormatItem("DATEMINIMUM")
  IF oldMinDate=0 {
    DO ##class(%SYS.NLS.Format).SetFormatItem("DATEMINIMUM",-672045)
    SET newMinDate = ##class(%SYS.NLS.Format).GetFormatItem("DATEMINIMUM")
    WRITE "Changed earliest date to ",newMinDate
    }
  ELSE { WRITE "Earliest date was already reset to ",oldMinDate}
The above example sets the MINVAL for your locale to the earliest permitted date (1/1/01).
Note:
InterSystems IRIS does not support using Julian dates with negative logical DATE values (%Library.Date values with MINVAL<0). Thus, these MINVAL<0 values are not compatible with the Julian date format returned by the TO_CHAR function.
Strings
The %Library.String data type supports a maximum string length of 3,641,144 characters. Commonly, extremely long strings should be assigned one of the %Stream.GlobalCharacter data types. For further details, refer to Maximum String Length in the “Data Types and Values” chapter of Using ObjectScript.
How such long strings are handled depends on your xDBC protocol:
The protocol that is used is the highest protocol supported by both the InterSystems IRIS instance and the ODBC driver facilities. If all facilities on both host and client support Protocol 47, that protocol is used. If any one facility only supports Protocol 46 that protocol is used, regardless of Protocol 47 support in other facilities. The protocol that was actually used is recorded in the InterSystems ODBC log.
Note that, by default, InterSystems IRIS establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
List Structures
InterSystems IRIS supports the list structure data type %List (data type class %Library.List). This is a compressed binary format, which does not map to a corresponding native data type for InterSystems SQL. In its internal representation it corresponds to data type VARBINARY with a default MAXLEN of 32749.
For this reason, Dynamic SQL cannot use %List data in a WHERE clause comparison. You also cannot use INSERT or UPDATE to set a property value of type %List.
Dynamic SQL returns the data type of list structured data as VARCHAR.
If you use an ODBC or JDBC client, %List data is projected to VARCHAR string data, using LogicalToOdbc conversion. A list is projected as a string with its elements delimited by commas. Data of this type can be used in a WHERE clause, and in INSERT and UPDATE statements. Note that, by default, InterSystems IRIS establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
For further details on data type class %Library.List, refer to the InterSystems Class Reference. For further details on using lists in a WHERE clause, see the %INLIST predicate and the FOR SOME %ELEMENT predicate. For further details on handling list data as a string, see the %EXTERNAL function.
InterSystems SQL supports eight list functions: $LIST, $LISTBUILD, $LISTDATA, $LISTFIND, $LISTFROMSTRING, $LISTGET, $LISTLENGTH, and $LISTTOSTRING. ObjectScript supports three additional list functions: $LISTVALID to determine if an expression is a list, $LISTSAME to compare two lists, and $LISTNEXT to sequentially retrieve elements from a list.
Stream Data Types
The Stream data types correspond to the InterSystems IRIS class property data types %Stream.GlobalCharacter (for CLOBs) and %Stream.GlobalBinary (for BLOBs).
A field with a Stream data type cannot be used as an argument to most SQL scalar, aggregate, or unary functions. Attempting to do so generates an SQLCODE -37 error code. The few functions that are exceptions are listed in the Storing and Using Stream Data (BLOBs and CLOBs) chapter of Using InterSystems SQL.
A field with a Stream data type cannot be used as an argument to most SQL predicate conditions. Attempting to do so generates an SQLCODE -313 error code. The predicates that accept a stream field are listed in the Storing and Using Stream Data (BLOBs and CLOBs) chapter of Using InterSystems SQL.
A sharded table cannot contain stream data type fields.
The use of Stream data types in indices, and when performing inserts and updates are also restricted. For further details on Stream restrictions, refer to the Storing and Using Stream Data (BLOBs and CLOBs) chapter of Using InterSystems SQL.
SERIAL Data Type
A field with a SERIAL (%Library.Counter) data type can take a user-specified positive integer value, or InterSystems IRIS can assign it a sequential positive integer value.
An INSERT operation specifies one of the following values for a SERIAL field:
Thus a SERIAL field contains a series incremental integer values. These values are not necessarily continuous or unique. For example, the following is a valid series of values for a SERIAL field: 1, 2, 3, 17, 18, 25, 25, 26, 27. Sequential integers are either InterSystems IRIS-generated or user-supplied; nonsequential integers are user-supplied. If you wish SERIAL field values to be unique, you must apply a UNIQUE constraint on the field.
An UPDATE operation has no effect on automatically-assigned SERIAL counter field values. However, an update performed using INSERT OR UPDATE causes a skip in integer sequence for subsequent insert operations for a SERIAL field.
An UPDATE operation can only change a serial field value if the field currently has no value (NULL), or its value is 0. Otherwise, an SQLCODE -105 error is generated.
InterSystems IRIS imposes no restriction on the number of SERIAL fields in a table.
ROWVERSION Data Type
The ROWVERSION data type defines a read-only field that contains a unique system-assigned positive integer, beginning with 1. InterSystems IRIS assigns sequential integers as part of each insert, update, or %Save operation. These values are not user-modifiable.
InterSystems IRIS maintains a single row version counter namespace-wide. All tables in a namespace that contain a ROWVERSION field share the same row version counter. Thus, the ROWVERSION field provides row-level version control, allowing you to determine the order in which changes were made to rows in one or more tables in a namespace.
You can only specify one field of ROWVERSION data type per table.
The ROWVERSION field should not be included in a unique key or primary key. The ROWVERSION field cannot be part of an IDKey index.
For details on using ROWVERSION, refer to RowVersion Field section of the “Defining Tables” chapter of Using InterSystems SQL.
ROWVERSION and SERIAL Counters
Both ROWVERSION and SERIAL (%Library.Counter) data type fields receive a sequential integer from an internal counter as part of an INSERT operation. But these two counters are significantly different and are used for different purposes:
DDL Data Types Exposed by InterSystems ODBC / JDBC
InterSystems ODBC exposes a subset of the DDL data types, and maps other data types to this subset of data types. These mappings are not reversible. For example, the statement CREATE TABLE mytable (f1 BINARY) creates an InterSystems IRIS class that is projected to ODBC as mytable (f1 VARBINARY). An InterSystems IRIS list data type is projected to ODBC as a VARCHAR string.
ODBC exposes the following data types: BIGINT, BIT, DATE, DOUBLE, GUID, INTEGER, LONGVARBINARY, LONGVARCHAR, NUMERIC, OREF, POSIXTIME, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY, VARCHAR. Note that, by default, InterSystems IRIS establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
When one of these ODBC/JDBC data type values is mapped to InterSystems SQL, the following operations occur: DOUBLE data is cast using $DOUBLE. NUMERIC data is cast using $DECIMAL.
The GUID data type corresponds to InterSystems SQL UNIQUEIDENTIFIER data type. Failing to specify a valid value to a GUID / UNIQUEIDENTIFIER field generates a #7212 General Error.
Query Metadata Returns Data Type
You can use Dynamic SQL to return metadata about a query, including the data type of a specified column in the query.
The following Dynamic SQL examples return the column name and the integer code for the ODBC data type for each of the columns in Sample.Person and Sample.Employee:
  SET myquery="SELECT * FROM Sample.Person"
  SET rset = ##class(%SQL.Statement).%New()
  SET tStatus = rset.%Prepare(myquery)
    SET x=rset.%Metadata.columns.Count()
  WHILE x>0 {
    SET column=rset.%Metadata.columns.GetAt(x)
  WRITE !,x," ",column.colName," ",column.ODBCType
  SET x=x-1 }
  WRITE !,"end of columns"
  SET myquery="SELECT * FROM Sample.Employee"
  SET rset = ##class(%SQL.Statement).%New()
  SET tStatus = rset.%Prepare(myquery)
    SET x=rset.%Metadata.columns.Count()
  WHILE x>0 {
    SET column=rset.%Metadata.columns.GetAt(x)
  WRITE !,x," ",column.colName," ",column.ODBCType
  SET x=x-1 }
  WRITE !,"end of columns"
List structured data, such as the FavoriteColors column in Sample.Person, returns a data type of 12 (VARCHAR) because ODBC represents an ObjectScript %List data type value as a string of comma-separated values.
Steams data, such as the Notes and Picture columns in Sample.Employee, return the data types -1 (LONGVARCHAR) or -4 (LONGVARBINARY).
A ROWVERSION field returns data type -5 because %Library.RowVersion is a subclass of %Library.BigInt.
For further details, refer to the Dynamic SQL chapter of Using InterSystems SQL and the %SQL.Statement class in the InterSystems Class Reference.
Integer Codes for Data Types
In query metadata and other contexts, the defined data type for a column may be returned as an integer code. There are two sets of integer codes used to represent data types.
xDBC data type codes (SQLType) are used by ODBC and JDBC. They are returned by %SQL.Statement.%Metadata.columns.GetAt() method, as shown in the example above. The JDBC codes are the same as the ODBC codes, except in the representation of time and date data types. These ODBC and JDBC values are listed below:
ODBC JDBC Data Type
-11 -11 GUID
-7 -7 BIT
-6 -6 TINYINT
-5 -5 BIGINT
-4 -4 LONGVARBINARY
-3 -3 VARBINARY
-2 -2 BINARY
-1 -1 LONGVARCHAR
0 0 Unknown type
1 1 CHAR
2 2 NUMERIC
3 3 DECIMAL
4 4 INTEGER
5 5 SMALLINT
6 6 FLOAT
7 7 REAL
8 8 DOUBLE
9 91 DATE
10 92 TIME
11 93 TIMESTAMP
12 12 VARCHAR
For further details, refer to the Dynamic SQL chapter of Using InterSystems SQL.
InterSystems IRIS also supports Unicode SQL types for ODBC applications working with multibyte character sets, such as in Chinese, Hebrew, Japanese, or Korean locales.
ODBC Data Type
-10 WLONGVARCHAR
-9 WVARCHAR
To activate this functionality, refer to Creating a DSN by Using the Control Panel in Using ODBC with InterSystems IRIS.
Creating User-Defined DDL Data Types
You can modify the set of data types either by overriding the data type mapping for a system data type parameter value, or by defining a new user data type. You can modify system data types to override the InterSystems default mappings. You can create user-defined data types to provide additional data type mappings that InterSystems does not supply.
To view and modify or add to the current user data type mappings, Go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, User-defined DDL Mappings. To add a user data type, select Create New User-defined DDL Mapping. In the displayed box, input a Name, for example VARCHAR(100) and a Datatype, for example MyString100(MAXLEN=100).
The result will be an entry in the list of user-defined DDL data types.
As shown in previous examples, there are several useful routines for entering user-defined DDL data types:
If you need to map a DDL data type to an InterSystems IRIS property with a collection type of Stream, specify %Stream.GlobalCharacter for Character Stream data and %Stream.GlobalBinary for Binary Stream data.
Pass-through if No DDL Mapping is Found
If DDL encounters a data type not in the DDL data type column of the UserDataTypes table, it next examines the SystemDataTypes table. If no mapping appears for the data type in either table, no conversion of the data type occurs, and the data type passes directly to the class definition as specified in DDL.
For example, the following field definitions could appears in a DDL statement:
     CREATE TABLE TestTable (
          Field1 %String,
          Field2 %String(MAXLEN=45)
          )
Given the above definitions, if DDL finds no mappings for %String or %String(MAXLEN=%1) or %String(MAXLEN=45) in UserDataTypes or SystemDataTypes, then the %String and %String(MAXLEN=45) types are passed directly to the appropriate class definition.
Converting Data Types
To convert data from one data type to another, use the CAST or CONVERT function.
CAST supports conversion to several character string and numeric data types, as well as to DATE, TIME, and the TIMESTAMP and POSIXTIME timestamp data types.
CONVERT has two syntactical forms. Both forms support conversion to and from DATE, TIME, and the TIMESTAMP and POSIXTIME timestamp data types, as well as conversion between other data types.
CAST and CONVERT Handling of VARCHAR
The VARCHAR data type (with no specified size) is mapped to a MAXLEN of 1 character, as shown in the above table. However, when you CAST or CONVERT a value to VARCHAR, the default size mapping is 30 characters. This default size of 30 characters is provided for compatibility with non-InterSystems IRIS software requirements.
See Also