MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for Microsoft Jet
Textual columns in Jet are now UNICODE instead of ANSI/DBCS. Compression is used to keep text from taking twice as much space. All IISAMs that store text columns in DBCS have implicit conversions done when read through Jet 4.0. When running against the Jet 3.5 OLE DB provider, all API calls had implicit coercions done already and it was possible to read ANSI data as UNICODE through the regular coercion process. Now, in a similar fashion, applications that need compatibility can use the coercion service in the OLE DB layer to convert back to ANSI if necessary. Not all ANSI/UNICODE conversions are lossless—converting from one to the other and back again can yield different results.
New data types have been added to Microsoft Jet 4.0. Specifically, Jet now has a numeric type for arbitrary precision data and a large, non-BLOB binary type. These are named Decimal and BigBinary, respectively. Information about these types is published in the PROVIDER_TYPES schema. These types do not work on previous .mdb file formats. The following table maps Jet data types to OLE DB data types.
| 4.0 Jet type name | OLE DB DBTYPE | Maximum size (4.0) | 3.5 type name |
| Bit | DBTYPE_BOOL | Bit | |
| BigBinary | DBTYPE_BYTES | 4000 bytes | n/a |
| Byte | DBTYPE_UI1 | Byte | |
| Currency | DBTYPE_CY | Currency | |
| DateTime | DBTYPE_DATE | DateTime | |
| Decimal | DBTYPE_NUMERIC | 28 digits (base 10) | n/a |
| Double | DBTYPE_R8 | Double | |
| GUID | DBTYPE_GUID | GUID | |
| Long | DBTYPE_I4 | Long | |
| LongBinary | DBTYPE_BYTES | 1073741823 bytes | LongBinary |
| LongText | DBTYPE_WSTR | 536870910 characters | LongText |
| Short | DBTYPE_I2 | Short | |
| Single | DBTYPE_R4 | Single | |
| VarBinary | DBTYPE_BYTES | 510 bytes | Binary |
| VarChar | DBTYPE_WSTR | 255 characters | Text |
To provide a more consistent data type naming scheme for Microsoft providers, Jet has modified the Text data type slightly. While the underlying storage has remained the same, the implications of the name text as used in SQL commands have changed. While Text in Jet referred to a short type, text in SQL Server is a BLOB field (akin to the Jet LongText/Memo type). When using commands, Text with no size now implies a LongText, while Text with a size (text(255)) still means the shorter, non-BLOB text field. The SQL data type varchar retains the original meaning of Text in Jet, so the OLE DB provider for Jet now exposes Jet's short text field as varchar instead of Text, because this behavior cannot be described in a generic fashion through OLE DB mechanisms. Applications that assume they can pass the provider type name as Text will break because the definition of Text is now ambiguous. As such, it doesn't resolve to anything in the version 4.0 provider.
To better comply with the OLE DB spec, the version 4.0 provider has also changed the name of the Binary column. The semantic meaning of Binary is unchanged, however, and it is still recognized in the version 4.0 provider as the short binary type. This change should be transparent to existing clients. New clients should use VarBinary in their applications.