Adding Records to a Table

< BACK  NEXT >

[oR]

So far in this chapter ADOXCE interfaces have been used to manage tables in a database. Now, ADOCE interfaces will be used to manipulate data in tables. Data is most often manipulated using recordsets, which are object models that represent an extraction of data from tables. They can also be used to add new records to tables. First, we will look at a simple case of adding new data to a table.

First, a recordset must be created and then associated with a connection to the database the recordset will be opened on. For example, the following code fragment creates an instance of a Recordset, creates a VARIANT with the connection string, and then opens the recordset on the "Customers" table. The ProgID for the recordset specifies the version number "3.1" there is no version-independent ProgID for recordsets in ADOCE.

 AdoNS::_RecordsetPtr pRecordset; hr = pRecordset.CreateInstance     (_T("ADOCE.Recordset.3.1")); _bstr_t bstrConnection(lpConnection); _variant_t varConnection(bstrConnection); _bstr_t bstrTable(_T("Customers")); _variant_t varTable(bstrTable); hr = pRecordset->Open(varTable,       varConnection,       AdoNS::adOpenDynamic,       AdoNS::adLockOptimistic,       AdoNS::adCmdTableDirect); 

The call to "Open" opens a recordset on the table "Customers". The constant AdoNS::adCmdTableDirect specifies that the "varTable" variant contain the name of a table and not a SELECT or other SQL statement. The recordset is opened with a dynamic cursor and optimistic locking this will allow records to be added.

The data to be added to a record in a table is defined in two SAFEARRAY variables. A SAFEARRAY is an n-dimensional array whose elements are accessed through functions, and these functions check that the elements being accessed are valid. One SAFEARRAY will contain the data being added to the record, and the other will define the columns in the table to which the data applies. The columns can be specified by name or index.

Table 16.2. _RecordsetPtr::Open Opens a recordset

RecordsetPtr::Open
VARIANT vtSource	Source for the recordset, such as a SQL SELECT statement or table name
VARIANT vtConnect	Variant containing a connection string or active _ConnectionPtr connection interface pointer
CursorTypeEnumCursorType	Constant indicating the type of access cursor required (see Table 16.3)
LockTypeEnumLockType	Determines what type of locking, or concurrency, should be used when updating records (see Table 16.4)
CommandEnumCommandType	Determines what type of source is specified in vtSource (See Table 16.5)
HRESULT Return Value	HRESULT indicating success or failure

Table 16.3. CursorTypeEnum cursor constants

Constant	Description
adOpenUnspecified = 21	Default cursor will be used.
adOpenForwardOnly = 0	Can only move forward through records in the recordset. In ADOCE the performance of this recordset type is identical to adOpenStatic.
adOpenKeyset = 1	Additions, changes, and deletions by other users are not visible in this recordset. All types of navigation through the recordset are allowed.
adOpenDynamic = 2	Additions, changes, and deletions by other users are visible in this recordset. All types of navigation through the recordset are allowed.
adOpenStatic = 3	This cursor creates a static copy of the records in the recordset. Additions, changes, or deletions by other users are not visible.

Table 16.4. LockTypeEnum Locking constants

Constant	Description
adLockUnspecified = 21	Default locking will be applied.
adLockReadOnly = 1	Read-only locking you cannot add, delete, or change records.
adLockPessimistic = 2	Pessimistic locking, record by record. Records are locked immediately when editing starts and unlocked when the update is completed.
adLockOptimistic = 3	Optimistic locking, record by record. Records are locked for the duration of the actual update, not when editing starts.

Table 16.5. CommandEnum Source type constants

Constant	Description
adCmdUnspecified = 21	Default type will be assumed.
adCmdText = 1	Source is a SQL statement, such as a SELECT.
adCmdTable = 2	Source refers to a table.
adCmdStoredProc = 4	Stored procedure, not supported in SQL Server for Windows CE.
adCmdUnknown = 8	Type of command is unknown; the provider will attempt to determine the source type.

The Customer table has three columns, so each of the two safe arrays should have one dimension with three elements, one for each column. The dimensions and bounds of the SafeArrays are specified using a SAFEARRAYBOUND structure for each dimension. In the following code, the SAFEARRAYBOUND structure is initialized so that the lower bound (lLBound) is 0 (that is the index for the first element in the array), and the number of elements is 3 (cElements). Two calls are then made to SafeArrayCreate to allocate memory for the arrays:

 SAFEARRAY *       pColumns    = NULL; SAFEARRAY *       pData       = NULL; SAFEARRAYBOUND bound[1]; bound[0].lLbound = 0; bound[0].cElements = 3; pColumns = SafeArrayCreate(VT_VARIANT, 1, bound); pData = SafeArrayCreate(VT_VARIANT, 1, bound); 

The first safe array, pColumns, will be used to store the indexes or names of the table columns, and pData will store the actual data. The function SafeArrayCreate is passed three arguments and returns a pointer to the new array:

• The data type of the elements in the array. In this case VT_VARIANT specifies that each element will be a variant.

• The number of dimensions in the new array, in this case 1.

• An array of SAFEARRAYBOUND structures, one for each dimension.

Elements can be placed in a safe array using the function SafeArrayPutElement. This function takes three parameters:

• Pointer to the safe array, for example, pColumns.

• Index into the array. This is a pointer to a LONG variable for a single dimension array, or a LONG array for a multidimensional safe array.

• A "void*" pointer to the data to place into the array.

The data for the columns and data safe array will always be a variant. For the columns array, the data type can be an integer (for example, VT_I2) if the column is referenced by an index, or a VT_BSTR if the column is referenced by name. For the data safe array, the variant will contain data in the appropriate type for the column. The following code places the first column name into the column's safe array:

 LONG lIndex = 0; _variant_t varColumn(_T("CustName")); SafeArrayPutElement(pColumns, &lIndex, &varColumn); 

Once the columns and data safe arrays have been initialized, the AddNew function can be called to add the record, passing the following two parameters:

• A variant referencing the safe arrays containing the column names or indexes

• A variant referencing the safe array containing the data values for the columns

The data type for passing a safe array is VT_ARRAY | VT_VARIANT, and the data member parray points at the safe array:

 _variant_t varColumns; varColumns.vt = VT_ARRAY | VT_VARIANT; varColumns.parray = pColumns; _variant_t varDataValues; varDataValues.vt = VT_ARRAY | VT_VARIANT; varDataValues.parray = pData; hr = pRecordset->AddNew(varColumns, varDataValues); 

Safe arrays must be deleted using the function SafeArrayDestroy, passing in a pointer to the safe array to delete:

 SafeArrayDestroy(pColumns); 

You should explicitly close an open recordset using the Close method, as in this example:

 pRecordset->Close(); 

The code in Listing 16.5 shows opening a recordset, setting up the safe arrays, adding the record (in function AddRecord), and then closing the recordset.

Listing 16.5 Adding records to a table

 BOOL AddRecord(AdoNS::_RecordsetPtr& pRecordset,     LPTSTR lpCustName, LONG lCustID,     LPTSTR lpCustAddr) {   HRESULT hr;   SAFEARRAYBOUND bound[1];   SAFEARRAY *      pColumns    = NULL;   SAFEARRAY *      pData       = NULL;   LONG lIndex = 0;   BOOL bRet = TRUE;   bound[0].lLbound = 0;   bound[0].cElements = 3;   pColumns = SafeArrayCreate(VT_VARIANT, 1, bound);   pData = SafeArrayCreate(VT_VARIANT, 1, bound);   if(pColumns == NULL || pData == NULL)   {     cout   _T("Could not create arrays.")   endl;     return FALSE;   }   _variant_t varColumn(_T("CustName"));   SafeArrayPutElement(pColumns, &lIndex, &varColumn);   lIndex++;   varColumn = _T("CustNum");   SafeArrayPutElement(pColumns, &lIndex, &varColumn);   lIndex++;   varColumn = _T("CustAddress");   SafeArrayPutElement(pColumns, &lIndex, &varColumn);   lIndex = 0;   _variant_t varData(lpCustName);   SafeArrayPutElement(pData, &lIndex, &varData);   lIndex++;   varData = lCustID;   SafeArrayPutElement(pData, &lIndex, &varData);   lIndex++;   varData = lpCustAddr;   SafeArrayPutElement(pData, &lIndex, &varData);   _variant_t varColumns;   varColumns.vt = VT_ARRAY | VT_VARIANT;   varColumns.parray = pColumns;   _variant_t varDataValues;   varDataValues.vt = VT_ARRAY | VT_VARIANT;   varDataValues.parray = pData;   hr = pRecordset->AddNew(varColumns, varDataValues);   if(FAILED(hr))   {      cout   _T("Could not add new record ")   endl;      bRet = FALSE;   }   if(pColumns)       SafeArrayDestroy(pColumns);   if(pData)       SafeArrayDestroy(pData);   return bRet; } void Listing16_5() {   HRESULT hr;   AdoNS::_RecordsetPtr pRecordset;   // Get the base table rowset   //   hr = pRecordset.CreateInstance         (_T("ADOCE.Recordset.3.1"));   if(FAILED(hr))   {      cout   _T("Could not create recordset:")   hr             endl;      return;   }   _bstr_t bstrConnection(lpConnection);   _variant_t varConnection(bstrConnection);   _bstr_t bstrTable(_T("Customers"));   _variant_t varTable(bstrTable);      cout   _T("About to open recordset")   endl;      hr = pRecordset->Open(varTable,                   varConnection,                   AdoNS::adOpenDynamic,                   AdoNS::adLockOptimistic,                   AdoNS::adCmdTableDirect);   if(FAILED(hr))   {       cout   _T("Could not open recordset")   endl;       return;   }   AddRecord(pRecordset, _T("Customer 1"), 1,       _T("1500 Ocean View"));   pRecordset->Close();   cout   _T("New record added")   endl; } 

< BACK  NEXT >