[Perl] Perl DBI examples for DBD::mysql

Perl DBI examples for DBD::mysql

• Connecting to a database
• Simple query
• Typical query
• Query using placeholders
• Execute and fetch in one command

For more examples see perldoc DBI and perldoc DBD::mysql or online versions available at e.g. http://cpan.uwinnipeg.ca/htdocs/DBI/DBI.html.

Note: For clarity, the following examples contain basic Perl code snippets which do not use my or other recommended practices. For happy Perl programming always use the pragma

use strict;
use warnings;

at the start of your scripts.

Connecting to a database

use DBI;
$dbh = DBI->connect('DBI:mysql:databasename', 'username', 'password'
	           ) || die "Could not connect to database: $DBI::errstr";
# (insert query examples here...)
$dbh->disconnect();

Connecting to a different host:

$dbh = DBI->connect('DBI:mysql:databasename;host=db.example.com', 'username', 'password',
	            { RaiseError => 1 }
	           );

Simple query

$dbh->do('CREATE TABLE exmpl_tbl (id INT, val VARCHAR(100))');
$dbh->do('INSERT INTO exmpl_tbl VALUES(1, ?)', undef, 'Hello');
$dbh->do('INSERT INTO exmpl_tbl VALUES(2, ?)', undef, 'World');
$c = $dbh->do('DELETE FROM exmpl_tbl WHERE id=1');
print "Deleted $c rows\n";

(Do not use $dbh->do() for SELECT statements because it does not return a statement handle, making it impossible to fetch data)

Typical query

$sth = $dbh->prepare('SELECT val FROM exmpl_tbl WHERE id=1');
$sth->execute();
$result = $sth->fetchrow_hashref();
print "Value returned: $result->{val}\n";

Query using placeholders

$sth = $dbh->prepare('SELECT id FROM exmpl_tbl WHERE val=?',
undef, 'World');
@result = $sth->fetchrow_array();
print "ID of World is $result[0]\n";

Execute and fetch in one command

$sth = $dbh->prepare('SELECT * FROM exmpl_tbl');
$results = $dbh->selectall_hashref('SELECT * FROM exmpl_tbl', 'id');
foreach my $id (keys %$results) {
  print "Value of ID $id is $results->{$id}->{val}\n";
}