2.7 Representing SQL data in Prolog

Databases have a poorly standardized but rich set of datatypes. Some have natural Prolog counterparts, some not. A complete mapping requires us to define Prolog data-types for SQL types that have no standardized Prolog counterpart (such as timestamp), the definition of a default mapping and the possibility to define an alternative mapping for a specific column. For example, many variations of the SQL DECIMAL type cannot be mapped to a Prolog integer. Nevertheless, mapping to an integer may be the proper choice for a specific application.

The Prolog/ODBC interface defines the following Prolog result types with the indicated default transformation. Different result-types can be requested using the types(TypeList) option for the odbc_query/4 and odbc_prepare/5 interfaces.

atom

Used as default for the SQL types char, varchar, longvarchar, binary, varbinary, longvarbinary, decimal and numeric. Can be used for all types.

string

SWI-Prolog extended type string. Use the type for special cases where garbage atoms must be avoided. Can be used for all types.

codes

List of character codes. Use this type if the argument must be analysed or compatibility with Prolog systems that cannot handle infinite-length atoms is desired. Can be used for all types.

integer

Used as default for the SQL types bit, tinyint, smallint and integer. Please note that SWI-Prolog integers are signed 32-bit values, where SQL allows for unsigned values as well. Can be used for the integral, and decimal types as well as the types date and timestamp, which are represented as POSIX time-stamps (seconds after Jan 1, 1970).

float

Used as default for the SQL types real, float and double. Can be used for the integral and decimal types as well as the types date and timestamp, which are represented as POSIX time-stamps (seconds after Jan 1, 1970). Representing time this way is compatible to SWI-Prologs time-stamp handling.

date

A Prolog term of the form date(Year,Month,Day) used as default for the SQL type date.

time

A Prolog term of the form time(Hour,Minute,Second) used as default for the SQL type time.

timestamp

A Prolog term of the form timestamp(Year,Month,Day,Hour,Minute,Second,Fraction) used as default for the SQL type timestamp.