Using ODBC Data Sources

Because the Web server plays a key role in database publishing, it must be able to access a variety of data. All the tools used by IIS to publish data rely on Open Database Connectivity (ODBC) data sources. ODBC is a database-independent technology for accessing data. ODBC is particularly useful in Web applications and simplifies development by allowing Web pages to utilize standard Structured Query Language (SQL) syntax to interact with a database.

Before you can access a database with ODBC, you must set up an ODBC data source. Data sources are established using ODBC Data Source Administrator, an applet identified as the ODBC icon in the Control Panel. The administrator window is shown in Figure 2-16.

Figure 2-16. ODBC Data Source Administrator.

Once inside ODBC Data Source Administrator, you can set up a new data source or modify an existing one. ODBC version 3.0 supports three different types of data sources: User, System, and File. A User data source is available only to the user who creates it and can be run only from the machine where it is defined. A System data source is available to all users of, and services running on, the current machine. A File data source is available to users across the enterprise, provided they have the necessary ODBC drivers installed on their machines.

ODBC drivers are middleware that is able to convert SQL syntax to proprietary formats required by individual database engines. ODBC drivers are the primary component in ODBC that provides the database independence. Each driver is responsible for mapping the common functionality of SQL to the specific functionality of any database engine.

Establishing an ODBC data source is an important first step in any Web development effort. The data source you set up should be appropriate for its intended use. You select a User, System, or File source based on the degree of availability you require for the data source. Typically, you would use a System data source for most Internet applications.

When you establish an ODBC data source, you are required to provide key information that allows the ODBC driver to access the data source. In all cases, you must first provide a name for the data source. The name is arbitrary and can be anything you want; it will be used by your script code to access the data source through ODBC. Depending on the driver, you might also have to provide additional information. For example, if you set up a SQL Server data source, you must tell the driver which machine on the enterprise has the SQL Server data and the name of the database on the machine that you want to connect with. The setup dialog box is shown in Figure 2-17.

Figure 2-17. The ODBC SQL Server Setup dialog box.

Once the required information is entered, you can close the dialog box and the data source is available for use. In most Web applications you create, you will want to make the ODBC connection available to your site through Visual InterDev. Adding an ODBC data source to Visual InterDev is accomplished by choosing Add To Project/Data Connection from the Project menu. A data connection allows Visual InterDev to recognize an ODBC data source and to help you manage it. When a data connection is added to a Visual InterDev project, a new tab, Data View, appears in the project window. (See Figure 2-18.) This tab gives you access to some sophisticated data tools that allow you to add, edit, update, and delete database records. You can even change the table structure directly in Visual InterDev. Throughout your projects, you will make extensive use of these tools. In many ways, they are superior to some of the tools that actually ship with many databases!

Figure 2-18. The Data View tab in Visual InterDev.