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.

Database Datatype Mapping

MySQL

PostgreSQL

SQLite

ODBC