Database Connectivity

[pschoonveld]

The best way to connect to a database is through the perl DBI module. This makes access to any given SQL db a cinch. Just install the DBI module, and then the DBD module for the given database. The DBD module is a driver not completely unlike a JDBC driver in Java.

Here is an example to connect to a PostgreSQL database, make a select and read the data. Also, there is data for an INSERT, DROP, or other non-returning stuff.

# Use DBI for generic database access
use DBI;
# DBI connect
$dbh = DBI->connect("DBI:Pg:dbname=$DBName", "$DBUserName", "$DBPasswd
+")
   or die "connectiong: $DBI::errstr";
# Make an sql SELECT
$query = "SELECT * from table1 WHERE pacos='tacos'";
# this is goofy, you prepare it, then actually execute it
# beware, this does not mean it is a prepared statement
$results = $dbh->prepare($query);
$results->execute or die "Exec err: ", $dbh->errstr;
# get the results and print them out
while ((@row) = $results->fetchrow_array) {
     print join(" | ", @row), "\n";
}

# a simple insert
$sql = "INSERT into table1 (pacos) values ('tacos')";
$dbh->do($sql) or print "Error inserting...";

# Disconnect at the end
# $dbh->disconnect;

[btrott]

Consider using placeholders instead of actually specifying the values--it'll speed up subsequent (equivalent) database queries because the database driver can then pull the compiled statement out of the cache.

my $query = "SELECT * from table1 WHERE pacos=?";
my $results = $dbh->prepare($query);
$results->execute("tacos") or die "Exec err: ", $dbh->errstr;

DBI will take care of all the quoting for you. Or, if you'd rather not use placeholders (for whatever reason), at least don't try to do the quoting yourself; use:

my $query = "SELECT * from table1 WHERE pacos=" . $dbh->quote("tacos")
+;

[httptech]

When using DBI you can save some typing by setting:

$dbh->{'RaiseError'} = 1;

after connecting to the database. Then you don't have to write:

$sth->prepare("YOUR SQL HERE")or die "blah $DBI::errstr";

You can just write:

$sth->prepare("YOUR SQL HERE");

If the function fails, DBI will die and give you the reason.

[perlcgi]

There's a really nice basic SQL tutorial at http://www.sqlcourse.com/ complete with exercises and standalone SQL interpreter.