Retrieving Records from a Table

< BACK  NEXT >

[oR]

Records can be retrieved from a table through a recordset. The recordset can either be based directly on the table itself (as in Listing 16.5) or based on a SQL SELECT statement (such as "SELECT * FROM Customers"). In the following code a recordset is opened based on a SQL SELECT statement using the constant AdoNS::adCmdText. The recordset is opened read-only (AdoNS::adLockReadOnly) since the data will not be updated and this is more efficient. The connection string is specified in the same way as Listing 16.5.

 _bstr_t bstrQuery(_T("Select * from Customers")); _variant_t varQuery(bstrQuery); hr = pRecordset->Open(varQuery,         varConnection,         AdoNS::adOpenStatic,         AdoNS::adLockReadOnly,         AdoNS::adCmdText); 

A recordset has a "current record" reference or, if there is no current record, the recordset will be at End of File (EOF) or Beginning of File (BOF). The function GetA_EOF will return true if the current record points beyond the end of file or if there are no records in the recordset. Note that the function is called GetA_EOF rather than GetEOF since EOF was renamed A_EOF in the type library import statement. The function GetBOF returns true if the current record is before the first record or if there are no records in the recordset.

The following functions can be used to navigate through the records in the recordset:

• MoveFirst Move to the first record in the recordset.

• MoveLast Move to the last record in the recordset.

• MoveNext Move to the next record in the recordset.

• MovePrevious Move to the previous record in the recordset.

You can use code like the following to navigate through each record in the recordset:

 while(!pRecordset->GetA_EOF()) {   // Do something with the current record...   pRecordset->MoveNext(); } 

The _RecordsetPtr::GetFields() function returns a pFields collection of field interfaces for the current record:

 AdoNS::FieldsPtr pFields; pFields = pRecordset->GetFields(); 

The fields collection can be enumerated to retrieve data and other field information associated with the columns in the recordset. The FieldsPtr:: GetItem function returns a FieldPtr interface for a column specified either by name or index. This function is passed a variant that contains a BSTR with the name of the column, or an integer containing the index number of the column. In the following code, a FieldPtr interface pointer is returned for the column called 'CustName'.

 AdoNS::FieldPtr pField; _variant_t vValue; _bstr_t bstrIndex(_T("CustName")); _variant_t varIndex(bstrIndex); pField = pFields->GetItem(varIndex); 

The FieldPtr::GetValue() function returns a variant containing the data associated with the field. You can use the vt structure member to determine the data type. However, in most cases you will know the data type of the field and can access the appropriate variant data union member. This code displays the value associated with the CustName column:

 vValue = pField->GetValue(); cout   _T("Customer: ")   vValue.bstrVal; 

The code in Listing 16.6 displays all the records in the Customer table byopening a recordset based on the SQL statement "SELECT * FROM Customers".

Listing 16.6 Retrieving records from a table

 void DisplayFields(AdoNS::FieldsPtr & pFields) {   AdoNS::FieldPtr pField;   _variant_t vValue;   _bstr_t bstrIndex(_T("CustName"));   _variant_t varIndex(bstrIndex);   pField = pFields->GetItem(varIndex);   vValue = pField->GetValue();   cout   _T("Customer: ")   vValue.bstrVal;   bstrIndex = _T("CustNum");   varIndex = bstrIndex;   pField = pFields->GetItem(varIndex);   vValue = pField->GetValue();   cout   _T(" Num: ")   vValue.lVal;   bstrIndex = _T("CustAddress");   varIndex = bstrIndex;   pField = pFields->GetItem(varIndex);   vValue = pField->GetValue();   cout   _T(" Addr: ")   vValue.bstrVal < endl; } void Listing16_6() {   HRESULT hr;   AdoNS::_RecordsetPtr pRecordset;   _bstr_t bstrConnection(lpConnection);   _variant_t varConnection(bstrConnection);   _bstr_t bstrQuery(_T("Select * from Customers"));   _variant_t varQuery(bstrQuery);   hr = pRecordset.CreateInstance         (_T("ADOCE.Recordset.3.1"));   if(FAILED(hr))   {      cout   _T("Could not create recordset:")             hr   endl;      return;   }   // Open the base table and retrieve rows   //   cout   _T("About to open recordset")   endl;   hr = pRecordset->Open(varQuery,           varConnection,           AdoNS::adOpenStatic,           AdoNS::adLockReadOnly,           AdoNS::adCmdText);   if(FAILED(hr))   {     cout   _T("Could not open recordset")   endl;     return;   }   while(!pRecordset->GetA_EOF())   {     AdoNS::FieldsPtr pFields;     pFields = pRecordset->GetFields();     DisplayFields(pFields);     pRecordset->MoveNext();   }   pRecordset->Close(); } 

< BACK  NEXT >