Chapter 2. Data Types

2.1. Overview

A Data type is used to input values to SQL queries and to get the query result.

tbCLI supports the following two types.

Tibero data types

Used to access data stored in database.
tbCLI data types

Used to manipulate data from an application program.

2.2. Tibero Data Types

This section describes the default data types provided by Tibero. These data types are used to create schema objects of a database. Also within a tbESQL program, variables corresponding to all data types can be used.

The following table shows Tibero data types.

Classification	Data type	Description
Character type	CHAR, VARCHAR, NCHAR, NVARCHAR, RAW, LONG, LONG RAW	Character strings or binary data. It is possible for LONG and LONG RAW Data types to store up to 2GB.
Numeric type	NUMBER, INTEGER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE	Integers or real numbers.
Date type	DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIMEZONE	Time, date, or timezone.
Interval type	INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND	Time intervals.
LOB type	CLOB, BLOB	LOB type. This object can have far larger length than the maximum length (8 KB) of other data types. It is possible to store up to 4GB.
Embedded type	ROWID	Type of column which is automatically inserted by Tibero in rows, even though a user has not declared it explicitly.

Note

For more information, refer to Tibero SQL Reference Guide.

The followings are detailed descriptions of each data type.

Data type	Description
CHAR VARCHAR	General character strings.
NCHAR NVARCHAR	Unicode character strings.
RAW	Binary data.
LONG LONG RAW	Large character strings and binary data. A single column can be declared in one table.
NUMBER INTEGER FLOAT	Integers and real numbers. When declaring the NUMBER type, precision and scale can be declared as well. - Precision: The total number of digits of the data value - Scale: The number of places after the decimal point
BINARY_FLOAT BINARY_DOUBLE	Integers and real numbers. BINARY_FLOAT: 32-bit floating point type BINARY_DOUBLE: 64-bit floating point type
DATE TIME TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE	Date, time, and timezone. - DATE: A date - TIMESTAMP: A date and time - TIMESTAMP WITH TIME ZONE: A time and timezone that are standardized with Coordinated Universal Time (UTC) - TIMESTAMP WITH LOCAL TIME ZONE: A date and time that are standardized with Coordinated Universal Time (UTC)
TERVAL YEAR TO MONTH INTERVAL DAY TO SECOND	Time intervals. - INTERVAL YEAR TO MONTH: An interval between times which is calculated as years and months. - INTERVAL DAY TO SECOND: An interval between times which is calculated as days, hours, minutes, seconds, and decimal seconds.
CLOB BLOB	Large character strings and binary data. Multiple columns can be declared in one table.
ROWID	Automatically given to each row by Tibero system in order to identify the row within database. Includes a physical position of each row.

Data type

Description

CHAR

VARCHAR

General character strings.

NCHAR

NVARCHAR

Unicode character strings.

RAW

Binary data.

LONG

LONG RAW

Large character strings and binary data.

A single column can be declared in one table.

NUMBER

INTEGER

FLOAT

Integers and real numbers.

When declaring the NUMBER type, precision and scale can be declared as well.

- Precision: The total number of digits of the data value

- Scale: The number of places after the decimal point

BINARY_FLOAT

BINARY_DOUBLE

Integers and real numbers.

BINARY_FLOAT: 32-bit floating point type
BINARY_DOUBLE: 64-bit floating point type

DATE

TIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

Date, time, and timezone.

- DATE: A date

- TIMESTAMP: A date and time

- TIMESTAMP WITH TIME ZONE: A time and timezone that are standardized with Coordinated Universal Time (UTC)

- TIMESTAMP WITH LOCAL TIME ZONE: A date and time that are standardized with Coordinated Universal Time (UTC)

TERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

Time intervals.

- INTERVAL YEAR TO MONTH: An interval between times which is calculated as years and months.

- INTERVAL DAY TO SECOND: An interval between times which is calculated as days, hours, minutes, seconds, and decimal seconds.

CLOB

BLOB

Large character strings and binary data.

Multiple columns can be declared in one table.

ROWID

Automatically given to each row by Tibero system in order to identify the row within database.

Includes a physical position of each row.

2.3. tbCLI Data Types

This section describes tbCLI data type that application developers use to create a database program.

The following is a table which shows typedef names of C and its corresponding data types of C.

C's typedef	C's data type
SQLCHAR	unsigned char
SQLSCHAR	signed char
SQLSMALLINT	short int
SQLUSMALLINT	unsigned short int
SQLINTEGER	long int
SQLUINTEGER	unsigned long int
SQLREAL	float
SQLDOUBLE, SQLFLOAT	double
DATE_STRUCT, SQL_DATE_STRUCT	[Example 2.1]
TIME_STRUCT, SQL_TIME_STRUCT	[Example 2.2]
TIME_STAMP_STRUCT, SQL_TIMESTAMP_STRUCT	[Example 2.3]

The following are examples of using tbCLI data types, which corresponds to C's typedef name.

DATE_STRUCT, SQL_DATE_STRUCT

[Example 2.1] DATE_STRUCT, SQL_DATE_STRUCT

typedef struct tagDATE_STRUCT
{
    SQLSMALLINT    year;
    SQLUSMALLINT   month;
    SQLUSMALLINT   day;
};

TIME_STRUCT, SQL_TIME_STRUCT

[Example 2.2] TIME_STRUCT, SQL_TIME_STRUCT

struct tagTIME_STRUCT
{
    SQLUSMALLINT   hour;
    SQLUSMALLINT   minute;
    SQLUSMALLINT   second;
};

TIME_STAMP_STRUCT, SQL_TIMESTAMP_STRUCT

[Example 2.3] TIME_STAMP_STRUCT, SQL_TIMESTAMP_STRUCT

struct tagTIMESTAMP_STRUCT
{
    SQLSMALLINT    year;
    SQLUSMALLINT   month;
    SQLUSMALLINT   day;
    SQLUSMALLINT   hour;
    SQLUSMALLINT   minute;
    SQLUSMALLINT   second;
    SQLUINTEGER    fraction;
};

Prev		Next
Chapter 1. Introduction to tbCLI	Home	Chapter 3. tbCLI Functions