DBIx::Simple::Examples(3)				User Contributed Perl Documentation				 DBIx::Simple::Examples(3)

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

       Rows as hash references

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

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

       or:

	   {
	       my $result = $db->query('SELECT name, email FROM people');
	       while ($result->into(my ($name, $email))) {
		   print "Name: $name, Email: $email
";
	       }
	   }

       Rows as lists

	   {
	       my $result = $db->query('SELECT name, email FROM people');
	       while (my @row = $result->list) {
		   print "Name: $row[0], Email: $row[1]
";
	       }
	   }

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

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

   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" (XHTML_Table Object) 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

perl v5.16.3							    2010-12-03						 DBIx::Simple::Examples(3)