#!/usr/local/bin/perl

### You will need to set this up for your installation.
### Assign appropriate values for the following:
$your_machine_name = "";
$user = "";
$pw = "";
$dbname = "";

# nameletters.pl
# desc: for each alphabet letter, show count of patrons
# desc: whose last names begin with that letter
# wordsettings: 
# end_autoz

# written by Roy Zimmer, Western Michigan University

use DBI;

### connect to database
$dbh = DBI->connect('DBI:Oracle:host=$your_machine_name;sid=LIBR',
                    $user, $pw)
  or die "connecting: $DBI::errstr";

$letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$idx = 0;
while ($idx < 26)
{
  $theletter = substr($letters, $idx, 1);
  $sqlquery = sprintf("select count (*)
                       from $dbname.patron
                       where last_name like '%s%c'", $theletter, 37);
  $sth = $dbh->prepare($sqlquery) or die "preparing query statement";
  $rc = $sth->execute;
  $count = $sth->fetchrow_array;
  printf ("%s: %6.6d\n", $theletter, $count);
  $idx++;
}

$sth->finish;
$dbh->disconnect;