120 likes | 133 Views
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.
E N D
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 + 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>