Database SQL to Maple Type Conversions
|
SQL types without a Maple Equivalent
|
|
•
|
Many of the SQL types have Maple equivalents. However, a few types do not map easily to Maple. In those cases, Database attempts to generate the best match possible. Descriptions of the matches follow.
|
•
|
As Maple has no time or date type, the DATE, TIME, and TIMESTAMP SQL types need a representation. Database uses the number of seconds since epoch, stored as a numeric. With Maple arbitrary precision floats, this representation unifies the three SQL types into one Maple type with enough accuracy to support TIMESTAMP's nanosecond precision. Notice that these types have a limited accuracy (most notably DATE, which is only accurate to the day, a range of 86400 seconds), so the numeric that is passed for one of these formats may be different from the value that is returned.
|
•
|
Database represents BINARY, VARBINARY, and LONGVARBINARY as one dimensional Arrays of type integer[1].
|
|
|
SQL to Maple
|
|
•
|
The following table shows how an SQL type is returned at the Maple level. This occurs when retrieving data using GetData and ToMaple.
|
SQL Type
|
Maple Type
|
---------------
|
---------------
|
BIGINT
|
integer
|
BINARY
|
Array( datatype=integer[1] )
|
BIT
|
boolean
|
BOOLEAN
|
boolean
|
CHAR
|
string
|
DATE
|
numeric
|
DECIMAL
|
numeric
|
DOUBLE
|
numeric
|
FLOAT
|
numeric
|
INTEGER
|
integer
|
LONGVARBINARY
|
Array( datatype=integer[1] )
|
LONGVARCHAR
|
string
|
NULL
|
NULL
|
NUMERIC
|
numeric
|
REAL
|
float
|
SMALLINT
|
integer
|
TIME
|
numeric
|
TIMESTAMP
|
numeric
|
TINYINT
|
integer
|
VARBINARY
|
Array( datatype=integer[1] )
|
VARCHAR
|
string
|
|
|
|
|
Maple to SQL
|
|
•
|
When sending data to SQL, Maple attempts to determine the correct SQL type to use. This section discusses the conversion process and how to override the automatic detection. These conversions are used in calls to PreparedStatement[Execute], UpdateData, and InsertRow.
|
•
|
Integers are converted to TINYINT, SMALLINT, INTEGER, BIGINT, or NUMERIC, depending on the value of the integer.
|
•
|
Numerics are converted to DOUBLE or NUMERIC, depending on the value of the numeric. Only the magnitude, and not the accuracy, of the number is used when determining which type to use. If the type chosen by Database does not have sufficient accuracy, then an explicit cast must be used. A numeric value representing a DATE, TIME, or TIMESTAMP requires an explicit cast.
|
•
|
Strings are converted to VARCHAR or LONGVARCHAR, depending on the length of the string.
|
•
|
Values of type Array( integer[1] ) are converted to VARBINARY or LONGVARBINARY, depending on the length of the array.
|
|
|
Explicit Casts
|
|
•
|
If the SQL type that Database selects is incorrect, you can override this behavior by using an explicit cast. To perform a cast, use :: and the SQL type name you want to cast to. For example, 1::DOUBLE or 12346789::TIMESTAMP. Specifying an illegal cast is an error.
|
•
|
The Result module UpdateData and InsertRow commands are able to look up column types, so using explicit casts is not required. However, looking up column types can require database access. This can introduce unacceptable overhead for these commands. To avoid this overhead, use an explicit cast.
|
|
|
SQL NULL
|
|
•
|
To enter an SQL NULL as a data item, you can pass 'SQLNULL' into any of the functions that allow for inputting of data.
|
•
|
Getting data from a column containing an SQL NULL will return Maple NULL.
|
|
|