4 SQL types and conversions
For most basic SQL types, connections automatically convert query results to appropriate Racket types. Likewise, query parameters are accepted as Racket values and converted to the appropriate SQL type.
Examples: |
> (query-value pgc "select count(*) from the_numbers") |
4 |
> (query-value pgc "select false") |
#f |
> (query-value pgc "select 1 + $1" 2) |
3 |
If a query result contains a column with a SQL type not supported by this library, an error is raised. As a workaround, cast the column to a supported type:
Examples: |
> (query-value pgc "select point(1,2)") |
query-value: unsupported type: point (typeid 600) |
> (query-value pgc "select cast(point(1,2) as varchar)") |
"(1,2)" |
The error for unsupported types in result columns is raised when the query is executed; for parameters it is raised when the parameter values are supplied. Thus even unexecutable prepared statements can be inspected using prepared-statement-parameter-types and prepared-statement-result-types.
4.1 Type correspondences
This sections describes the correspondences between SQL types and Racket types for the supported database systems.
4.1.1 PostgreSQL
The following table lists the PostgreSQL types known to this library, along with their corresponding Racket representations.
PostgreSQL type
pg_type.typname
Racket type
'boolean
bool
'char1
char
'smallint
int2
'integer
int4
'bigint
int8
'real
float4
'double
float8
'decimal
numeric
'character
bpchar
'varchar
varchar
'text
text
'bytea
bytea
'date
date
'time
time
'timetz
timetz
'timestamp
timestamp
'timestamptz
timestamptz
'interval
interval
The 'char1 type, written "char" in PostgreSQL’s SQL syntax (the quotation marks are significant), is one byte, essentially a tiny integer written as a character.
A SQL value of type decimal is converted to either an exact rational or +nan.0. When converting Scheme values to SQL decimal, exact rational values representable by finite decimal strings are converted without loss of precision. (Precision may be lost, of course, if the value is then stored in a database field of lower precision.) Other real values are converted to decimals with a loss of precision. In PostgreSQL, numeric and decimal refer to the same type.
Examples: |
> (query-value pgc "select real '+Infinity'") |
+inf.0 |
> (query-value pgc "select numeric '12345678901234567890'") |
12345678901234567890 |
PostgreSQL defines many other types, such as network addresses, various geometric shapes, and array types. These are currently not supported, but future versions of this library may include new type correspondences and conversions.
4.1.2 MySQL
The following table lists the MySQL types known to this package, along with their corresponding Racket representations.
MySQL type
Racket type
'integer
'tinyint
'smallint
'mediumint
'bigint
'real
'double
'decimal
'varchar
'var-string
'date
'time
'datetime
MySQL does not report specific parameter types for prepared queries,
instead assigning them the type var-string. Consequently,
conversion of Racket values to var-string parameters accepts, in
addition to strings, numbers (rational?—
A SQL value of type decimal is converted to an exact rational (MySQL seems not to support infinite decimal values).
In MySQL, the time type represents time intervals, which may not correspond to times of day (for example, the interval may be negative or larger than 24 hours). In conversion from MySQL results to Racket values, those time values that represent times of day are converted to sql-time values; the rest are represented by sql-interval values.
4.1.3 SQLite
The following table lists the SQLite types known to this package, along with their corresponding Racket representations.
Unlike PostgreSQL and MySQL, SQLite does not enforce declared type constraints (with the exception of integer primary key) on columns. Rather, every SQLite value has an associated “storage class”.
SQLite storage class
Racket type
integer
real
text
blob
SQLite does not report specific parameter and result types for prepared queries. Instead, they are assigned the pseudotype 'any. Conversion of Racket values to parameters accepts strings, bytes, and real numbers.
An exact integer that cannot be represented as a 64-bit signed integer is converted as real, not integer.
Examples: |
> (expt 2 80) |
1208925819614629174706176 |
> (query-value slc "select ?" (expt 2 80)) |
1.2089258196146292e+24 |
4.1.4 ODBC
The following table lists the ODBC types known to this package, along with their corresponding Racket representations.
ODBC type
Racket type
'character
'varchar
'longvarchar
'numeric
'decimal
'integer
'tinyint
'smallint
'bigint
'float
'real
'double
'date
'time
'datetime
'timestamp
'binary
'varbinary
'longvarbinary
'bit1
Not all ODBC drivers provide parameter type information for prepared
queries. In such situations the connection assigns the parameter the
pseudotype 'unknown. Conversion of Racket values to
'unknown parameters accepts strings, bytes, numbers
(rational?—
The ODBC type 'bit1 represents a single bit, unlike the standard SQL bit(N) type.
4.2 SQL data
SQL NULL is translated into the unique sql-null value.
Example: |
> (query-value c "select NULL") |
#<sql-null> |
(sql-null->false x) → any/c |
x : any/c |
Examples: |
> (sql-null->false "apple") |
"apple" |
> (sql-null->false sql-null) |
#f |
> (sql-null->false #f) |
#f |
(false->sql-null x) → any/c |
x : any/c |
Examples: |
> (false->sql-null "apple") |
"apple" |
> (false->sql-null #f) |
#<sql-null> |
New Racket datatypes are also provided for a few SQL types that have no existing close analogues.
(struct sql-date (year month day)) |
year : exact-integer? |
month : (integer-in 0 12) |
day : (integer-in 0 31) |
MySQL allows DATE values with zero components as an extension.
(struct sql-time (hour minute second nanosecond tz)) |
hour : exact-nonnegative-integer? |
minute : exact-nonnegative-integer? |
second : exact-nonnegative-integer? |
nanosecond : exact-nonnegative-integer? |
tz : (or/c exact-integer? #f) |
| ||||||||||||||||||||||||||||||||||||||||
year : exact-nonnegative-integer? | ||||||||||||||||||||||||||||||||||||||||
month : exact-nonnegative-integer? | ||||||||||||||||||||||||||||||||||||||||
day : exact-nonnegative-integer? | ||||||||||||||||||||||||||||||||||||||||
hour : exact-nonnegative-integer? | ||||||||||||||||||||||||||||||||||||||||
minute : exact-nonnegative-integer? | ||||||||||||||||||||||||||||||||||||||||
second : exact-nonnegative-integer? | ||||||||||||||||||||||||||||||||||||||||
nanosecond : exact-nonnegative-integer? | ||||||||||||||||||||||||||||||||||||||||
tz : (or/c exact-integer? #f) |
The tz field indicates the time zone offset as the number of seconds east of GMT (as in SRFI 19). If tz is #f, the time or timestamp does not carry time zone information.
The sql-time and sql-timestamp structures store fractional seconds to nanosecond precision for compatibility with SRFI 19. Note, however, that database systems generally do not support nanosecond precision; PostgreSQL, for example, only supports microsecond precision.
Examples: |
> (query-value pgc "select date '25-dec-1980'") |
#(struct:sql-date 1980 12 25) |
> (query-value pgc "select time '7:30'") |
#(struct:sql-time 7 30 0 0 #f) |
> (query-value pgc "select timestamp 'epoch'") |
#(struct:sql-timestamp 1970 1 1 0 0 0 0 #f) |
> (query-value pgc "select timestamp with time zone 'epoch'") |
#(struct:sql-timestamp 1969 12 31 19 0 0 0 -18000) |
Examples: |
> (query-value myc "select date('1980-12-25')") |
#(struct:sql-date 1980 12 25) |
> (query-value myc "select time('7:30')") |
#(struct:sql-time 7 30 0 0 #f) |
> (query-value myc "select from_unixtime(0)") |
#(struct:sql-timestamp 1969 12 31 19 0 0 0 #f) |
| ||
| ||
| ||
| ||
| ||
|
Examples: | ||
| ||
#(struct:tm:date 0 0 30 7 0 0 0 0) | ||
| ||
#(struct:tm:date 0 0 0 0 25 12 1980 0) | ||
| ||
#(struct:tm:date 0 0 0 0 1 1 1970 0) |
| |||||||||||||||||||||||||||||||||||
years : exact-integer? | |||||||||||||||||||||||||||||||||||
months : exact-integer? | |||||||||||||||||||||||||||||||||||
days : exact-integer? | |||||||||||||||||||||||||||||||||||
hours : exact-integer? | |||||||||||||||||||||||||||||||||||
minutes : exact-integer? | |||||||||||||||||||||||||||||||||||
seconds : exact-integer? | |||||||||||||||||||||||||||||||||||
nanoseconds : exact-integer? |
That is, an interval consists of two groups of components: year-month and day-time, and normalization is done only within groups. In fact, the SQL standard recognizes those two types of intervals separately (see sql-year-month-interval? and sql-day-time-interval?, below), and does not permit combining them. Representing intervals such as 1 month 3 days is a PostgreSQL extension.
(sql-year-month-interval? x) → boolean? |
x : any/c |
(sql-day-time-interval? x) → boolean? |
x : any/c |
(sql-day-time-interval->seconds interval) → rational? |
interval : sql-day-time-interval? |
(sql-interval->sql-time interval [failure]) → any |
interval : sql-interval? |
failure : any/c = (lambda () (error ....)) |
hours, minutes, seconds, and nanoseconds must all be non-negative
hours must be between 0 and 23
If interval is out of range, the failure value is called, if it is a procedure, or returned, otherwise.
The sql-interval->sql-time function can be used as a predicate for intervals representing times of day by passing #f as the failure argument.
(sql-time->sql-interval time) → sql-day-time-interval? |
time : sql-time? |