The inspiration for this was simple: I got tired of waiting for Access or SQL server to load when I was asked to perform some piddly 20 second task.
I had to do a little bit of searching and a fair bit of experimentation, but I was able to dummy up a bit of code that can easily be modified to make the most of all the Stored Procedures on the server.
# 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();