2008 Winter Scripting Games

Solution to Advanced VBScript Event 2: Skating on Thin Ice

Windows PowerShell solution to Event 2 in the 2008 Winter Scripting Games.

Solutions are also available for Windows PowerShell and Perl.

Skating on Thin Ice

Event 2 came close to being a disaster for the Scripting Guys. We started off by writing the VBScript version, using a disconnected recordset to store and sort the data. That seemed to work just fine. Consequently, we decided to use the same approach with the Windows PowerShell version. That script also seemed to work fine; in fact, it came up with the exact same results as the VBScript version:

Weird scores, but, hey, who were we to argue with a computer, at least when it comes to doing math?

Unfortunately, though, we ran into problems trying to port this script to Perl. We could create a disconnected recordset in Perl; we could even store data in that disconnected recordset. What we couldn’t do was sort that recordset; the script would blow up every time we called the Sort command, and no matter how we called the Sort command. Consequently, we ended up taking a different path with Perl. And, much to our surprise, we got very different results:

Gold Medal: Guido Chuffart, 88.2
Silver Medal: Jack Creasey, 85.8
Bronze Medal: Cecilia Cornejo, 85.4

The medal winners are all correct, but the scores are different: Perl was apparently truncating the scores after one decimal place. That triggered an exhaustive Internet search as we desperately searched for a way to get Perl to display more than 1 decimal place.

So why wouldn’t Perl display more than 1 decimal place? There’s actually a good reason for that: the answers didn’t have more than one decimal place. When we imported all the scores into Excel, the better to check our math, we discovered that Perl was right, and VBScript and Windows PowerShell were wrong.

Or, more correctly, the disconnected recordset was wrong.

As it turned out, in using the disconnected recordset we stored the score for each value as a variant; although we didn’t realize it at the time, that resulted in a score like 88.2 being stored as 88.200000000000003. (Why? To tell you the truth, we didn’t bother trying to find out.) When we changed the data type for the Score field to Double (which is what we probably should have used in the first place), both VBScript and PowerShell reported back the following results:

Gold Medal: Guido Chuffart, 88.2
Silver Medal: Jack Creasey, 85.8
Bronze Medal: Cecilia Cornejo, 85.4

Oy vey!

On the bright side, we caught the problem ahead of time, and we ended up producing a script that actually worked. Speaking of which, here’s that script now:

Const ForReading = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adFldIsNullable = 32
Const adDouble = 5

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "Competitor", adVarChar, MaxCharacters, AdFldIsNullable
DataList.Fields.Append "Score", adDouble, , AdFldIsNullable
DataList.Open

Dim arrSortedScores(7)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Skaters.txt", ForReading)

Do Until objFile.AtendOfStream
    intTotal = 0
    strScores = objFile.ReadLine
    arrScores = Split(strScores, ",")
    strName = arrScores(0)

    j = 0

    For i = 1 to UBound(arrScores)
        arrSortedScores(j) = arrScores(i)
        j = j + 1
    Next

    For x = (UBound(arrSortedScores) - 1) to 0 Step -1
        For y = 0 to x
            If arrSortedScores(y) > arrSortedScores(y + 1) Then
                strHolder = arrSortedScores(y + 1)
                arrSortedScores(y + 1) = arrSortedScores(y)
                arrSortedScores(y) = strHolder
            End If
        Next
    Next 

    intTotal = CInt(arrSortedScores(1)) + CInt(arrSortedScores(2)) + CInt(arrSortedScores(3)) + CInt(arrSortedScores(4)) + CInt(arrSortedScores(5))
    dblAverage = intTotal / 5

    DataList.AddNew
    DataList("Competitor") = strName
    DataList("Score") = dblAverage
    DataList.Update
Loop

objFile.Close

DataList.Sort = "Score DESC"

DataList.MoveFirst

Wscript.Echo "Gold Medal: " & DataList.Fields.Item("Competitor") & ", " & DataList.Fields.Item("Score")
DataList.MoveNext

Wscript.Echo "Silver Medal: " & DataList.Fields.Item("Competitor") & ", " & DataList.Fields.Item("Score")
DataList.MoveNext

Wscript.Echo "Bronze Medal: " & DataList.Fields.Item("Competitor") & ", " & DataList.Fields.Item("Score")
DataList.MoveNext

So how do you go about solving Event 2? Well, first you need to calculate the official score for each skater; you then need to determine which skaters had the three highest scores. That means that, among other things, we have to have some place to store the name and score for each skater. On top of that, we also need a way to sort that data so that we can determine the top three skaters.

It’s primarily that second reason (the need to sort data) that led us to use a disconnected recordset to complete this event; as we probably don’t need to tell you, VBScript isn’t particularly adept at sorting data. That’s a problem with the language itself, but isn’t a problem if we use a disconnected recordset. That’s because a disconnected recordset is a database table that’s stored only in memory (as opposed to being tied to an actual database file); like any other database table, we can sort the records in a disconnected recordset. That makes it easy to keep track of competitors and their scores, and just as easy to determine the top three skaters.

With that in mind, the first thing our script does is define a number of constants:

Const ForReading = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adFldIsNullable = 32
Const adDouble = 5

The constant ForReading will be used when we open the text file containing the skaters’ scores; the other four constants will be used when we define the fields for our disconnected recordset. Speaking of which, that’s what we do next:

Set DataList = CreateObject("ADOR.Recordset")

DataList.Fields.Append "Competitor", adVarChar, MaxCharacters, AdFldIsNullable
DataList.Fields.Append "Score", adDouble, , AdFldIsNullable

DataList.Open

As you can see, in line 1 we create an instance of the ADOR.Recordset object, the object used with disconnected recordsets. In the next line we use the Append method to add a field named Competitor to this recordset. As you can see, the Append method takes several parameters:

After repeating this process to add a second field (Score) to the recordset, we then use this line of code to open that recordset (something we have to do if we want to add data to the thing):

DataList.Open

And now it starts to get fun.

As you might recall, each line in the text file Skaters.txt looks like this, featuring the competitor’s name followed by seven scores:

Janko Cajhen,84,80,61,81,71,62,76

That’s eight pieces of information to keep track of (one name and seven scores); that’s also why we set up an array named arrSortedScores that’s capable of holding eight items:

Dim arrSortedScores(7)

Once our array has been initialized we create an instance of the Scripting.FileSystemObject object, then use the following line of code to open the file C:\Scripts\Skaters.txt for reading:

Set objFile = objFSO.OpenTextFile("C:\Scripts\Skaters.txt", ForReading)

From there we set up a Do loop that runs until we’ve read all the lines in the file (that is, until the file’s AtEndOfStream property is True):

Do Until objFile.AtendOfStream

As you might expect, all sorts of excitement is going to take place inside this loop.

For instance? Well, to begin with, we assign the value 0 to a variable named intTotal; we’ll use this variable to keep track of the total points awarded to the skater. After that we use the ReadLine method to read the first line in the file (that is, the information for the first skater), storing that data in a variable named strScores. That brings us to this line of code:

arrScores = Split(strScores, ",")

What we’re doing here is using the Split function to split the information for skater 1 into an array name arrScores; that’s going to result in an array similar to this:

Janko Cajhen
84
80
61
81
71
62
76

We then take the first item in that array (item 0, the skater’s name) and assign it to a variable named strName:

strName = arrScores(0)

At this point, it starts to get a little complicated. We now have seven scores for Janko Cajhen. That’s fine, except that we don’t want seven scores for Janko Cajhen; instead, we need to throw out Janko’s high score and his low score. And how exactly are we supposed to do that?

Our solution (for better or for worse) requires us to copy Janko’s scores (just the scores, not his name) to the array arrSortedScores; that’s what this block of code is for:

j = 0

For i = 1 to UBound(arrScores)
    arrSortedScores(j) = arrScores(i)
    j = j + 1
Next

In order to identify (and throw out) the high score and the low score, the easiest thing to do is sort the values in the array. Believe it or not, that’s what this chunk of code does:

For x = (UBound(arrSortedScores) - 1) to 0 Step -1
    For y = 0 to x
        If arrSortedScores(y) > arrSortedScores(y + 1) Then
            strHolder = arrSortedScores(y + 1)
            arrSortedScores(y + 1) = arrSortedScores(y)
            arrSortedScores(y) = strHolder
        End If
    Next
Next

This is an example of a bubble sort. We aren’t going to explain today how the bubble sort works; that would take way too much time. In essence, though, the bubble sort takes the first score in the array and compares it to the second score. If the second score is higher than the first, then the two scores swap places: what used to be the second score in the array becomes the first score, and vice versa. This process continues until every score has been compared against every other score. The net result? The array arrSortedScores will look like this:

84
81
80
76
71
62
61

Now we can throw out the high and low scores; to do that we can simply grab the values of the middle five scores. In fact, that’s exactly what we do in the following line of code; we grab the middle five scores, add them together, and then store the resulting sum in the variable intTotal:

intTotal = CInt(arrSortedScores(1)) + CInt(arrSortedScores(2)) + CInt(arrSortedScores(3)) + CInt(arrSortedScores(4)) + CInt(arrSortedScores(5))

In our competition, a skater’s score is determined by throwing out the high and low scores (done), adding together the remaining five scores (done), and then determining the average of those five scores. We determine the average using this line of code:

dblAverage = intTotal / 5

We now have the two pieces of information we need for skater 1: the skater’s name (Janko Cajhen) and his or her score (74). That means we can use this block of code to add Janko to the disconnected recordset:

DataList.AddNew
DataList("Competitor") = strName
DataList("Score") = dblAverage
DataList.Update

As you can see, this part isn’t very complicated. We first call the AddNew method to create a new, blank record in our recordset. After assigning values to the field Competitor and Score, we then call the Update method to officially add this record to the recordset. And then we loop around and repeat this process with the next line in the text file.

Hold on a second; we need to stop and catch our breath. Whew!

So what happens after all the scores have been calculated and all the skaters have been added to the disconnected recordset? Well, to begin with, we use the Sort method to sort the recordset by score, and in descending order (that is, with the highest score first):

DataList.Sort = "Score DESC"

After calling the MoveFirst method to ensure that our cursor is positioned on the first record of the recordset, we use this line of code to echo back the values of the Competitor and Score fields for that first record:

Wscript.Echo "Gold Medal: " & DataList.Fields.Item("Competitor") & ", " & DataList.Fields.Item("Score")

Because we sorted the recordset in descending order, that’s also going to be the name and score of the skater who out-skated everyone else and won the competition. We call the NextRecord method to move on to record 2, and echo back the name and score of the silver medalist, then do the same thing for the bronze medalist. When all is said and done, we should see the following information displayed onscreen:

Gold Medal: Guido Chuffart, 88.2
Silver Medal: Jack Creasey, 85.8
Bronze Medal: Cecilia Cornejo, 85.4

And there you have it, folks: the three greatest skaters in the world.

Or at least the three greatest skaters in the scripting world.

Top of page