Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Web Database Developer's Guide with Visual Basic 5

World Wide Web Database Developer's Guide with Visual Basic 5




-23-

MS SQL Server Internet Capabilities

Visual Basic is appearing everywhere--in the entire Microsoft Office Suite, in Active Server Web pages, and in the standard Visual Basic development environment. Also on the rise is the client/server relational database management system (RDBMS) of choice in a Microsoft Windows NT environment: SQL Server. SQL Server is a high-performance RDBMS system that is tightly integrated with the Windows NT operating system. SQL Server supports ANSI-92 SQL and offers high-performance features, such as stored procedures, triggers, rules, defaults, constraints, and SMP support. In an intranet/Internet environment based on Microsoft technologies, SQL Server can play a large role as a back-end database for your Web applications. SQL Server integrates well with Visual Basic. By using SQL Server stored procedures, you can invoke a Visual Basic ActiveX component from a SQL Server stored procedure. For easy-to-use Web access, SQL Server has a user-friendly hook called the Web Assistant that enables you to easily generate static HTML pages from SQL Server tables. SQL Server was the first RDBMS supported by an interesting database Web technology called the Advanced Data Connector (ADC). This chapter will show you how to use the SQL Server Web Wizard to generate HTML pages. For the more advanced SQL Server users reading this book, the stored procedures behind the Web Assistant, as well some trigger and scheduling ideas, are covered. This chapter also will introduce you to the Microsoft ADC.


NOTE: If you want to get up to speed on SQL Server and be the local guru, check out these books by Sams Publishing: Microsoft SQL Server 6.5 DBA Survival Guide (ISBN: 0-672-30959-9) and Microsoft SQL Server 6.5 Unleashed (ISBN: 0-672-30956-4). Both are excellent books that complement each other quite well.

SQL Server Web Assistant

The SQL Server Web Assistant automates the generation of HTML Web pages. You can use this wizard to publish data that resides in SQL Server on the Internet or intranet. As you can imagine, this link between SQL Server and the Internet/intranet creates several new and exciting possibilities for the distribution of information from SQL Server. Some of the uses for distributing SQL Server information in the HTML format follow:

Stepping Through the SQL Server Web Assistant

Follow these steps to use the SQL Server Web Assistant:

1. Double-click the SQL Server Web Assistant icon in the Microsoft SQL Server 6.5 (Common) program group. The SQL Server Web Assistant-Login dialog box appears, as shown in Figure 23.1.

FIGURE 23.1. The SQL Server Web Assistant-Login dialog box.

2. Enter the server name, logon ID, and password. If you are using integrated security, enable the Use Windows NT security to log in instead of entering a logon ID and/or a password checkbox (or, you can specify a Password option). You do not have to provide a logon ID or password if you use integrated security. Click Next. The SQL Server Web Assistant-Query dialog box appears, as shown in Figure 23.2.

FIGURE 23.2. SQL Server Web Assistant-Query dialog box.

3. Select your data source. This example uses a freeform text query as the data source. The following list explains the data source options:
Click Next. The SQL Server Web Assistant-Scheduling dialog box appears, as shown in Figure 23.3.

FIGURE 23.3. The SQL Server Web Assistant-Scheduling dialog box.

4. Select your scheduling options. The example in Figure 23.3 regenerates the Web page whenever the data in the Authors table changes. When you select the option to regenerate the Web page "when data changes," the following three types of triggers are generated for the Authors table: INSERT, UPDATE, and DELETE. When the data is modified, the trigger executes a system procedure that regenerates the Web page.
Click Next. The SQL Server Web Assistant-File Options dialog box appears, as shown in Figure 23.4.

FIGURE 23.4. The SQL Server Web Assistant-File Options dialog box.

5. Enter a filename, display information, and URL link information. Click Next. The SQL Server Web Assistant-Formatting dialog box appears, as shown in Figure 23.5.

FIGURE 23.5. The SQL Server Web Assistant-Formatting dialog box.

6. Select a formatting style and other Web page options.

7. Click Finish to generate the HTML file and any scheduling or trigger options.

That's it! You have just created a Web page that will automatically be generated the next time data changes in the pubs database table Authors. You can view the page generated by using Microsoft Internet Explorer, as shown in Figure 23.6.

FIGURE 23.6. Viewing a Web page created by the SQL Server Web Assistant.

SQL Server Web Assistant Components

The SQL Server Web Assistant provides a tremendous amount of automation through a simple-to-use graphical interface. If you are interested in how the wizard performs Web-page automation, take a closer look at the Web Assistant's components. Understanding the Assistant's functionality is useful when you need to modify an existing Web. The following sections explain the components used by the wizard. Web System Stored Procedures The following system procedures are used by the Web Assistant to generate Web pages:

You can use these system procedures to further extend the functionality of the Web Assistant, although the Web Assistant does a good job of doing everything you need. Triggers If you want your Web page to be a current reflection of your data, you will have to regenerate the Web page whenever your data changes. (Remember that the data in an HTML file is static; to reflect updates, the file must be regenerated.) To automate the generation of the HTML file, the SQL Server Web Assistant can create a set of INSERT, UPDATE, and DELETE triggers that schedule program calls to regenerate the Web page (see Figure 23.7). A trigger is a user-defined collection of SQL statements that are executed when an item is added, updated, or deleted in a table. Triggers often are used for data and referential integrity. The good news for you, as a Visual Basic programmer, is that the Web Assistant automatically generates the required triggers.

FIGURE 23.7.A trigger used to regenerate a Web page.

SQL Server's Task Scheduler SQL Server's Task Scheduler is another component of the SQL Server Web Assistant. If you want the wizard to regenerate the Web page on an hourly, daily, or weekly basis, the wizard will schedule a task that calls sp_runwebtask (see Figure 23.8). The Web page is rebuilt when this system procedure is executed (see Figure 23.9). A perfect use for the Task Scheduler is to generate weekly, monthly, or quarterly status reports that do not change after they are generated. You can schedule a stored procedure to execute and roll up the data in a table and then execute the stored procedure sp_runwebtask to update the HTML.

You can invoke the Task Scheduler from SQL Server's GUI front-end tool called the Enterprise Manager (see Figure 23.8). When using the Enterprise Manager, you can get to the Task Scheduler by choosing Server | Scheduled Tasks.

FIGURE 23.8.Task Scheduler and Web page integration.

FIGURE 23.9.The call to sp_runwebtask from the Task Scheduler.

Stored Procedures When the sp_runwebtask system procedure is executed from the Task Scheduler, it calls a stored procedure generated by the SQL Server Assistant. This stored procedure contains the information input by the user when the original Web page was created. To determine the name of the stored procedure being called, look at the code in Figure 23.9:

EXEC sp_runwebtask @procname

A stored procedure is a series of SQL statements that are precompiled and reside on SQL Server. A stored procedure can declare variables, pass parameters, call other stored procedures, and perform conditional logic as well as call functions that reside on SQL Server.

The Advanced Data Connector

As stated earlier, Microsoft wants to make SQL Server the RDBMS of choice in Windows NT environments as well as NT Web environments. Therefore, the RDBMS that works immediately with new Web database technologies released from Microsoft is SQL Server. SQL Server 6.5 was the first database to support the ADC. What's the ADC? Well, if you have a client/server database background (like I do), you probably have been waiting for the day when you could create Web-based applications that use bound controls across HTTP. There are no scripts to write and no IDC or HTX files--just a bound control on a Web page, similar to your current client/server applications that use RDO or DAO to communicate with an RDBMS system. The ADC enables you to use a special ActiveX data control on a Web page to bind to other ActiveX controls, such as grids and textboxes, and populate them with data retrieved from an RDBMS (just like a client/server application). No scripts are required to retrieve and format each record in HTML. Furthermore, the ADC retrieves and caches the database records locally on the client PC so that a user can edit them in the browser and then submit the changes back to the database. The ADC uses OLE DB to communicate with SQL Server and Oracle (check Microsoft's Website for other databases supported).

Figure 23.10 shows a general, high-level look at the architecture used with the ADC.

The first thing you might notice about Figure 23.10 is that many pieces make up the request to retrieve data from SQL Server. Unlike a typical two-tier client/server application, the ADC uses a three-tier architecture. The browser and Advanced Data Control provide the user interface services. The ADC comes with a default business object called the Advanced Data Factory object. This object sends SQL statements from the client to SQL Server and passes the results back to the client. The Advanced Data Factory object provides the business services. The data services are provided by an RDBMS system--in this case, SQL Server.

Use Figure 23.10 to walk through an interaction between the browser and SQL Server. A user on the browser loads a Web page that contains an ActiveX grid control and the Advanced Data Control. The Advanced Data Control is an invisible ActiveX control that enables you to bind ActiveX controls to a database recordset.


NOTE: ADC stands for Active Data Connector. ADC is made up of several components including the invisible ActiveX control referred to as the Advanced Data Control. When the abbreviation ADC is used, the Advanced Data Connector technology is being discussed, not the data control.

FIGURE 23.10. An architectural overview of the ADC.

As the page loads, a SQL statement is executed. The ADC creates an Advanced Data Factory object proxy on the client. The proxy is required because the business object on the client cannot communicate directly with the business object on the server. The proxy passes the request via HTTP to the Microsoft Internet Information Server. IIS determines the type of business object it is and creates an instance of the business object on the server, in this case the Advanced Data Factory object. The SQL statement then goes to SQL Server via OLE DB layered on top of ODBC. The recordset is retrieved and loaded into a server-side data cache. The information then is passed to the server-side Advanced Data Factory object and then is packaged and sent back to the client-side proxy. The information is unpackaged, placed in the client-side data cache, and passed to the Advanced Data Control, which loads the ActiveX grid control with the data.


RESOURCE: The architectural overview in Figure 23.10 is at a high level. For more details on the low-level nuts and bolts of what makes the ADC tick, check out the Advanced Data Connector site at



http://www.microsoft.com/adc/default.htm



From the description of the ADC's architecture, the ADC sounds very difficult to use. Actually, I think you will find the ADC about as difficult to use as the ActiveX data control that comes with Visual Basic. Now look at one of the sample applications that comes with the ADC, as shown in Figure 23.11.

FIGURE 23.11. Microsoft's sample ADC query page.

The ADC Query Page application comes with the ADC. The application is a good way to test whether you have properly configured your Web server and SQL Server to run the ADC. The application enables you to enter any valid select statement against the SQL Server pubs database. After you click the Run button (located at the bottom of the Web page and not shown in Figure 23.11), the data grid is almost immediately filled with the recordset. Again, the ADC is an invisible bound control, so you do not have to write an IDC file or ADO script to execute your script. Think of the ADC as client/server for the Web.

Now take a quick look at some important properties and methods of the ADC's Advanced Data Control required to retrieve data. For starters, look at an example of the <OBJECT> tag used for the Advanced Data Control. Listing 23.1 shows the <OBJECT> tag used in the sample application for the Advanced Data Control.

Listing 23.1. The Advanced Data Control <OBJECT> tag.

<OBJECT CLASSID="clsid:9381D8F2-0288-11d0-9501-00AA00B911A5"
        ID=tstADC HEIGHT=10 WIDTH = 10
    CODEBASE="HTTP://www.kscsinc.com/MSADC/msadc10.cab">
        <PARAM NAME="BINDINGS" VALUE="tstGrid;">
    </OBJECT>

Notice in Listing 23.1 the BINDINGS parameter, which has a value equal to the name of the grid on the page. Look at some other properties you must set before you can query the SQL Server via the ADC. For starters, you must set the ADC Server property to the IIS Server that will communicate to the SQL Server. The following line of code sets the Server property of the Advanced Data Control to a fictitious Web server, for example:

MyADC.Server = http//www.mspenik.com

The Advanced Data Control has a Connect property that, for SQL Server, requires a system ODBC DSN and a user ID and password. The following line of code is an example of setting the Connect property:

MyADC.Connect = "DSN=SQLPubs;UID=guest;PWD=guest;"

To set the Advanced Data Control with the SQL statement you want to execute, use the SQL property, as in this example:

MyADC.SQL = Select * from titles

After you have the control properly bound via the <OBJECT> tag and the SQL, Server, and Connect properties set, you are ready to retrieve some data.


NOTE: The SQL, Server, and Connect properties can be set as parameters of the <OBJECT> tag.

To populate your bound controls, use the Advanced Data Control's Refresh method, as shown in this example:

MyADC.Refresh

You can navigate through the cached recordset by using the following Advanced Data Control methods:

You can modify several records on the client side--in a grid, for example--and then send the batched changes back to SQL Server by using the SubmitChanges method of the Advanced Data Control.

Where to Go from Here

The ADC is a very new and exciting technology. Because the ADC integrates so well with SQL Server, I decided to introduce you to the technology well after the book outline was completed. This section has only scratched the surface. The ADC in itself is a powerful and compelling technology and probably can fill an entire book. To learn more about the ADC and to get a copy of the ADC, visit the Microsoft site at

http://www.microsoft.com/adc/default.htm

Summary

SQL Server is the perfect RDBMS for a Windows NT and Microsoft IIS environment. Using the Web Assistant, you easily can generate HTML from any existing SQL Server table. You can take advantage of SQL Server features, such as triggers, to update the Web pages as your data changes. Or, you can use the SQL Server Task Scheduler to update information from a table on a regular basis. The Microsoft ADC is being integrated into the SQL Server environment, and it enables you to create applications that use database bound ActiveX controls over HTTP.