.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBIx::Simple::Examples 3pm"
.TH DBIx::Simple::Examples 3pm "2023-09-01" "perl v5.36.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBIx::Simple::Examples \- Examples of how to use DBIx::Simple
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
DBIx::Simple provides a simplified interface to \s-1DBI,\s0 Perl's powerful database
module.
.SH "EXAMPLES"
.IX Header "EXAMPLES"
.SS "General"
.IX Subsection "General"
.Vb 3
\&    #!/usr/bin/perl \-w
\&    use strict;
\&    use DBIx::Simple;
\&
\&    # Instant database with DBD::SQLite
\&    my $db = DBIx::Simple\->connect(\*(Aqdbi:SQLite:dbname=file.dat\*(Aq)
\&        or die DBIx::Simple\->error;
\&
\&    # Connecting to a MySQL database
\&    my $db = DBIx::Simple\->connect(
\&        \*(AqDBI:mysql:database=test\*(Aq,     # DBI source specification
\&        \*(Aqtest\*(Aq, \*(Aqtest\*(Aq,                # 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;
.Ve
.SS "Simple Queries"
.IX Subsection "Simple Queries"
.Vb 1
\&    $db\->query(\*(AqDELETE FROM foo WHERE id = ?\*(Aq, $id) or die $db\->error;
\&
\&    for (1..100) {
\&        $db\->query(
\&            \*(AqINSERT INTO randomvalues VALUES (?, ?)\*(Aq,
\&            int rand(10),
\&            int rand(10)
\&        ) or die $db\->error;
\&    }
\&
\&    $db\->query(
\&        \*(AqINSERT INTO sometable VALUES (??)\*(Aq,
\&        $first, $second, $third, $fourth, $fifth, $sixth
\&    );
\&    # (??) is expanded to (?, ?, ?, ?, ?, ?) automatically
.Ve
.SS "Single row queries"
.IX Subsection "Single row queries"
.Vb 2
\&    my ($two)          = $db\->query(\*(AqSELECT 1 + 1\*(Aq)\->list;
\&    my ($three, $four) = $db\->query(\*(AqSELECT 3, 2 + 2\*(Aq)\->list;
\&
\&    my ($name, $email) = $db\->query(
\&        \*(AqSELECT name, email FROM people WHERE email = ? LIMIT 1\*(Aq,
\&        $mail
\&    )\->list;
.Ve
.PP
Or, more efficiently:
.PP
.Vb 2
\&    $db\->query(\*(AqSELECT 1 + 1\*(Aq)\->into(my $two);
\&    $db\->query(\*(AqSELECT 3, 2 + 2\*(Aq)\->into(my ($three, $four));
\&
\&    $db\->query(
\&        \*(AqSELECT name, email FROM people WHERE email = ? LIMIT 1\*(Aq,
\&        $mail
\&    )\->into(my ($name, $email));
.Ve
.SS "Fetching all rows in one go"
.IX Subsection "Fetching all rows in one go"
\fIOne big flattened list (primarily for single column queries)\fR
.IX Subsection "One big flattened list (primarily for single column queries)"
.PP
.Vb 1
\&    my @names = $db\->query(\*(AqSELECT name FROM people WHERE id > 5\*(Aq)\->flat;
.Ve
.PP
\fIRows as array references\fR
.IX Subsection "Rows as array references"
.PP
.Vb 3
\&    for my $row ($db\->query(\*(AqSELECT name, email FROM people\*(Aq)\->arrays) {
\&        print "Name: $row\->[0], Email: $row\->[1]\en";
\&    }
.Ve
.PP
\fIRows as hash references\fR
.IX Subsection "Rows as hash references"
.PP
.Vb 3
\&    for my $row ($db\->query(\*(AqSELECT name, email FROM people\*(Aq)\->hashes) {
\&        print "Name: $row\->{name}, Email: $row\->{email}\en";
\&    }
.Ve
.SS "Fetching one row at a time"
.IX Subsection "Fetching one row at a time"
\fIRows into separate variables\fR
.IX Subsection "Rows into separate variables"
.PP
.Vb 7
\&    {
\&        my $result = $db\->query(\*(AqSELECT name, email FROM people\*(Aq);
\&        $result\->bind(my ($name, $email));
\&        while ($result\->fetch) {
\&            print "Name: $name, Email: $email\en";
\&        }
\&    }
.Ve
.PP
or:
.PP
.Vb 6
\&    {
\&        my $result = $db\->query(\*(AqSELECT name, email FROM people\*(Aq);
\&        while ($result\->into(my ($name, $email))) {
\&            print "Name: $name, Email: $email\en";
\&        }
\&    }
.Ve
.PP
\fIRows as lists\fR
.IX Subsection "Rows as lists"
.PP
.Vb 6
\&    {
\&        my $result = $db\->query(\*(AqSELECT name, email FROM people\*(Aq);
\&        while (my @row = $result\->list) {
\&            print "Name: $row[0], Email: $row[1]\en";
\&        }
\&    }
.Ve
.PP
\fIRows as array references\fR
.IX Subsection "Rows as array references"
.PP
.Vb 6
\&    {
\&        my $result = $db\->query(\*(AqSELECT name, email FROM people\*(Aq);
\&        while (my $row = $result\->array) {
\&            print "Name: $row\->[0], Email: $row\->[1]\en";
\&        }
\&    }
.Ve
.PP
\fIRows as hash references\fR
.IX Subsection "Rows as hash references"
.PP
.Vb 6
\&    {
\&        my $result = $db\->query(\*(AqSELECT name, email FROM people\*(Aq);
\&        while (my $row = $result\->hash) {
\&            print "Name: $row\->{name}, Email: $row\->{email}\en";
\&        }
\&    }
.Ve
.SS "Building maps (also fetching all rows in one go)"
.IX Subsection "Building maps (also fetching all rows in one go)"
\fImap\fR
.IX Subsection "map"
.PP
A hash of hashes
.IX Subsection "A hash of hashes"
.PP
.Vb 4
\&    my $customers =
\&        $db
\&        \-> query(\*(AqSELECT id, name, location FROM people\*(Aq)
\&        \-> map_hashes(\*(Aqid\*(Aq);
\&
\&    # $customers = { $id => { name => $name, location => $location }, ... }
.Ve
.PP
A hash of arrays
.IX Subsection "A hash of arrays"
.PP
.Vb 4
\&    my $customers =
\&        $db
\&        \-> query(\*(AqSELECT id, name, location FROM people\*(Aq)
\&        \-> map_arrays(0);
\&
\&    # $customers = { $id => [ $name, $location ], ... }
.Ve
.PP
A hash of values (two-column queries)
.IX Subsection "A hash of values (two-column queries)"
.PP
.Vb 4
\&    my $names =
\&        $db
\&        \-> query(\*(AqSELECT id, name FROM people\*(Aq)
\&        \-> map;
\&
\&    # $names = { $id => $name, ... }
.Ve
.PP
\fIgroup\fR
.IX Subsection "group"
.PP
A hash of arrays of hashes
.IX Subsection "A hash of arrays of hashes"
.PP
.Vb 4
\&    my $customers =
\&        $db
\&        \-> query(\*(AqSELECT id, name, location FROM people\*(Aq)
\&        \-> group_hashes(\*(Aqlocation\*(Aq);
\&
\&    # $customers = { $location => [ { id => $id, name => $name }, ... ], ... }
.Ve
.PP
A hash of arrays of arrays
.IX Subsection "A hash of arrays of arrays"
.PP
.Vb 4
\&    my $customers =
\&        $db
\&        \-> query(\*(AqSELECT id, name, location FROM people\*(Aq)
\&        \-> group_arrays(2);
\&
\&    # $customers = { $location => [ [ $id, $name ], ... ], ... }
.Ve
.PP
A hash of arrays of values (two-column queries)
.IX Subsection "A hash of arrays of values (two-column queries)"
.PP
.Vb 4
\&    my $names =
\&        $db
\&        \-> query(\*(AqSELECT location, name FROM people\*(Aq)
\&        \-> group;
\&
\&    # $names = { $location => [ $name, $name, ... ], ... }
.Ve
.SH "EXAMPLES WITH SQL::Interp"
.IX Header "EXAMPLES WITH SQL::Interp"
If you have SQL::Interp installed, you can use the semi-abstracting method
\&\f(CW\*(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.
.PP
You should read SQL::Interp. These examples are not enough to fully
understand all the possibilities.
.PP
The following examples are based on the documentation of SQL::Interp.
.PP
.Vb 3
\&    my $result = $db\->iquery(\*(AqINSERT INTO table\*(Aq, \e%item);
\&    my $result = $db\->iquery(\*(AqUPDATE table SET\*(Aq, \e%item, \*(AqWHERE y <> \*(Aq, \e2);
\&    my $result = $db\->iquery(\*(AqDELETE FROM table WHERE y = \*(Aq, \e2);
\&
\&    # These two select syntax produce the same result
\&    my $result = $db\->iquery(\*(AqSELECT * FROM table WHERE x = \*(Aq, \e$s, \*(AqAND y IN\*(Aq, \e@v);
\&    my $result = $db\->iquery(\*(AqSELECT * FROM table WHERE\*(Aq, {x => $s, y => \e@v});
\&
\&    for ($result\->hashes) { ... }
.Ve
.PP
Use a syntax highlighting editor for good visual distinction.
.PP
If you need the helper functions \f(CW\*(C`sql\*(C'\fR and \f(CW\*(C`sql_type\*(C'\fR, you can import them
with \f(CW\*(C`use SQL::Interp;\*(C'\fR
.SH "EXAMPLES WITH SQL::Abstract"
.IX Header "EXAMPLES WITH SQL::Abstract"
If you have SQL::Abstract installed, you can use the abstracting methods
\&\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
instead of a query and bind arguments, use abstracted arguments.
.PP
You should read SQL::Abstract. These examples are not enough to fully
understand all the possibilities.
.PP
The SQL::Abstract object is available (writable) through the \f(CW\*(C`abstract\*(C'\fR
property.
.PP
The following examples are based on the documentation of SQL::Abstract.
.SS "Overview"
.IX Subsection "Overview"
If you don't like the defaults, just assign a new object:
.PP
.Vb 6
\&    $db\->abstract = SQL::Abstract\->new(
\&        case    => \*(Aqlower\*(Aq,
\&        cmp     => \*(Aqlike\*(Aq, 
\&        logic   => \*(Aqand\*(Aq, 
\&        convert => \*(Aqupper\*(Aq
\&    );
.Ve
.PP
If you don't assign any object, one will be created automatically using the
default options. The SQL::Abstract module is loaded on demand.
.PP
.Vb 4
\&    my $result = $db\->select($table, \e@fields, \e%where, \e@order);
\&    my $result = $db\->insert($table, \e%fieldvals || \e@values);
\&    my $result = $db\->update($table, \e%fieldvals, \e%where);
\&    my $result = $db\->delete($table, \e%where);
\&
\&    for ($result\->hashes) { ... }
.Ve
.SS "Complete examples"
.IX Subsection "Complete examples"
\fIselect\fR
.IX Subsection "select"
.PP
.Vb 7
\&    my @tickets = $db\->select(
\&        \*(Aqtickets\*(Aq, \*(Aq*\*(Aq, {
\&            requestor => \*(Aqinna\*(Aq,
\&            worker    => [\*(Aqnwiger\*(Aq, \*(Aqrcwe\*(Aq, \*(Aqsfz\*(Aq],
\&            status    => { \*(Aq!=\*(Aq, \*(Aqcompleted\*(Aq }
\&        }
\&    )\->hashes;
.Ve
.PP
\fIinsert\fR
.IX Subsection "insert"
.PP
If you already have your data as a hash, inserting becomes much easier:
.PP
.Vb 1
\&    $db\->insert(\*(Aqpeople\*(Aq, \e%data);
.Ve
.PP
Instead of:
.PP
.Vb 8
\&    $db\->query(
\&        q[
\&            INSERT 
\&            INTO people (name, phone, address, ...)
\&            VALUES (??)
\&        ],
\&        @data{\*(Aqname\*(Aq, \*(Aqphone\*(Aq, \*(Aqaddress\*(Aq, ... }
\&    );
.Ve
.PP
\fIupdate, delete\fR
.IX Subsection "update, delete"
.PP
.Vb 7
\&    $db\->update(
\&        \*(Aqtickets\*(Aq, {
\&            worker    => \*(Aqjuerd\*(Aq,
\&            status    => \*(Aqcompleted\*(Aq
\&        },
\&        { id => $id }
\&    )
\&
\&    $db\->delete(\*(Aqtickets\*(Aq, { id => $id });
.Ve
.PP
\fIwhere\fR
.IX Subsection "where"
.PP
The \f(CW\*(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.
.PP
But using the \f(CW\*(C`abstract\*(C'\fR property, you can still easily access it:
.PP
.Vb 1
\&    my $where = $db\->abstract\->where({ foo => $foo });
.Ve
.SH "EXAMPLES WITH DBIx::XHTML_Table"
.IX Header "EXAMPLES WITH DBIx::XHTML_Table"
If you have DBIx::XHTML_Table installed, you can use the result methods
\&\f(CW\*(C`xto\*(C'\fR and \f(CW\*(C`html\*(C'\fR.
.PP
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.
.PP
DBIx::XHTML_Table is loaded on demand.
.SS "Overview"
.IX Subsection "Overview"
To print a simple table, all you have to do is:
.PP
.Vb 1
\&    print $db\->query(\*(AqSELECT * FROM foo\*(Aq)\->html;
.Ve
.PP
Of course, anything that produces a result object can be used. The same thing
using the abstraction method \f(CW\*(C`select\*(C'\fR would be:
.PP
.Vb 1
\&    print $db\->select(\*(Aqfoo\*(Aq, \*(Aq*\*(Aq)\->html;
.Ve
.PP
A DBIx::XHTML_Table object can be generated with the \f(CW\*(C`xto\*(C'\fR (\fBX\fR\s-1HTML_\s0\fBT\fRable
\&\fBO\fRbject) method:
.PP
.Vb 1
\&    my $table = $db\->query($query)\->xto;
.Ve
.SS "Passing attributes"
.IX Subsection "Passing attributes"
DBIx::Simple sends the attributes you pass to \f(CW\*(C`html\*(C'\fR both to the constructor
and the output method. This allows you to specify both \s-1HTML\s0 attributes (like 
\&\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)
all at once:
.PP
.Vb 4
\&    print $result\->html(
\&        tr         => { bgcolor => [ qw/silver white/ ] },
\&        no_ucfirst => 1
\&    );
.Ve
.SS "Using an XHTML_Table object"
.IX Subsection "Using an XHTML_Table object"
Not everything can be controlled by passing attributes. For full flexibility,
the XHTML_Table object can be used directly:
.PP
.Vb 3
\&    my $table = $db\->query($query)\->xto(
\&        tr => { bgcolor => [ qw/silver white/ ] }
\&    );
\&
\&    $table\->set_group(\*(Aqclient\*(Aq, 1);
\&    $table\->calc_totals(\*(Aqcredit\*(Aq, \*(Aq%.2f\*(Aq);
\&
\&    print $table\->output({ no_ucfirst => 1 });  # note the {}!
.Ve
.SH "EXAMPLES WITH Text::Table"
.IX Header "EXAMPLES WITH Text::Table"
.ie n .IP """$result\->text(""neat"")""" 8
.el .IP "\f(CW$result\->text(``neat'')\fR" 8
.IX Item "$result->text(""neat"")"
Neither neat nor pretty, but useful for debugging. Uses \s-1DBI\s0's \f(CW\*(C`neat_list\*(C'\fR
method. Doesn't display column names.
.Sp
.Vb 4
\&    \*(Aq1\*(Aq, \*(AqCamel\*(Aq, \*(Aqmammal\*(Aq
\&    \*(Aq2\*(Aq, \*(AqLlama\*(Aq, \*(Aqmammal\*(Aq
\&    \*(Aq3\*(Aq, \*(AqOwl\*(Aq, \*(Aqbird\*(Aq
\&    \*(Aq4\*(Aq, \*(AqJuerd\*(Aq, undef
.Ve
.ie n .IP """$result\->text(""table"")""" 8
.el .IP "\f(CW$result\->text(``table'')\fR" 8
.IX Item "$result->text(""table"")"
Displays a simple table using \s-1ASCII\s0 lines.
.Sp
.Vb 6
\&    id | animal |  type
\&    \-\-\-+\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-
\&     1 |  Camel | mammal
\&     2 |  Llama | mammal
\&     3 |  Owl   | bird
\&     4 |  Juerd |
.Ve
.ie n .IP """$result\->text(""box"")""" 8
.el .IP "\f(CW$result\->text(``box'')\fR" 8
.IX Item "$result->text(""box"")"
Displays a simple table using \s-1ASCII\s0 lines, with an outside border.
.Sp
.Vb 8
\&    +\-\-\-\-+\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-+
\&    | id | animal |  type  |
\&    +\-\-\-\-+\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-+
\&    |  1 |  Camel | mammal |
\&    |  2 |  Llama | mammal |
\&    |  3 |  Owl   | bird   |
\&    |  4 |  Juerd |        |
\&    +\-\-\-\-+\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-+
.Ve
.PP
For \f(CW\*(C`table\*(C'\fR and \f(CW\*(C`box\*(C'\fR, you need Anno Siegel's Text::Table module installed.
.SH "AUTHOR"
.IX Header "AUTHOR"
Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/>
.SH "SEE ALSO"
.IX Header "SEE ALSO"
DBIx::Simple, SQL::Abstract