# Some basic DBI Functionality

# Tested using:
# Active State Perl
# WinXP
# Sql Server 7

# Note - By default, DBI objects will throw ODBC error messages in add
+ition to the die message
# (thus the extra line break in the die messages)


use DBI();

#Uses a formatted connection string
my $db = DBI->connect("DBI:ODBC:dsn=test_dsn;uid=skippy;pwd=jumpy;") o
+r die "\nBad Connection\n";


# Specify the recordsource using

my $qry = $db->prepare("SELECT * FROM tblNames");

# - OR -

# (The following references a Stored Procedure that returns the same r
+ecordset)
# my $qry = $db->prepare("spGetUsers");

$qry->execute() or die "\nBad SQL\n";


# Print the records using

#('hashref' loads the row into a hash)
#while (my $rw = $qry->fetchrow_hashref()) {
#      print "Name:\t$rw->{'FName'} $rw->{'LName'}\n";
#      print "ID:\t$rw->{'EmpID'}\n";
#      print "Title:\t$rw->{'Title'}\n\n";
#}

# - OR -

#('array' loads the row into an array)
while (my @rw = $qry->fetchrow_array()) {
      print "Name:\t$rw[0] $rw[1]\n";
      print "ID:\t$rw[2]\n";
      print "Title:\t$rw[4]\n\n";
}



#Extending the functionality a little bit
$cmdArg = "\u$ARGV[0]" or exit(0);

#Adds a new Employee
if ($cmdArg eq 'A') {
   #Get user input
   print "\nFirst Name:\t";
   chomp($Fnom = <STDIN>);
   print "Last Name:\t";
   chomp($Lnom = <STDIN>);
   print "Emp ID:\t";
   chomp($EmpID = <STDIN>);
   print "Title:\t";
   chomp($Title = <STDIN>);
   
   #Performing Insert function
   my $nuEmp = $db->prepare("INSERT INTO tblNames (FName, LName, EmpID
+, Title) VALUES 
      ('$Fnom', '$Lnom', $EmpID, '$Title')");
      
   $nuEmp->execute() or die "\nBad Insert\n";
   
   print $nuEmp->rows() . " Employees Added\n";
}

if ($cmdArg eq 'D') {
   #Get user input
   print "\nFirst Name:\t";
   chomp($Fnom = <STDIN>);
   print "Last Name:\t";
   chomp($Lnom = <STDIN>);
   
   #Passing arguments Fnom and Lnom to a Stored Procedure
   $delEmp = $db->prepare("spDelEmp '$Fnom', '$Lnom'");
   
   $delEmp->execute() or die "\nDelete Failed\n";
   
   print $delEmp->rows() . " Employees Removed\n";
}


$db->disconnect();
[download]