Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

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;

Comment on Database Connectivity
Download Code
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 http://www.sqlcourse.com/ complete with exercises and standalone SQL interpreter.

Back to Snippets Section

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (8)
As of 2015-07-08 04:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (94 votes), past polls