Data type support

Storage classes represent the actual data types that are used to store values in a database. The following storage classes are used by the database:

NULL

The value is a NULL value.

INTEGER

The value is a signed integer.

REAL

The value is a floating-point number value.

TEXT

The value is a text string (limited to 256 MB).

BLOB

The value is a Binary Large Object (BLOB); in other words, raw binary data (limited to 256 MB).

All values supplied to the database as literals embedded in a SQL statement or values bound using parameters to a prepared SQL statement are assigned a storage class before the SQL statement is executed.

Literals that are part of a SQL statement are assigned storage class TEXT if they are enclosed by single or double quotes, INTEGER if the literal is specified as an unquoted number with no decimal point or exponent, REAL if the literal is an unquoted number with a decimal point or exponent and NULL if the value is a NULL. Literals with storage class BLOB are specified using the X'ABCD' notation. For more information, see Literal values in expressions.

Values supplied as parameters using the SQLStatement.parameters associative array are assigned the storage class that most closely matches the native data type bound. For example, int values are bound as INTEGER storage class, Number values are given the REAL storage class, String values are given the TEXT storage class, and ByteArray objects are given the BLOB storage class.

The affinity of a column is the recommended type for data stored in that column. When a value is stored in a column (through an INSERT or UPDATE statement), the runtime attempts to convert that value from its data type to the specified affinity. For example, if a Date value (an ActionScript or JavaScript Date instance) is inserted into a column whose affinity is TEXT, the Date value is converted to the String representation (equivalent to calling the object's toString() method) before being stored in the database. If the value cannot be converted to the specified affinity an error occurs and the operation is not performed. When a value is retrieved from the database using a SELECT statement, it is returned as an instance of the class corresponding to the affinity, regardless of whether it was converted from a different data type when it was stored.

If a column accepts NULL values, the ActionScript or JavaScript value null can be used as a parameter value to store NULL in the column. When a NULL storage class value is retrieved in a SELECT statement, it is always returned as the ActionScript or JavaScript value null, regardless of the column's affinity. If a column accepts NULL values, always check values retrieved from that column to determine if they're null before attempting to cast the values to a non-nullable type (such as Number or Boolean).

Each column in the database is assigned one of the following type affinities:

TEXT (or String)

A column with TEXT or String affinity stores all data using storage classes NULL, TEXT, or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted to text form before being stored.

NUMERIC

A column with NUMERIC affinity contains values using storage classes NULL, REAL, or INTEGER. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class (for example, a value of '10.05' is converted to REAL storage class before being stored). If the conversion cannot be performed an error occurs. No attempt is made to convert a NULL value. A value that's retrieved from a NUMERIC column is returned as an instance of the most specific numeric type into which the value fits. In other words, if the value is a positive integer or 0, it's returned as a uint instance. If it’s a negative integer, it’s returned as an int instance. Finally, if it has a floating-point component (it's not an integer) it's returned as a Number instance.

INTEGER (or int)

A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, with one exception. If the value to be stored is a real value (such as a Number instance) with no floating point component or if the value is a text value that can be converted to a real value with no floating point component, it is converted to an integer and stored using the INTEGER storage class. If an attempt is made to store a real value with a floating point component an error occurs.

REAL (or Number)

A column with REAL or NUMBER affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. A value in a REAL column is always returned from the database as a Number instance.

Boolean

A column with Boolean affinity stores true or false values. A Boolean column accepts a value that is an ActionScript or JavaScript Boolean instance. If code attempts to store a String value, a String with a length greater than zero is considered true, and an empty String is false. If code attempts to store numeric data, any non-zero value is stored as true and 0 is stored as false. When a Boolean value is retrieved using a SELECT statement, it is returned as a Boolean instance. Non-NULL values are stored using the INTEGER storage class (0 for false and 1 for true) and are converted to Boolean objects when data is retrieved.

Date

A column with Date affinity stores date and time values. A Date column is designed to accept values that are ActionScript or JavaScript Date instances. If an attempt is made to store a String value in a Date column, the runtime attempts to convert it to a Julian date. If the conversion fails an error occurs. If code attempts to store a Number, int, or uint value, no attempt is made to validate the data and it is assumed to be a valid Julian date value. A Date value that's retrieved using a SELECT statement is automatically converted to a Date instance. Date values are stored as Julian date values using the REAL storage class, so sorting and comparing operations work as you would expect them to.

XML or XMLList

A column that uses XML or XMLList affinity stores XML structures. When code attempts to store data in an XML column using a SQLStatement parameter the runtime attempts to convert and validate the value using the ActionScript XML() or XMLList() function. If the value cannot be converted to valid XML an error occurs. If the attempt to store the data uses a literal SQL text value (for example INSERT INTO (col1) VALUES ('Invalid XML (no closing tag)'), the value is not parsed or validated — it is assumed to be well-formed. If an invalid value is stored, when it is retrieved it is returned as an empty XML object. XML and XMLList Data is stored using the TEXT storage class or the NULL storage class.

Object

A column with Object affinity stores ActionScript or JavaScript complex objects, including Object class instances as well as instances of Object subclasses such as Array instances and even custom class instances. Object column data is serialized in AMF3 format and stored using the BLOB storage class. When a value is retrieved, it is deserialized from AMF3 and returned as an instance of the class as it was stored. Note that some ActionScript classes, notably display objects, cannot be deserialized as instances of their original data type. Before storing a custom class instance, you must register an alias for the class using the flash.net.registerClassAlias() method (or in Flex by adding [RemoteObject] metadata to the class declaration). Also, before retrieving that data you must register the same alias for the class. Any data that can't be deserialized properly, either because the class inherently can't be deserialized or because of a missing or mismatched class alias, is returned as an anonymous object (an Object class instance) with properties and values corresponding to the original instance as stored.

NONE

A column with affinity NONE does not prefer one storage class over another. It makes no attempt to convert data before it is inserted.

The following binary comparison operators =, <, <=, >= and != are supported, along with an operation to test for set membership, IN, and the ternary comparison operator BETWEEN. For details about these operators see Operators.

The results of a comparison depend on the storage classes of the two values being compared. When comparing two values the following rules are applied:

• A value with storage class NULL is considered less than any other value (including another value with storage class NULL).

• An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.

• A TEXT value is less than a BLOB value. When two TEXT values are compared, a binary comparison is performed.

• When two BLOB values are compared, the result is always determined using a binary comparison.

The ternary operator BETWEEN is always recast as the equivalent binary expression. For example, a BETWEEN b AND c is recast to a >= b AND a <= c, even if this means that different affinities are applied to a in each of the comparisons required to evaluate the expression.

Expressions of the type a IN (SELECT b ....) are handled by the three rules enumerated previously for binary comparisons, that is, in a similar manner to a = b. For example, if b is a column value and a is an expression, then the affinity of b is applied to a before any comparisons take place. The expression a IN (x, y, z) is recast as a = +x OR a = +y OR a = +z. The values to the right of the IN operator (the x, y, and z values in this example) are considered to be expressions, even if they happen to be column values. If the value of the left of the IN operator is a column, then the affinity of that column is used. If the value is an expression then no conversions occur.

How comparisons are performed can also be affected by the use of a COLLATE clause. For more information, see COLLATE.

For each of the supported mathematical operators, *, /, %, +, and -, numeric affinity is applied to each operand before evaluating the expression. If any operand cannot be converted to the NUMERIC storage class successfully the expression evaluates to NULL.

When the concatenation operator || is used each operand is converted to the TEXT storage class before the expression is evaluated. If any operand cannot be converted to the TEXT storage class then the result of the expression is NULL. This inability to convert the value can happen in two situations, if the value of the operand is NULL, or if it's a BLOB containing a non-TEXT storage class.

When values are sorted by an ORDER BY clause, values with storage class NULL come first. These are followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values in binary order or based on the specified collation (BINARY or NOCASE). Finally come BLOB values in binary order. No storage class conversions occur before the sort.

When grouping values with the GROUP BY clause, values with different storage classes are considered distinct. An exception is INTEGER and REAL values which are considered equal if they are numerically equivalent. No affinities are applied to any values as the result of a GROUP BY clause.

The compound SELECT operators UNION, INTERSECT, and EXCEPT perform implicit comparisons between values. Before these comparisons are performed an affinity may be applied to each value. The same affinity, if any, is applied to all values that may be returned in a single column of the compound SELECT result set. The affinity that is applied is the affinity of the column returned by the first component SELECT statement that has a column value (and not some other kind of expression) in that position. If for a given compound SELECT column none of the component SELECT statements return a column value, no affinity is applied to the values from that column before they are compared.