2008 Winter Scripting Games
Solution to Beginner Perl Event 9: Pool Party
Perl solution to Event 9 in the 2008 Winter Scripting Games.
Solutions are also available for VBScript and Windows PowerShell.
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:
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
my $database = "c:\\scripts\\games08\\pool.mdb";
my $table = "SwimmingPool";
$Conn = Win32::OLE->new("ADODB.Connection");
$RS = Win32::OLE->new("ADODB.Recordset");
$DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=$database;UID=;PWD=;";
$Conn->Open($DSN);
$SQL = "SELECT * FROM $table";
$RS->Open($SQL, $Conn, 1, 1);
$RS->MoveFirst;
until ($RS->EOF)
{
my $l = $RS->Fields("Length")->value;
my $w = $RS->Fields("Width")->value;
if (!$RS->Fields("Slope")->Value)
{
$d = $RS->Fields("Depth")->Value;
$vol = $l * $w * $d;
}
else
{
$sh = $RS->Fields("SStart")->Value;
$deep = $RS->Fields("SEnd")->Value;
$vol = $l * $w * (($deep + $sh)/2);
}
$vol = $vol * 1000;
$customer = $RS->Fields("Customer")->Value;
print "Name: $customer\n";
print " Volume in Liters: $vol\n\n";
$RS->MoveNext;
}
$RS->Close;
$Conn->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.
In order to use ADO in Perl we need to include the objects, methods and properties that we’ll be using to access these objects. We get access to these by including two use statements:
use Win32::OLE; use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
Next we define a couple of variables that set the database and tables:
my $database = "c:\\scripts\\pool.mdb"; my $table = "SwimmingPool";
We’ll use the $database variable when we open the database. Notice the double-backslash (\\) characters in this string. This is required anytime you’re defining paths in Perl. We also set the $table variable to the name of the table within the database we’ll be reading from.
Now it’s time to create two objects. We use the New method of the Win32::OLE module to do this:
$Conn = Win32::OLE->new("ADODB.Connection");
$RS = Win32::OLE->new("ADODB.Recordset");
The first object ($Conn) references a connection to ADO, while the second ($RS) references the recordset. Next comes this strange-looking string:
$DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=$database;UID=;PWD=;";
Here’s we’re defining our actual connection string. This string specifies the provider and driver required to connect to an Access database, as well as the name of the database itself. Don’t be too concerned about this, just know that anytime you want to connect to an Access database, use this string.
And now that we have the connection string, we can open the connection:
$Conn->Open($DSN);
To open a connection we call the Open method, passing it that complicated string we just defined.
Next we define our SQL Select statement:
$SQL = "SELECT * FROM $table";
This SQL statement will retrieve all the records (that’s what the * means – everything) from the SwimmingPool table (defined in the $table variable) in our database.
Now that we have an open connection to our database and we have our SQL statement for pulling records from that database we can open the recordset that we’ll use to read those records:
$RS->Open($SQL, $Conn, 1, 1);
We open the recordset by calling the Open method, passing it the SQL Select statement that specifies which records we want to open and the connection object. We also pass in a couple of parameters that 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 parameters 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 an until loop to do that:
until ($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:
my $l = $RS->Fields("Length")->value;
my $w = $RS->Fields("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 (!$RS->Fields("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 ! 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("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("SStart")->Value;
$deep = $RS->Fields("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("SStart")->Value;
$deep = $RS->Fields("SEnd")->Value;
Now that we have the shallow end and deep end depths we can calculate the volume:
$vol = $l * $w * (($deep + $sh)/2);
We wanted our volume calculated in liters, so we need to multiply the volume by 1000:
$vol = $vol * 1000;
In addition, we need to display the name of the customer who has ordered the pool we’re working with. We retrieve the customer by reading in the Value of the Customer field for this record:
$customer = $RS->Fields("Customer")->Value;
All that’s left at this point for the current record is to display our results:
print "Name: $customer\n"; print " Volume in Liters: $vol\n\n";
First we display the name of the customer, then we display the volume of water required to fill that customer’s pool. Notice the extra \n on the end of the print statement where we print out the volume. This gives us an additional blank line between this record and the next.
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; $Conn->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
