Just enough DBI to be dangerous

A presentation given at the Raleigh Perl Mongers meeting on September 21, 2000.

Slides

The slides of the first, introductory portion were made in StarOffice and reformatted here into HTML for your viewing pleasure. I'm not sure how much utility they will have as they stand alone (they were not made to be exhaustive, just to give a framework for the talk).

Sample Code

The scripts below were formatted for HTML viewing using Code2HTML. You can also download the scripts (together) as a tarball.

• medline-sql.pl - a script showing the creation of a table and the passthrough features of the DBI (SQL passed through DBI doesn't have to be standard SQL, as long as the database can interpret it). The table created is for storing information about articles from the scientific literature
• simple-search.pl - this script prompts for a name and then searches for that name in a local database. The standard SQL '%' wildcard is used for searching. Check local listings of Your Favorite Database for any specialized searching or pattern matching techniques it might have (for example, RLIKE in MySQL does regexp matching) or for case sensitivity issues.
• batchload.pl - a quick script that could be modified to a useful utility. Reads in a file containing some meta-info in the first few lines (database name, user, table) then a list of column headers folllowed by lines containing '^' delimited fields (all the good delimiters are taken). These values are then entered into the appropriate table.
• prepare-compare.pl - this script performs a rather crude comparison of 'prepared' execute calls versus the use of interpolated strings directly into the SQL to be sent to the database.
• transactions.pl - shows the use of transactions and of catching errors raised by the DBI driver. Transaction support (if your database has it) allows you to perform a number of operations and at the end decide if you really meant it (in which case you commit()) or didn't (so you rollback()).
• populate-medline-table-online.pl - this script takes as commandline arguments a list of PubMED ids (a type of unique identifier for citations) and, using the LWP package, fetches information about these references from NCBI's PubMED database online. This information is then inserted into a local table (the one defined by medline-sql.pl).

Handouts

These are in .pdf format

• Sheet 1 has a short glossary of abbreviations and a list of some resources (online or paper) that a beginner in DBI might find useful.
• Sheet 2 lists some tips/tricks and also a few ideas (some more serious than others) for what you can do with perl's DBI.