NAME

DBIx::Simple::Examples - Examples of how to use DBIx::Simple

DESCRIPTION

DBIx::Simple provides a simplified interface to DBI, Perl's powerful database module.

EXAMPLES

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;

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

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));

Fetching all rows in one go

One big flattened list (primarily for single column queries)

    my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;

Rows as array references

    for my $row ($db->query('SELECT name, email FROM people')->arrays) {
        print "Name: $row->[0], Email: $row->[1]\n";
    }

Rows as hash references

    for my $row ($db->query('SELECT name, email FROM people')->hashes) {
        print "Name: $row->{name}, Email: $row->{email}\n";
    }

Fetching one row at a time

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

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

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

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

Building maps (also fetching all rows in one go)

A hash of hashes

    my $customers =
        $db
        -> query('SELECT id, name, location FROM people')
        -> map_hashes('id');

    # $customers = { $id => { name => $name, location => $location } }

A hash of arrays

    my $customers =
        $db
        -> query('SELECT id, name, location FROM people')
        -> map_arrays(0);

    # $customers = { $id => [ $name, $location ] }

A hash of values (two-column queries)

    my $names =
        $db
        -> query('SELECT id, name FROM people')
        -> map;

    # $names = { $id => $name }

Subquery emulation

deprecated

    $db->emulate_subqueries = 1;
    
    my @projects = $db->query(q{
        SELECT project_name
        FROM   projects
        WHERE  user_id = (
            SELECT id
            FROM   users
            WHERE  email = ?
        )
    }, $email )->flat;

LICENSE

There is no license. This software was released into the public domain. Do with it what you want, but on your own risk. The author disclaims any responsibility.

AUTHOR

Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/>

SEE ALSO

DBIx::Simple

cpanm DBIx::Simple

perl -MCPAN -e shell
install DBIx::Simple