Application Note for QuNect ODBC for QuickBase
Scalar Type Conversion Function Supported by QuNect ODBC for QuickBase
Explicit data type conversion is specified in terms of SQL data type definitions.
The ODBC syntax for the explicit data type conversion function does not restrict conversions. The validity of specific conversions of one data type to another data type will be determined by each driver-specific implementation. The driver will, as it translates the ODBC syntax into the native syntax, reject those conversions that, although legal in the ODBC syntax, are not supported by the data source. The ODBC function SQLGetInfo, with the conversion options (such as SQL_CONVERT_BIGINT, SQL_CONVERT_BINARY, SQL_CONVERT_INTERVAL_YEAR_MONTH, and so on), provides a way to inquire about conversions supported by the data source.
The format of the CONVERT function is:
{fn CONVERT(value_exp, data_type)}
The function returns the value specified by value_exp converted to the specified data_type, where data_type is one of the following keywords:
SQL_BIGINT | SQL_INTERVAL_HOUR_TO_MINUTE |
SQL_BINARY | SQL_INTERVAL_HOUR_TO_SECOND |
SQL_BIT | SQL_INTERVAL_MINUTE_TO_SECOND |
SQL_CHAR | SQL_LONGVARBINARY |
SQL_DECIMAL | SQL_LONGVARCHAR |
SQL_DOUBLE | SQL_NUMERIC |
SQL_FLOAT | SQL_REAL |
SQL_GUID | SQL_SMALLINT |
SQL_INTEGER | SQL_DATE |
SQL_INTERVAL_MONTH | SQL_TIME |
SQL_INTERVAL_YEAR | SQL_TIMESTAMP |
SQL_INTERVAL_YEAR_TO_MONTH | SQL_TINYINT |
SQL_INTERVAL_DAY | SQL_VARBINARY |
SQL_INTERVAL_HOUR | SQL_VARCHAR |
SQL_INTERVAL_MINUTE | SQL_WCHAR |
SQL_INTERVAL_SECOND | SQL_WLONGVARCHAR |
SQL_INTERVAL_DAY_TO_HOUR | SQL_WVARCHAR |
SQL_INTERVAL_DAY_TO_MINUTE | |
SQL_INTERVAL_DAY_TO_SECOND |
The ODBC syntax for the explicit data type conversion function does not support specification of conversion format. If specification of explicit formats is supported by the underlying data source, a driver must specify a default value or implement format specification.
The argument value_exp can be a column name, the result of another scalar function, or a numeric or string literal. For example:
{ fn CONVERT( { fn CURDATE() }, SQL_CHAR ) }
converts the output of the CURDATE scalar function to a character string.
Because ODBC does not mandate a data type for return values from scalar functions (because the functions are often data sourcespecific), applications should use the CONVERT scalar function whenever possible to force data type conversion.