=head1 NAME
DBIx::Simple::Examples - Examples of how to use DBIx::Simple
=head1 DESCRIPTION
DBIx::Simple provides a simplified interface to DBI, Perl's powerful database
module.
=head1 EXAMPLES
=head2 General
#!/usr/bin/perl -w
use strict;
use DBIx::Simple;
# Instant database with DBD::SQLite
my $db = DBIx::Simple->connect('dbi:SQLite:dbname=file.dat')
or die DBIx::Simple->error;
# Connecting to a MySQL database
my $db = DBIx::Simple->connect(
'DBI:mysql:database=test', # DBI source specification
'test', 'test', # Username and password
{ RaiseError => 1 } # Additional options
);
# Using an existing database handle
my $db = DBIx::Simple->connect($dbh);
# Abstracted example: $db->query($query, @variables)->what_you_want;
$db->commit or die $db->error;
=head2 Simple Queries
$db->query('DELETE FROM foo WHERE id = ?', $id) or die $db->error;
for (1..100) {
$db->query(
'INSERT INTO randomvalues VALUES (?, ?)',
int rand(10),
int rand(10)
) or die $db->error;
}
$db->query(
'INSERT INTO sometable VALUES (??)',
$first, $second, $third, $fourth, $fifth, $sixth
);
# (??) is expanded to (?, ?, ?, ?, ?, ?) automatically
=head2 Single row queries
my ($two) = $db->query('SELECT 1 + 1')->list;
my ($three, $four) = $db->query('SELECT 3, 2 + 2')->list;
my ($name, $email) = $db->query(
'SELECT name, email FROM people WHERE email = ? LIMIT 1',
$mail
)->list;
Or, more efficiently:
$db->query('SELECT 1 + 1')->into(my $two);
$db->query('SELECT 3, 2 + 2')->into(my ($three, $four));
$db->query(
'SELECT name, email FROM people WHERE email = ? LIMIT 1',
$mail
)->into(my ($name, $email));
=head2 Fetching all rows in one go
=head3 One big flattened list (primarily for single column queries)
my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;
=head3 Rows as array references
for my $row ($db->query('SELECT name, email FROM people')->arrays) {
print "Name: $row->[0], Email: $row->[1]\n";
}
=head3 Rows as hash references
for my $row ($db->query('SELECT name, email FROM people')->hashes) {
print "Name: $row->{name}, Email: $row->{email}\n";
}
=head2 Fetching one row at a time
=head3 Rows into separate variables
{
my $result = $db->query('SELECT name, email FROM people');
$result->bind(my ($name, $email));
while ($result->fetch) {
print "Name: $name, Email: $email\n";
}
}
or:
{
my $result = $db->query('SELECT name, email FROM people');
while ($result->into(my ($name, $email))) {
print "Name: $name, Email: $email\n";
}
}
=head3 Rows as lists
{
my $result = $db->query('SELECT name, email FROM people');
while (my @row = $result->list) {
print "Name: $row[0], Email: $row[1]\n";
}
}
=head3 Rows as array references
{
my $result = $db->query('SELECT name, email FROM people');
while (my $row = $result->array) {
print "Name: $row->[0], Email: $row->[1]\n";
}
}
=head3 Rows as hash references
{
my $result = $db->query('SELECT name, email FROM people');
while (my $row = $result->hash) {
print "Name: $row->{name}, Email: $row->{email}\n";
}
}
=head2 Building maps (also fetching all rows in one go)
=head3 A hash of hashes
my $customers =
$db
-> query('SELECT id, name, location FROM people')
-> map_hashes('id');
# $customers = { $id => { name => $name, location => $location } }
=head3 A hash of arrays
my $customers =
$db
-> query('SELECT id, name, location FROM people')
-> map_arrays(0);
# $customers = { $id => [ $name, $location ] }
=head3 A hash of values (two-column queries)
my $names =
$db
-> query('SELECT id, name FROM people')
-> map;
# $names = { $id => $name }
=head1 EXAMPLES WITH SQL::Interp
If you have SQL::Interp installed, you can use the semi-abstracting method
C. This works just like C, but with parts of the query
interleaved with the bind arguments, passed as references.
You should read L. These examples are not enough to fully
understand all the possibilities.
The following examples are based on the documentation of SQL::Interp.
my $result = $db->iquery('INSERT INTO table', \%item);
my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);
# These two select syntax produce the same result
my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});
for ($result->hashes) { ... }
Use a syntax highlighting editor for good visual distinction.
If you need the helper functions C and C, you can import them
with C