MDAC 2.5 SDK - ODBC Programmer's Reference
Chapter 6: Connecting to a Data Source or Driver

Choosing a Data Source or Driver

The data source or driver used by an application is sometimes hard-coded in the application. For example, a custom application written by an MIS department to transfer data from one data source to another would contain the names of those data sources—the application simply would not work with any other data sources. Another example is a vertical application, such as one used for order entry. Such an application always uses the same data source, which has a predefined schema known by the application.

Other applications choose the data source or driver at run time. Usually, these are generic applications that do ad hoc queries, such as a spreadsheet that uses ODBC to import data. Such applications usually list the available data sources or drivers and let users choose the ones they want to work with. Whether a generic application lists data sources, drivers, or both often depends on whether the application uses DBMS-based or file-based drivers.

DBMS-based drivers usually require a complex set of connection information, such as the network address, network protocol, database name, and so on. The purpose of a data source is to hide all of this information. Hence, the data source paradigm lends itself to use with DBMS-based drivers. An application can display a list of data sources to the user in one of two ways. It can call SQLDriverConnect with the DSN (Data Source Name) keyword and no associated value; the Driver Manager will display a list of data source names. If the application wants control over the appearance of the list, it calls SQLDataSources to retrieve a list of available data sources and constructs its own dialog box. This function is implemented by the Driver Manager and can be called before any drivers are loaded. The application then calls a connection function and passes it the name of the chosen data source.

If a data source is not specified, the default data source indicated by the system information is used. (For more information, see "Default Subkey" in Chapter 19, "Configuring Data Sources.") If SQLConnect is called with a ServerName argument that cannot be found, is a null pointer, or is "DEFAULT", the Driver Manager connects to the default data source. The default data source is also used if the connection string used in a call to SQLDriverConnect or SQLBrowseConnect contains the DSN keyword set to "DEFAULT" or if the specified data source is not found. Additionally, the default data source is used if the connection string used in a call to SQLDriverConnect does not contain the DSN keyword.

With file-based drivers, it is possible to use a file paradigm. For data stored on the local machine, users often know that their data is in a particular file, such as Employee.dbf. Rather than choosing an unknown data source, it is easier for such users to choose the file they know. To implement this, the application first calls SQLDrivers. This function is implemented by the Driver Manager and can be called before any drivers are loaded. SQLDrivers returns a list of available drivers; it also returns values for the FileUsage and FileExtns keywords. The FileUsage keyword explains whether file-based drivers treat files as tables, as does Xbase, or as databases, as does Microsoft® Access. The FileExtns keyword lists the file extensions the driver recognizes, such as .dbf for an Xbase driver. Using this information, the application constructs a dialog box through which the user chooses a file. Based on the extension of the chosen file, the application then connects directly to the driver by calling SQLDriverConnect with the DRIVER keyword.

There is nothing to stop an application from using a data source with a file-based driver or calling SQLDriverConnect with the DRIVER keyword to connect directly to a DBMS-based driver. Here are several common uses of the DRIVER keyword for DBMS-based drivers:

• Not creating data sources. For example, a custom application might use a particular driver and database. If the driver name and all information needed to connect to the database is hard-coded in the application, users do not need to create a data source on their computer to run the application—all they need to do is install the application and driver.

  A disadvantage of this method is that the application must be recompiled and redistributed if the connection information changes. If a data source name is hard-coded in the application instead of complete connection information, each user needs to change only the information in the data source.

• Accessing a particular DBMS a single time. For example, a spreadsheet that retrieves data by calling ODBC functions might contain the DRIVER keyword to identify a particular driver. Because the driver name is meaningful to any users that have that driver, the spreadsheet could be passed among those users. If the spreadsheet contained a data source name, each user would have to create the same data source to use the spreadsheet.
  
  
• Browsing the system for all databases accessible to a particular driver. For more information, see "Connecting with SQLBrowseConnect," later in this chapter.