Export (0) Print
Expand All

Creating SQL Server Tables

The SQL 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 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 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 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 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 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 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 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 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.

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 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 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.

ms131327.note(en-US,SQL.90).gifNote:
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 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 Native Client OLE DB provider attempts to create the table on the server.

The SQL 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 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 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 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 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 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 Native Client OLE DB provider is an SQL-92 compliant provider. Connected sessions exhibit SQL-92 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 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 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 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 Native Client OLE DB provider returns an error when DBPROP_COL_PRIMARYKEY and DBPROP_COL_NULLABLE are both VARIANT_TRUE.

The SQL 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 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 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 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 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 Native Client OLE DB provider creates tables named by the consumer. When VARIANT_TRUE, The SQL 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 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);
    }

Community Additions

ADD
Show:
© 2014 Microsoft