SQL Implementation Specifics

This section describes how the Desktop Database Drivers implement SQL statements and clauses through Microsoft Jet, particularly when they are not SQL-92 compliant or there are limitations to their use.

Aggregate Functions

An aggregate function and a non-aggregate field (column) reference cannot both be used in the clauses of a single SQL statement.

Between…And Operator (BETWEEN Predicate)

ODBC SQL supports the same Between…And Operator syntax as ANSI, as follows:

expression1 Between expression2 And expression3

The semantics of this syntax are different for the Desktop Database Drivers and Microsoft Jet SQL. When the Desktop Database Drivers are used, this statement returns TRUE only if expression1 is greater than or equal to expression2, and expression1 is less than or equal to expression3. This is different from Microsoft Jet SQL, in which expression2 can be greater than expression3, so that the statement would return TRUE only if expression1 is greater than or equal to expression3, and expression1 is less than or equal to expression2.

Data Conversion

Each driver converts the C data types used by ODBC applications to the SQL data types used in Microsoft Jet, and vice versa. The drivers convert the data types in two steps.

1. A generic conversion function in ODBCTL32.DLL converts the C data types to an SQL data type that is common to all drivers.
   
   
2. Each driver’s code within ODBCJT32.DLL converts the common SQL data types to the driver-specific SQL data type used by Microsoft Jet.

ODBCTL32.DLL also contains a generic conversion function for parsing SQL strings that are passed in ODBC escape sequences, such as date literals, procedure calls, scalar functions, and outer join calls. Like data conversion, this is a two-step process, in which the generic conversion process performs parsing that is common to all drivers, and each driver performs its own additional parsing as required.

Datetime Values

For maximum interoperability, applications should pass datetime literals using the ODBC escape-clause syntax. Pass date literals using {d ‘value’}, where value is of the form “yyyy-mm-dd.” Pass time literals using {t ‘ value ‘}, where value is of the form “hh-mm-ss,” and timestamp literals using {ts ‘ value ‘}, where value is of the form “yyyy-mm-dd hh-mm-ss[.f…].” Datetime literals can also be delimited by the Microsoft Jet-specific datetime delimiter (“#”), which is not portable. If a datetime literal is not passed using the ODBC escape clause or the datetime delimiter, the literal will be treated as an arithmetic expression without raising a warning or error. For example, an application should represent the date “February 7, 1997” as {d ‘1997-02-07’} or #02-07-1997#. If the application submits only 02-07-1997, it will be evaluated as 2 minus 7 minus 1997.

Neither the SQL_DATE nor the SQL_TIMESTAMP data type can be converted to another data type (or itself) by the CONVERT function in escape-clause syntax.

The Desktop Database Drivers support adding or subtracting an integer from an SQL_DATE field. The integer specifies the number of days to add or subtract. Date arithmetic is not supported for adding (or subtracting) an SQL_DATE data type to (or from) another SQL_DATE data type.

DISTINCT Predicate

The Desktop Database Drivers do not support the DISTINCT predicate for binary data, Memo (Long Text) fields in Microsoft Access, or Memo fields in dBASE or FoxPro. The drivers also do not support the DISTINCT predicate in the set functions (Avg, Max, Min, and Sum).

Identifier Quote Character

The identifier quote character allows strings or characters that would normally be invalid to be used in identifiers. For the Desktop Database Drivers, a valid identifier is a string of no more than 64 characters. The first character of a valid identifier cannot be a space. Valid identifiers cannot include control characters or special characters (`|#*?[].!$\), or the reserved words listed in Appendix C of the Microsoft ODBC 3.0 Software Development Kit and Programmer’s Reference. If an identifier (field or table name) violates any of these rules, the identifier must be enclosed in the identifier quote character to be used. For the Desktop Database Drivers, this character is a back quote (`). Note that for many other SQL processors, the identifier quote character is a forward quote character.

Like Operator (LIKE Predicate)

The Desktop Database Drivers support the Like operator, but do not support the use of escape clauses with a Like operator. A Like operator used in a procedure is supported only with literals. The drivers support ANSI Like pattern matching. If data in a Long Text (Memo) field is longer than 255 characters, the Like comparison is based only on the first 255 characters.

Multiple SQL Statements

Microsoft Jet can perform neither ad-hoc nor stored queries consisting of multiple SQL statements. Only single SQL statements are supported.

NULL/NOT NULL

The Desktop Database Drivers do not support the NOT NULL constraint in a CREATE TABLE statement. A Microsoft Access application can create a field that does not allow Null values by creating an index on a field with the DISALLOW NULL option. However, an application using the Desktop Database Drivers is unable to detect whether a field can contain Null values, because SQLColumns returns SQL_NULLABLE in the NULLABLE field of its result set for all fields (even if Null values are disallowed by virtue of an index), or for a table that has a field for which the Required property has been set to Yes.

ORDER BY Clause

The fields in the ORDER BY clause do not have to be in the SELECT statement’s field list. Expressions can be used in the ORDER BY clause. If a SELECT statement contains a GROUP BY clause and an ORDER BY clause, the ORDER BY clause can contain only a field in the result set or an expression that also appears in the GROUP BY clause.

OUTER JOIN Clauses

The Desktop Database Drivers support left and right outer joins, as well as inner joins. You can use the right table in a left outer join, or the left table in a right outer join, in an inner join. The Desktop Database Drivers do not support full or nested outer joins.

A SELECT statement can contain a list of OUTER JOIN clauses. The field names in the ON clause of the outer join do not have to be in the same order as their respective table names in the OUTER JOIN clause. The comparison operator in the ON clause can be any of the ODBC comparison operators.

To create an outer join, execute an ODBC OUTER JOIN statement in the ODBC escape clause, or use Microsoft Jet syntax. The ODBC OUTER JOIN syntax is as follows:

{oj tablename1 [LEFT or RIGHT] OUTER JOIN tablename2 ON
útablename1.field=tablename2.field}

See Also Appendix B, “SQL Reference,” shows the syntax of join statements in Microsoft Jet.

Procedure Invocation

To invoke procedures by using the Microsoft Access driver, call the SQLExecDirect or SQLPrepare function with the following escape-clause syntax

{CALL procedure-name [(parameter[,parameter]...)]}

where procedure-name specifies the name of a procedure stored on the data source and parameter specifies a procedure parameter. This escape clause acts as an indicator to the driver that it must translate the statement within it from ODBC standard syntax to DBMS-specific syntax.

To use a parameter query with the same syntax, pass markers (“?”) for parameters in the CALL statement and use SQLBindParameter to bind the parameter markers. The Desktop Database Drivers do not support expressions and constants as parameters to a called procedure. An input parameter must be a parameter marker; the drivers support only bound parameters. If a procedure name includes a hyphen, it must be delimited by the identifier quote character, a back quote (`).

ODBC Scalar Functions

This section describes the ODBC string functions, numeric functions, time and date functions, and data type conversion functions supported by the Desktop Database Drivers. System scalar functions are not supported. The Desktop Database Drivers do not support user-defined functions.

The Desktop Database Drivers support the following ODBC string functions:

The Desktop Database Drivers support the following ODBC numeric functions:

The Desktop Database Drivers support the following ODBC time and date functions:

The Microsoft Access, Microsoft Excel, and Text drivers support the following additional ODBC time and date functions:

Explicit data-type conversions, using the CONVERT function in an ODBC escape sequence, can be performed on the following ODBC data types:

Explicit data-type conversions cannot be performed on the following ODBC data types:

SQL_BIGINT

SQL_BIT

SQL_DECIMAL

See Also For more information about the ODBC scalar functions that Microsoft Jet supports, see Appendix E, “Scalar Functions,” of the Microsoft ODBC 3.0 Software Development Kit and Programmer’s Reference.

Search Conditions

The maximum number of fields in a WHERE clause is 40. The maximum number of search conditions in a HAVING clause is 40.