™.. DBIx::Simple::Examples - Online Linux Manual PageSection : 3
Updated : 2007-07-14
Source : perl v5.10.1
Note : User Contributed Perl Documentation
NAMEDBIx::Simple::Examples − Examples of how to use DBIx::Simple
DESCRIPTIONDBIx::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 goOne 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 timeRows 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::InterpIf you have SQL::Interp installed, you can use the semi-abstracting method \*(C`iquery\*(C'\fR. This works just like \f(CW\*(C`query\*(C'\fR, 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 \*(C`sql\*(C'\fR and \f(CW\*(C`sql_type\*(C'\fR, you can import them with \*(C`use SQL::Interp;\*(C'\fR
EXAMPLES WITH SQL::AbstractIf you have SQL::Abstract installed, you can use the abstracting methods \*(C`select\*(C'\fR, \f(CW\*(C`insert\*(C'\fR, \f(CW\*(C`update\*(C'\fR, \f(CW\*(C`delete\*(C'\fR. These work like \f(CW\*(C`query\*(C'\fR, 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 \*(C`abstract\*(C'\fR property. The following examples are based on the documentation of SQL::Abstract.
OverviewIf 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 examplesselect 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 \*(C`where\*(C'\fR 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 \*(C`abstract\*(C'\fR property, you can still easily access it: my $where = $db−>abstract−>where({ foo => $foo });
EXAMPLES WITH DBIx::XHTML_TableIf you have DBIx::XHTML_Table installed, you can use the result methods \*(C`xto\*(C'\fR and \f(CW\*(C`html\*(C'\fR. 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.
OverviewTo 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 \*(C`select\*(C'\fR would be: print $db−>select('foo', '*')−>html;
A DBIx::XHTML_Table object can be generated with the \*(C`xto\*(C'\fR (\fBX\fR\s-1HTML_\s0\fBT\fRable Object) method: my $table = $db−>query($query)−>xto;
Passing attributesDBIx::Simple sends the attributes you pass to \*(C`html\*(C'\fR both to the constructor and the output method. This allows you to specify both HTML attributes (like \*(C`bgcolor\*(C'\fR) and options for XHTML_Table (like \f(CW\*(C`no_ucfirst\*(C'\fR and \f(CW\*(C`no_indent\*(C'\fR) all at once: print $result−>html(
tr => { bgcolor => [ qw/silver white/ ] },
no_ucfirst => 1
);
Using an XHTML_Table objectNot 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 \*(C`neat_list\*(C'\fR 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 \*(C`table\*(C'\fR and \f(CW\*(C`box\*(C'\fR, you need Anno Siegel's Text::Table module installed.
LICENSEThere 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.
AUTHORJuerd Waalboer <juerd@cpan.org> <http://juerd.nl/>
SEE ALSODBIx::Simple, SQL::Abstract 0
Johanes Gumabo
Data Size : 46,412 byte
man-DBIx::Simple::Examples.3pmBuild : 2024-12-05, 20:55 :
Visitor Screen : x
Visitor Counter ( page / site ) : 4 / 168,935
Visitor ID : :
Visitor IP : 18.117.74.47 :
Visitor Provider : AMAZON-02 :
Provider Position ( lat x lon ) : 39.962500 x -83.006100 : x
Provider Accuracy Radius ( km ) : 1000 :
Provider City : Columbus :
Provider Province : Ohio , : ,
Provider Country : United States :
Provider Continent : North America :
Visitor Recorder : Version :
Visitor Recorder : Library :
Online Linux Manual Page : Version : Online Linux Manual Page - Fedora.40 - march=x86-64 - mtune=generic - 24.12.05
Online Linux Manual Page : Library : lib_c - 24.10.03 - march=x86-64 - mtune=generic - Fedora.40
Online Linux Manual Page : Library : lib_m - 24.10.03 - march=x86-64 - mtune=generic - Fedora.40
Data Base : Version : Online Linux Manual Page Database - 24.04.13 - march=x86-64 - mtune=generic - fedora-38
Data Base : Library : lib_c - 23.02.07 - march=x86-64 - mtune=generic - fedora.36
Very long time ago, I have the best tutor, Wenzel Svojanovsky . If someone knows the email address of Wenzel Svojanovsky , please send an email to johanes_gumabo@yahoo.co.id .
If error, please print screen and send to johanes_gumabo@yahoo.co.id
Under development. Support me via PayPal.
ERROR : Need New Coding : (parse_manual_page_|249|DBIx::Simple::Examples.3pm|36/37|el══─{─══.|.el══─{─══. ds -- \|\(em\|
) (htmlprn|149|DBIx::Simple::Examples.3pm|36/37|.el══─{─══. ds -- — |.el══─{─══. ds -- \|\(em\|
) (parse_manual_page_|249|DBIx::Simple::Examples.3pm|41|br══─}─══|'br══─}─══
) (htmlprn|149|DBIx::Simple::Examples.3pm|41|'br══─}─══ |'br══─}─══
) (rof_nr_x|149|DBIx::Simple::Examples.3pm|51/52|\nF|.ie \nF ══─{─══. de IX
) (rof_unit_scale_px|41|DBIx::Simple::Examples.3pm|51/52|F|.ie \nF ══─{─══. de IX
) (rof_if|19|DBIx::Simple::Examples.3pm|51/52|\nF|.ie \nF ══─{─══. de IX
) (htmlprn|149|DBIx::Simple::Examples.3pm|51/52|.ie \nF ══─{─══. de IX|.ie \nF ══─{─══. de IX
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|53|\$1\t\\n%\t"\\$2" |. tm Index:\\$1\t\\n%\t"\\$2"
) (parse_manual_page_|249|DBIx::Simple::Examples.3pm|57|══─}─══|.══─}─══
) (htmlprn|149|DBIx::Simple::Examples.3pm|57|.══─}─══ |.══─}─══
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|334|\*(C`iquery\*(C'\fR. This works just like \f(CW\*(C`query\*(C'\fR, but with parts of the query |\&\f(CW\*(C`iquery\*(C'\fR. This works just like \f(CW\*(C`query\*(C'\fR, but with parts of the query
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|356|\*(C`sql\*(C'\fR and \f(CW\*(C`sql_type\*(C'\fR, you can import them |If you need the helper functions \f(CW\*(C`sql\*(C'\fR and \f(CW\*(C`sql_type\*(C'\fR, you can import them
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|357|\*(C`use SQL::Interp;\*(C'\fR |with \f(CW\*(C`use SQL::Interp;\*(C'\fR
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|361|\*(C`select\*(C'\fR, \f(CW\*(C`insert\*(C'\fR, \f(CW\*(C`update\*(C'\fR, \f(CW\*(C`delete\*(C'\fR. These work like \f(CW\*(C`query\*(C'\fR, but |\&\f(CW\*(C`select\*(C'\fR, \f(CW\*(C`insert\*(C'\fR, \f(CW\*(C`update\*(C'\fR, \f(CW\*(C`delete\*(C'\fR. These work like \f(CW\*(C`query\*(C'\fR, but
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|367|\*(C`abstract\*(C'\fR |The SQL::Abstract object is available (writable) through the \f(CW\*(C`abstract\*(C'\fR
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|450|\*(C`where\*(C'\fR method is not wrapped directly, because it doesn't generate a |The \f(CW\*(C`where\*(C'\fR method is not wrapped directly, because it doesn't generate a
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|453|\*(C`abstract\*(C'\fR property, you can still easily access it: |But using the \f(CW\*(C`abstract\*(C'\fR property, you can still easily access it:
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|461|\*(C`xto\*(C'\fR and \f(CW\*(C`html\*(C'\fR. |\&\f(CW\*(C`xto\*(C'\fR and \f(CW\*(C`html\*(C'\fR.
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|478|\*(C`select\*(C'\fR would be: |using the abstraction method \f(CW\*(C`select\*(C'\fR would be:
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|484|\*(C`xto\*(C'\fR (\fBX\fR\s-1HTML_\s0\fBT\fRable |A DBIx::XHTML_Table object can be generated with the \f(CW\*(C`xto\*(C'\fR (\fBX\fR\s-1HTML_\s0\fBT\fRable
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|492|\*(C`html\*(C'\fR both to the constructor |DBIx::Simple sends the attributes you pass to \f(CW\*(C`html\*(C'\fR both to the constructor
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|494|\*(C`bgcolor\*(C'\fR) and options for XHTML_Table (like \f(CW\*(C`no_ucfirst\*(C'\fR and \f(CW\*(C`no_indent\*(C'\fR) |\&\f(CW\*(C`bgcolor\*(C'\fR) and options for XHTML_Table (like \f(CW\*(C`no_ucfirst\*(C'\fR and \f(CW\*(C`no_indent\*(C'\fR)
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|523|\*(C`neat_list\*(C'\fR |Neither neat nor pretty, but useful for debugging. Uses \s-1DBI\s0's \f(CW\*(C`neat_list\*(C'\fR
) (rof_escape_sequence|91|DBIx::Simple::Examples.3pm|561|\*(C`table\*(C'\fR and \f(CW\*(C`box\*(C'\fR, you need Anno Siegel's Text::Table module installed. |For \f(CW\*(C`table\*(C'\fR and \f(CW\*(C`box\*(C'\fR, you need Anno Siegel's Text::Table module installed.
)