Hey, Scripting Guy! Event 4 *Solutions* from Expert Commentators (Beginner and Advanced; the high jump)

  

(Note: These solutions were written for Event 4.)   

Beginner Event 4: The High Jump

In the high jump event, you must soar to new heights as you read a database containing high jumpers training data in order to predict who will win on gameday.

Guest commentator: Bruno Terkaly

Image of guest commentator Bruno Terkaly

Bruno Terkaly is a Microsoft developer evangelist who covers Northern California. He has been a presenter and instructor in 10 countries, including Asia, Canada, Latin America, and Europe as well as cities too numerous to list throughout the U.S. Bruno is an expert at finding fast food at airports and navigating his way to the car-rental agencies, as well as always requesting the aisle seat on airplanes. Previously, Bruno also has experience as a Rapid Response Engineer at Microsoft where he helped Premier Customers with Advanced Debugging techniques and performed overnight, emergency fixes to production systems under massive pressure. Bruno has coded in many languages, SDKs, Frameworks, and operating systems, including DOS, Linux, UNIX, and of course, Windows. Bruno still finds time to use VI as his editor when he is not coding in Visual Studio.


VBScript solution

The code below executes a query that calculates the average of a jumper’s best personal jump height and season’s best jump height. Based on the highest average jump, a winner is predicted. To make things easy, I decided to use Microsoft Office Access to build the query. This is seen here.

Image of using Office Acces to build the query


The completed BeginnerEvent4Solution.vbs script is seen here.

BeginnerEvent4Solution.vbs

‘*********Declare Variables************
dim connectString
dim objConnection
dim objRecordSet
‘*********Setup and Init Variables*****
connectString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=HighJumperDatabase.mdb;User Id=admin;Password=;”
‘*********Main Code********************
wscript.echo “Opening Database..”
call OpenDatabase()

wscript.echo “Executing Query..”
call ExecQuery()

‘*********Sub Routines****************
sub OpenDatabase()
  on error resume next
  Set objConnection = CreateObject(“ADODB.Connection”)
  call CheckForError(“Creating connection”)
  Set objRecordSet = CreateObject(“ADODB.Recordset”)
  call CheckForError(“Creating recordset”)
  objConnection.Open “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
                        “Data Source = HighJumperDatabase.mdb”
  call CheckForError(“Openening Connection”)
  wscript.echo “Database opened successfully…”
end sub


sub ExecQuery()
   on error resume next
   objRecordSet.ActiveConnection = objConnection
  
   strSQL = “SELECT [High Jumper Data Query].Name, [High Jumper Data Query].Country, ” & _
            “[High Jumper Data Query].[Personal Best], [High Jumper Data Query].[Season Best], ” & _
            “(([High Jumper Data Query].[Personal Best]+[High Jumper Data Query].[Season Best])/2) as JumpAverage ” & _
            “FROM [High Jumper Data Query] ” & _
            “ORDER BY ([High Jumper Data Query].[Personal Best]+[High Jumper Data Query].[Season Best]/2) DESC;”

  
   objRecordSet.Source = strSQL
   objRecordSet.Open
   call CheckForError(“Opening recordset…”)
  
   Do Until objRecordSet.EOF
      strField1 = objRecordSet.Fields(“Name”).Value
      strField2 = objRecordSet.Fields(“Country”).Value
      strField3 = objRecordSet.Fields(“JumpAverage”).Value
      Wscript.Echo strField1 & “——-” & strField2 & “——-” & strField3
      objRecordSet.MoveNext
   Loop
   wscript.echo “Executed query successfully…”
end sub

sub checkForError(msg)
  if err.number <> 0 then
     wscript.echo “Error encountered for ” & msg & “, Error = ” & err.description
  end if
end sub

When we run the BeginnerEvent4Solution.vbs script, in CScript, we see the following information displayed.

Image of what's displayed after running the script

Guest commentator: Richard Siddaway

Richard Siddaway is an Infrastructure Technical Architect, and Windows PowerShell MVP. He is the founder and leader of the UK Windows PowerShell User Group. He maintains the Of PowerShell and Other Things blog, and is the author PowerShell in Practice.

Windows PowerShell solution

In the High Jump event, query an Access database and retrieve the name of the high jumper that you expect to win. The expected winner, is the one who has the highest training jumps. This translates to “Read the database, calculate sums, and find out who can jump the highest”

The first thing we do is look at the data to get an idea of what are we dealing with. We find the following fields.

Field

Data Type

ID

AutoNumber

Name

Text

Country

Text

Personal Best

Number

Season Best

Number

 

We can ignore the ID field as it is just an identifier for the row in the data table. The Country field isn’t important for this task. We definitely need the Name and the two types of scores. Having two numeric fields is going to complicate matters a bit but Windows PowerShell can easily handle this situation.

Let us look at how we can solve this. The first thing I do after the header rows is to clear the screen. I always do this in the scripting games. You cannot guarantee how the previous script left things and I do not want my display lost in among everything else.

To read the database, we use ADO.NET. This is because we do not have a cmdlet built into Windows PowerShell. ADO.NET supplies a number of classes for reading databases. You can track them down on MSDN as they are all in the System.data namespace. Because we  mustread an Office Access database, we use the OleDb classes.

We have to start by connecting to the database. This is not much more complicated than using the Get-Content cmdlet. We must create a connection by using the System.Data.OleDb.OleDbConnection class. This needs a connection string. There is a wonderful site called ConnectionStrings.Com that answers our needs. The site is worth remembering because it has connection strings for all the data stores you can imagine plus a few more. Our connection string is fairly simple. We just supply the provider (type of database) and the path to the database. If this was a production script we would use Test-Path before this line to make sure it exists. After creating the connection, we can open it. I assume the database is in the c:scripts folder. This is shown here.

$conn = New-Object System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:ScriptsHighJumperDatabase.mdb”)
$conn.Open()

Now we can read the data. We must create a by command using SQL and our connection. I have just selected all the data in the table with this command. Notice that the name of the table is in brackets []. That is because it has spaces in the name. The command will fall apart and error without the brackets []. We could actually answer the whole question at this point by writing a piece of SQL that is a bit more complicated. This is a Windows PowerShell competition rather than a SQL competition, so Iwill behave and save that for a blog posting.

Having created the command we can run it against the database by using the ExecuteReader method. This will pull back all records that match the SQL command into a DataReader object. This is seen here.

$cmd = New-Object System.Data.OleDb.OleDbCommand(“Select * FROM [High Jumper Data]”, $conn)
$data = $cmd.ExecuteReader()

Now that we have our data what are we going to do with it? We could keep playing with ADO.NET, but Windows PowerShell is the name of the game so we will turn it into objects we can work with. We deal with bunches of objects by using arrays, so we had better create one. We then loop through the DataReader by using a while loop. It will keep looping while there are still records in the reader pulling back the next record at each loop.

We can create a new object to hold the data. This could be just given as New-Object PSObject but it does not hurt to see the whole name. We can then use Add-Member to add the data as properties to the object. We don’t bother with the first column on data, the ID. We could have dropped it altogether by just putting the columns we needed in the select string but that’s more typing. At the end of the loop, the object is added to the array. This is shown here.

$jumpers = @()
while ($data.read()) {
         
          $jump = New-Object -TypeName System.Management.Automation.PSObject
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “Name” -Value $data.GetValue(1)
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “Country” -Value $data.GetValue(2)
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “PersonalBest” -Value $data.GetValue(3)
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “SeasonBest” -Value $data.GetValue(4)
         
          $jumpers += $jump

}

It is now time to close the connection to the database. This step is not required but it is good practice, especially if you have several connection going to the database. You will eventually use up all the connections if you do not close them. To close the connection, call the close method as seen here.

$conn.Close()

A title is written via Write-Host and then we create a sorted version of the array. I decided to sort on season-best jump and use personal best as a tie breaker as the expected winner should be the one who is jumping best at the moment. The sorted list of jumpers is displayed with Format-Table as seen here.

#$jumpers | sort PersonalBest -Descending | Format-Table -AutoSize

As a cosmetic touch I decide to swap the name so it appears as first-name last-name for readability. A quick split and then string substitution is used in the final Write-Host to predict the winner. When substituting into the Write-Host string, I have had to use a subexpression  e.g. $($names[1]) $($names[0]). This forces the inner expression to be evaluated and passed into the substitution. Play around with this a bit as it can get very interesting. The completed BeginnerEvent4Solution.ps1 script is seen here.

BeginnerEvent4Solution.ps1


cls
## open database connection
##  Assuming that the database should be
##  in C:Scripts as in previous years
$conn = New-Object System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:ScriptsHighJumperDatabase.mdb”)
$conn.Open()

## read the data
$cmd = New-Object System.Data.OleDb.OleDbCommand(“Select * FROM [High Jumper Data]”, $conn)
$data = $cmd.ExecuteReader()

## create an array
$jumpers = @()
while ($data.read()) {
         
          $jump = New-Object -TypeName System.Management.Automation.PSObject
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “Name” -Value $data.GetValue(1)
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “Country” -Value $data.GetValue(2)
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “PersonalBest” -Value $data.GetValue(3)
          Add-Member -InputObject $jump -MemberType NoteProperty -Name “SeasonBest” -Value $data.GetValue(4)
         
          $jumpers += $jump

}
## close the connection
$conn.Close()

#$jumpers | sort PersonalBest -Descending | Format-Table -AutoSize

Write-Host “The current results are:”

$order = $jumpers | sort SeasonBest, PersonalBest -Descending
$order | Format-Table -AutoSize

$names = ($order[0].Name).Split(“,”)

Write-Host “`n The expected winner is:- $($names[1]) $($names[0]) of $($order[0].Country)”

When the BeginnerEvent4Solution.ps1 script is run, it displays the output shown here:

Image of the script's output


Advanced Event 4: The High Jump

The high jump event is an event in which participants jump over a horizontal bar that is placed at different heights. For this event, you will be given the results of a series of jumps and be asked to draw a graph that indicates the trend of the jumps.

Guest commentator: Rajesh Ravindranath

Image of guest commentator Rajesh Ravindranath


Rajesh is a Software Development Engineer at Microsoft where he works on the Remote Desktop Services (RDS) team. He mainly focuses on management aspects of RDS. He was on the team that implemented Remote Desktop Services Management through Windows PowerShell. He is currently working on Windows PowerShell scripts to ease the management of Remote Desktop Services. For information related to Remote Desktop Services Management through Windows PowerShell visit Terminal Services Team Blog.

VBScript solution

How do we solve this problem? By reading the problem statement, we can infer that there are three major tasks to perform.

· Read statistics from the results file.

· Populate the data read into Excel application.

· Draw a chart from the data populated.

A look into the contents of the statistics file reveals that we have a list of N line separated entries for N participants. Each entry has a comma-separated list of items, of which, the first two correspond to the participant’s name and rest represent the score or height of the jump in each attempt.

To start with, we create an instance of the Excel.Application object and add a workbook to it. A workbook can be compared with a ledger with three worksheets in it by default. Because our task can be accomplished with a single worksheet we, will work with the first worksheet and name it “High Jump Data”. To make the script run in a cleaner fashion, the application instance is set to invisible mode until the data is populated and chart is plotted. This is shown here.

Set xlApp = CreateObject(“Excel.Application”)

xlApp.Visible = False                 &nb