DBIx::Simple::Examples man page on OpenMandriva

Man page or keyword search:  
man Server   8135 pages
apropos Keyword Search (all sections)
Output format
OpenMandriva logo
[printable version]

DBIx::Simple::ExamplesUser Contributed Perl DocumentaDBIx::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]\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" (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)
[top]

List of man pages available for OpenMandriva

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net