Data Types
Description
The following topics are described here:
-
A table of the supported DDL data types and their class property mappings
-
Data type precedence used to select the most inclusive data type from data values having different data types
-
Date, Time, and TimeStamp data types
-
Usage in SqlCategory of standard and user-defined logical values
-
Configurable support for dates prior to December 31, 1840
-
-
Support for long strings, the List data type, and the Stream data type
-
Support for the ROWVERSION data type
-
Data types exposed by Caché ODBC / JDBC
-
Determining a column's data type using query metadata methods and data type integer codes
-
Creating user-defined data types
-
Handling of undefined data types
-
Data type conversion functions
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 or ALTER TABLE. When defining an SQL field, 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 Caché data type class listed in the right-hand column. In Caché when defining a field you can specify either a DDL data type or a data type class. DDL data type names are not case-sensitive. Data type class names are case-sensitive. %Library data type classes can be specified either by full name (for example, %Library.StringOpens in a new tab) or by short name (%StringOpens in a new tab).
The DDL data types and data type classes they map to often provide different parameters and parameter default values. Data type classes commonly provide more parameters to define allowed data values than the DDL data types. For further details, refer to Understanding DDL Data Type Mappings.
To view the current system data type mappings, go to the Management Portal, select System, Configuration, 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, Configuration, User-defined DDL Mappings.
Table of DDL Data Types
DDL Data Type | Corresponding Caché Data Type Class |
---|---|
BIGINT |
%Library.BigIntOpens in a new tab (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.BigIntOpens in a new tab The %1 is ignored. Equivalent to BIGINT. Provided for MySQL compatibility. |
BINARY | %Library.BinaryOpens in a new tab(MAXLEN=1) |
BINARY(%1) | %Library.BinaryOpens in a new tab(MAXLEN=%1) |
BINARY VARYING | %Library.BinaryOpens in a new tab(MAXLEN=1) |
BINARY VARYING(%1) | %Library.BinaryOpens in a new tab(MAXLEN=%1) |
BIT | %Library.BooleanOpens in a new tab |
CHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
CHAR(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
CHAR VARYING | %Library.StringOpens in a new tab(MAXLEN=1) |
CHAR VARYING(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
CHARACTER | %Library.StringOpens in a new tab(MAXLEN=1) |
CHARACTER VARYING | %Library.StringOpens in a new tab(MAXLEN=1) |
CHARACTER VARYING(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
CHARACTER(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
DATE | %Library.DateOpens in a new tab |
DATETIME | %Library.DateTimeOpens in a new tab |
DATETIME2 | %Library.DateTimeOpens in a new tab |
DEC | %Library.NumericOpens in a new tab MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0. |
DEC(%1) | %Library.NumericOpens in a new tab 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.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) |
DECIMAL | %Library.NumericOpens in a new tab MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0. |
DECIMAL(%1) | %Library.NumericOpens in a new tab 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.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) |
DOUBLE | %Library.DoubleOpens in a new tab 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.DoubleOpens in a new tab 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.GlobalBinaryOpens in a new tab |
INT | %Library.IntegerOpens in a new tab (MAXVAL=2147483647, MINVAL=-2147483648) |
INT(%1) |
%Library.IntegerOpens in a new tab (MAXVAL=2147483647, MINVAL=-2147483648). The %1 is ignored. Equivalent to INT. Provided for MySQL compatibility. |
INTEGER | %Library.IntegerOpens in a new tab (MAXVAL=2147483647, MINVAL=-2147483648) |
LONG | %Stream.GlobalCharacterOpens in a new tab |
LONG BINARY | %Stream.GlobalBinaryOpens in a new tab |
LONG RAW | %Stream.GlobalBinaryOpens in a new tab |
LONGTEXT |
%Stream.GlobalCharacterOpens in a new tab Equivalent to LONG. Provided for MySQL compatibility. |
LONG VARCHAR | %Stream.GlobalCharacterOpens in a new tab |
LONG VARCHAR(%1) | %Stream.GlobalCharacterOpens in a new tab The %1 is ignored. |
LONGVARBINARY | %Stream.GlobalBinaryOpens in a new tab |
LONGVARBINARY(%1) | %Stream.GlobalBinaryOpens in a new tab The %1 is ignored. |
LONGVARCHAR | %Stream.GlobalCharacterOpens in a new tab |
LONGVARCHAR(%1) | %Stream.GlobalCharacterOpens in a new tab The %1 is ignored. |
MEDIUMINT |
%Library.IntegerOpens in a new tab(MAXVAL=8388607,MINVAL=-8388608) Provided for MySQL compatibility. |
MEDIUMINT(%1) |
%Library.IntegerOpens in a new tab(MAXVAL=8388607,MINVAL=-8388608) The %1 is ignored. Provided for MySQL compatibility. |
MONEY | %Library.CurrencyOpens in a new tab(MAXVAL=922337203685477.5807, MINVAL=-922337203685477.5808, SCALE=4) |
NATIONAL CHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHAR(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NATIONAL CHAR VARYING | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHAR VARYING(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NATIONAL CHARACTER | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHARACTER(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NATIONAL CHARACTER VARYING | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL CHARACTER VARYING(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NATIONAL VARCHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
NATIONAL VARCHAR(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NCHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
NCHAR(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NTEXT | %Stream.GlobalCharacterOpens in a new tab |
NUMBER | %Library.NumericOpens in a new tab A 64–bit signed integer. (MAXVAL=9223372036854775807, MINVAL=-9223372036854775808, SCALE=0) |
NUMBER(%1) | %Library.NumericOpens in a new tab 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.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) |
NUMERIC | %Library.NumericOpens in a new tab MAXVAL=999999999999999, MINVAL=-999999999999999, SCALE=0. |
NUMERIC(%1) | %Library.NumericOpens in a new tab 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.NumericOpens in a new tab (MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2) |
NVARCHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
NVARCHAR(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NVARCHAR(%1,%2) | %Library.StringOpens in a new tab(MAXLEN=%1) |
NVARCHAR(MAX) |
%Stream.GlobalCharacterOpens in a new tab Equivalent to LONGVARCHAR. Provided for TSQL compatibility. |
RAW(%1) | %Library.BinaryOpens in a new tab(MAXLEN=%1) |
ROWVERSION |
%Library.RowVersionOpens in a new tab(MAXVAL=9223372036854775807, MINVAL=1) A system-assigned sequential integer. See ROWVERSION Data Type for details. |
SERIAL | %Library.CounterOpens in a new tab System-generated: (MAXVAL=2147483647, MINVAL=1). User-supplied: (MAXVAL=2147483647, MINVAL=-2147483648) |
SMALLDATETIME | %Library.DateTimeOpens in a new tab MAXVAL=’2079-06-06 23:59:59’; MINVAL=’1900-01-01 00:00:00’) |
SMALLINT | %Library.SmallIntOpens in a new tab (MAXVAL=32767, MINVAL=-32768) |
SMALLINT(%1) |
%Library.SmallIntOpens in a new tab The %1 is ignored. Equivalent to SMALLINT. Provided for MySQL compatibility. |
SMALLMONEY | %Library.CurrencyOpens in a new tab SCALE=4 |
SYSNAME | %Library.StringOpens in a new tab(MAXLEN=128) |
TEXT | %Stream.GlobalCharacterOpens in a new tab |
TIME | %Library.TimeOpens in a new tab |
TIMESTAMP | |
TINYINT | %Library.TinyIntOpens in a new tab (MAXVAL=127, MINVAL=-128) |
TINYINT(%1) |
%Library.TinyIntOpens in a new tab The %1 is ignored. Equivalent to TINYINT. Provided for MySQL compatibility. |
UNIQUEIDENTIFIER | %Library.UniqueIdentifierOpens in a new tab |
VARBINARY | %Library.BinaryOpens in a new tab(MAXLEN=1) |
VARBINARY(%1) | %Library.BinaryOpens in a new tab(MAXLEN=%1) |
VARCHAR | %Library.StringOpens in a new tab(MAXLEN=1) |
VARCHAR(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
VARCHAR(%1,%2) | %Library.StringOpens in a new tab(MAXLEN=%1) |
VARCHAR2(%1) | %Library.StringOpens in a new tab(MAXLEN=%1) |
VARCHAR(MAX) |
%Stream.GlobalCharacterOpens in a new tab Equivalent to LONGVARCHAR. Provided for TSQL compatibility only. |
Each of the DDL or Caché 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.
MAXLEN and Space Usage
Caché SQL should not be affected by an overly large MAXLEN value. When specifying a %Library.StringOpens in a new tab data type, the MAXLEN value you specify does not have to correspond closely to the actual size of the data. If the field value is "ABC", Caché only uses that much space on disk, in the global buffers, and in private process memory. Even if the field is declared with MAXLEN=1000, the private process memory does not allocate that much space for the field. Caché only allocates memory for the actual size of the field value, regardless of the declared length.
ODBC applications may be affected by an overly large MAXLEN value. ODBC applications try to make decisions about the size of a field needed based on metadata from the server, so the application may allocate more buffer space than is actually needed. For this reason, Caché supplies a system-wide default ODBC VARCHAR maximum length of 4096; this default is configurable. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab. The Caché ODBC driver takes the data from the TCP buffer and converts it into the applications buffer, so MAXLEN size does not affect our ODBC client.
JDBC applications should not be affected by an overly large MAXLEN value. Java and .Net do not have the application allocate buffers. The clients only allocated what is needed to hold the data as a native type.
Precision and Scale
Numeric data types such as NUMERIC(6,2) have two integer values (p,s) precision and scale. 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):
-
Precision: an integer between 0 and 19+s (inclusive). This value determines the maximum and minimum permitted value. This is, commonly, the total number of digits in the number; however, its exact value is determined by the %Library class data type mapping. The maximum integer value is 9223372036854775807. A precision larger than 19+s defaults to 19+s.
-
Scale: an integer that specifies the maximum number of decimal (fractional) digits permitted. Can be a positive integer, 0, or a negative integer. If s is larger than or equal to p, only a fractional value is permitted, the actual p value is ignored. The largest permitted scale is 18, which corresponds to .999999999999999999. A scale larger than 18 defaults to 18.
The following example shows the maximum values for different combinations of precision and scale:
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 Caché 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, Configuration, System-defined DDL Mappings.
Understanding DDL Data Type Mappings
When mapping data types from DDL to Caché, regular parameters and function parameters follow these rules:
-
Regular Parameters — These are identified in the DDL data type and the Caché data type in the format %#. For example:
VARCHAR(%1)
maps to:
%String(MAXLEN=%1)
Hence, a DDL data type of:
VARCHAR(10)
maps to:
%String(MAXLEN=10)
-
Function Parameters — These are used when a parameter in the DDL data type has to undergo some transformation before it can be put into the Caché data type. An example of this is the transformation of a DDL data type’s numeric precision and scale parameters into a Caché data type’s MAXVAL, MINVAL, and SCALE parameters. For example:
DECIMAL(%1,%2)
maps to:
%Numeric(MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>, MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>, SCALE=%2)
The DDL data type DECIMAL takes parameters Precision (%1) and Scale (%2), but the Caché data type %Numeric does not have a precision parameter. Therefore, to convert DECIMAL to %Numeric, the Precision parameter must be converted to appropriate %Numeric parameters, in this case by applying the Caché functions format, maxval, and minval to the parameters supplied by DECIMAL. The special <|'xxx'|> syntax (as shown above) signals the DDL processor to do parameter replacement and then call the function with the values supplied. The <|'xxx'|> expression is then replaced with the value returned from the function call.
Considering this example with actual values, there might be a DECIMAL data type with a precision of 4 digits and a scale of 2:
DECIMAL(4,2)
This maps to:
%Numeric(MAXVAL=<|'$$maxval^%apiSQL(4,2)'|>, MINVAL=<|'$$minval^%apiSQL(4,2)'|>, SCALE=2)
which evaluates to:
%Numeric(MAXVAL=99.99,MINVAL=-99.99,SCALE=2)
For information about numeric formatting, refer to the $FNUMBER function in the Caché ObjectScript Reference. For more information about the maxval and minval functions, see Creating User-Defined DDL Data Types.
-
Additional Parameters — A data type class may define additional data definition parameters that cannot be defined using a DDL data type. These include data validation operations such as an enumerated list of permitted data values, pattern matching of permitted data values, and automatic truncation of data values that exceed the MAXLEN maximum length.
Data Type Precedence
When an operation can return several different values, and these values may have different data types, Caché 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.DataTypeOpens in a new tab 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()Opens in a new tab method for the %TimeStamp data type:
SET indate=63445
SET tsdate=##class(%Library.TimeStamp).Normalize(indate)
WRITE "%TimeStamp date: ",tsdate
SET indate="2014-2-2"
SET tsdate=##class(%Library.TimeStamp).Normalize(indate)
WRITE "%TimeStamp date: ",tsdate
The following examples show the IsValid()Opens in a new tab method for the %TimeStamp data type:
SET datestr="July 4, 2014"
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="2004-02-29 00:00:00"
SET noleap="2005-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, and TimeStamp Data Types
You can define date, time, and timestamp data types, and interconvert dates and timestamps through standard Caché 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.DateOpens in a new tab, %Library.TimeOpens in a new tab, %Library.TimeStampOpens in a new tab, %MV.DateOpens in a new tab, %Library.FilemanDateOpens in a new tab, and %Library.FilemanTimeStampOpens in a new tab are treated as follows with regard to SqlCategory:
-
%Library.DateOpens in a new tab 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. %Library.DateOpens in a new tab stores a date value as an unsigned or negative integer in the range -672045 to 2980013. Date values can be input as follows:
-
Logical mode accepts +HOROLOG integer values, such as 65619 (August 28, 2020).
-
Display mode uses the DisplayToLogical() conversion method. It accepts a date in the display format for the current locale, for example ‘8/28/2020’.
-
ODBC mode uses the ODBCToLogical() conversion method. It accepts a date in ODBC standard format, for example ‘2020–08–28’. It also accepts a logical date value (a +HOROLOG integer value).
-
-
%Library.TimeOpens in a new tab 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. %Library.TimeOpens in a new tab stores a time value as an unsigned integer in the range 0 through 86399 (a count of seconds since midnight). Time values can be input as follows:
-
Logical mode accepts $PIECE($HOROLOG,”,”,2) integer values, such as 84444 (23:27:24).
-
Display mode uses the DisplayToLogical() conversion method. It accepts a time in the display format for the current locale, for example ‘23:27:24’.
-
ODBC mode uses the ODBCToLogical() conversion method. It accepts a time in ODBC standard format, for example ‘23:27:24’. It also accepts a logical time value (an integer in the range 0 through 86399).
$HOROLOG is accurate to whole seconds. TIME supports fractional seconds, so this data type can also be used for HH:MI:SS.FF to a data-specified number of fractional digits of precision, up to a maximum of 9. TIME (%Time) retains whatever fractional digits of precision are specified in the data value.
A field using the TIME datatype reports metadata precision of 18 and metadata scale of 0. The actual precision and scale depend upon the number of digits specified as fractional seconds in the data value. For details on returning data type, precision and scale metadata, refer to Select-item Metadata.
-
-
%Library.TimeStampOpens in a new tab 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.TimeStampOpens in a new tab derives its maximum precision from the system platform’s precision.
-
%Library.DateTimeOpens in a new tab is a subclass of %Library.TimeStampOpens in a new tab. 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.
-
%MV.DateOpens in a new tab classes, or any user-defined data type class that has a logical date value of $HOROLOG-46385, should use MVDATE as the SqlCategory.
-
%Library.FilemanDateOpens in a new tab classes, or any user-defined data type class that has a logical date value of CYYMMDD, should use FMDATE as the SqlCategory.
-
%Library.FilemanTimeStampOpens in a new tab classes, or any user-defined data type class that has a logical date value of CYYMMDD.HHMMSS, should use FMTIMESTAMP as the SqlCategory.
-
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.DateOpens in a new tab logical value, and a DateToLogical() method to convert a %Library.DateOpens in a new tab logical value to the user-defined logical date value.
-
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.TimeOpens in a new tab logical value, and a TimeToLogical() method to convert a %Library.TimeOpens in a new tab logical value to the user-defined logical time value.
-
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.TimeStampOpens in a new tab logical value, and a TimeStampToLogical() method to convert a %Library.TimeStampOpens in a new tab logical value to the user-defined logical timestamp value.
When comparing FMTIMESTAMP category values with DATE category values, Caché no longer strips 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.
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).
Caché 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.
Long Strings
Caché provides support for long strings. Long strings are strings greater than 32,767 characters (64K bytes) up to a maximum length of 3,641,144 characters. Commonly, such strings should be assigned one of the %Stream.GlobalCharacterOpens in a new tab data types. (Prior to version 2011.1, these were assigned the CStream%String (or %Library.GlobalCharacterStreamOpens in a new tab) data type; these older data types continue to be fully supported.)
Long string support is enabled by default. You can disable or enable long string support for a Caché instance. If disabled, you can enable long strings using either the Management Portal or the ObjectScript EnableLongStringsOpens in a new tab property of the Config.MiscellaneousOpens in a new tab class. In the Management Portal select System, Configuration, Memory and Startup. To enable support for long strings system-wide, select the Enable Long Strings check box. Then click the Save button. After long strings are enabled, any future invoked process on that system will support long strings. For further details, refer to Long Strings in the “Data Types and Values” chapter of Using Caché ObjectScript.
With long strings enabled, you can assign a %Library.StringOpens in a new tab data types a MAXLEN of greater than 16,374 Unicode characters (or 32K 8-bit characters). How such long strings are handled depends on your xDBC protocol:
-
Protocol 46: When ODBC or JDBC accesses %Library.StringOpens in a new tab data with a MAXLEN greater than 16,374 characters, only the first 16,374 characters are returned. If you need to support data in a single field that is longer than 16,374 characters, you should use a stream data type.
-
Protocol 47: No ODBC or JDBC string length limit.
The protocol that is used is the highest protocol supported by both the Caché 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 Caché ODBC log.
Note that, by default, Caché establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
List Structures
Caché 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 Caché SQL. In its internal representation it corresponds to data type VARBINARY with a default MAXLEN of 32749. Caché supports the list structure data type %ListOfBinary (data type class %Library.ListOfBinary) which corresponds to data type VARBINARY with a default MAXLEN of 4096.
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. To determine if a field in a Dynamic SQL query is of data type %List or %ListOfBinary you can use the select-item columns metadata; the CType (client data type) integer code for these data types is 6.
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, Caché establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable.
For further details on data type class %Library.ListOpens in a new tab, 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.
Caché 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 Caché class property data types %Stream.GlobalCharacterOpens in a new tab (for CLOBs) and %Stream.GlobalBinaryOpens in a new tab (for BLOBs). These data type classes can define a stream field with a specified LOCATION parameter, or omit this parameter and default to a system-defined storage location.
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 Caché 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 Caché SQL.
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 Caché SQL.
SERIAL Data Type
A field with a SERIAL data type can take a user-specified positive integer value, or Caché can assign it a sequential positive integer value.
An INSERT operation specifies one of the following values for a SERIAL field:
-
No value, 0 (zero), or a nonnumeric value: Caché ignores the specified value, and instead increments this field's current serial counter value by 1, and inserts the resulting integer into the field.
-
A positive integer value: Caché inserts the user-specified value into the field, and changes the serial counter value for this field to this integer value.
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 Caché-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 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.
Caché 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. Caché assigns sequential integers as part of each insert, update, or %Save operation. These values are not user-modifiable.
Caché 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 Caché SQL.
ROWVERSION and %Counter
Both ROWVERSION and %Counter (%Library.CounterOpens in a new tab) assign a sequential integer to a field as part of an INSERT operation. But these two counters are significantly different and are used for different purposes:
-
The ROWVERSION counter is at the namespace level. The %Counter counter is at the table level. These two counters are completely independent of each other and independent of the RowID counter.
-
The ROWVERSION counter is incremented by insert, update, or %Save operations. The %Counter counter is only incremented by insert operations.
-
A ROWVERSION field value cannot be user-specified; the value is always supplied from the ROWVERSION counter. A %Counter field value is supplied from the table’s %Counter counter during an insert if you do not specify a value for this field. If an insert supplies a %Counter integer value, that value is inserted rather than the current counter value:
-
If an insert supplies a %Counter field value greater than the current counter value, Caché inserts that value and resets the %Counter counter to the next sequential integer.
-
If an insert supplies a %Counter field value lesser than the current counter value, Caché does not reset the %Counter counter.
-
An insert can supply a %Counter field value as a negative integer or a fractional number. Caché truncates a fractional number to its integer component. If the supplied %Counter field value is 0 (or truncates to 0), Caché inserts the current counter value.
You cannot update an existing %Counter field value.
-
-
A ROWVERSION value is always unique. Because you can insert a user-specified %Counter value, you must specify a UNIQUE field constraint to guarantee unique %Counter values.
-
The ROWVERSION counter cannot be reset. A TRUNCATE TABLE resets the %Counter counter; performing a DELETE on all rows does not reset the %Counter counter.
-
Only one ROWVERSION field is allowed per table. You can specify multiple %Counter fields in a table.
DDL Data Types Exposed by Caché ODBC / JDBC
Caché 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 a Caché class that is projected to ODBC as mytable (f1 VARBINARY). A Caché 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, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY, VARCHAR. Note that, by default, Caché 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 Caché SQL, the following operations occur: DOUBLE data is cast using $DOUBLE. NUMERIC data is cast using $DECIMAL.
The GUID data type corresponds to Caché 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 tStatement=##class(%SQL.Statement).%New()
SET tStatus=tStatement.%Prepare(myquery)
SET x=tStatement.%Metadata.columnCount
WHILE x>0 {
SET column=tStatement.%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 tStatement=##class(%SQL.Statement).%New()
SET tStatus=tStatement.%Prepare(myquery)
SET x=tStatement.%Metadata.columnCount
WHILE x>0 {
SET column=tStatement.%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 a Caché %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 Caché SQL and the %SQL.StatementOpens in a new tab 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:
-
Client data type (CType) codes are returned by the %Library.ResultSet.GetColumnType()Opens in a new tab method. The documentation for that method lists the integer values. These integer codes are also listed in the %SQL.StatementColumnOpens in a new tab clientTypeOpens in a new tab property. For further details, refer to Select-item Metadata in the “Using Dynamic SQL” chapter of Using InterSystems SQL.
-
xDBC data type codes (SQLType) are used by ODBC and JDBC. ODBC data type codes are returned by %SQL.Statement.%Metadata.columns.GetAt() method, as shown in the example above. SQL Shell metadata also returns ODBC data type codes. 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 Caché SQL.
Caché 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 Caché with ODBC.
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, Configuration, 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.
You can create a user-defined data type as a data type class. For example, you might wish to create a string data type that takes up to 10 characters and then truncates the rest of the input data. You would create this data type Sample.TruncStr, as follow:
Class Sample.TruncStr Extends %Library.String
{
Parameter MAXLEN=10;
Parameter TRUNCATE=1;
}
To use this data type in a table definition, you simply specify the data type class name:
CREATE TABLE Sample.ShortNames (Name Sample.TruncStr)
As shown in previous examples, there are several useful routines for entering user-defined DDL data types:
-
maxval^%apiSQL() — Given a precision and scale, returns the maximum valid value (MAXVAL) for each of the Caché numeric data types. The syntax is:
maxval^%apiSQL(precision,scale)
where both precision and scale are required.
-
minval^%apiSQL() — Given a precision and scale, returns the minimum valid value (MINVAL) for each of the Caché numeric data types. The syntax is:
minval^%apiSQL(precision,scale)
where both precision and scale are required.
If you need to map a DDL data type to a Caché property with a collection type of Stream, specify %Stream.GlobalCharacter for Character Stream data and %Stream.GlobalBinary for Binary Stream data. (Prior to version 2011.1, character stream data was assigned the CStream%String (or %Library.GlobalCharacterStreamOpens in a new tab) data type, and binary stream data was assigned the BStream%String (or %Library.GlobalBinaryStreamOpens in a new tab) data type. These older data types continue to be fully supported.)
Pass-through if No DDL Mapping is Found
If DDL encounters a data type not in the DDL data type column of the SystemDataTypes table, it next examines the UserDataTypes 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 SystemDataTypes or UserDataTypes, 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 TIMESTAMP data types.
CONVERT has two syntactical forms. Both forms support conversion to and from DATE, TIME, and 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-Caché software requirements.