SQL Data Types 

< > InstantSQL Components InstantSQL Data Conversion Table of Contents
InstantSQL directly supports the SQL data types commonly found in databases. These data types are denoted by constantnames defined in the copy file lisqldef.cpy. For any particular data source, it is common that the data source supports only a subset of these data types. The data type numbers supported by a particular data source can be obtained using the SQL QUERY TYPES statement, which also returns additional information about each supported data type.
In addition to the common data types that are directly supported, InstantSQL supports any driverspecific or other ODBC data type if the driver can convert the data type to character data. These data types are effectively treated by InstantSQL as if they were converted to LONGVARCHAR.
InstantSQL uses the ODBC extension level 1 function SQLGetTypeInfo to determine which data types are supported by a data source and whether the data type is unsigned. InstantSQL then uses this information to validate data types provided by the user. If the driver does not support the SQLGetTypeInfo function, InstantSQL assumes any data type provided by the user is valid and, if it is a numeric type, that it is signed. ODBC errors can occur if this assumption is incorrect.
The following table lists the SQL data types directly supported by InstantSQL.
SQL Type 
ConstantName 
Description 
BIGINT 
sqlBigInt 
Exact
numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0
(signed: –(2**63) <= n <= +((2**63) – 1), unsigned: 0 <= n <= ((2**64) – 1). 
BINARY(n) 
sqlBinary 
Binary
data of fixed length n. 
BIT 
sqlBit 
An
unsigned exact numeric type that may have the value 0 (commonly equivalent to
false) or 1 (commonly equivalent to true). 
CHAR(n) 
sqlChar 
Character
string with a fixed length n. 
DATE 
sqlDate 
Year,
month, and day fields, conforming to the rules of the Gregorian calendar. 
DECIMAL(p,
s) 
sqlDecimal 
Signed,
exact numeric value with a precision of at least p and scale s. (The maximum precision is driverdefined.) (1
<= p <= 15; s <= p) 
DOUBLE
PRECISION 
sqlDouble 
Signed,
approximate numeric value with a binary precision 53 (zero or absolute value
10**–308 to 10**308). 
FLOAT(p) 
sqlFloat 
Signed,
approximate, numeric value with a binary precision of at least p. (The
maximum precision is driverdefined.) 
INTEGER 
sqlInteger 
Exact
numeric value with precision 10 and scale 0 (signed: –(2**31) <= n <= ((2**31) – 1),
unsigned: 0 <= n <= ((2**32) –
1). 
LONGVARBINARY 
sqlLongVarBinary 
Variable
length binary data. Maximum length is
data source – dependent. 
LONGVARCHAR 
sqlLongVarChar 
Variable
length character data. Maximum length
is data source – dependent. 
NUMERIC(p,
s) 
sqlNumeric 
Signed,
exact numeric value with a precision p and scale s. (1
<= p <= 15; s <= p) 
REAL 
sqlReal 
Signed,
approximate numeric value with a binary precision 24 (zero or absolute value
10**–38 to 10**38). 
SMALLINT 
sqlSmallInt 
Exact
numeric value with precision 5 and scale 0 (signed: –32,768 <= n <= 32,767, unsigned: 0 <= n <= 65,535). 
TIME(p) 
sqlTime 
Hour,
minute, and second fields, with valid values for hours of 00 to 23, valid values
for minutes of 00 to 59, and valid values for seconds of 00 to 61. Precision p indicates the seconds
precision. 
TIMESTAMP(p) 
sqlTimestamp 
Year,
month, day, hour, minute, and second fields, with valid values as defined for
the DATE and TIME data types. 
TINYINT 
sqlTinyInt 
Exact
numeric value with precision 3 and scale 0 (signed: –128 <= n <= 127,
unsigned: 0 <= n <= 255). 
VARBINARY(n) 
sqlVarBinary 
Variablelength
binary data with a maximum length n. 
VARCHAR(n) 
sqlVarChar 
Variablelength
character string with a maximum string length n. 
The SQL types listed are only typical names for the data type. The names that must be used in the SQL CREATE TABLE and ALTER TABLE statements are data source  dependent. The InstantSQL statement SQL QUERY TYPES can be used to obtain the names used by a particular data source, as well as the types supported by that data source.
© Copyright 20002020 Micro Focus or one of its affiliates.