GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  DBIX::SIMPLE::EXAMPLES (3)

.ds Aq ’

NAME

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

CONTENTS

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 }



EXAMPLES WITH SQL::Interp

If you have SQL::Interp installed, you can use the semi-abstracting method iquery. This works just like query, but with parts of the query interleaved with the bind arguments, passed as references.

You should read SQL::Interp. 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 sql and sql_type, you can import them with use SQL::Interp;

EXAMPLES WITH SQL::Abstract

If you have SQL::Abstract installed, you can use the abstracting methods select, insert, update, delete. These work like query, but instead of a query and bind arguments, use abstracted arguments.

You should read SQL::Abstract. These examples are not enough to fully understand all the possibilities.

The SQL::Abstract object is available (writable) through the abstract property.

The following examples are based on the documentation of SQL::Abstract.

    Overview

If you don’t like the defaults, just assign a new object:



    $db->abstract = SQL::Abstract->new(
        case    => lower,
        cmp     => like,
        logic   => and,
        convert => upper
    );



If you don’t assign any object, one will be created automatically using the default options. The SQL::Abstract module is loaded on demand.



    my $result = $db->select($table, \@fields, \%where, \@order);
    my $result = $db->insert($table, \%fieldvals || \@values);
    my $result = $db->update($table, \%fieldvals, \%where);
    my $result = $db->delete($table, \%where);

    for ($result->hashes) { ... }



    Complete examples

select



    my @tickets = $db->select(
        tickets, *, {
            requestor => inna,
            worker    => [nwiger, rcwe, sfz],
            status    => { !=, completed }
        }
    )->hashes;



insert

If you already have your data as a hash, inserting becomes much easier:



    $db->insert(people, \%data);



Instead of:



    $db->query(
        q[
            INSERT
            INTO people (name, phone, address, ...)
            VALUES (??)
        ],
        @data{name, phone, address, ... }
    );



update, delete



    $db->update(
        tickets, {
            worker    => juerd,
            status    => completed
        },
        { id => $id }
    )

    $db->delete(tickets, { id => $id });



where

The where method is not wrapped directly, because it doesn’t generate a query and thus doesn’t really have anything to do with the database module.

But using the abstract property, you can still easily access it:



    my $where = $db->abstract->where({ foo => $foo });



EXAMPLES WITH DBIx::XHTML_Table

If you have DBIx::XHTML_Table installed, you can use the result methods xto and html.

You should read DBIx::XHTML_Table. These examples are not enough to fully understand what is going on. When reading that documentation, note that you don’t have to pass hash references to DBIx::Simple’s methods. It is supported, though.

DBIx::XHTML_Table is loaded on demand.

    Overview

To print a simple table, all you have to do is:



    print $db->query(SELECT * FROM foo)->html;



Of course, anything that produces a result object can be used. The same thing using the abstraction method select would be:



    print $db->select(foo, *)->html;



A DBIx::XHTML_Table object can be generated with the xto (<B>XB>HTML_<B>TB>able <B>OB>bject) method:



    my $table = $db->query($query)->xto;



    Passing attributes

DBIx::Simple sends the attributes you pass to html both to the constructor and the output method. This allows you to specify both HTML attributes (like bgcolor) and options for XHTML_Table (like no_ucfirst and no_indent) all at once:



    print $result->html(
        tr         => { bgcolor => [ qw/silver white/ ] },
        no_ucfirst => 1
    );



    Using an XHTML_Table object

Not everything can be controlled by passing attributes. For full flexibility, the XHTML_Table object can be used directly:



    my $table = $db->query($query)->xto(
        tr => { bgcolor => [ qw/silver white/ ] }
    );

    $table->set_group(client, 1);
    $table->calc_totals(credit, %.2f);

    print $table->output({ no_ucfirst => 1 });  # note the {}!



EXAMPLES WITH Text::Table

$result->text(‘‘neat’’) Neither neat nor pretty, but useful for debugging. Uses DBI’s neat_list method. Doesn’t display column names.



    1, Camel, mammal
    2, Llama, mammal
    3, Owl, bird
    4, Juerd, undef



$result->text(‘‘table’’) Displays a simple table using ASCII lines.



    id | animal |  type
    ---+--------+-------
     1 |  Camel | mammal
     2 |  Llama | mammal
     3 |  Owl   | bird
     4 |  Juerd |



$result->text(‘‘box’’) Displays a simple table using ASCII lines, with an outside border.



    +----+--------+--------+
    | id | animal |  type  |
    +----+--------+--------+
    |  1 |  Camel | mammal |
    |  2 |  Llama | mammal |
    |  3 |  Owl   | bird   |
    |  4 |  Juerd |        |
    +----+--------+--------+



For table and box, you need Anno Siegel’s Text::Table module installed.

AUTHOR

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

SEE ALSO

DBIx::Simple, SQL::Abstract
Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 DBIX::SIMPLE::EXAMPLES (3) 2010-12-03

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.