Escape clauses and data types (reference)
Which ODBC escape clauses the driver understands in a statement, and with which SQL data type it
reports each column of the service to an application. You need this page if a statement with
{d …}, {ts …}, or {fn …} doesn't run as expected, or if an application shows a different type,
length, or type name than through the SQL Server ODBC driver.
The ODBC specification describes escape clauses as a vendor-neutral notation for literals, functions, and joins (Microsoft: Escape Sequences in ODBC). A driver doesn't have to support all of them; the table below lists what this driver translates itself.
Escape clauses
| Clause | Behavior |
|---|---|
{d 'yyyy-mm-dd'} | Date. Only exactly this form; any other text is rejected |
{t 'hh:mm:ss'} | Time. Only exactly this form |
{ts 'yyyy-mm-dd hh:mm:ss[.f]'} | Timestamp with up to seven fractional digits. Only exactly this form |
{guid '…'} | Identifier (uniqueidentifier, Edm.Guid) |
LIKE … {escape 'x'} and LIKE … ESCAPE 'x' | Escape character for % and _ in the pattern; after that, the rules for LIKE under Supported SQL apply |
{call …}, {? = call …} (procedure call) | not translated. With an address for custom statements, the statement goes to the database unchanged; SQL Server doesn't understand the braces and rejects with 42000. Write EXEC p ? |
{fn …} (scalar functions), {oj …} (outer join), and any other clause | not translated. With an address for custom statements, the statement goes to the database unchanged, and its server decides whether it understands the clause; without an address, the driver rejects it with 42000 |
A value that doesn't match the clause ({d '01.06.2023'}, {ts '06/01/2023'}) makes the statement untranslatable: with
an address for custom statements, it goes to the database unchanged; without one, it's rejected with
42000 and a message that names the clause and the value. The driver doesn't interpret the value
itself, because a guessed interpretation – day or month first – would produce a different set of rows
without any notice.
GUID as text. Where a text stands for a GUID column – as a string in the statement or as a bound
text value – the driver accepts the plain form, the form with braces, and the form that Access writes
itself ({guid {33333333-…}}). A bound value can also be a GUID (SQL_C_GUID) or 16 bytes
(SQL_C_BINARY); that's how Access binds the key of a linked table when reloading, changing, and
deleting.
Data types
The service describes each column with an OData type (Edm.…) and, where it offers it, with the
database type name (Store.Type, see
Requirements for an OData service). From this, the driver reports
an ODBC SQL type, a length, and a type name to the application:
| Service type | SQL type (ODBC 3) | Type name | Length or precision |
|---|---|---|---|
Edm.String with length up to 4,000 | SQL_WVARCHAR | nvarchar | length from the service |
Edm.String with length up to 4,000 and Store.Type char or nchar | SQL_WCHAR | nchar | length from the service |
Edm.String without length or longer | SQL_WLONGVARCHAR | ntext; with Store.Type nvarchar/varchar (that is, (max)) nvarchar | 1 073 741 823 |
Edm.Boolean | SQL_BIT | bit | 1 |
Edm.Byte | SQL_TINYINT | tinyint | 3 |
Edm.SByte, Edm.Int16 | SQL_SMALLINT | smallint | 5 |
Edm.Int32 | SQL_INTEGER | int | 10 |
Edm.Int64 | SQL_BIGINT | bigint | 19 |
Edm.Decimal | SQL_DECIMAL; with Store.Type numeric SQL_NUMERIC | decimal or numeric; with Store.Type money/smallmoney that name | precision and scale from the service, if unspecified 38 and 0 |
Edm.Single | SQL_REAL | real | 24 bits |
Edm.Double | SQL_DOUBLE; with Store.Type float SQL_FLOAT | float | 53 bits |
Edm.Date | SQL_TYPE_DATE | date | 10 |
Edm.TimeOfDay | SQL_TYPE_TIME | time | 8 or 9 + fractional digits |
Edm.DateTimeOffset | SQL_TYPE_TIMESTAMP | datetime; with Store.Type datetime2 or smalldatetime that name | 19 or 20 + fractional digits (default 3, at most 7); smalldatetime 16 |
Edm.Guid | SQL_GUID | uniqueidentifier | 36 |
Edm.Binary with length up to 8,000 | SQL_VARBINARY; with Store.Type binary SQL_BINARY | varbinary or binary | length from the service |
Edm.Binary as a row version (Store.Type timestamp/rowversion) | SQL_BINARY | timestamp | 8 |
Edm.Binary without length or longer | SQL_LONGVARBINARY | image; with Store.Type varbinary (that is, (max)) varbinary | 1 073 741 823 |
| any other type | – | – | The column isn't offered |
A column that the service numbers itself carries the type name with the suffix identity
(int identity), as with the SQL Server driver; from this, Access creates a field of type
AutoNumber.
Why it works this way. The decisions and their reasons:
| Decision | Reason |
|---|---|
Text always as Unicode (SQL_W…) | OData transmits text as a JSON string. A narrow type would force every application to convert through a code page that can't represent every character of the service |
Unlimited text as SQL_WLONGVARCHAR | Only from this does Access create a Long Text field. The SQL Server driver reports nvarchar(max) as SQL_WVARCHAR with length 0; an application that sizes buffers by the length would read 0 as “empty” |
Edm.DateTimeOffset as a timestamp without a zone, in Coordinated Universal Time | An ODBC timestamp has no zone. A service that writes a wall-clock time without a zone with Z (as the data service Wkl.Data.Gateway does) gets its stored time back unchanged; a value with a real zone is converted so that a comparison compares two points in time |
| Database type names where the ODBC type is the same | ODBC Driver 18 reports datetime2 and the (max) types with their base names. Applications show these names (ADO.NET GetDataTypeName, schema tables) |
No type names datetimeoffset, time (with fraction), and xml of the SQL Server driver | There, they come with types of their own (SQL_SS_TIMESTAMPOFFSET, SQL_SS_TIME2, SQL_SS_XML) that this driver doesn't offer; a name without its type would promise more than the value delivers |
| Floating point with precision in bits (24, 53) | That way, NUM_PREC_RADIX counts 2, as with the SQL Server driver |
ODBC 2 applications. If an application registers with ODBC 2 (SQL_ATTR_ODBC_VERSION), as Access
does, the driver names date, time, and timestamp with the ODBC 2 numbers (SQL_DATE, SQL_TIME,
SQL_TIMESTAMP). If it requests a value without a target type of its own (SQL_C_DEFAULT), the
driver returns bigint and GUID as text: ODBC 2 has no C type of its own as a default for either;
for GUID, ODBC Driver 18 behaves the same way.
Conversions. SQLGetInfo(SQL_CONVERT_FUNCTIONS) reports no conversion function; CONVERT and
CAST aren't part of the translated subset and run only through the address for custom statements. For
SQL_CONVERT_NUMERIC, it reports conversion to SQL_NUMERIC, so that ADO.NET binds decimal values
as numbers and not as text in the computer's culture.