Teach Yourself Database Programming
with Visual C++ 6 in 21 days

Appendix F

Answers

Day 1, "Choosing the Right Database Technology"

Quiz

  1. What are the primary benefits of using a record manager (such as Btrieve) rather than inventing your own database routines?
    If you use a record manager (such as Btrieve), you don't have to build the code for navigation, searching, indexing, and locking for multiple users.
  2. What do the desktop databases provide that record managers do not?
    Desktop databases provide data files that contain metadata, so the data files are self-describing. Record managers do not.
  3. What are the benefits of using a database technology that provides open, accessible data stores?
    If you use a database technology that provides open, accessible data stores, your application will live longer. Also, in the future, you will not be derided by your customers and other developers for your lack of vision in building an inaccessible database with your application.
  4. Which database technologies provide open, accessible data stores?
    The database technologies that provide open, accessible databases are desktop databases and relational database servers.
  5. What is the significance of server-side processing of set-based operations in a client/server architecture?
    Server-side processing of set-based operations means less network traffic and greater scalability of your application.

Exercises

  1. The code in Listing 1.1 creates a data file that contains order information. Write a program that reads the order data from that file.
    The code below opens the data.dat file and reads the date and product. It then sends the product name to the display. 
ifstream is( "data.dat", ios::binary | ios::nocreate );
   if( is )
   { 
      is.read( (char *) &dt, sizeof( dt ) );
      is.read( (char *) &prod, sizeof( prod ) );
      cout << prod.szName  << endl;
   }
   else
   {
      cout << "ERROR: Cannot open file 'data.dat'." << endl;
   }
)

  1. Decide which database technology would be most appropriate for the sample application described earlier. Create a list of capabilities that the database for this application needs to provide. Justify your decision by comparing the database requirements with the capabilities of the database technology that you have chosen.
    The database for this application needs to work well in a LAN environment, provide multiple users with simultaneous access to the data, give good performance so that people don't have to wait long when placing their orders, and use an open database format so that the data can be analyzed by managers. Also, of course, you don't want to spend a long time just building the database. A desktop database such as Access fills most requirements, except perhaps in the area of performance. If the number of users (the sales reps who take the phone calls) is fewer than five or six, Access's performance will probably be adequate. If the number of users is more than five or six, a relational database server is the technology that will fill all the requirements.

Day 2, "Tools for Database Development in Visual C++ Developer Studio"

Quiz

  1. Which editions of Visual C++ enable viewing and editing data from relational databases inside Visual Studio?
    The Visual C++ Professional and Enterprise Editions.
  2. What is a DSN?
    A DSN is a data source name. The term DSN refers to an ODBC data source created on a computer, which points to some database and specifies some ODBC driver that can read from and write to that database.
  3. What gives a database its value and why?
    A database's value is derived from its structure. The better, the more complete, the more descriptive, the more widely accepted the structure of a database, the more valuable its data can be.
  4. What is the fundamental requirement for records in relational database?
    Each record must be unique. There must not be any duplicate records in a relational database.
  5. What mechanism is used to relate records in different tables to one another?
    Records in different tables are related to each other through primary and foreign keys. A record's primary key uniquely identifies it in the database. That primary key can appear in other tables to indicate a relationship between that record and the records in other tables. A primary key that appears in another table is called a foreign key.

Exercises

  1. Open the Orders table in the database project you created today. Note the foreign keys that appear in the table. Open the Customers and Products tables and see primary keys for customers and products. Try to change one of the foreign key values, such as a customer number, to a number that doesn't exist as a primary key. What happens? Does the database help enforce the integrity of the data?
    When you try to change one of the foreign key values in the Orders table, such as a customer number, to a number that does not exist as a primary key, the database will not accept the change, and you will get an error message saying that this change would violate referential integrity rules.
  2. Open the Orders table in the database project you created today. Try to change one of the order numbers in the table by typing in letters for the contents of the field. When you move the cursor off that record, what happens? Does the database validate the data type you tried to enter? (You can press Esc to abort the edit.)

Day 3, "Retrieving Data Through Structured Query Language (SQL)

Quiz

  1. What is SQL?
    SQL is an acronym for Structured Query Language. SQL is a data manipulation and definition language designed specifically for relational databases.
  2. What is an SQL join?
    An SQL join is a SELECT statement that produces a resultset by using data from two or more tables in a relational database.
  3. What is wrong with this SQL query? 
SELECT customers.*
WHERE customers.custnumber = 3

The query is missing the FROM clause. It needs to say the following: 

SELECT customers.*
FROM customers
WHERE customers.custnumber = 3

  1. What is an aggregate function?
    An aggregate function operates on multiple records and returns a single value.
  2. What does a cursor make possible?
    A cursor defines a position in a resultset and makes it possible to move through the resultset one record at a time.

Exercises

  1. Discover what happens when you add a table name to the FROM clause without mentioning that table in the WHERE class of an SQL SELECT statement, like this: 
SELECT customers.*
FROM customer, orders
WHERE customers.custnumber = 3
    The resultset contains a Cartesian product of the two tables, meaning that if one table has 3 records and the other has 5 records, the resultset will contain 15 records. In the case of the database you've been using today, the resultset will contain 4 records.

  1. Add a join to the SQL query shown in Figure 3.22 to retrieve the name of the customer who placed the most recent order.
    You must nest the subquery that finds the last order date in the subquery that finds the customer number, which subquery you must nest in the query to obtain the customer information. 
SELECT 'Customers'.*
FROM 'Customers'
WHERE custnumber IN
(
      SELECT 'Orders'.customernumber
      FROM 'Orders'
      WHERE orderdate =
      (
             SELECT MAX(orderdate)
             FROM Orders
       )
)

Day 4, "Retrieving SQL Data Through a C++ API"

Quiz

  1. What does a database API do?
    A database API translates between the type system of C++ and the type system of the database. It also provides a means for passing SQL code to the database's interpreter for execution and a means for retrieving data queried from the database.
  2. What database APIs work with nonrelational data sources?
    OLE DB and ADO.
  3. What does an ADO Connection object do?
    ADO Connection object encapsulates the functionality of logging in to and out of a database, making queries, and retrieving resultsets.
  4. What does the current record mean?
    The current record is the record at which the cursor in the recordset is positioned.

Exercises

  1. Change the code in Listing 4.12 so that the customers are sorted by last name.
    You need to change line 7 in Listing 4.12 to include an ORDER BY clause on the last-name field.
  2. Change the code in Listing 4.12 to display the customer number as well as the customer first and last name.
    You need to add a column to the list control for the customer number. You must also add a call to the Recordset GetCollect function to retrieve the customer number field and store it in a _variant_t. After the GetCollect call, you can simply cast this _variant_t to a _bstr_t, even though the original data type is numeric. _variant_t and _bstr_t handle the conversion for you.

Day 5, "Adding, Modifying, and Deleting Data"

Quiz

  1. What is a forward-only cursor?
    A forward-only cursor is a cursor that moves only forward through the resultset.
  2. What function do you use to place a value in a field of the current record in an ADO Recordset?
    The PutCollect function places a value in a field in the current record of an ADO Recordset.
  3. What is wrong with this SQL statement? 
DELETE FROM customers
    Perhaps nothing is wrong with it. However, it's crucial to note that this statement will delete every record in the Customers table because it has no WHERE clause.

  1. What are the two arguments that you must pass to the ADO Recordset AddNew function?
    You must pass two arrays of VARIANTs, the first containing the list of fields and the second containing the list of data values to be placed in those fields.
  2. What happens if you specify only one field/value pair in the SET clause of the SQL UPDATE function?
    Only the data in that one field is updated. The other fields are unchanged.

Exercises

  1. Discover what happens in the Price field when you specify only the PartNumber and ProductName fields in a SQL INSERT statement for the Products table, like this: 
INSERT INTO Products(PartNumber, ProductName)
VALUES ('xxx', 'yyy')
    The database will place a default value in the field. In this case, zero is the default value for the Price field, so zero will appear in the Price field of the new record.

  1. Modify the code in Listing 5.1 so that it doesn't specify a price for the new record.
    You need to change the CreateOneDim function call so that the arrays have two elements instead of three. Then you delete the code that defined the third elements (the Price field name and data) in each array. 
// Create an array for the list of fields in
// the Products table.
COleSafeArray vaFieldlist;
vaFieldlist.CreateOneDim(VT_VARIANT,2);
// Fill in the field names now.
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldlist.PutElement(lArrayIndex,
  &(_variant_t("PartNumber")));
lArrayIndex[0] = 1;
vaFieldlist.PutElement(lArrayIndex,
  &(_variant_t("ProductName")));
// Create an array for the list of values to go in
// the Products table.
COleSafeArray vaValuelist;
vaValuelist.CreateOneDim(VT_VARIANT,2);
// Fill in the values for each field.
lArrayIndex[0] = 0;
vaValuelist.PutElement(lArrayIndex,
  &(_variant_t("8TRACK-003")));
lArrayIndex[0] = 1;
vaValuelist.PutElement(lArrayIndex,
  &(_variant_t("Bell Bottom Hits")));

Day 6, "Harnessing the Power of Relational Database Servers"

Quiz

  1. What is a single-tier application?
    A single-tier application consists of a single program that contains all the code and logic, which runs in one process and tries to accomplish all the work of the application.
  2. How do you make the SQL INSERT statement insert multiple records?
    You make the SQL INSERT statement insert multiple records by replacing the VALUES clause with a SELECT statement that returns multiple rows.
  3. What databases help you preserve the referential integrity of your data?
    The databases that help you preserve the referential integrity of your data are relational database servers and Microsoft Access.
  4. How is a stored procedure in a relational database different from a Query in Microsoft Access?
    A Query in Microsoft Access is not compiled like a stored procedure. A Query in Microsoft Access doesn't execute at the server like a stored procedure. Also, a Query in Microsoft Access is treated as a View in Visual Studio.
  5. Where can you find the data types available for use in ADO Parameter objects?
    You can find the data types available for use in ADO Parameter objects in the ParameterDirectionEnum in msado15.tlh.

Exercises

  1. Modify the SELECT statement in Listing 6.2 so that the customer number is not hard-coded. Make it so that the customer number is retrieved based on the customer's last name.
    To retrieve the customer number based on the customers last name, the SELECT statement needs to use a subquery. It would look like this: 
SELECT 4, { d '1998-11-16' },
     (SELECT custnumber
      FROM customers
      WHERE custlastname = 'clinton'),
     PartNumber, Price,
     4, 'MC 1223 9873 2028 8374 9/99'
FROM Products
WHERE (PartNumber LIKE '8TRACK%')
  1. Add code to the OrderedSinceDate handler shown in Listing 6.8 to change the value of the parameter after it has been appended to the command but before the Command has been executed.
    The code to change the value of a parameter attached to an ADO Command object could look like this: 
(pCommand->Parameters->GetItem(_variant_t("ParamDate")))
->PutValue(_variant_t(COleDateTime(1998, 11, 15, 0, 0, 0)));

Day 7, "Database Design"

Quiz

  1. What is the highest normal form in the relational database model?
    The highest normal form in the relational model is the domain/key normal form.
  2. What are entity relationships?
    Entity relationships are the relationships between records in various tables in a relational database.
  3. How can you guarantee that a table conforms to the second normal form?
    The second normal form requires that no fields apply to only part of the primary key. If a table has a single key field, the table is guaranteed of conforming to 2NF.
  4. What is the proper term for the structure (the tables, indexes, constraints, and so on) of a relational database?
    The structure of a relational database is called its schema.
  5. What does a referential integrity constraint do?
    A referential integrity constraint ensures that the records related through primary and foreign keys are not deleted independently of each other. The constraint keeps the records in related tables in synch with each other.

Exercises

  1. Write a SELECT statement that shows all the products purchased on each order. Hint: The SELECT statement should perform a join between the NewOrders, ProductsPurchased, and Products tables.
    This SELECT statement shows all the products purchased on each order: 
SELECT neworders.ordernumber, products.productname
FROM neworders, productspurchased, products
WHERE neworders.ordernumber = productspurchased.ordernumber
AND productspurchased.partnumber = products.partnumber
  1. Write a SELECT statement showing the products purchased by each customer.
    This SELECT statement shows the products purchased by each customer: 
SELECT customers.custfirstname, customers.custlastname, products.productname
FROM neworders, productspurchased, products, customers
WHERE neworders.ordernumber = productspurchased.ordernumber
AND productspurchased.partnumber = products.partnumber
AND neworders.customernumber = customers.custnumber

Day 8, "Utilizing the Capabilities of Database Servers"

Quiz

  1. What are the ACID properties of a transaction?
    The ACID properties of a transaction are atomicity, consistency, isolation, and durability.
  2. What is the isolation level of a transaction?
    The isolation level of a transaction is a setting in the database that specifies how zealous it should be in protecting a user's work from interaction with the work of other concurrent users.
  3. How does the GROUP BY clause interact with the SQL aggregate functions?
    Placing a GROUP BY clause with an aggregate function in a SELECT statement causes the aggregate function to perform its calculation on sets of records that are determined by the GROUP BY clause.
  4. How many triggers can be attached to a table in Microsoft SQL Server?
    Three triggers can be attached to a table in Microsoft SQL Server: an insert trigger, an update trigger, and a delete trigger.
  5. Does a view on a large table occupy much room in the database? Why or why not?
    A view on a large table doesn't occupy much room in the database because the view stores no records. Only the SQL SELECT statement that defines the view is stored in the database (and a SELECT statement is relatively small).

Exercises

  1. Modify the SELECT statement in Listing 8.7 so that the resultset is sorted by the total sales volume, from the highest volume to the least.
    The code to sort the resultset in order to total sales, the highest first, would look like this: 
SELECT neworders.ordernumber,
SUM(price + shippingandhandling)
FROM neworders, productspurchased
WHERE neworders.ordernumber = productspurchased.ordernumber
GROUP BY neworders.ordernumber
ORDER BY SUM(price + shippingandhandling) DESC
  1. Modify the SELECT statement in Listing 8.7 so that the query returns the average product price, from the highest to the least.
    The code to return the average product price, from the highest to the least, would look like this: 
SELECT neworders.ordernumber,
AVG(price)
FROM neworders, productspurchased
WHERE neworders.ordernumber = productspurchased.ordernumber
GROUP BY neworders.ordernumber
ORDER BY AVG(price) DESC

Day 9, "Understanding COM"

Quiz

  1. Why can't you load a DLL into memory and send messages to it from your app?
    When your application loads a DLL into memory, the DLL becomes part of your app. Your app cannot send messages (window messages or otherwise) to the DLL and have the DLL act independent of your application. The DLL code is mapped into your app's address space, and any objects the DLL creates, your app owns.
  2. What makes a C++ class an abstract base class?
    An abstract base class must have at least one pure virtual function as a member. A pure virtual function is a function with =0 after its declaration.
  3. What is a class factory?
    A class factory is a class that knows how to create instances of a class that is a COM server. A class factory must be implemented in every COM DLL and EXE. The class factory exposes a function named CreateInstance, which the OS can call to get pointers to instances of the COM servers whose code resides in that file.
  4. Why is it necessary for a COM client to call Release on a COM server after it's finished with it?
    The Release function decrements the usage count for that COM object (the server). When the usage count reaches zero, the server object is deleted or is told to delete itself. If no clients are using a server object, it can and should be deleted to free its resources.
  5. What is a CLSID, and why must all CLSIDs be unique?
    A CLSID uniquely identifies a COM server. The CLSID is used in the registry to store information on the location of the DLL or EXE file that contains the COM server code. If there were duplicate CLSIDs, there would be a chance that one CLSID could be overwritten by another in the registry, preventing the first one from ever being launched. Also, COM clients expect certain behavior and support for certain interfaces from COM servers, based on the CLSID. The uniqueness of the CLSID makes that expectation possible.

Exercises

  1. Add another method to the IDbComponent interface. Make this method take, as a parameter, an address to a variable of some sort. Modify this variable in the server code and make sure it gets back okay to the client.
    The important thing for a parameter that is passed in to the server to be modified and returned is that it be marked as [out] in the Parameters edit box in the Add Method to Interface dialog.
  2. Use the ATL COM AppWizard to create a COM server in an EXE. Expose a function in its interface that is similar to one in the DLL COM server. Compare the performance of the EXE-based COM server (the out-of-proc server) versus the DLL-based COM server (the inproc server).
    You should find that the out-of-proc server is much slower-has much more function call overhead-than the inproc server.

Day 10, "Database Client Technologies and the Secrets of ADO"

Quiz

  1. What is the goal or purpose of ODBC?
    The goal or purpose of ODBC is to provide a uniform API for communicating with relational databases from different vendors.
  2. How is ODBC's call-level interface different from embedded SQL?
    ODBC's call-level interface differs from embedded SQL in that the SQL code in an ODBC application is not compiled by a precompiler and translated to native database calls. Rather, the SQL code in an ODBC application is interpreted at runtime by the database (or by the database's ODBC driver).
  3. Where does the ADO type library reside and how can you view it?
    The ADO type library resides in the ADO DLL, which is called MSADO15.DLL and is typically installed in the C:\Program Files\Common Files\System\ADO directory. You can view the ADO type library by using the OLE-COM Object Viewer included with Microsoft Visual Studio.
  4. Why does ADO throw exceptions when errors occur?
    ADO throws exceptions when errors occur because that is how the high-level ADO functions produced by #import are implemented. The code for the high-level ADO functions can be found in the MSADO15.TLI file.
  5. What function that you use with #import does not throw exceptions but returns a failed HRESULT instead?
    The smart pointer CreateInstance function doesn't throw exceptions but returns a failed HRESULT in the case of an error.

Exercises

  1. Set break points in the inline functions in MSADO15.TLI, such as the _Connection::Open function, and run ADOMFC1 in debug mode to develop a feel for how code in MSADO15.TLH and MSADO15.TLI is executed. Debug step into all the functions to discern when you are executing code in your ADOMFC1 project and when you are executing code in the ADO DLL
    When you set breakpoints in the MSADO15.TLI file, you will find that the functions with the raw_ prefix directly call into the ADO library.
  2. Modify the code in Listing 10.7 so that the call to the ADO Command Execute function in line 24 directly calls the low-level Execute function.
    The code for the Execute call should look like this: 
22:      _RecordsetPtr pRS;
23:
24:      hr = pCommand->Raw_Execute( &vNull, &vNull, adCmdUnknown, Â&pRS );

Day 11, "Multitier Architectures"

Quiz

  1. Interfaces and abstractions are the two pillars on which multitier applications rest. Each tier that provides an effective level of abstraction has interfaces that are understandable and distinct. Distinct interfaces between the tiers enable the tiers to be updated independently of each other.
  2. Thin client programs are often more desirable than fat clients, because they do not require updates as often as fat client programs.
  3. HTML specifies how information will be displayed. XML specifies what the data is and what it means.
  4. In a typical RDS application, the RDS DataControl object is instantiated on the client machine in the browser's process space. Often COM server(s) such as ActiveX controls are also instantiated in the browser to enable a feature-rich UI. The RDS DataControl object causes a DataFactory object to be instantiated on the middle tier (Web server) machine.
  5. The security risk posed by the RDS COM servers is caused by the fact that the DataFactory object can be instantiated on a Web server machine. This can provide access to the database to anyone on the Web who knows a valid data source, username, and password for that database.

Day 12, "Using Microsoft Transaction Server to Build Scalable Applications"

Quiz

  1. DCOM alone is insufficient for building multitier applications because DCOM by itself provides no support for transactions, thread pooling, or database connection pooling. You would have to write those features yourself to develop a multitier application with just DCOM and without MTS.
  2. [out, retval] is the specification in IDL for a parameter that is the return value of the method.
  3. Yes, MTS will run on Windows 98, if the machine has at least 32MB of RAM.
  4. Each MTS package specifies a process in which MTS components are to run. This enables process isolation, as well as common security settings for the collection of components that reside in the package.
  5. A disconnected Recordset is an ADO Recordset that contains data but currently has no a connection to a database. Disconnected Recordsets can be sent between COM servers and clients as a flexible and powerful data structure.

Exercises

  1. The linker will produce an error because it cannot open the DLL file for writing. This is because the DLL is loaded. To fix the problem, you need to click the Refresh button on IE4 or exit IE4 completely. Sometimes even this does not work. At those times, you need to shut down the server processes using the Transaction Server Explorer by right-clicking My Computer and selecting Shutdown Server Process.
  2. Run the Transaction Server Explorer and navigate to the package that contains MTSComp1.Component1. Select MTSComp1.Component1 and press the Delete key to remove the component from MTS. To register the DLL so that you can use it as a normal COM server, enter regsvr32 MTSComp1.DLL from the DOS prompt in the directory where the DLL resides. You might need to mark it safe for scripting and initialization again.

Day 13, "Melding Object-Oriented Programming with Relational Databases"

Quiz

  1. You can't store C++ objects in relational database fields because the only things that the database lets you store in fields are instances of the data types that exist in the database's type system.
  2. You can't use SQL for object-oriented programming because the language has no object constructs or mechanisms. Its sole purpose is to manipulate data in two-dimensional tables inside relational databases.
  3. C++ object databases directly support the C++ type system, including types (classes) that programmers create themselves. Data in C++ object databases is accessible only to C++ programs that understand the types (the classes) that are stored therein. A relational database supports only the data types that it defines. The data in relational databases is accessible to any application that can understand the relational model and that can map between its type system and the database's type system.
  4. When you are designing an application that will use object and relational technology, it is generally best to start by designing the relational database schema. You can then use the schema as the basis for the object schema.
  5. The benefits of a live object cache include greatly improved application performance because of reduced database access, and reduced network traffic, again because of reduced database access.

Exercises

  1. This SELECT statement retrieves the shoe type based on the shoe ID from the Shoes table. 
SELECT ShoeType
FROM Shoes
WHERE ShoeID = 1
  1. This SELECT statement shows the products purchased by each customer. 
SELECT Shoes.*, BasketballShoes.*
FROM Shoes, BasketballShoes
WHERE Shoes.ShoeID = BasketballShoes.ShoeID

Day 14, "Legacy Database APIs"

Quiz

  1. CDatabase
  2. The environment handle saves information necessary for the application to connect to a data source. The Driver Manager will construct the handle and then give the connected driver a copy.
  3. dbDBEngine. No
  4. CRecordSet::dynaset

Exercises

  1. You can find the DAO classes in the OLE/COM Object Viewer by expanding the All Objects element in the tree control in the left window pane. The DAO classes all start with DAO. To view the type information, double-click one of the classes to instantiate the object, double-click one of the non-IUnknown interfaces, and click the View Type Info button.

Chapter 15, "The ODBC API and the MFC ODBC Classes"

Quiz

  1. The Record Field Exchange (RFX) implemented by DoFieldExchange().
  2. The document class contains the recordset. The Document in the MFC Document/View architecture represents the application's data store, whereas the View is a window to that data.
  3. OnInitialUpdate
  4. CRecordSet::dynaset

Exercises

  1. The edit boxes should match the field declarations in the AddressBook database. Yes, the fields are updated whenever the cursor is moved. This is accomplished via the Record Field Exchange (RFX) implemented by DoFieldExchange(). The ODBC wrappers implement the cursor as part of the recordset, and the View automatically creates a message interface from the cursor buttons to the recordset's cursors.
  2. Identical to answer 1.
  3. The code should look something like this: 
rs.AddNew();
rs.m_Last_Name = "Smith";
rs.m_First_Name = "Jennifer";
rs.Street = "234 WayWay St.";
rs.m_City = "Columbus";
rs.m_State = "OH";
rs.m_Zip = 45400;
rs.m_Phone = "614-5550101";
rs.Update();

Day 16, "The Ultimate Database API: OLE DB"

Quiz

  1. What are the two basic OLE DB components?
    A data provider and a data consumer. A data provider is an application that responds to queries and returns data in a usable form. A data consumer is an application, or other COM component, that uses the OLE DB API to access a data source.
  2. How does OLE DB currently enable access to ODBC data sources for which no OLE DB provider is yet available?
    The OLE DB SDK contains an OLE DB provider for ODBC data, called MSDASQL, which allows you to access ODBC data sources from OLE DB consumer applications. The OLE DB provider for ODBC resides in MSDASQL.DLL.
  3. What are the major OLE DB objects?
    The OLE DB. The major OLE DB components are called CoTypes. The OLE DB CoTypes are TDataSource, TDBSession, TCommand, TRowset, TIndex, TErrorObject, and TTransaction.
  4. Which header files must be included to access OLE DB objects?
    The header files OLEDB.H and OLEDBERR.H must be included for OLE DB applications. These header files are used to include the OLE DB classes and OLE DB error-handling classes, respectively.
  5. What is the URL for the OLE DB home page?
    The URL of the OLE DB Web site is http://www.microsoft.com/data/oledb/.

Day 17, "Accessing a Data Source with OLE DB"

Quiz

  1. What is the role of a data provider and a data consumer in the OLE DB architecture?
    A data provider is a COM component that provides an OLE DB-compliant interface. A data consumer is an application or component that uses an OLE DB interface to access a data source.
  2. What is an interface? How does the COM architecture use an interface?
    Interfaces describe the functionality provided by the component and also provide the structured mechanism that these components use to talk with each other. A COM component is an object that uses the rules in the COM specification to provide access to the interfaces provided by a component.
  3. What is interface factoring?
    Interface factoring is the capability of COM objects to support multiple interfaces, which can provide different levels of functionality, depending on the consumer. A consumer uses the interface appropriate to its needs.
  4. What method is used to determine whether a COM object supports a particular interface?
    The interfaces supported by a COM component can be determined by calling the QueryInterface method. When an application determines whether a component supports a specific interface, it is guaranteed the functionality of that interface.
  5. Describe the basic flow of information in an OLE DB application.
    The consumer application uses the Enumerator object to determine which OLE DB data source providers are available. It then creates a DataSource object and uses the DataSource object to create a Session object. Then the application uses the Session object to create a Command object. The application uses the Command object's Execute function to create a Rowset object. Next, the application navigates through the Rowset containing the data. Finally, the application releases the objects.
  6. What is an Enumerator object, and how is it used?
    The Enumerator class retrieves information regarding all the OLE DB providers available on the system.
  7. What interfaces are supported by an Enumerator object?
    The Enumerator object supports the following interfaces: IparseDisplayName, IsourcesRowset, IDBInitialize, IDBProperties, and IsupportErrorInfo.
  8. What is a DataSource object, and how is it created?
    The DataSource object abstracts the actual data source. It is created by binding a moniker returned from the Enumerator class or by directly calling CoCreateInstance, using the appropriate CLSID.
  9. What interfaces does a DataSource object support?
    The DataSource object supports the following interfaces: IDBCreateSession, IDBInitialize, IDBProperties, Ipersist, IDBDataSourceAdmin, IDBInfo, IpersistFile, and IsupportErrorInfo.
  10. What methods initialize and release the DLLs required by a COM application?
    The CoInitialize and CoUninitialize functions load and release the appropriate COM-related DLLs. You must call these functions at the start and end of any application that uses COM.

Exercises

  1. Review the Visual C++ books online documentation (provided with Visual C++) for more information regarding the specifics of COM programming.
    Several places in the online documentation deal with COM. The best way to find them is to search for terms such as COM, QueryInterface, and OLE DB. When you find a useful article (or document), press the Locate button to find that article in the table of contents. Often you will find other useful information in the neighboring documents.
  2. The applications developed yesterday and today do not really consider error handling. How would you integrate error handling into the application in Listing 17.4? (Hint: Most of the COM related functions return an HRESULT type value.)
    You should always check the value of the HRESULTs returned by COM functions.

Chapter 18, "Querying a Data Source with OLE DB"

Quiz

  1. The Session object provides a context for transactions and commands. A Session object is created by using the IDBCreateSession interface of the DataSource object. The CreateSession method of this interface actually creates a session. You can use the Session object to create a Command object, to access a row set directly, and to create or modify data source tables and indexes.
  2. The IDBSchemaRowset retrieves data source schema information. Schema information describes the data contained in the data source. The IDBSchemaRowset interface is optional for Session objects.
  3. The Command object performs commands that the provider supports. Using the SQL Server data provider (or even the OLE DB ODBC provider) and a database such as SQL Server, you can use the Command object to execute SQL commands. OLE DB data providers aren't required to support commands.
  4. The ICommandText interface sets and retrieves the actual command text, which specifies the data source command to execute. The Command object requires the ICommandText interface.
  5. In SQL, parameters in commands are usually specified with the ? placeholder. The actual parameter value replaces the ?. Parameterized statements are similar to procedures in any programming language; they are useful for executing a particular statement repeatedly. A parameterized statement can execute a command whose parameters are specified while an application is running.
  6. The ICommandPrepare interface converts a command to a prepared command. A prepared command is a command that has been precompiled so that it can execute faster. If you expect a command to be executed repeatedly, transforming it into a prepared command can improve application performance.

Day 20, "Properties, Transactions, and Indexes"

Quiz

  1. List the major property groups.
    The major property groups are DBPROPSET_COLUMN_DATASOURCE, DBPROPSET_DATASOURCEINFO, DBPROPSET_DATAINIT, DBPROPSET_INDEX, DBPROPSET_ROWSET, DBPROPSET_SESSION, and DBPROPSET_TABLE. These groups are used to group properties in the DBPROPIDSET and DBPROPSET structures.
  2. What structure returns the collection of property values?
    The DBPROPSET structure holds the collection of property values when retrieving and setting properties.
  3. Name the property that determines the level of SQL command support provided by an OLE DB data provider?
    The DBPROP_SQLSUPPORT property determines the level of SQL command support provided by an OLE DB data provider.
  4. What method opens a transaction?
    The StartTransaction method opens a transaction.
  5. What isolation level maintains the most consistent access to a row set in a multiuser environment?
    The ISOLATIONLEVEL_SERIALIZABLE flag specifies the serializable isolation level, which offers the highest level of data integrity inside a transaction.
  6. What property ensures that a row set is retained in a valid state after a transaction is closed?
    The Rowset property DBPROP_COMMITPRESERVE determines the state of a row set when a transaction is committed or aborted. If true, the row set is resynchronized to reflect the results of the transaction; otherwise, the row set becomes invalid.
  7. What Index object method searches for a specific key value in an index The Seek method of the Index object searches for a specific key value.

Day 21, "OLE DB Error Handling"

Quiz

  1. Name the two macros used to check the result of an OLE DB method call.
    Visual C++ provides two macros that can determine generally whether a method was successful. These macros are SUCCEEDED and FAILED. As you can tell by their names, the SUCCEEDED macro returns true if the call to the method is successful, and the FAILED macro returns true if the call to the method is not successful.
  2. Which interface checks whether an OLE DB object supports extended error information?
    The IsupportErrorInfo interface determines whether an object supports the necessary interfaces to retrieve additional error information. The IsupportErrorInfo defines a single method, InterfaceSupportsErrorInfo, that returns S_OK if additional error information is supported.
  3. What are the special requirements of OLE DB error handling that aren't resolved by Automation error-handling objects?
    The OLE DB has two requirements that the standard IerrorInfo interface does not meet:
  4. What information does the GetBasicErrorInfo method return? Describe the elements of this structure.
    The GetBasicErrorInfo method retrieves the basic ERRORINFO structure for the record number specified in the 1RecNum parameter. The ERRORINFO structure holds basic information about an error.
  5. How do you retrieve a custom error object? What custom error objects does OLE DB provide?
    The GetCustomErrorObject method retrieves a provider-specific custom error. OLE DB provides one custom error interface for providers that support the SQL command language: ISQLErrorInfo. The ISQLErrorInfo method returns the current SQL status and error value. The dwMinor field contains a provider-specific error value. The clsid field contains the class ID of the OLE DB object that generated this error. The iid parameter contains the interface ID of the interface that generated this error. The dispid field contains the method that generated the error.
  6. List the basic techniques for OLE DB error handling.
    The basic error-handling techniques are the following:
  7. Which method does a data provider use to add a new error?
    Data providers use the AddErrorRecord method to add new error records.
  8. Explain the difference between HRESULT constants with the prefix DB_S and those with the prefix DB_E. HRESULT constants that begin with S or DB_S are values that indicate success, and HRESULT constants that begin with E or DB_E are error value constants.

© Copyright, Sams Publishing. All rights reserved.