Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now


Code Creation for Web Automation

In the most recent Public Domain column, in the Feb. 14 issue, we created the rules AudioLocker would follow when playing audio files in a group. If we followed a formal software lifetime model, creating those rules would give us our "concept" and "requirements" phases.

This is the second article of a three-part installment examining the AudioLocker.
Sidebar 1AudioLocker Fields Forever

What fields should each defined for each database record? How big should each field be? Again, referring to the AudioLocker rules, these following fields seemed sufficient:

GroupNum As Integer ‘ The Group Number that this file belongs to.

IndexNum As Integer ‘ Enumerated position of within the group (1, 2, 3, etc.)

Start As String * 8 ‘ Leaving these as fixed length strings,

End As String * 8 ‘ as they are only valid in HH:MM:SS format

Monday As Boolean ‘ TRUE if the file plays on a MONDAY, FALSE otherwise)

Tuesday As Boolean ‘ (the remaining days are similar)

Wednesday As Boolean

Thursday As Boolean

Friday As Boolean

Saturday As Boolean

Sunday As Boolean

FileName As String * 64 ‘ The audio filename with optional path. The path can be absolute, as in “C:\ATC.WAV” or relative, as in “..\ATC.WAV”

Comment As String * 64 ‘ I love comments. Everyone should.
In the most recent Public Domain column, in the Feb. 14 issue, we created the rules AudioLocker would follow when playing audio files in a group. If we followed a formal software lifetime model, creating those rules would give us our “concept” and “requirements” phases.

This column will describe the design and some of the key parts of the programming.The programming tool of choice for developing AudioLocker was Microsoft Visual Basic (VB) version 5.0, which was the latest version in 1999. The source code has been recompiled using VB version 6.0

The ‘Visual’ aspect

Fawcette Technical Publications President James E. Fawcette recently wrote an opinion column in a programming journal.

“A major part of what made VB so successful – reaching more developers than all other languages before it combined – is that it made levels of programming accessible to developers who don’t live and breathe development day in and day out,” he wrote.

“These are the people in finance departments, or in science and engineering – people who solve business problems first and write code second.”

I believe Fawcette’s analysis is correct. I chose VB for development of the AudioLocker project precisely because it is a problem-solving environment and deadlines were too tight to begin worrying about MemAlloc, heap management and the other “trivial” aspects of C++ programming.

The robustness of VB for this use is demonstrated by an examination of the log files.

The AudioLocker log file that covered Oct. 17, 2000 to March 1, 2001 showed that the program ran without interruption except when it was shut down by a human to insert “live” event audio into the Internet stream, or to test an add-on application that generates HTML files from AudioLocker-supplied information. AudioLocker is the only application running on that WinNT computer.

Because an original requirement is to play a group of audio files repeatedly within a Start and End time, I decided to implement the playlist storage in a Microsoft Access Database – easy for VB to access, read and write.

This decision eliminated the drudgery of managing and sorting huge memory arrays. In my previous DOS applications, that was the only practical way to hold data at the time. Yuck!

A world of possibilities opened up using a standard Access database. Users could directly create and edit the database using Access 97 and the Structured Query Language (SQL) could be used in place of sorting routines to return exactly the right filename to play.

Because I had never used these database tools, it required some digging into Help files, reading sample programs and referring to some VB programming books to begin to understand what all the database excitement was about.


One of the most frustrating aspects of building an SQL query is knowing what you want to do is possible, but finding out how difficult it is to intuitively write the correct English-like commands.

I needed to keep AudioLocker from becoming too complicated. Rather than write procedures for creating or compacting the MDB database, I used Access 97 to design it and saved copies of the empty database as a backup.
Sidebar 2Repeat Records

Listed below are the contents of the database, saved by AudioLocker to a plain text file. This schedule may be good for debugging, but would be pretty boring to listen to; it has only one group that plays the same files every day, one by one.

At noon or when the current file finishes playing immediately after 12 noon, the playlist resets and plays the file marked by index 1, current.atc1.wav.
The programming in the first sidebar implies that the end-user would create a playlist of groups logically – that is, creating a group whose members have the same Start and End times and Day settings. Because it is nice to let the user see the current playlist, a separate screen form holds a scrollable copy of the database for just that purpose.

The user must have a way of conveniently changing the playlist as needed and saving a text copy of the current playlist if desired. I reused source code from an automation application I wrote in MS QuickBasic 4.5 and updated it for VB.

The user can edit or create a schedule using any plain text editor and load it into AudioLocker using a command line:

c:\audiolocker.exe load

or by a menu option. I chose to use the single quote (‘) to mark a comment line in the schedule, allowing the user to extensively document a schedule.

AudioLocker ignores any line beginning with the single quote. As each line is loaded, the single quote-delimited fields are inspected for the correct range and invalid lines are discarded. A message window reports the number of valid and invalid lines to the user.

The “Save” function inserts helpful header comments into the file, so the user, or the poor developer trying to debug the program, can recognize the single quote-delimited fields more readily.

This header turned out to be a sanity-saver. A file Saved by AudioLocker can be Loaded back into the database without editing.

Why am I here?

Once the database details were worked out, it was time to design the initialization procedures.

In most automation systems, the system must answer these questions:

    • “What day and time is it?”

    • “What am I supposed to be doing now?” and

    • “What is the next event and when does it happen?”

    For AudioLocker, we get the time and day through intrinsic VB functions and then plug the values into the following SQL query:

    “SELECT * FROM Schedule WHERE (Start <= #” & sPresentTime & “#) AND (End >= #” & sPresentTime & “#) AND ” & TodayIs & ” = True ” & “Order by IndexNum, Group”

    In most cases, this query will return a collection of records from the database, which we can then count and store in a dynamic array.

    If, however, no records are returned, we must assume that AudioLocker was started later in the day, and we must look for groups that are scheduled to play past midnight by finding a group start time that is between now and 23:59:59:

    “SELECT * From Schedule WHERE ” & “(End < Start) AND ” & “(#” & sPresentTime & “# BETWEEN Start AND #23:59:59#) AND ” & “(” & TodayIs & ” = True) Order by Indexnum, Group”

    If this search returns no records, we make one last search for a group that was scheduled to start yesterday and might not have finished.

    For example, this situation would occur if a user wrote a playlist that contained a group with a Start time of 22:33:00 that is to play only on Monday night and an End time of 02:00:00. It is now Tuesday morning and we want the group to keep playing until it reaches its End time at 02:00:00:

    “SELECT * From Schedule WHERE ” & “(End < Start) AND ” & “(#” & sPresentTime & “# BETWEEN #00:00:00# AND End) AND ” & “(” & sYesterday & ” = True) Order by Indexnum, Group”

    Finally, if no records are returned after the third search, we have to assume there is a big problem with the schedule.

    Because we do not know what file to play, we will just load our emergency.wav file and keep playing it until either a valid group is found, which is checked each time after emergency.wav finishes; or until a human intervenes.

    Next time we will explore the valid group dynamic and the rest of the automation functions.

    AudioLocker is available to NPR member stations (source code and executable) by browsing to