Skip to main content

This is documentation for Caché and Ensemble. See the InterSystems IRIS version of this content.Opens in a new tab

Please read this important announcement about the future of Caché and Ensemble maintenanceOpens in a new tab. Contact your account team to migrate to InterSystems IRIS.

System-Defined SQL Datatypes

Map SQL datatypes to their Caché equivalents.

Synopsis

[SqlSysDatatypes]    x=a

Description

The [SqlSysDatatypes] section contains system-defined datatype descriptions. Each description maps an SQL datatype to its Caché equivalent in the format x=a, as follows:

  • Each keyword x is the name of the SQL datatype plus any allowed arguments.

  • The value a is the Caché equivalent, including any constraints on the arguments.

Examples

Within a Caché parameter file, a line break indicates the end of a parameter definition. The following example of a [SqlSysDatatypes] section contains line breaks that would not be valid in a Caché parameter file. You should interpret every character that you see between an equals sign (=) and the next parameter name as being on the same line in the file. 

[SqlSysDatatypes]
BIGINT=%Library.BigInt
BIGINT(%1)=%Library.BigInt
BINARY=%Library.Binary(MAXLEN=1)
BINARY VARYING=%Library.Binary(MAXLEN=1)
BINARY VARYING(%1)=%Library.Binary(MAXLEN=%1)
BINARY(%1)=%Library.Binary(MAXLEN=%1)
BIT=%Library.Boolean
CHAR=%Library.String(MAXLEN=1)
CHAR VARYING=%Library.String(MAXLEN=1)
CHAR VARYING(%1)=%Library.String(MAXLEN=%1)
CHAR(%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.TimeStamp
DEC=%Library.Numeric(MAXVAL=999999999999999,MINVAL=-999999999999999,SCALE=0)
DEC(%1)=%Library.Numeric(MAXVAL=<|'$$maxval^%apiSQL(%1,0)'|>,MINVAL=<|'$$minval^%apiSQL(%1,0)'|>,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(MAXVAL=<|'$$maxval^%apiSQL(%1,0)'|>,MINVAL=<|'$$minval^%apiSQL(%1,0)'|>,SCALE=0)
DECIMAL(%1,%2)=%Library.Numeric(MAXVAL=<|'$$maxval^%apiSQL(%1,%2)'|>,MINVAL=<|'$$minval^%apiSQL(%1,%2)'|>,SCALE=%2)
DOUBLE=%Library.Double
DOUBLE PRECISION=%Library.Double
FLOAT=%Library.Float
FLOAT(%1)=%Library.Float(MAXVAL=<|'$$maxval^%apiSQL(%1)'|>,MINVAL=<|'$$minval^%apiSQL(%1)'|>)
IMAGE=%Stream.GlobalBinary
INT=%Library.Integer(MAXVAL=2147483647,MINVAL=-2147483648)
INT(%1)=%Library.Integer(MAXVAL=2147483647,MINVAL=-2147483648)
INTEGER=%Library.Integer(MAXVAL=2147483647,MINVAL=-2147483648)
LONG=%Stream.GlobalCharacter
LONG BINARY=%Stream.GlobalBinary
LONG RAW=%Stream.GlobalBinary
LONG VARCHAR=%Stream.GlobalCharacter
LONG VARCHAR(%1)=%Stream.GlobalCharacter
LONGTEXT=%Stream.GlobalCharacter
LONGVARBINARY=%Stream.GlobalBinary
LONGVARBINARY(%1)=%Stream.GlobalBinary
LONGVARCHAR=%Stream.GlobalCharacter
LONGVARCHAR(%1)=%Stream.GlobalCharacter
MEDIUMINT=%Library.Integer(MAXVAL=8388607,MINVAL=-8388608)
MEDIUMINT(%1)=%Library.Integer(MAXVAL=8388607,MINVAL=-8388608)
MONEY=%Library.Currency
NATIONAL CHAR=%Library.String(MAXLEN=1)
NATIONAL CHAR VARYING=%Library.String(MAXLEN=1)
NATIONAL CHAR VARYING(%1)=%Library.String(MAXLEN=%1)
NATIONAL CHAR(%1)=%Library.String(MAXLEN=%1)
NATIONAL CHARACTER=%Library.String(MAXLEN=1)
NATIONAL CHARACTER VARYING=%Library.String(MAXLEN=1)
NATIONAL CHARACTER VARYING(%1)=%Library.String(MAXLEN=%1)
NATIONAL CHARACTER(%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(SCALE=0)
NUMBER(%1)=%Library.Numeric(MAXVAL=<|'$$maxval^%apiSQL(%1)'|>,MINVAL=<|'$$minval^%apiSQL(%1)'|>,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(MAXVAL=<|'$$maxval^%apiSQL(%1,0)'|>,MINVAL=<|'$$minval^%apiSQL(%1,0)'|>,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)=%Stream.GlobalCharacter
RAW(%1)=%Library.Binary(MAXLEN=%1)
REAL=%Library.Float
SERIAL=%Library.Counter
SMALLDATETIME=%Library.TimeStamp
SMALLINT=%Library.SmallInt
SMALLINT(%1)=%Library.SmallInt
SMALLMONEY=%Library.Currency
SYSNAME=%Library.String(MAXLEN=128)
TEXT=%Stream.GlobalCharacter
TIME=%Library.Time
TIMESTAMP=%Library.TimeStamp
TINYINT=%Library.TinyInt
TINYINT(%1)=%Library.TinyInt
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)
VARCHAR(MAX)=%Stream.GlobalCharacter
VARCHAR2(%1)=%Library.String(MAXLEN=%1)
Range of Values

As described above.

Management Portal

On the page System Administration > Configuration > SQL and Object Settings > System-defined DDL Mappings, select Edit to modify a datatype definition.


FeedbackOpens in a new tab