Caché SQL Reference Data Types [Home]  [Next]

• 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

• 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.
• Scope: 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 scope is 18, which corresponds to .999999999999999999. A scope larger than 18 defaults to 18.

  FOR i=0:1:6 {
      WRITE "Max for (",i,",2)=",$$maxval^%apiSQL(i,2),!}

• 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 the next topic.

LONGVARBINARY
LONGVARCHAR
VARBINARY
VARCHAR
GUID
TIMESTAMP
DOUBLE
NUMERIC
BIGINT
INTEGER
DATE
TIME
SMALLINT
TINYINT
BIT

  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

  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",!}

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.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.
4. %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.
5. %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.
6. %Library.FilemanDate classes, or any user-defined data type class that has a logical date value of CYYMMDD, should use FMDATE as the SqlCategory.
7. %Library.FilemanTimeStamp classes, or any user-defined data type class that has a logical date value of CYYMMDD.HHMMSS, should use FMTIMESTAMP as the SqlCategory.
8. 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.
9. 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.
10. 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.

  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}

• Protocol 46: When ODBC or JDBC accesses %Library.String 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.

• 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.

• 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.

  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"

• Client data type codes are returned by the %Library.ResultSet.GetColumnType() method. These values are listed in the %Library.ResultSet class in the InterSystems Class Reference.
• 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

• 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.

     CREATE TABLE TestTable (
          Field1 %String,
          Field2 %String(MAXLEN=45)
          )

• CAST, CONVERT
• TO_CHAR, TO_DATE, TO_NUMBER