2008 Winter Scripting Games

Solution to Beginner Windows PowerShell Event 9: Pool Party

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

Solutions are also available for VBScript and Perl.

Event 9 – Pool Party

To successfully complete this event you needed to be able to do a little bit of simple arithmetic, and more importantly you needed to learn how to read from a Microsoft Access database. But really, it wasn’t so much reading from an Access database as reading from an ADODB recordset. Take a look at the script and we’ll explain what we mean:

$adOpenStatic = 3
$adLockOptimistic = 3

$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADoDB.Recordset

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = c:\scripts\pool.mdb")
$rs.Open("SELECT * FROM SwimmingPool", $cn, $adOpenStatic, $adLockOptimistic)

$rs.MoveFirst()

while (-not $rs.EOF)
{
    $l = $rs.Fields.Item("Length").Value
    $w = $rs.Fields.Item("Width").Value

    if (-not $rs.Fields.Item("Slope").Value)
    {
        $d = $rs.Fields.Item("Depth").Value
        $vol = $l * $w * $d
    }
    else
    {
        $sh = $rs.Fields.Item("SStart").Value
        $deep = $rs.Fields.Item("SEnd").Value
        $vol = $l * $w * (($deep + $sh)/2)
    }

    "Name: " + $rs.Fields.Item("Customer").Value
    "    Volume in Liters: " + $vol * 1000
    " "

    $rs.MoveNext()
}

$rs.Close()
$cn.Close()

The event instructions mention that you don’t need to have Access installed on your computer to complete this event. The reason for this is that it’s possible to read from (and write to) an Access database using a disconnected recordset – in other words by using ADODB – which just happens to be a part of the Windows operating system. And that’s how we’re going to solve this event.

We start by declaring a couple of constants:

$adOpenStatic = 3
$adLockOptimistic = 3

We’ll use these constants to open our recordset. But first we need to create two objects. We use the New-Object cmdlet to do this:

$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADoDB.Recordset

Notice that we specify the –comobject parameter to create these objects. The first ($cn) references a connection to ADO, while the second ($rs) references the recordset. Now we need to open each of these, starting with the connection:

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = c:\scripts \pool.mdb")

To open a connection we call the Open method, passing it a string that specifies the provider and the data source. In this case the provider is the provider for an Access database, and the data source is the name of that database.

Now that we have an open connection to our database (pool.mdb) we can open a recordset that we’ll use to read from that database:

$rs.Open("SELECT * FROM SwimmingPool", $cn, $adOpenStatic, $adLockOptimistic)

We open the recordset by calling the Open method, passing it the SQL Select statement that specifies which records we want to open. Let’s take a quick look at the Select statement:

SELECT * FROM SwimmingPool

Here’s we’re retrieving all the records (that’s what the * means – everything) from the SwimmingPool table in our database.

We also pass the Open method the connection we just opened, as well as the constants we defined earlier. (These constants have to do with restricting access to the database while we’re reading from it. Don’t worry too much about this, for the most part you can just plug in these constants whenever you want to connect to a database.)

Now it’s time to start reading the records we just retrieved. We begin by making sure we start with the first record in our recordset:

$rs.MoveFirst()

The MoveFirst method does just that. We’re at the first record, but we know we’re going to have to read through all the records, so we set up a while loop to do that:

while (-not $rs.EOF)

We’re going to continue looping until we’ve reached the end of the recordset, at which point the recordset’s EOF property will be True.

Inside the loop we read the Length and Width fields from the recordset entry and store them in the $l and $w variables:

$l = $rs.Fields.Item("Length").Value
$w = $rs.Fields.Item("Width").Value

One of the things we needed to do is determine whether each swimming pool is uniform depth or whether there’s a slope to it, with a deep end and a shallow end. We do this by checking the Slope field:

if (-not $rs.Fields.Item("Slope").Value)

The Slope field is a Yes/No (True/False) field. First we check to see if there is not a slope (that’s what the -not in the if statement is for). If the Slope field is false, meaning there is no slope, we fall into the If statement and perform these lines of code:

$d = $rs.Fields.Item("Depth").Value
$vol = $l * $w * $d

Because there’s no slope we can retrieve the depth of the pool from the Depth field and assign it to the variable $d. At this point we can calculate the volume of water necessary to fill this pool:

$vol = $l * $w * $d

Remember the volume of water is calculated by multiplying the length x width x depth, which we’ve then assigned to the variable $vol.

If the pool has a slope to it, our Slope field will be True so we’ll fall into the else part of our if statement:

$sh = $rs.Fields.Item("SStart").Value
$deep = $rs.Fields.Item("SEnd").Value
$vol = $l * $w * (($deep + $sh)/2)

To calculate the volume of water in a pool with a slope to it we average the depth of the deep end with the depth of the shallow end ((deep end + shallow end)/2). To do that we need to read from the SStart and SEnd fields to retrieve those depths:

$sh = $rs.Fields.Item("SStart").Value
$deep = $rs.Fields.Item("SEnd").Value

Now that we have the shallow end and deep end depths we can calculate the volume:

$vol = $l * $w * (($deep + $sh)/2)

All that’s left at this point for the current record is to display our results:

    "Name: " + $rs.Fields.Item("Customer").Value
    "    Volume in Liters: " + $vol * 1000
    " "

First we display the name of the customer (stored in the Customer field), then we display the volume of water required to fill that customer’s pool. Notice we multiplied the volume by 1000 to get the volume in liters. We then display a blank line. Before we loop around to read the next record, we have to call the MoveNext method on the recordset to manually move to that next record:

$rs.MoveNext()

At this point we loop around and start over with the next customer. After we’ve read through the entire database we can close our recordset and our connection, and we’re done:

$rs.Close()
$cn.Close()

And here are our results:

Name: Jones
    Volume in Liters: 2500000

Name: Myer
    Volume in Liters: 1500000

Name: Smith
    Volume in Liters: 1500000

Name: Kumar
    Volume in Liters: 1200000

Name: Chen
    Volume in Liters: 1500000

Name: Garcia
    Volume in Liters: 1058000

Top of page