Modifying the URL Checker to Run Automatically

In "Creating a URL Checker for Databases," June 2000, I showed you how to use Visual Basic (VB) to create a simple but useful application, DBURLChecker, that reads database tables containing URLs and tests each URL it finds. You can modify DBURLChecker to run either interactively from a desktop or from the command prompt. Running DBURLChecker as a batch program from the command prompt means that you can use a scheduler (e.g., the At command) with it.

If you don't already have the DBURLChecker.vbp project, you need to download it from the Win32 Scripting Journal Code Library for the June 2000 issue (http://www.win32scripting.com). After you have the files on your system, you can modify this project by inserting a new module, adding the various code that the new module needs, and adapting the form.

Inserting a New Module
When you run DBURLChecker interactively, it needs to display a user interface (UI). However, when you run DBURLChecker as a batch program, it can't display the UI. Because DBURLChecker is currently set up to always display the UI, you need to insert a new module called MainCode that lets you control when the UI appears.

Begin by opening DBURLChecker.vbp in VB. Select Add Module from the Project menu, and name this module MainCode.

Next, create a subroutine named Main in the module with the code

Sub Main()End Sub

You can use the Sub and End Sub statements to create subroutines. You can also use the Function and End Function statements to create subroutines that return values. A good practice to get into when you create subroutines is to define every variable that you'll use. That way, your development tasks will go much faster because you'll have fewer errors.

The Main subroutine executes when DBURLChecker starts. This subroutine lets DBURLChecker run interactively or in batch mode.

For the Main subroutine to work, you must change DBURLChecker's properties. Select DBURLChecker Properties from the Project menu. On the dialog box that appears, select Sub Main from the Startup Object drop-down list, as Figure 1 shows.

Adding the Main Code
After you create the MainCode module and the Main subroutine, you can add the subroutine's code. In the MainCode module, add the code in Listing 1 between the Sub and End Sub lines. (If the MainCode module in the VB editor isn't open, double-click it in Project Explorer.)

The first line in Listing 1 executes the SetProperties subroutine. (I'll explain this subroutine shortly.) Then, a series of four If...Then...Else statements executes certain actions based on whether you set any arguments for DBURLChecker.

When a VB application executes, it can accept arguments from the command line or process entries from an .ini file. DBURLChecker accepts two command-line arguments. The first argument is /s, which tells DBURLChecker to run in Silent mode. When the application runs in Silent mode, it places all its output in a log file instead of displaying the output in the form. The second argument is LogFile. Setting the LogFile argument directs DBURLChecker's output to the log file you specify. For example, if you run DBURLChecker from the command line with the code

DBURLChecker.exe  /s   /logfile=kens.log

you're telling the application to run in Silent mode and send its output to the log file kens.log.

Because these arguments are optional, the Main subroutine's four If...Then...Else statements check for the arguments' presence. The first If...Then...Else statement checks whether you set the LogFile argument. If you set it, the statement opens the log file you specified. The second If...Then...Else statement checks whether you set the Silent-mode argument. If you didn't set this argument, the statement displays the UI. If you set this argument, the statement executes the CheckDatabaseFromINI function instead of displaying the UI. The third If...Then...Else statement closes the log file if you set the LogFile argument. The final statement ends the application if you set the Silent-mode argument.

Adding the SetProperties and GetArguments Code
The SetProperties subroutine ensures that DBURLChecker receives the arguments that you set. This subroutine calls the GetArguments function, which pulls the arguments from the command line and places them in an array. SetProperties then loops through the array and sets the bSilent and sLogFile variables to their respective values. If you set the Silent mode, the SetProperties procedure turns on the LogToFile option by setting the sLogFile variable to the filename of the log file.

To add the SetProperties code, type in the code in Listing 2, page 7, in the CodeMain module. To add the GetArguments code, type in the code in Listing 3, page 7, in the CodeMain module.

Adding the CheckDatabase Code
The next step is to create a function called CheckDatabase, which verifies the URLs in the database. To add the CheckDatabase code, type in the code in Listing 4 in the CodeMain module.

The CheckDatabase function is set up like Main.frm's event code for the Check Any DB button. However, the function differs from the event code in several important respects:

Adding the AddToLog and CheckDatabaseFromINI Code
The AddToLog subroutine sends all output to the log file. To add this subroutine, type in the code in Listing 5 in the CodeMain module.

If DBURLChecker is running in Silent mode, the CheckDatabaseFromINI subroutine reads an .ini file to determine which databases to check. Thus, you need to not only add the CheckDatabaseFromINI code to the MainCode module but also create an .ini file called dburlchecker.ini, which Figure 2 shows. Although this example file has only one entry, the .ini file can contain any number of entries. To add a new entry, copy the existing entry (don't include the [Servers] heading), increment each key's number by one (e.g., change Server1 to Server2, Database1 to Database 2), and input the appropriate values for the keys.

To add the CheckDatabaseFromINI code to the module, type in the code in Listing 6 in the CodeMain module.

The CheckDatabaseFromINI subroutine builds the keys that you want to check in the .ini file by appending the value of i to Server, then Database, and so on for each key. The subroutine then uses the resulting set of keys (e.g., Server1, Server2) with the Win32 API functions to extract the entries from the .ini file. For each key in the .ini file, the Do...Loop statement executes the GetPrivateProfileString function to retrieve the key's value from the .ini file. After the subroutine retrieves all the values and places them in local variables, the Main subroutine executes the CheckDatabase function. Finally, the Do...Loop statement increments the i variable by 1, then loops through the next entry in the .ini file.

Adapting the Main Form
The last step in modifying DBURLChecker is to adapt the Main form. Open Main.frm, and double-click the Check Any DB button to open the Click event in the code editor. Replace the existing code with the following code:

txtOutput = CheckDatabase _(txtServerName, _txtDatabaseName, txtUserID, _txtPassword, txtTableName, _txtURLColumnName, _ txtIDColumnName)

This code executes the CheckDatabase function and sets CheckDatabase's return value to txtOutput.

Next, double-click the Check SQL Mag DB button and delete the code in the code editor. Then, delete the Check SQL Mag DB button. The button and code were simply for illustration and testing purposes, so you don't need them anymore.

Finally, open the Forms code in the code editor by right-clicking the form in Project Explorer and selecting View Code. At the bottom of the Form_Terminate function, add this End statement:

Private Sub Form_Terminate()EndEnd Sub

This code terminates DBURLChecker when you close Main.frm after an interactive session. Close Main.frm. You're now finished modifying DBURLChecker. You can find all the files for this modified application in the Code Library on the Win32 Scripting Journal Web site at http://www.win32scripting.com.

Automate Your Management Tasks
As the adapted DBURLChecker illustrates, you can use VB to build powerful tools that automate management tasks. Creating an application like DBURLChecker doesn't take that long. You can even take the code here and adapt it further to meet your needs.