Creating a Catalog (Database)

< BACK  NEXT >

[oR]

The first task you will need to complete on a SQL Server for Windows CE installation is creating a database, or catalog, as it is known by ADOXCE. You will need to define a connection string that defines the OLEDB provider used to access SQL Server for Windows CE (for example, "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;") and the name of the database to be created (for example, "DataSource=\eVCAdo.db"). Note that in this case the database "eVCAdo.db" will be created in the root folder of the object store you would probably want to change its location to another folder in your applications.

A Catalog interface can be obtained by creating an "ADOXCE.Catalog" component through a "AdoXNS::_CatalogPtr" smart pointer with the CreateInstance function:

 hr = pCatalog.CreateInstance(_T("ADOXCE.Catalog")); 

The actual catalog (that is, database) is created with the "Create" method. This is passed a variant variable that contains a BSTR with the connection string specifying the name of the database and the provider, as in the following example:

 varConnection = pCatalog->Create(bstrConnection); 

The function Create returns a VARIANT whose vt type should be VT_ DISPATCH. The ppdispVal union member points to a Connection interface, described later. If the call fails, the _com_issue_errorex function shown above will display the HRESULT error. The code in Listing 16.1 shows the complete code required to create a catalog.

Listing 16.1 Creating a catalog (database)

 const LPTSTR lpConnection =   _T("Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data\ Source=\\eVCADO.db"); void Listing16_1() {   AdoXNS::_CatalogPtr  pCatalog;   HRESULT hr;   hr = pCatalog.CreateInstance(_T("ADOXCE.Catalog.3.1"));   if(FAILED(hr))   {       cout   _T("Could not create catalog object")              endl;       return;   }   _bstr_t bstrConnection(lpConnection);   _variant_t varConnection;   varConnection = pCatalog->Create(bstrConnection);   if(varConnection.vt != VT_DISPATCH)   {       cout   _T("Could not create catalog")   endl;       return;       }       cout   _T("Database (Catalog) created")   endl;   } 

The _CatalogPtr template class provides the following functions with error handling:

 TablesPtr GetTables ( ); _variant_t GetActiveConnection ( ); void PutActiveConnection (const _variant_t & pVal ); void PutRefActiveConnection (IDispatch * pVal ); _variant_t Create (_bstr_t ConnectString ); 

The tasks performed by these functions are the following:

• GetTables returns a TablesPtr class object that is a collection of all the tables in the catalog.

• GetActiveConnection returns a _ConnectionPtr that represents the connection used to the catalog.

• PutActiveConnection and PutRefActiveConnection allow you to set the connection used for the catalog object. PutActiveConnection is passed a VARIANT containing the connection string, while PutRefActiveConnection is passed the _ConnectionPtr object directly.

Opening a Database (Catalog)

A database can be opened so that objects (such as tables) can be manipulated. To do this, a _CatalogPtr interface must be obtained by calling CreateInstance, using the ProgID"ADOXCE.Catalog.3.1", and then PutActiveConnection function is passed the connection string representing the catalog to use. The following code shows a function that is passed a connection string (such as lpConnection in Listing 16.1), creates a _CatalogPtr interface, and sets the connection string. The _CatalogPtr interface, after calling this function, can be used to access the objects (such as tables) in the database.

 BOOL OpenCatalog(LPTSTR lpConnection,     AdoXNS::_CatalogPtr &pCatalog) {   HRESULT hr;   hr = pCatalog.CreateInstance(_T("ADOXCE.Catalog.3.1"));   if(FAILED(hr))   {     cout   _T("Could not create catalog object")            endl;     return FALSE;   }   _bstr_t bstrConnection(lpConnection);   _variant_t varConnection(bstrConnection);   pCatalog->PutActiveConnection(varConnection);   return TRUE; } 

Creating a Table

The first stage to creating a table in a database is to obtain a _CatalogPtr pointer using the OpenCatalog function described in the previous section. Next, you will need to create a new _TablePtr object that represents the new table:

 AdoXNS::_CatalogPtr    pCatalog; AdoXNS::_TablePtr     pTable; if(!OpenCatalog(lpConnection, pCatalog))     return; hr = pTable.CreateInstance(T("ADOXCE.Table.3.1")); 

The table should be named using the "Name"_TablePtr function. This function takes a single BSTR parameter containing the name of the new table ("Customers"). The GetColumns function can then be used to obtain a _ColumnPtr interface representing the columns in this new table. The collection will initially be empty.

 AdoXNS::_ColumnPtr  pColumn; _bstr_t bstrTableName(_T("Customers")); pTable->Name = bstrTableName; pColumns = pTable->GetColumns(); 

New columns are added using the ColumnsPtr interface function "Append", which requires three parameters:

• The name of the column

• The data type of the column (for example, adVarWChar for variable length Unicode strings)

• Maximum length of the column in bytes

The following code adds a new variable-length Unicode string column called 'Col1' that can store up to 50 Unicode characters:

 _bstr_t bstrColumn(_T("Col1")); _variant_t varColumn(bstrColumn); hr = pColumns->Append(varColumn,    AdoXNS::adVarWChar, 100); 

Once the columns have been defined, a TablesPtr interface is obtained from the _CatalogPtr interface using GetTables, and the table is appended to the TablesPtr collection using the Append function. The table is passed to Append as a VARIANT, with the vt member being set to VT_DISPATCH and the ppdispVal union member containing a pointer to the _TablePtr interface. Note that, in the following code, the pTable interface pointer is cast to IDispatch to select the correct _variant_t constructor. By default _variant_t would create a VT_IUNKNOWN variant, and this would cause Append to fail.

 AdoXNS::TablesPtr  pTables; pTables = pCatalog->GetTables(); _variant_t varTable((IDispatch*)pTable); hr = pTables->Append(varTable); 

Listing 16.2 shows the complete code for opening a catalog and creating a new table called "Customers" with three new fields. The function AddColumn extracts out the code to append new columns to the ColumnsPtr collection.

Listing 16.2 Creating a table

 BOOL AddColumn(AdoXNS::ColumnsPtr& pColumns,     LPTSTR lpColName,     AdoXNS::DataTypeEnum dt, LONG lSize) {   HRESULT hr;   _bstr_t bstrColumn(lpColName);   _variant_t varColumn(bstrColumn);   hr = pColumns->Append(varColumn, dt, lSize);   if(FAILED(hr))   {     cout   _T("Could not append column:")            lpColName   endl;     return FALSE;   }   return TRUE; } void Listing16_2() {   AdoXNS::_CatalogPtr     pCatalog;   AdoXNS::_TablePtr       pTable;   AdoXNS::ColumnsPtr      pColumns;   AdoXNS::_ColumnPtr      pColumn;   AdoXNS::TablesPtr       pTables;   HRESULT hr;   if(!OpenCatalog(lpConnection, pCatalog))     return;   hr = pTable.CreateInstance(_T("ADOXCE.Table.3.1"));   if(FAILED(hr))   {       cout   _T("Could not create table object")              endl;       return;   }   // Create the table   _bstr_t bstrTableName(_T("Customers"));   pTable->Name = bstrTableName;   // Retrieve the pointer to the column   // collection from the table object   pColumns = pTable->GetColumns();   // Append the columns   if(!AddColumn(pColumns, _T("CustName"),           AdoXNS::adVarWChar, 50))       return;   if(!AddColumn(pColumns, _T("CustNum"),         AdoXNS::adInteger, 4))       return;   if(!AddColumn(pColumns, _T("CustAddress"),           AdoXNS::adVarWChar, 1000))       return;   // Get a pointer to the tables collection   pTables = pCatalog->GetTables();   // Add the table to the DB. Need to ensure that   // the variant is VT_DISPATCH and not VT_IUNKNOWN   _variant_t varTable((IDispatch*)pTable);   hr = pTables->Append(varTable);   if(FAILED(hr))   {       cout   _T("Could not append table")   endl;       return;   }   cout   _T("Created")   endl; } 

< BACK  NEXT >