Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

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 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.
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.
Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: snippet [id://7507]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2023-03-29 04:40 GMT
Find Nodes?
    Voting Booth?
    Which type of climate do you prefer to live in?

    Results (70 votes). Check out past polls.