Creating SQL Server Tables
The SQL Server Native Client OLE DB provider exposes the ITableDefinition::CreateTable function, allowing consumers to create SQL Server tables. Consumers use CreateTable to create consumer-named permanent tables, and permanent or temporary tables with unique names generated by the SQL Server Native Client OLE DB provider.
When the consumer calls ITableDefinition::CreateTable, if the value of the DBPROP_TBL_TEMPTABLE property is VARIANT_TRUE, the SQL Server Native Client OLE DB provider generates a temporary table name for the consumer. The consumer sets the pTableID parameter of the CreateTable method to NULL. The temporary tables with names generated by the SQL Server Native Client OLE DB provider do not appear in the TABLES rowset, but are accessible through the IOpenRowset interface.
When consumers specify the table name in the pwszName member of the uName union in the pTableID parameter, the SQL Server Native Client OLE DB provider creates a SQL Server table with that name. SQL Server table naming constraints apply, and the table name can indicate a permanent table, or either a local or global temporary table. For more information, see CREATE TABLE. The ppTableID parameter can be NULL.
The SQL Server Native Client OLE DB provider can generate the names of permanent or temporary tables. When the consumer sets the pTableID parameter to NULL and sets ppTableID to point to a valid DBID*, the SQL Server Native Client OLE DB provider returns the generated name of the table in the pwszName member of the uName union of the DBID pointed to by the value of ppTableID. To create a temporary, SQL Server Native Client OLE DB provider-named table, the consumer includes the OLE DB table property DBPROP_TBL_TEMPTABLE in a table property set referenced in the rgPropertySets parameter. SQL Server Native Client OLE DB provider-named temporary tables are local.
CreateTable returns DB_E_BADTABLEID if the eKind member of the pTableID parameter does not indicate DBKIND_NAME.
DBCOLUMNDESC Usage
The consumer can indicate a column data type by using either the pwszTypeName member or the wType member. If the consumer specifies the data type in pwszTypeName, the SQL Server Native Client OLE DB provider ignores the value of wType.
If using the pwszTypeName member, the consumer specifies the data type by using SQL Server data type names. Valid data type names are those returned in the TYPE_NAME column of the PROVIDER_TYPES schema rowset.
The SQL Server Native Client OLE DB provider recognizes a subset of OLE DB-enumerated DBTYPE values in the wType member. For more information, see Data Type Mapping in ITableDefinition.
Note
CreateTable returns DB_E_BADTYPE if consumer sets either the pTypeInfo or pclsid member to specify the column data type.
The consumer specifies the column name in the pwszName member of the uName union of the DBCOLUMNDESC dbcid member. The column name is specified as a Unicode character string. The eKind member of dbcid must be DBKIND_NAME. CreateTable returns DB_E_BADCOLUMNID if eKind is invalid, pwszName is NULL, or if the value of pwszName is not a valid SQL Server identifier.
All column properties are available on all columns defined for the table. CreateTable can return DB_S_ERRORSOCCURRED or DB_E_ERRORSOCCURRED if property values are set in conflict. CreateTable returns an error when invalid column property settings cause SQL Server table-creation failure.
Column properties in a DBCOLUMNDESC are interpreted as follows.
Property ID |
Description |
|---|---|
DBPROP_COL_AUTOINCREMENT |
R/W: Read/write Default: VARIANT_FALSE Description: Sets the identity property on the column created. For SQL Server, the identity property is valid for a single column within a table. Setting the property to VARIANT_TRUE for more than a single column generates an error when the SQL Server Native Client OLE DB provider attempts to create the table on the server. The SQL Server identity property is only valid for the integer, numeric, and decimal types when the scale is 0. Setting the property to VARIANT_TRUE on a column of any other data type generates an error when the SQL Server Native Client OLE DB provider attempts to create the table on the server. The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED when DBPROP_COL_AUTOINCREMENT and DBPROP_COL_NULLABLE are both VARIANT_TRUE and the dwOption of DBPROP_COL_NULLABLE is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_AUTOINCREMENT and DBPROP_COL_NULLABLE are both VARIANT_TRUE and the dwOption of DBPROP_COL_NULLABLE equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_NULLABLE dwStatus member is set to DBPROPSTATUS_CONFLICTING. |
DBPROP_COL_DEFAULT |
R/W: Read/write Default: None Description: Creates a SQL Server DEFAULT constraint for the column. The vValue DBPROP member can be any of a number of types. The vValue.vt member should specify a type compatible with the data type of the column. For example, defining BSTR N/A as the default value for a column defined as DBTYPE_WSTR is a compatible match. Defining the same default on a column defined as DBTYPE_R8 generates an error when the SQL Server Native Client OLE DB provider attempts to create the table on the server. |
DBPROP_COL_DESCRIPTION |
R/W: Read/write Default: None Description: The DBPROP_COL_DESCRIPTION column property is not implemented by the SQL Server Native Client OLE DB provider. The dwStatus member of the DBPROP structure returns DBPROPSTATUS_NOTSUPPORTED when the consumer attempts to write the property value. Setting the property does not constitute a fatal error for the SQL Server Native Client OLE DB provider. If all other parameter values are valid, the SQL Server table is created. |
DBPROP_COL_FIXEDLENGTH |
R/W: Read/write Default: VARIANT_FALSE Description: The SQL Server Native Client OLE DB provider uses DBPROP_COL_FIXEDLENGTH to determine data type-mapping when the consumer defines a column's data type by using the wType member of the DBCOLUMNDESC. For more information, see Data Type Mapping in ITableDefinition. |
DBPROP_COL_NULLABLE |
R/W: Read/write Default: None Description: When creating the table, the SQL Server Native Client OLE DB provider indicates whether the column should accept null values if the property is set. When the property is not set, the ability of the column to accept NULL as a value is determined by the SQL Server ANSI_NULLS default database option. The SQL Server Native Client OLE DB provider is an ISO-compliant provider. Connected sessions exhibit ISO behaviors. If the consumer does not set DBPROP_COL_NULLABLE, columns accept null values. |
DBPROP_COL_PRIMARYKEY |
R/W: Read/write Default: VARIANT_FALSE Description: When VARIANT_TRUE, the SQL Server Native Client OLE DB provider creates the column with a PRIMARY KEY constraint. When defined as a column property, only a single column can determine the constraint. Setting the property VARIANT_TRUE for more than a single column returns an error when the SQL Server Native Client OLE DB provider attempts to create the SQL Server table. Note: The consumer can use IIndexDefinition::CreateIndex to create a PRIMARY KEY constraint on two or more columns. The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and the dwOption of DBPROP_COL_UNIQUE is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and the dwOption of DBPROP_COL_UNIQUE equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_PRIMARYKEY dwStatus member is set to DBPROPSTATUS_CONFLICTING. The SQL Server Native Client OLE DB provider returns an error when DBPROP_COL_PRIMARYKEY and DBPROP_COL_NULLABLE are both VARIANT_TRUE. The SQL Server Native Client OLE DB provider returns an error from SQL Server when the consumer attempts to create a PRIMARY KEY constraint on a column of invalid SQL Server data type. PRIMARY KEY constraints cannot be defined on columns created with the SQL Server data types bit, text, ntext, and image. |
DBPROP_COL_UNIQUE |
R/W: Read/write Default: VARIANT_FALSE Description: Applies a SQL Server UNIQUE constraint to the column. When defined as a column property, the constraint is applied on a single column only. The consumer can use IIndexDefinition::CreateIndex to apply a UNIQUE constraint on the combined values of two or more columns. The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_PRIMARYKEY dwStatus member is set to DBPROPSTATUS_CONFLICTING. The SQL Server Native Client OLE DB provider returns DB_S_ERRORSOCCURRED when DBPROP_COL_NULLABLE and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_NULLABLE and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_NULLABLE dwStatus member is set to DBPROPSTATUS_CONFLICTING. The SQL Server Native Client OLE DB provider returns an error from SQL Server when the consumer attempts to create a UNIQUE constraint on a column of invalid SQL Server data type. UNIQUE constraints cannot be defined on columns created with the SQL Server bit data type. |
When the consumer calls ITableDefinition::CreateTable, the SQL Server Native Client OLE DB provider interprets table properties as follows.
Property ID |
Description |
|---|---|
DBPROP_TBL_TEMPTABLE |
R/W: Read/write Default: VARIANT_FALSE Description: By default, the SQL Server Native Client OLE DB provider creates tables named by the consumer. When VARIANT_TRUE, The SQL Server Native Client OLE DB provider generates a temporary table name for the consumer. The consumer sets the pTableID parameter of CreateTable to NULL. The ppTableID parameter must contain a valid pointer. |
If the consumer requests that a rowset be opened on a successfully created table, the SQL Server Native Client OLE DB provider opens a cursor-supported rowset. Any rowset properties can be indicated in the property sets passed.
This example creates a SQL Server table.
// This CREATE TABLE statement shows the details of the table created by
// the following example code.
//
// CREATE TABLE OrderDetails
// (
// OrderID int NOT NULL
// ProductID int NOT NULL
// CONSTRAINT PK_OrderDetails
// PRIMARY KEY CLUSTERED (OrderID, ProductID),
// UnitPrice money NOT NULL,
// Quantity int NOT NULL,
// Discount decimal(2,2) NOT NULL
// DEFAULT 0
// )
//
// The PRIMARY KEY constraint is created in an additional example.
HRESULT CreateTable
(
ITableDefinition* pITableDefinition
)
{
DBID dbidTable;
const ULONG nCols = 5;
ULONG nCol;
ULONG nProp;
DBCOLUMNDESC dbcoldesc[nCols];
HRESULT hr;
// Set up column descriptions. First, set default property values for
// the columns.
for (nCol = 0; nCol < nCols; nCol++)
{
dbcoldesc[nCol].pwszTypeName = NULL;
dbcoldesc[nCol].pTypeInfo = NULL;
dbcoldesc[nCol].rgPropertySets = new DBPROPSET;
dbcoldesc[nCol].pclsid = NULL;
dbcoldesc[nCol].cPropertySets = 1;
dbcoldesc[nCol].ulColumnSize = 0;
dbcoldesc[nCol].dbcid.eKind = DBKIND_NAME;
dbcoldesc[nCol].wType = DBTYPE_I4;
dbcoldesc[nCol].bPrecision = 0;
dbcoldesc[nCol].bScale = 0;
dbcoldesc[nCol].rgPropertySets[0].rgProperties =
new DBPROP[NCOLPROPS_MAX];
dbcoldesc[nCol].rgPropertySets[0].cProperties = NCOLPROPS_MAX;
dbcoldesc[nCol].rgPropertySets[0].guidPropertySet =
DBPROPSET_COLUMN;
for (nProp = 0; nProp < NCOLPROPS_MAX; nProp++)
{
dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].
dwOptions = DBPROPOPTIONS_REQUIRED;
dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].colid
= DB_NULLID;
VariantInit(
&(dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].
vValue));
dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].
vValue.vt = VT_BOOL;
}
}
// Set the column-specific information.
dbcoldesc[0].dbcid.uName.pwszName = L"OrderID";
dbcoldesc[0].rgPropertySets[0].rgProperties[0].dwPropertyID =
DBPROP_COL_NULLABLE;
dbcoldesc[0].rgPropertySets[0].rgProperties[0].vValue.boolVal =
VARIANT_FALSE;
dbcoldesc[0].rgPropertySets[0].cProperties = 1;
dbcoldesc[1].dbcid.uName.pwszName = L"ProductID";
dbcoldesc[1].rgPropertySets[0].rgProperties[0].dwPropertyID =
DBPROP_COL_NULLABLE;
dbcoldesc[1].rgPropertySets[0].rgProperties[0].vValue.boolVal =
VARIANT_FALSE;
dbcoldesc[1].rgPropertySets[0].cProperties = 1;
dbcoldesc[2].dbcid.uName.pwszName = L"UnitPrice";
dbcoldesc[2].wType = DBTYPE_CY;
dbcoldesc[2].rgPropertySets[0].rgProperties[0].dwPropertyID =
DBPROP_COL_NULLABLE;
dbcoldesc[2].rgPropertySets[0].rgProperties[0].vValue.boolVal =
VARIANT_FALSE;
dbcoldesc[2].rgPropertySets[0].cProperties = 1;
dbcoldesc[3].dbcid.uName.pwszName = L"Quantity";
dbcoldesc[3].rgPropertySets[0].rgProperties[0].dwPropertyID =
DBPROP_COL_NULLABLE;
dbcoldesc[3].rgPropertySets[0].rgProperties[0].vValue.boolVal =
VARIANT_FALSE;
dbcoldesc[3].rgPropertySets[0].cProperties = 1;
dbcoldesc[4].dbcid.uName.pwszName = L"Discount";
dbcoldesc[4].wType = DBTYPE_NUMERIC;
dbcoldesc[4].bPrecision = 2;
dbcoldesc[4].bScale = 2;
dbcoldesc[4].rgPropertySets[0].rgProperties[0].dwPropertyID =
DBPROP_COL_NULLABLE;
dbcoldesc[4].rgPropertySets[0].rgProperties[0].vValue.boolVal =
VARIANT_FALSE;
dbcoldesc[4].rgPropertySets[0].rgProperties[1].dwPropertyID =
DBPROP_COL_DEFAULT;
dbcoldesc[4].rgPropertySets[0].rgProperties[1].vValue.vt = VT_BSTR;
dbcoldesc[4].rgPropertySets[0].rgProperties[1].vValue.bstrVal =
SysAllocString(L"0");
dbcoldesc[4].rgPropertySets[0].cProperties = 2;
// Set up the dbid for OrderDetails.
dbidTable.eKind = DBKIND_NAME;
dbidTable.uName.pwszName = L"OrderDetails";
if (FAILED(hr = pITableDefinition->CreateTable(NULL, &dbidTable,
nCols, dbcoldesc, NULL, 0, NULL, NULL, NULL)))
{
DumpError(pITableDefinition, IID_ITableDefinition);
goto SAFE_EXIT;
}
SAFE_EXIT:
// Clean up dynamic allocation in the property sets.
for (nCol = 0; nCol < nCols; nCol++)
{
for (nProp = 0; nProp < NCOLPROPS_MAX; nProp++)
{
if (dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].
vValue.vt == VT_BSTR)
{
SysFreeString(dbcoldesc[nCol].rgPropertySets[0].
rgProperties[nProp].vValue.bstrVal);
}
}
delete [] dbcoldesc[nCol].rgPropertySets[0].rgProperties;
delete [] dbcoldesc[nCol].rgPropertySets;
}
return (hr);
}