Jump to main content
Product Documentation
Customer Support
HCL OneDB 2.0.1
What's new in
HCL OneDB™
2.0.1
Getting Started
Installing
Administering
Migrating and upgrading
Data warehousing
Security
Client APIs and tools
SQL programming
JSON compatibility
Extending
HCL OneDB™
Designing databases
Embedding
HCL OneDB™
Release information
Troubleshooting
HCL OneDB™
OneDB PDF guides
Search
Home
SQL programming
You can use the
HCL OneDB™
implementation of the SQL language to develop applications for
HCL OneDB™
database servers.
Guide to SQL: Reference
The
HCL OneDB™ Guide to SQL: Reference
contains the reference information for the system catalog tables, data types, and environment variables of the
HCL OneDB™
dialect of the SQL language, as implemented in
HCL OneDB™
.
These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with
HCL OneDB™
.
Data types
Every column in a table in a database is assigned a data type. The data type precisely defines the kinds of values that you can store in that column.
Description of Data Types
SQL programming
You can use the
HCL OneDB™
implementation of the SQL language to develop applications for
HCL OneDB™
database servers.
Guide to SQL: Syntax
The
HCL OneDB™ Guide to SQL: Syntax
describes the syntax of the statements, data types, expressions, operators, and built-in functions of the
HCL OneDB™
dialect of the SQL language.
Guide to SQL: Reference
The
HCL OneDB™ Guide to SQL: Reference
contains the reference information for the system catalog tables, data types, and environment variables of the
HCL OneDB™
dialect of the SQL language, as implemented in
HCL OneDB™
.
These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with
HCL OneDB™
.
System catalog tables
The
system catalog
consists of tables and views that describe the structure of the database. Sometimes called the
data dictionary
, these table objects contain everything that the database knows about itself. Each system catalog table contains information about specific elements in the database. Each database has its own system catalog.
Data types
Every column in a table in a database is assigned a data type. The data type precisely defines the kinds of values that you can store in that column.
Summary of data types
HCL OneDB™
supports the most common set of built-in data types. Additionally, an extended set of data types are supported on the database server.
Description of Data Types
BIGINT data type
The BIGINT data type stores integers from -(2
63
-1) to 2
63
-1, which is –9,223,372,036,854,775,807 to 9,223,372,036,854,775,807, in eight bytes.
BIGSERIAL data type
The BIGSERIAL data type stores a sequential integer, of the BIGINT data type, that is assigned automatically by the database server when a new row is inserted. The behavior of the BIGSERIAL data type is similar to the SERIAL data type, but with a larger range.
BLOB data type
The BLOB data type stores any kind of binary data in random-access chunks, called sbspaces. Binary data typically consists of saved spreadsheets, program-load modules, digitized voice patterns, and so on. The database server performs no interpretation of the contents of a BLOB column.
BOOLEAN data type
The BOOLEAN data type stores
TRUE
or
FALSE
data values as a single byte.
BYTE data type
The BYTE data type stores any kind of binary data in an undifferentiated byte stream. Binary data typically consists of digitized information, such as spreadsheets, program load modules, digitized voice patterns, and so on.
CHAR(n) data type
The CHAR data type stores any string of letters, numbers, and symbols. It can store single-byte and multibyte characters, based on the database locale.
CHARACTER(n) data type
The CHARACTER data type is a synonym for CHAR.
CHARACTER VARYING(m,r) data type
The CHARACTER VARYING data type stores a string of letters, digits, and symbols of varying length, where
m
is the maximum size of the column (in bytes) and
r
is the minimum number of bytes reserved for that column.
CLOB data type
The CLOB data type stores any kind of text data in random-access chunks, called sbspaces. Text data can include text-formatting information, if this information is also textual, such as PostScript™, Hypertext Markup Language (HTML), Standard Graphic Markup Language (SGML), or Extensible Markup Language (XML) data.
DATE data type
The DATE data type stores the calendar date. DATE data types require four bytes. A calendar date is stored internally as an integer value equal to the number of days since December 31, 1899.
DATETIME data type
The DATETIME data type stores an instant in time expressed as a calendar date and time of day.
DEC data type
The DEC data type is a synonym for DECIMAL.
DECIMAL
The DECIMAL data type can take two forms: DECIMAL(
p
) floating point and DECIMAL(
p,s
) fixed point.
DISTINCT data types
A DISTINCT type is a data type that is derived from a source type (called the base type).
DOUBLE PRECISION data types
The DOUBLE PRECISION keywords are a synonym for the FLOAT keyword.
FLOAT(n)
IDSSECURITYLABEL data type
The IDSSECURITYLABEL type stores a security label in a table that is protected by a label-based access control (LBAC) security policy.
INT data type
The INT data type is a synonym for INTEGER.
INT8
The INT8 data type stores whole numbers that can range in value from –9,223,372,036,854,775,807 to 9,223,372,036,854,775,807 [or -(2
63
-1) to 2
63
-1], for 18 or 19 digits of precision.
INTEGER data type
The INTEGER data type stores whole numbers that range from -2,147,483,647 to 2,147,483,647 for 9 or 10 digits of precision.
INTERVAL data type
The INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes:
year-month intervals
and
day-time intervals
.
LIST(e) data type
The LIST data type is a collection type that can store ordered non-NULL elements of the same SQL data type.
LVARCHAR(m) data type
Use the LVARCHAR data type to create a column for storing variable-length character strings whose upper limit (
m
) can be up to 32,739 bytes.
MONEY(p,s) data type
The MONEY data type stores currency amounts.
MULTISET(e) data type
The MULTISET data type is a collection type that stores a non-ordered set that can include duplicate element values.
Named ROW
NCHAR(n) data type
The NCHAR data type stores fixed-length character data. The data can be a string of single-byte or multibyte letters, digits, and other symbols that are supported by the code set of the database locale.
NUMERIC(p,s) data type
The NUMERIC data type is a synonym for fixed-point DECIMAL.
NVARCHAR(m,r) data type
The NVARCHAR data type stores strings of varying lengths. The string can include digits, symbols, and both single-byte and (in some locales) multibyte characters.
OPAQUE data types
An OPAQUE type is a data type for which you must provide information to the database server.
REAL data type
The REAL data type is a synonym for SMALLFLOAT.
ROW data type, Named
A named ROW data type must be declared with a name. This SQL identifier must be unique among data type names within the same database.
ROW data type, Unnamed
An unnamed ROW type contains fields but has no user-declared name. An unnamed ROW type is defined by its structure.
SERIAL(n) data type
The SERIAL data type stores a sequential integer, of the INT data type, that is automatically assigned by the database server when a new row is inserted.
SERIAL8(n) data type
The SERIAL8 data type stores a sequential integer, of the INT8 data type, that is assigned automatically by the database server when a new row is inserted.
SET(e) data type
The SET data type is an unordered collection type that stores unique elements
SMALLFLOAT
The SMALLFLOAT data type stores single-precision floating-point numbers with approximately nine significant digits.
SMALLINT data type
The SMALLINT data type stores small whole numbers that range from –32,767 to 32,767. The maximum negative number, –32,768, is a reserved value and cannot be used.
TEXT data type
The TEXT data type stores any kind of text data. It can contain both single-byte and multibyte characters that the locale supports. The term
simple large object
refers to an instance of a TEXT or BYTE data type.
Unnamed ROW
VARCHAR(m,r) data type
The VARCHAR data type stores character strings of varying length that contain single-byte and (if the locale supports them) multibyte characters, where
m
is the maximum size (in bytes) of the column and
r
is the minimum number of bytes reserved for that column.
Built-In Data Types
Extended Data Types
Data Type Casting and Conversion
Operator Precedence
Environment variables
Various
environment variables
affect the functionality of your
HCL OneDB™
products. You can set environment variables that identify your terminal, specify the location of your software and define other parameters.
Appendixes
Guide to SQL: Tutorial
The
HCL OneDB™
Guide to SQL: Tutorial
shows how to use basic and advanced structured query language (SQL) to access and manipulate the data in your databases.
It discusses the data manipulation language (DML) statements as well as triggers and stored procedure language (SPL) routines, which DML statements often use.
Description of Data Types
This section describes the data types that
HCL OneDB™
supports.
BIGINT data type
The BIGINT data type stores integers from -(2
63
-1) to 2
63
-1, which is –9,223,372,036,854,775,807 to 9,223,372,036,854,775,807, in eight bytes.
BIGSERIAL data type
The BIGSERIAL data type stores a sequential integer, of the BIGINT data type, that is assigned automatically by the database server when a new row is inserted. The behavior of the BIGSERIAL data type is similar to the SERIAL data type, but with a larger range.
BLOB data type
The BLOB data type stores any kind of binary data in random-access chunks, called sbspaces. Binary data typically consists of saved spreadsheets, program-load modules, digitized voice patterns, and so on. The database server performs no interpretation of the contents of a BLOB column.
BOOLEAN data type
The BOOLEAN data type stores
TRUE
or
FALSE
data values as a single byte.
BYTE data type
The BYTE data type stores any kind of binary data in an undifferentiated byte stream. Binary data typically consists of digitized information, such as spreadsheets, program load modules, digitized voice patterns, and so on.
CHAR(n) data type
The CHAR data type stores any string of letters, numbers, and symbols. It can store single-byte and multibyte characters, based on the database locale.
CHARACTER(n) data type
The CHARACTER data type is a synonym for CHAR.
CHARACTER VARYING(m,r) data type
The CHARACTER VARYING data type stores a string of letters, digits, and symbols of varying length, where
m
is the maximum size of the column (in bytes) and
r
is the minimum number of bytes reserved for that column.
CLOB data type
The CLOB data type stores any kind of text data in random-access chunks, called sbspaces. Text data can include text-formatting information, if this information is also textual, such as PostScript™, Hypertext Markup Language (HTML), Standard Graphic Markup Language (SGML), or Extensible Markup Language (XML) data.
DATE data type
The DATE data type stores the calendar date. DATE data types require four bytes. A calendar date is stored internally as an integer value equal to the number of days since December 31, 1899.
DATETIME data type
The DATETIME data type stores an instant in time expressed as a calendar date and time of day.
DEC data type
The DEC data type is a synonym for DECIMAL.
DECIMAL
The DECIMAL data type can take two forms: DECIMAL(
p
) floating point and DECIMAL(
p,s
) fixed point.
DISTINCT data types
A DISTINCT type is a data type that is derived from a source type (called the base type).
DOUBLE PRECISION data types
The DOUBLE PRECISION keywords are a synonym for the FLOAT keyword.
FLOAT(n)
IDSSECURITYLABEL data type
The IDSSECURITYLABEL type stores a security label in a table that is protected by a label-based access control (LBAC) security policy.
INT data type
The INT data type is a synonym for INTEGER.
INT8
The INT8 data type stores whole numbers that can range in value from –9,223,372,036,854,775,807 to 9,223,372,036,854,775,807 [or -(2
63
-1) to 2
63
-1], for 18 or 19 digits of precision.
INTEGER data type
The INTEGER data type stores whole numbers that range from -2,147,483,647 to 2,147,483,647 for 9 or 10 digits of precision.
INTERVAL data type
The INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes:
year-month intervals
and
day-time intervals
.
LIST(e) data type
The LIST data type is a collection type that can store ordered non-NULL elements of the same SQL data type.
LVARCHAR(m) data type
Use the LVARCHAR data type to create a column for storing variable-length character strings whose upper limit (
m
) can be up to 32,739 bytes.
MONEY(p,s) data type
The MONEY data type stores currency amounts.
MULTISET(e) data type
The MULTISET data type is a collection type that stores a non-ordered set that can include duplicate element values.
Named ROW
NCHAR(n) data type
The NCHAR data type stores fixed-length character data. The data can be a string of single-byte or multibyte letters, digits, and other symbols that are supported by the code set of the database locale.
NUMERIC(p,s) data type
The NUMERIC data type is a synonym for fixed-point DECIMAL.
NVARCHAR(m,r) data type
The NVARCHAR data type stores strings of varying lengths. The string can include digits, symbols, and both single-byte and (in some locales) multibyte characters.
OPAQUE data types
An OPAQUE type is a data type for which you must provide information to the database server.
REAL data type
The REAL data type is a synonym for SMALLFLOAT.
ROW data type, Named
A named ROW data type must be declared with a name. This SQL identifier must be unique among data type names within the same database.
ROW data type, Unnamed
An unnamed ROW type contains fields but has no user-declared name. An unnamed ROW type is defined by its structure.
SERIAL(n) data type
The SERIAL data type stores a sequential integer, of the INT data type, that is automatically assigned by the database server when a new row is inserted.
SERIAL8(n) data type
The SERIAL8 data type stores a sequential integer, of the INT8 data type, that is assigned automatically by the database server when a new row is inserted.
SET(e) data type
The SET data type is an unordered collection type that stores unique elements
SMALLFLOAT
The SMALLFLOAT data type stores single-precision floating-point numbers with approximately nine significant digits.
SMALLINT data type
The SMALLINT data type stores small whole numbers that range from –32,767 to 32,767. The maximum negative number, –32,768, is a reserved value and cannot be used.
TEXT data type
The TEXT data type stores any kind of text data. It can contain both single-byte and multibyte characters that the locale supports. The term
simple large object
refers to an instance of a TEXT or BYTE data type.
Unnamed ROW
VARCHAR(m,r) data type
The VARCHAR data type stores character strings of varying length that contain single-byte and (if the locale supports them) multibyte characters, where
m
is the maximum size (in bytes) of the column and
r
is the minimum number of bytes reserved for that column.
Rate this topic
5 stars
4 stars
3 stars
2 stars
1 star
Comment on this topic.
By clicking this box, you acknowledge that you are NOT a U.S. Federal Government employee or agency, nor are you submitting information with respect to or on behalf of one. HCL provides software and services to U.S. Federal Government customers through its partners immixGroup, Inc. Contact this team at
https://hcltechsw.com/resources/us-government-contact
. Do not include any personal data in this Comment box.