Perl Database – Just Enough

Transcript Perl Database – Just Enough

Perl Database – Just Enough
Pepper
Overview
• Use the DBI package
– use DBI;
•
•
•
•
Define all connection info
Connect to DB
Prepare an sql statement text
Execute the statement
– Your preparation variable will hold the result
• Fetch the result one row at a time
Connect
• Info needed to connect
– What connection type (dbi driver): mysql
• Dbi – database interface
– What database name: (dbi_dsn): your db
– What user : (dbi_user): your user
– What password: (dbi_password): your password
– Set into environment variables or on connect
command
Setting Env Variables for DBI
• $ENV{"DBI_DRIVER"} = "mysql";
• $ENV{"DBI_DSN"} = "pepperdb";
• $ENV{"DBI_USER"} = "your user";
• $ENV{"DBI_PASS"} = "your password";
Make the connection
$dbh=DBI->connect() or
die "Error opening database: $DBI::errstr\n";
• $dbh is your connection variable now.
– Ask $dbh for anything you want from the
database
• $DBI::errstr holds last error message
• $dbh can be whatever name you want
Prepare SQL Statement
$sth=$dbh->prepare("SELECT * from
tutorials_tbl;")
or die "Prepare failed: $DBI::errstr\n";
• Create a statement object
– It needs an sql statement string
– Build it in a string or right in the prepare
command argument
• $sth holds what you plan to execute now
– It knows it is working with $dbh
Execute your SQL statement
$sth->execute() or
die "Couldn't execute query: $DBI::errstr\n";
• $sth now holds all the rows your query
returned
– Same thing you would see when you ran the
query on mysql
Fetch your results
• $sth holds the resulting rows, but you want
them.
• Ask $sth to send back an array of the columns
of one row at a time:
– $sth ->fetchrow_array
• Put the fetched array into variables, as many
as you expect (one per column)
• ( $id, $name) = $sth ->fetchrow_array
Repeat for all rows
• Surround with while:
while (( $id, $name) = $sth ->fetchrow_array) {
print "$name has ID $id\n";
}
Clean-up
• Close the statement object
– $sth->finish();
• Disconnect from the database
– $dbh->disconnect || die "Failed to disconnect\n";
Database summary
•
•
•
•
•
Use the DBI package
Define all connection info
Connect to DB
Prepare an sql statement text
Execute the prepared statement
– Your preparation variable will hold the result
• Fetch the result one row at a time
Web site
• Set up the top of your script to indicate perl and
plain text
#!/usr/bin/perl
print "Content-type:text/plain\n\n";
• You can put html tags which the browser will
understand, or not
• Ensure that others can read and execute:
– chmod o+rx yourfile.pl
• Web address
– www.adelphi.edu/~pe16132/271/yourscript.pl
– Must be under public_html
HelloWorld perl on internet
The program is:
#!/usr/bin/perl
print "Content-type:text/plain\n\n";
print "content-type:text/html; charset=utf-8\n\n";
print "hello world!";
Taking in parms
• Use an html form to prompt for input
• Change our script to use CGI
• Print out a form section of html that will run
your own script
<form method="post" action="myscript.pl">
<input type="test" name="input" />
<input type="submit" value="Submit /">
</form>
• Retrieve from the the search word from the
form
Script changes to retrieve results
print "content-type:text/html; charset=utf-8\n\n";
use strict;
use CGI qw/:standard/;
my $search = param("input")||"";
print qq(<!DOCTYPE html><head></head><body>
<form method="post" action="testinput4.pl">
<label for="input">What is the search word:
</label><input type="text" name="input" value="" />
<input type="submit" value="Post Input" /></form>);
if ($search) {
print qq(<br />the search word is $search<br />);
yourpage();
}
print qq(</body></html>);
sub yourpage {
print "do whatever you want in your page here <br />";
print "and you can use $search <br />";
print "\n does nothing ";
print "and you need to but br inside angle brackets to get the tne next line <br />";
}
Summary Web + Database
• Create a perl script that uses a preset search word
input form
• Insert your code inside the yourpage method
– Fill in your env variables for db connection
– Connect, Prepare a statement, Execute Query
– Fetch and Display your results
• Place script in public_html
• Set permissions (chmod o+rx your script.pl)
• Run as www.adelphi.edu/~<your dir>/<yourscript>

Perl Database – Just Enough

Transcript Perl Database – Just Enough

Directory