Perl DBI examples for DBD::mysql

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"; }