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