Gambas Documentation - /doc/db-type-mapping

# Database Datatype Mapping

The database component maintains a mapping between each Gambas datatype and each datatype of the underlying database driver.

Of course, this mapping is not bijective. Sometimes a Gambas datatype does not exist in the database system, and, more often, it is the contrary.

Because of that mapping, you must be very careful when you design your database. If you don't want any problem, be sure to always use the database manager provided with Gambas.

Beware with the NULL SQL value!

In Gambas, NULL and the void string are the same thing, but usually it's not in database servers.

Gambas always stores NULL in the database, never a void string, and you must take that into account when writing your SQL requests.

For example, don't do:

    SELECT * FROM table WHERE name = '';

But do:

    SELECT * FROM table WHERE name IS NULL;

No solution has been found to handle that automatically yet.

## MySQL

The mapping for the MySQL database driver is the following:

Gambas datatype | MySQL datatype | Datatype used by default
--- | --- | ---
Boolean | BOOL TINYINT (length = 1) | BOOL
Integer | TINYINT (length > 1) SMALLINT MEDIUMINT INT YEAR | INT
Long | BIGINT | BIGINT
Serial | INT UNSIGNED NOT NULL AUTO_INCREMENT BIGINT UNSIGNED NOT NULL AUTO_INCREMENT | BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
Float | FLOAT DOUBLE DECIMAL REAL NUMERIC | DOUBLE
Date | TIMESTAMP DATE TIME DATETIME | DATETIME
Blob | LONGBLOB LONGTEXT BLOB (length >= 16777216) TEXT (length >= 16777216) | TEXT
String | VARCHAR BLOB (length < 16777216) TEXT (length < 16777216) All others. | VARCHAR if length <= 255. TEXT otherwise.

MySQL makes no difference between a blob or a text field. So Gambas relies on the field length to decide if a field is a text field or a blob.

## PostgreSQL

The mapping for the PostgreSQL database driver is the following:

Gambas datatype | PostgreSQL datatype | Datatype used by default
--- | --- | ---
Boolean | BOOL | BOOL
Integer | INT2 INT4 INT | INT
Long | INT8 BIGINT | BIGINT
Serial | BIGSERIAL | BIGSERIAL
Float | NUMERIC FLOAT4 FLOAT8 | FLOAT8

Date

ABSTIME
RELTIME
DATE
TIME
TIMESTAMP
DATETIME
TIMESTAMPTZ

TIMESTAMP

Blob | BYTEA | BYTEA

String

CHAR
BPCHAR
VARCHAR
TEXT
NAME
CASH
All others.

VARCHAR
TEXT for unlimited string fields.

## SQLite

The mapping for the SQLite database driver is the following:

Gambas datatype | SQLite datatype | Datatype used by default
--- | --- | ---
Boolean | CHAR TINYINT INT1 BOOL | BOOL
Integer | SMALLINT INT2 MEDIUMINT INT INT4 | INT4
Long | INT8 BIGINT | BIGINT
Serial | INTEGER | INTEGER AUTOINCREMENT

Float

DECIMAL
NUMERIC
DOUBLE
FLOAT8
REAL
FLOAT
FLOAT4

FLOAT8

Date | DATE TIME TIMESTAMP DATETIME | DATETIME
Blob | BLOB | BLOB

String

CHAR()
CLOB
TEXT
VARCHAR
ENUM
SET
YEAR
All others.

VARCHAR
TEXT for unlimited string fields.

## ODBC