This simple application (sample_app.pl) enables you to query and enter employee information. The application establishes a connection to a SQL Server database and, based on information entered at the prompts, either queries a table to retrieve employee information or enters employee information into the database. This application is meant to demonstrate in a simplified manner how to connect to a database and enter or retrieve information using ODBC with PERL.
Table Creates and Population
For this application to work, you will need to create a table. In this example, the table name is employee. The Create statement for this table is straightforward:
CREATE TABLE employee (LastName char(20),FirstName char(15),EmpID varchar(5),
Office varchar(20), HireDate datetime)
The Sample Application
The sample application is named sample_app.pl and can be created in a text editor. This command-prompt application allows you either to query employee information or to enter employee information.
NOTE: You will need to modify the connect string to include a data source that is on your machine (such as SQL Server Wire Protocol) as well as an appropriate user ID and password for your database.
use DBI;
my $dbh = DBI->connect('DBI:ODBC:datasource','userid','pwd')
or die "Couldn't connect to database:" . DBI->errstr;
my $sth1 = $dbh->prepare('Select * from employee where LastName = ?')
or die "Couldn't prepare statement:" . $dbh->errstr;
my $sth2 = $dbh->prepare('insert into employee
(LastName,FirstName,EmpID,Office,HireDate) values (?,?,?,?,?)')
or die "Couldn't prepare statement:" . $dbh->errstr;
sub trimwhitespace($);
sub trimwhitespace($)
{
my $string = shift;
string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}
print "Employee Information: \n";
print "Enter 1 to Search Employee Information\n";
print "Enter 2 to Enter Employee Information: >" ;
$AppFn = <STDIN>;
chomp $AppFn;
if ($AppFn == 1) {
print "\nEnter Employee last name: >";
$Last = <STDIN>;
$Last = trimwhitespace($Last);
my @data;
$sth1->execute($Last)
or die "Couldn't execute the statement:" .$sth1->errstr;
#READ ONLY THE MATCHING RECORDS AND THEN PRINT THEM OUT
while (@data = $sth1->fetchrow_array()) {
my $Last = trimwhitespace($data[0]);
my $First = trimwhitespace($data[1]);
my $EmpID = trimwhitespace($data[2]);
my $Office = trimwhitespace($data[3]);
my $Hire = trimwhitespace($data[4]);
print "\nLast Name: $Last\nFirstName: $First\nEmployee ID: $EmpID\nOffice:
$Office\nHire Date: $Hire\n";
}
if ($sth1->rows == 0) {
print "No names matched `$Last'.\n\n";
}
$sth1->finish;
$dbh->disconnect;
}
if ($AppFn == 2){
print "\nEnter Employee Last Name: >";
$Last = <STDIN>;
$Last = trimwhitespace($Last);
print "\nEnter Employee First Name: >";
$First = <STDIN>;
$First = trimwhitespace($First);
print "\nEnter Employee ID: >";
$EmpID = <STDIN>;
$EmpID = trimwhitespace($EmpID);
print "\nEnter Employee office: >";
$Office = <STDIN>;
$Office = trimwhitespace($Office);
print "\nEnter Employee hire date (yyyy-mm-dd): >";
$tmpHire = <STDIN>;
$tmpHire = trimwhitespace($tmpHire);
$Hire = "{d '$tmpHire'}";
print $Hire;
$sth2->execute($Last,$First,$EmpID,$Office,$Hire)
or die "Couldn't execute the statement:" .$sth2->errstr;
$sth2->finish;
$dbh->disconnect;
}
Annotation
In this annotation section, the code sample is followed by an explanatory paragraph.
This code states which modules are used in the sample application. For sample_app.pl, the DBI module enables the application to get information from the SQL Server database through ODBC.
my $dbh = DBI->connect('DBI:ODBC:Datasource','userid','pwd')
or die "Couldn't connect to database:" . DBI->errstr;
This is the database connection. The parenthetical statement indicates that DBI will be used for ODBC, and which data source, user ID, and password will be used to connect. You will need to modify the code to use an existing data source that you have created, as well as the user ID and password that you use to connect to the database.
my $sth1 = $dbh->prepare('Select * from employee where LastName = ?')
or die "Couldn't prepare statement:" . $dbh->errstr;
my $sth2 = $dbh->prepare('insert into employee
(LastName,FirstName,EmpID,Office,HireDate) values (?,?,?,?,?)')
or die "Couldn't prepare statement:" . $dbh->errstr;
These two statements are prepares for the SQL statements that the application uses.
is to retrieve any employee information that matches the input from the user.
The second statement ($sth2)
is to insert the employee data that the user enters from a series of prompts.
sub trimwhitespace($);
sub trimwhitespace($)
{
my $string = shift;
string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}
This code declares and defines a subroutine that strips any leading and trailing white space from the variables in the application.
print "Employee Information: \n";
print "Enter 1 to Search Employee Information\n";
print "Enter 2 to Enter Employee Information: >" ;
$AppFn = <STDIN>;
trimwhitespace($AppFn);
This code prompts the user to enter the function that should be performed, assigns the input to a variable, and strips the variable of any trailing white space.
if ($AppFn == 1) {
print "\nEnter Employee last name: >";
$Last = <STDIN>;
$Last = trimwhitespace($Last);
my @data;
$sth1->execute($Last)
or die "Couldn't execute the statement:" .$sth1->errstr;
If the user enters 1
to search employee information, this code takes the user input and assigns it to
The first prepared statement for
selecting employee information ($sth1) is then executed with this
parameter ($Last).
#READ ONLY THE MATCHING RECORDS AND THEN PRINT THEM OUT
while (@data = $sth1->fetchrow_array()) {
my $Last = trimwhitespace($data[0]);
my $First = trimwhitespace($data[1]);
my $EmpID = trimwhitespace($data[2]);
my $Office = trimwhitespace($data[3]);
my $Hire = trimwhitespace($data[4]);
print "\nLast Name: $Last\nFirstName: $First\nEmployee ID: $EmpID\nOffice:
$Office\nHire Date: $Hire\n";
}
This code reads the result set from the array, strips the results of any leading or trailing white space, and prints the results on the screen.
if ($sth1->rows == 0) {
print "No names matched `$Last'.\n\n";
}
$sth1->finish;
$dbh->disconnect;
If there are no matching results from the query, this code prints out a message stating that there were no matches. It then drops the connection.
if ($AppFn == 2){
print "\nEnter Employee Last Name: >";
$Last = <STDIN>;
$Last = trimwhitespace($Last);
print "\nEnter Employee First Name: >";
$First = <STDIN>;
$First = trimwhitespace($First);
print "\nEnter Employee ID: >";
$EmpID = <STDIN>;
$EmpID = trimwhitespace($EmpID);
print "\nEnter Employee office: >";
$Office = <STDIN>;
$Office = trimwhitespace($Office);
print "\nEnter Employee hire date (yyyy-mm-dd): >";
$tmpHire = <STDIN>;
$tmpHire = trimwhitespace($tmpHire);
$Hire = "{d '$tmpHire'}";
print $Hire;
$sth2->execute($Last,$First,$EmpID,$Office,$Hire)
or die "Couldn't execute the statement:" .$sth2->errstr;
$sth2->finish;
$dbh->disconnect;
}
If the user enters 2
to insert employee information, then this section of code prompts the user to enter the associated employee information, assigns each input to a variable, and strips it of any leading or trailing white spaces.
The prepared statement ($sth2)
to insert the data is then executed with the associated parameters. The statement finishes and the connection is dropped.
Running the Application
Refer to the instructions for executing the application in your particular PERL environment. If you are running WinActive's PERL implementation on Windows, change to the directory where the application resides and enter the following line at the command prompt:
The following prompt is displayed,
at which point the user types 2
and presses ENTER to insert employee information.
Employee Information:
Enter 1 to Search Employee Information
Enter 2 to Enter Employee Information: >2
The following prompts are then displayed (shown with sample responses):
Employee Information:
Enter 1 to Search Employee Information
Enter 2 to Enter Employee Information: >2
Enter Employee Last Name: >Smith
Enter Employee First Name: >Joseph
Enter Employee ID: >00001
Enter Employee office: >Raleigh
Enter Employee hire date (yyyy-mm-dd): >2005-01-01
The application then updates the database with the appropriate information. To query the database and view the information, run the application again, this time entering 1 to search employee information:
Employee Information:
Enter 1 to Search Employee Information
Enter 2 to Enter Employee Information: >1
At the prompt, enter the name of the employee described in the previous step to view the information in the database.
Enter Employee last name: >Smith
Last Name: Smith
FirstName: Joseph
Employee ID: 00001
Office: Raleigh
Hire Date: 2005-01-01 00:00:00.000