Database Connectivity

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;` [download] 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") +;` [download]	[reply] [d/l] [select]
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.	[reply]
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;` [download] after connecting to the database. Then you don't have to write: `$sth->prepare("YOUR SQL HERE")or die "blah $DBI::errstr";` [download] You can just write: `$sth->prepare("YOUR SQL HERE");` [download] If the function fails, DBI will die and give you the reason.	[reply] [d/l] [select]


Clear questions and runnable code get the best and fastest answer
	PerlMonks