DBIx::Simple::Examples - Examples of how to use DBIx::Simple
DBIx::Simple provides a simplified interface to DBI, Perl's powerful database module.
#!/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;
$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
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));
my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;
for my $row ($db->query('SELECT name, email FROM people')->arrays) { print "Name: $row->[0], Email: $row->[1]\n"; }
for my $row ($db->query('SELECT name, email FROM people')->hashes) { print "Name: $row->{name}, Email: $row->{email}\n"; }
{ 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"; } }
{ my $result = $db->query('SELECT name, email FROM people'); while (my @row = $result->list) { print "Name: $row[0], Email: $row[1]\n"; } }
{ my $result = $db->query('SELECT name, email FROM people'); while (my $row = $result->array) { print "Name: $row->[0], Email: $row->[1]\n"; } }
{ my $result = $db->query('SELECT name, email FROM people'); while (my $row = $result->hash) { print "Name: $row->{name}, Email: $row->{email}\n"; } }
my $customers = $db -> query('SELECT id, name, location FROM people') -> map_hashes('id'); # $customers = { $id => { name => $name, location => $location } }
my $customers = $db -> query('SELECT id, name, location FROM people') -> map_arrays(0); # $customers = { $id => [ $name, $location ] }
my $names = $db -> query('SELECT id, name FROM people') -> map; # $names = { $id => $name }
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.
select
insert
update
delete
query
You should read SQL::Abstract. These examples are not enough to fully understand what is going on.
The SQL::Abstract object is available (writable) through the abstract property.
abstract
The following examples are based on the documentation of SQL::Abstract.
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) { ... }
my @tickets = $db->select( 'tickets', '*', { requestor => 'inna', worker => ['nwiger', 'rcwe', 'sfz'], status => { '!=', 'completed' } } )->hashes;
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', ... } );
$db->update( 'tickets', { worker => 'juerd', status => 'completed' }, { id => $id } ) $db->delete('tickets', { id => $id });
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.
where
But using the abstract property, you can still easily access it:
my $where = $db->abstract->where({ foo => $foo });
If you have DBIx::XHTML_Table installed, you can use the result methods xto and html.
xto
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.
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 (XHTML_Table Object) method:
my $table = $db->query($query)->xto;
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:
bgcolor
no_ucfirst
no_indent
print $result->html( tr => { bgcolor => [ qw/silver white/ ] }, no_ucfirst => 1 );
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 {}!
$result->text("neat")
Neither neat nor pretty, but useful for debugging. Uses DBI's neat_list method. Doesn't display column names.
neat_list
'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.
table
box
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.
Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/>
DBIx::Simple, SQL::Abstract
To install DBIx::Simple, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Simple
CPAN shell
perl -MCPAN -e shell install DBIx::Simple
For more information on module installation, please visit the detailed CPAN module installation guide.