Creating a URL Checker for Databases

More than ever, systems managers need to understand development languages and how those languages are useful to them. This trend isn't new. In the past, systems programmers wrote applications for automating and managing mainframes' systems and applications. Then, with the introduction of LANs, the idea of having a systems programmer as part of the administration staff got lost. However, the need for systems programming is resurfacing. In response, Microsoft and other vendors have added features to make systems programming possible once again.

Today, Windows 2000 (Win2K) and Windows NT systems offer a variety of automation options. For example, you can use Windows Script Host (WSH) to automate tasks. Another automation tool is Visual Basic (VB). WSH scriptwriters typically use VBScript, which makes them VB developers because VBScript is part of the VB language.

In several situations, using VB is more advantageous than using WSH. VB is better if you need to write a script whose source code must be hidden from prying eyes or if you need to create an application with a user interface (UI). In addition, compared with WSH, VB is typically quicker to use and has a better debugger.

To demonstrate VB's power and flexibility, let's create a simple but useful program, DBURLChecker, that reads a database table containing URLs and tests each URL it finds. With this program, you can automatically validate URLs in any database. To create DBURLChecker, you need to create the application's UI, add code, and add event handlers.

Creating the Application's UI
The first step in creating any VB application is to start VB. From the Start menu, select Programs, Microsoft Visual Studio, Microsoft Visual Basic 6.0. DBURLChecker is a standard Windows application, so click the Standard EXE icon in the New Project window. Click Open, and go to the VB interface that Screen 1, page 2, shows.

In Screen 1, the upper right window is the Project Explorer, which shows your application's tree structure. The tree's root entry reveals that the project inherited the default name Project1. To change that name, right-click Project1 and select Properties. In the properties dialog box that appears, type DBURLChecker in the Project Name field and DB URL Checker in the Project Description field. Click OK.

At this point, Form1 is open. However, you need to name this form and build its UI. Follow these steps:

You've now created the application's UI.

Adding the URL-Checking Code
The next stage is adding the code that will check the URLs. To accomplish this task, you need to use Microsoft Internet Transfer Control 6.0. This ActiveX control provides the application with access to the HTTP protocol it needs to access the URLs you're going to check.

By default, VB doesn't automatically enable the Internet Transfer Control. You need to enable it by right-clicking the toolbox and selecting Components from the Shortcut menu. In the list of components, select the Microsoft Internet Transfer Control 6.0 check box and click OK to register this control with VB and add it to the toolbox.

In the toolbox, double-click the Internet Transfer Control to add it to the Main form. You can put the control anywhere on the form because the form won't display the control at runtime. The code that you're about to add will refer to the Internet Transfer Control as Inet, so click the new control and make sure Inet appears in the Name property field.

You now can add the code that uses the Internet Transfer Control. Select Add Module from the Project menu to add a new .bas file to the project. Click the Module icon, then click Open to complete the process and open the file in the code editor. This file will contain the application's Internet-specific code. Press F4 to open the properties dialog box for the module, and set the Name property to HTTPStuff.

In the code editor, type or copy the code in Listing 1, page 4, into the module. Here's how HTTPStuff.bas works. The first line of code creates a function named CheckURL, which you can call from any code in the application. This function has one argument: sURL. Although sURL must be a string (which As String specifies), the function can return any type of data (which As Variant specifies). The next line defines a variable named sReturn that the function uses. The Debug.Print line prints the CheckURL function in the Immediate window when you run the application in Debug mode.

The code at callout A in Listing 1 first sets the RequestTimeout property of the Internet Transfer Control to 20 seconds. You can adjust this interval to meet your application's needs. Next, the If...Then...Else statement checks sURL for data. If the argument is empty, the statement sets sReturn to the error message URL not found and sends the script's flow to the ExitFunction label. If the argument isn't empty, the script's flow proceeds to the next line.

The line after callout A sets the Main form's URL property to sURL's value. However, the Trim function first trims all the leading and trailing spaces from the URL. Next, the script clears the sReturn variable, then calls the Open method to open the URL. This method returns the results of the requested Web page, which the script assigns to sReturn.

The If...Then...Else statement at callout B in Listing 1 checks sReturn for any data. If the variable is empty, the statement sets sReturn to the error message URL not found. If the variable isn't empty, the statement sets sReturn to the message URL OK. The rest of the code in Listing 1 sets up the error handler and performs cleanup tasks.

Adding the First Event Handler
With the URL-checking code in place, you can add the event handler for the Check It button. Begin by double-clicking the Main form in the Project Explorer to open it. On the opened form, double-click the Check It button to open this button's Click event in the code editor. Between the Sub and End Sub lines, add the code

txtStatus = CheckURL(txtUrlToGet)

This code passes the value that the user types in the txtUrlToGet text box (i.e., the field next to the Check It button) to the CheckURL function as an argument. The code then executes the function and displays the message that the function returns (i.e., URL OK or URL not found) in the txtStatus text box (i.e., the second-to-the-last field in the form).

Before you go any further, you need to save the project by clicking Save Project on the File menu. Saving the project protects your code in case the application hangs in some way when you try to run it. If the application hangs and you didn't save the project, you might lose the form and code.

At this point, you can test and even use the application in a limited capacity. Start the application by pressing F5 to display the form in Run mode. Next, enter a URL in the txtURLToGet text box, and click the Check It button. You'll see either URL OK or URL not found in the txtStatus text box. Because the Check It button quickly tests HTTP code by checking one Web site, this button comes in handy during the Web-development process.

Adding the Database Code
Now you need to add the code that manipulates the database. First, select References from the Project menu, and find Microsoft ActiveX Data Objects 2.1 Library in the list of components. Select this check box, then click OK.

Next, select Add Module from the Project menu to add another .bas file to the project. Name it DatabaseStuff, and add the script DatabaseStuff.bas to the module. You can find this script in the Code Library on the Win32 Scripting Journal Web site at http://
www.win32scripting.com. In DatabaseStuff.bas, the OpenDatabase function opens the database that the argument specifies and returns an ADO Recordset object that lets you manipulate the database. For the purposes here, you don't need to know the details of how the function works. You just need to know how to use it. (For information about ADO, see Dino Esposito, "An Introduction to Scripting Technologies and Object Models," May 2000.)

Adding Two More Event Handlers
With the database code in place, you can add the event handlers for the Check SQL Mag DB and Check Any DB buttons. The event-handling code for these buttons is part of the script Main.frm. You can find Main.frm in the Code Library on the Win32 Scripting Journal Web site. To use Main.frm with your application, download it from the Code Library. Add it to your form by selecting Add From from the Project menu, then clicking the Existing tab and navigating to the location in which you downloaded Main.frm.

Check SQL Mag DB event code. To add the event handler for the Check SQL Mag DB button, double-click the Main form to open it, then double-click the Check SQL Mag DB button to open the Click event in the code editor. Enter the code in Listing 2. This code calls the OpenDatabase function to retrieve all the URL entries, then loops through them.

In Listing 2, the first four lines define the variables and set default values for both the Check SQL Mag DB and Check Any DB buttons. The next line executes the OpenDatabase function. The function's arguments specify where to find the data and what to return. Specifically, the first two arguments specify the name of the server that hosts the database (in this case, "Server1") and the name of the database ("SQLMagStuff"), respectively. The next argument specifies the name of the table to retrieve ("URLs"). The final two arguments specify the user's ID ("sa") and password ("") to access the database, respectively.

Next, the code uses a Do statement to loop through the URLs. The Do loop continues until it detects an End of File (EOF) condition. The first line inside the loop displays the message Checking CustomerName in the txtStatus text box, where CustomerName is the name of the customer in the CustomerName column in the current record the code is checking in the URL table.

The second line in the loop executes the DoEvents statement, which tells the application to let the other events in the queue process. If you don't call DoEvents, the text box won't display its messages until the other processing events finish. By that time, it's too late to let the user know what is going on.

The third line in the loop executes the CheckURL function. The function's argument specifies the URL to check. The subsequent If...Then...Else statement looks at the function's return value (i.e., URL OK or URL not found). If the URL is not found, the URL is added to the vOutput variable, which will be placed in the txtOutput text box when the code completes. The balance of the code in Listing 2 completes the loop and performs cleanup tasks.

Check Any DB event code. To add the event handler for the Check Any DB button, double-click that button to open the Click event in the code editor. Enter the code in Listing 3, page 5.

The code in Listing 3 is similar to that in Listing 2. The only difference lies in the way the code passes information to the OpenDatabase method. Instead of hard-coding references to the server, database, and so forth, the code in Listing 3 sets the local variables to the various text boxes on the form. These variables then provide the information (i.e., the arguments) that the OpenDatabase function and other statements need. This approach lets you run the application and plug in the values in the Main form at runtime to check any database that you have access to.

A Useful, Adaptable Tool
Because many Web applications today are data driven and have links buried deep in databases, DBURLChecker will likely prove useful. This application checks URLs in databases not only on LANs but also on remote servers that you access over the Internet. The only difference is that DBURLChecker takes slightly longer to run when you use it on remote servers.

You can modify DBURLChecker to run automatically at scheduled intervals. Next month, I'll show you how to transform DBURLChecker into an application that you can run from the command prompt so that you can use a scheduler such as the At command in NT or the task scheduler in Microsoft SQL Server.