| title | Parameter and rowset metadata (Native Client OLE DB provider) | |
|---|---|---|
| description | Metadata - Parameter and Rowset in SQL Server Native Client | |
| author | markingmyname | |
| ms.author | maghan | |
| ms.date | 03/14/2017 | |
| ms.service | sql | |
| ms.topic | reference | |
| helpviewer_keywords |
|
[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]
This topic provides information about the following type and type members, related to the OLE DB date and time enhancements.
-
DBBINDING structure
-
ICommandWithParameters::GetParameterInfo
-
ICommandWithParameters::SetParameterInfo
-
IColumnsRowset::GetColumnsRowset
-
IColumnsInfo::GetColumnInfo
The following information is returned in the DBPARAMINFO structure through prgParamInfo:
| Parameter Type | wType | ulParamSize | bPrecision | bScale | dwFlags DBPARAMFLAGS_SS_ISVARIABLESCALE |
|---|---|---|---|---|---|
| date | DBTYPE_DBDATE | 6 | 10 | 0 | Clear |
| time | DBTYPE_DBTIME2 | 10 | 8, 10..16 | 0..7 | Set |
| smalldatetime | DBTYPE_DBTIMESTAMP | 16 | 16 | 0 | Clear |
| datetime | DBTYPE_DBTIMESTAMP | 16 | 23 | 3 | Clear |
| datetime2 | DBTYPE_DBTIMESTAMP | 16 | 19,21..27 | 0..7 | Set |
| datetimeoffset | DBTYPE_DBTIMESTAMPOFFSET | 20 | 26,28..34 | 0..7 | Set |
Notice that in some cases value ranges are not continuous. This is due to the addition of a decimal point when fractional precision is greater than zero.
DBPARAMFLAGS_SS_ISVARIABLESCALE is only valid when connected to a [!INCLUDEsql2008-md] (or later) server. DBPARAMFLAGS_SS_ISVARIABLESCALE is never set when connected to down-level servers.
The information provided in the DBPARAMBINDINFO structure must conform to the following:
| pwszDataSourceType (provider specific) |
pwszDataSourceType (OLE DB generic) |
ulParamSize | bScale |
|---|---|---|---|
| DBTYPE_DATE | 6 | Ignored | |
| date | DBTYPE_DBDATE | 6 | Ignored |
| DBTYPE_DBTIME | 10 | Ignored | |
| time | DBTYPE_DBTIME2 | 10 | 0..7 |
| smalldatetime | 16 | Ignored | |
| datetime | 16 | Ignored | |
| datetime2 or DBTYPE_DBTIMESTAMP | DBTYPE_DBTIMESTAMP | 16 | 0..7 |
| datetimeoffset | DBTYPE_DBTIMESTAMPOFFSET | 20 | 0..7 |
The bPrecision parameter is ignored.
"DBPARAMFLAGS_SS_ISVARIABLESCALE" is ignored when sending data to the server. Applications can force the use of legacy tabular-data stream (TDS) types by using the provider-specific type names "datetime" and "smalldatetime". When connected to [!INCLUDEsql2008-md] (or later) servers, "datetime2" format will be used and an implicit server conversion will occur, if necessary, when the type name is "datetime2" or "DBTYPE_DBTIMESTAMP". bScale is ignored if the provider specific type names "datetime" or "smalldatetime" are used. Otherwise, applications must ensure that bScale is set correctly. Applications upgraded from MDAC and [!INCLUDEssNoVersion] Native Client from [!INCLUDEssVersion2005] that use "DBTYPE_DBTIMESTAMP" will fail if they do not set bScale correctly. When connected to server instances earlier than [!INCLUDEsql2008-md], a bScale value other than 0 or 3 with "DBTYPE_DBTIMESTAMP" is an error and E_FAIL will be returned.
When ICommandWithParameters::SetParameterInfo is not called, the provider implies the server type from the binding type as specified in IAccessor::CreateAccessor as follows:
| Binding Type | pwszDataSourceType (provider specific) |
|---|---|
| DBTYPE_DATE | datetime2(0) |
| DBTYPE_DBDATE | date |
| DBTYPE_DBTIME | time(0) |
| DBTYPE_DBTIME2 | time(7) |
| DBTYPE_DBTIMESTAMP | datetime2(7) |
| DBTYPE_DBTIMESTAMPOFFSET | datetimeoffset(7) |
IColumnsRowset::GetColumnsRowset returns the following columns:
| Column Type | DBCOLUMN_TYPE | DBCOLUM_COLUMNSIZE | DBCOLUMN_PRECISION | DBCOLUMN_SCALE, DBCOLUMN_DATETIMEPRECISION | DBCOLUMN_FLAGS, DBCOLUMNFLAGS_SS_ISVARIABLESCALE |
|---|---|---|---|---|---|
| date | DBTYPE_DBDATE | 6 | 10 | 0 | Clear |
| time | DBTYPE_DBTIME2 | 10 | 8, 10..16 | 0..7 | Set |
| smalldatetime | DBTYPE_DBTIMESTAMP | 16 | 16 | 0 | Clear |
| datetime | DBTYPE_DBTIMESTAMP | 16 | 23 | 3 | Clear |
| datetime2 | DBTYPE_DBTIMESTAMP | 16 | 19, 21..27 | 0..7 | Set |
| datetimeoffset | DBTYPE_DBTIMESTAMPOFFSET | 20 | 26, 28..34 | 0..7 | Set |
In DBCOLUMN_FLAGS, DBCOLUMNFLAGS_ISFIXEDLENGTH is always true for date/time types and the following flags are always false:
-
DBCOLUMNFLAGS_CACHEDEFERRED
-
DBCOLUMNFLAGS_ISBOOKMARK
-
DBCOLUMNFLAGS_ISCHAPTER
-
DBCOLUMNFLAGS_ISLONG
-
DBCOLUMNFLAGS_ISROWID
-
DBCOLUMNFLAGS_ISROWVER
-
DBCOLUMNFLAGS_MAYDEFER
The remaining flags (DBCOLUMNFLAGS_ISNULLABLE, DBCOLUMNFLAGS_MAYBENULL, DBCOLUMNFLAGS_WRITE, and DBCOLUMNFLAGS_WRITEUNKNOWN) can be set, depending on how the column is defined and the actual query.
A new flag DBCOLUMNFLAGS_SS_ISVARIABLESCALE is provided in DBCOLUMN_FLAGS to allow an application to determine the server type of columns, where DBCOLUMN_TYPE is DBTYPE_DBTIMESTAMP. DBCOLUMN_SCALE or DBCOLUMN_DATETIMEPRECISION must also be used to identify the server type.
DBCOLUMNFLAGS_SS_ISVARIABLESCALE is only valid when connected to a [!INCLUDEsql2008-md] (or later) server. DBCOLUMNFLAGS_SS_ISVARIABLESCALE is undefined when connected to down-level servers.
DBCOLUMNINFO structure returns the following information:
| Parameter Type | wType | ulColumnSize | bPrecision | bScale | dwFlags DBPARAMFLAGS_SS_ISVARIABLESCALE |
|---|---|---|---|---|---|
| date | DBTYPE_DBDATE | 6 | 10 | 0 | Clear |
| time(1..7) | DBTYPE_DBTIME2 | 10 | 8, 10..16 | 0..7 | Set |
| smalldatetime | DBTYPE_DBTIMESTAMP | 16 | 16 | 0 | Clear |
| datetime | DBTYPE_DBTIMESTAMP | 16 | 23 | 3 | Clear |
| datetime2 | DBTYPE_DBTIMESTAMP | 16 | 19, 21..27 | 0..7 | Set |
| datetimeoffset | DBTYPE_DBTIMESTAMPOFFSET | 20 | 26, 28..34 | 0..7 | Set |
In dwFlags, DBCOLUMNFLAGS_ISFIXEDLENGTH is always true for date/time types and the following flags are always false:
-
DBCOLUMNFLAGS_CACHEDEFERRED
-
DBCOLUMNFLAGS_ISBOOKMARK
-
DBCOLUMNFLAGS_ISCHAPTER
-
DBCOLUMNFLAGS_ISLONG
-
DBCOLUMNFLAGS_ISROWID
-
DBCOLUMNFLAGS_ISROWVER, MAYDEFER
The remaining flags (DBCOLUMNFLAGS_ISNULLABLE, DBCOLUMNFLAGS_MAYBENULL, DBCOLUMNFLAGS_WRITE, and DBCOLUMNFLAGS_WRITEUNKNOWN) can be set.
A new flag DBCOLUMNFLAGS_SS_ISVARIABLESCALE is provided in dwFlags to allow an application to determine the server type of columns, where wType is DBTYPE_DBTIMESTAMP. bScale must also be used to identify the server type.