Data Types

This chapter describes data types used in tbCLI program and data type conversion.

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.

---

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.

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.

For further 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	Date, time, and timezone.
TIME TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE	- DATE: specific date - TIMESTAMP: specific date and time - TIMESTAMP WITH TIME ZONE: specific time and timezone that are standardized with Coordinated Universal Time (UTC) - TIMESTAMP WITH LOCAL TIME ZONE: specific 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 based on years and months. - INTERVAL DAY TO SECOND: An interval between times which is based on 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.

---

tbCLI Data Type

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

The following is a table that 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	[EX. 5] DATE_STRUCT, SQL_DATE_STRUCT
TIME_STRUCT, SQL_TIME_STRUCT	[Ex. 6] TIME_STRUCT, SQL_TIME_STRUCT
TIME_STAMP_STRUCT, SQL_TIMESTAMP_STRUCT	[EX. 7] TIME_STAMP_STRUCT, SQL_TIMESTAMP_STRUCT

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

DATE_STRUCT, SQL_DATE_STRUCT

[Ex. 5] DATE_STRUCT, SQL_DATE_STRUCT

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

TIME_STRUCT, SQL_TIME_STRUCT

[Ex. 6] TIME_STRUCT, SQL_TIME_STRUCT

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

TIME_STAMP_STRUCT, SQL_TIMESTAMP_STRUCT

[Ex. 7] TIME_STAMP_STRUCT, SQL_TIMESTAMP_STRUCT

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