1 / 12

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.

sherril
Download Presentation

Perl Database – Just Enough

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Perl Database – Just Enough Pepper

  2. 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

  3. 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

  4. Setting Env Variables for DBI • $ENV{"DBI_DRIVER"} = "mysql"; • $ENV{"DBI_DSN"} = "pepperdb"; • $ENV{"DBI_USER"} = "your user"; • $ENV{"DBI_PASS"} = "your password";

  5. 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

  6. 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

  7. 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

  8. 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

  9. Repeat for all rows • Surround with while: while (( $id, $name) = $sth ->fetchrow_array) { print "$name has ID $id\n"; }

  10. Clean-up • Close the statement object • $sth->finish(); • Disconnect from the database • $dbh->disconnect || die "Failed to disconnect\n";

  11. 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

  12. 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>

More Related