Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Database Connectivity

by pschoonveld (Pilgrim)
on Apr 13, 2000 at 22:40 UTC ( #7507=snippet: print w/replies, xml ) Need Help??
Description: 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;

Replies are listed 'Best First'.
RE: Database Connectivity
by btrott (Parson) on Apr 14, 2000 at 00:42 UTC
    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") +;
RE: Database Connectivity
by httptech (Chaplain) on Apr 14, 2000 at 20:56 UTC
    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.
RE: Database Connectivity
by perlcgi (Hermit) on Apr 15, 2000 at 00:31 UTC
    There's a really nice basic SQL tutorial at complete with exercises and standalone SQL interpreter.
Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://7507]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2018-02-20 14:22 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (271 votes). Check out past polls.