Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Basic SQL Server Interaction

by bilfurd (Hermit)
on Jul 11, 2002 at 04:37 UTC ( [id://180939]=CUFP: print w/replies, xml ) Need Help??

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();

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: CUFP [id://180939]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2024-03-28 20:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found