[ Pobierz całość w formacie PDF ]

\%attr, @bind_values); allDBIfunctions that return a reference, the
reference returned may be overwritten on repeated
calls to this function.
$ary_ref = This method combines aprepareandexecute
$dbh->selectcol_arrayref($statement, and fetches one column from all the rows
\%attr, @bind_values); matching the WHERE filter. Like
fetchrow_arrayrefit returns a reference to an
array containing the values of that column. If you
wish to store these rather than using them
immediately you ll need to copy these values.
Read theDBIdocumentation to learn how to select
multiple columns using this method.
An example of usingselectrow_arrayrefwould be:
my $result = $dbh->selectrow_arrayref(
"SELECT FirstName, LastName, Wage
FROM Staff
WHERE StaffID = ?",
undef,
$staffid)
or die "Failed to select row:". $dbh->errstr;
28 Perl Training Australia (http://perltraining.com.au/)
Chapter 5. Programming with the DBI
Exercise
1. Modify yourinsert.plprogram to use one of theselect*methods rather thanprepareand
execute.
Selecting everything
Occasionally it s useful to get all of the rows returned by the database at once. This allows you to
perform processing on the rows in a block before finishing them with them. An example might be a
batch script which copies all new entries in a database into a different database. In this case it may be
more efficient to ask for all the entries rather than calling the fetch method for each and every row.
However this can also use significantly more memory than a row-by-row approach.
DBIprovides twofetchallmethods for this purpose:
1.fetchall_arrayref
2.fetchall_hashref
These also come with appropriately namedselectallmethods.
Reading large data sets into memory can slow your system down or crash your database or program.
These methods should only be used if you are certain that the resulting memory footprint is going to
be manageable. Thefetchall_arrayrefmethod optionally takes an argument to limit the number
of rows fetched. It can then be called again to fetch more rows.
For more information about selecting large data sets from the database read the
documentation for these methods in perldoc DBI.
Stored procedures
Anything you can do with your database directly, you can do throughprepareandexecuteor the
selectrow*andselectcol_arrayrefmethods. Hence, if your database has a stored procedure
calledget_abcwhich takes three values ($x,$yand$z) and returns a single tuple of three fields you
can call it as follows:
my @array = $dbh->selectrow_array("CALL get_abc(?, ?, ?)", undef, $x, $y, $z)
or die $dbh->errstr;
print "@array\n";
Calling finish
In our previous examples we ve assumed that we always want all the data returned to us.
Occasionally we may wish to stop working with a statement handle before we ve used all of its data.
We could just leave it and continue on with our program but this leaves the statement handle still
active and taking up memory or disk space with the unprocessed records.
Perl Training Australia (http://perltraining.com.au/) 29
Chapter 5. Programming with the DBI
To tell the database that we re finished with the statement handle even if we haven t read all the data
from it, we can call thefinishmethod:
$sth->execute() or die $dbh->errstr;
for ( my $i = 0; $i
if( @array = $sth->fetchrow_array() ) {
print "@array\n";
}
}
$sth->finish; # end statement even if we haven t used all the data
finishis automatically called whenever a fetch method returns an end-of-data status so most of the
time you won t need to use it. Likewise callingexecuteagain on your statement handle
automatically finishes the previous call.finishis also called when a statement handle goes out of
scope and gets destroyed.
If you don t call finish on statement handles which are still active (still have unprocessed data) you
will receive the following warning when you disconnect:
disconnect invalidates 2 active statement handles
(either destroy statement handles or call finish on them before disconnecting)
You can read more about thefinishmethod on page 117 of the Cheetah book.
Disconnecting from the database
Just as it s a good idea to close a file handle once you ve finished with it, it s also a good idea to
disconnect from the database when you no longer need it. Perl will call disconnect on all databases at
the end of your program s execution if you haven t done so yourself.
The disconnect method is a database handle method and is called as follows:
$dbh->disconnect();
If you have more than one database handle open and want to close them all at once you can call:
DBI->disconnect_all();
This will disconnect all currently open database handles thatDBIknows about. This is not generally
a good idea, it s better to explicitly close the specific database handles you don t want open.
Exercise
1. Add a call todisconnectin yourinsert.plprogram.
30 Perl Training Australia (http://perltraining.com.au/)
Chapter 5. Programming with the DBI
Chapter summary
" To establish a connection to the database we use theconnectmethod.
" We must remember to tellDBIour desired commit behaviour. We can do this by including the{
AutoCommit => 1 }flag when we callconnect.
" To perform stand alone non-SELECTSQL queries we can use thedomethod.
" To include placeholders in an SQL statement we place question marks (?) where we wish to add
data. We then pass the data to the method as bind values.
" When we need to perform a statement which is essentially the same each time, or when we wish
to select data out from the database we use theprepareandexecutemethods.
" Data can be fetched from the database by using thefetchrow_array,fetchrow_arrayrefand
fetchrow_hashrefmethods.
" To disconnect from the database we use thedisconnectmethod.
Perl Training Australia (http://perltraining.com.au/) 31
Chapter 5. Programming with the DBI
32 Perl Training Australia (http://perltraining.com.au/)
Chapter 6. SQL::Abstract
In this chapter...
SQL is an expressive and heavily used language for working with relational databases, however for a
software developer it does present some problems. Very often we may have information contained in
a data structure that we wish to use as the basis of a query, but transforming that data into SQL can
be challenging.
This task is particularly difficult when the data may contain a variable number of fields and
constraints, and the job of managing the SQL generation is often left to the individual developer.
Hand generating is not only tedious, it can also be error prone.
In this chapter we will examineSQL::Abstract, a Perl module for automatically generating SQL
queries for use with DBI.
Using SQL::Abstract
TheSQL::Abstractmodule is available from the CPAN. It is uses an object-oriented interface for
controlling options and generating SQL. Creating aSQL::Abstractobject is straightforward:
use SQL::Abstract;
my $sql = SQL::Abstract->new();
Insert statements
UsingSQL::Abstractto generate an insert statement is simple to use and understand. Provided with
a hash of key/value pairs,SQL::Abstractwill generate a corresponding insert statement. For
example, using the following code:
my %record = (
FirstName =>  Buffy ,
LastName =>  Summers ,
Address =>  1630 Revello Drive ,
City =>  Sunnydale ,
State =>  California ,
Position =>  Slayer ,
Wage => 50000
);
my ($stmt, @bind) = $sql->insert( Staff ,\%record);
would generate:
$stmt = "INSERT INTO Staff
(FirstName, LastName, Address, City,
State, Position, Wage)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
@bind = ( Buffy , Summers , 1630 Revello Drive ,
 Sunnydale , California , Slayer ,50000);
33
Perl Training Australia (http://perltraining.com.au/)
Chapter 6. SQL::Abstract
These variables can now be used directly with DBI:
my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);
# Alternatively:
$dbh->do($stmt,undef,@bind);
Being able to turn a hash into SQL can save a large amount of time both writing SQL and selecting [ Pobierz całość w formacie PDF ]

zanotowane.pl

doc.pisz.pl

pdf.pisz.pl

forum-gsm.htw.pl