Ultimate Neuro Linguistic Programming (NLP)
Condon Richard Kandydat
145. Lennox Marion Sposob na kawalera
TrzydzieśÂci nocy
Zeydler Zborowski Zygmunt Almiritis pijć sśÂonć wodćÂ
London, Jack Crucero del Snark
Christie, Agata Hercule Poirot 21 Morphium
L J Smith NightWorld 00 Black Dawn
Gabriele Amorth Wyznania Egzorcysty
Ellis Lucy Randka w Toronto
[ Pobierz całość w formacie PDF ]
without our having to copy it later. Binding columns is the most efficient way to fetch data.
my ( $staffid, $firstname, $lastname, $address, $city, $state);
my $sth = prepare("SELECT StaffId, FirstName, LastName, Address, City,
State
FROM Staff
WHERE City = ?");
$sth->execute( Melbourne );
$sth->bind_col(1, \$staffid);
$sth->bind_col(2, \$firstname);
$sth->bind_col(3, \$lastname);
$sth->bind_col(4, \$address);
$sth->bind_col(5, \$city);
$sth->bind_col(6, \$state);
while( $sth->fetchrow_arrayref() ) {
print "$firstname $lastname lives at $address\n";
}
Likebind_param,bind_coltakes a position and a reference to the variable to bind. The position
refers to the position in the selected fields for which this variable should match. If you wish to ignore
the data in a given column you can choose not to bind a variable to that column number.
It is important to bind the columns after the call toexecuterather than before. This is to ensure that
Perl has enough information to associate the output columns with the Perl variables.
Should you be selecting a large number of values from a statement you may find the plural form
bind_columnseasier. This is covered further below.
Bound variables can be changed between calls to execute and a variable can be unbound by passing
bind_coltheundefvalue in its place. Bound variables remain bound for as long as the statement
handle exists, including successive calls toexecute.
The binding is performed at a low level using Perl aliasing. This means that whenever rows are
fetched from the database the variable is updated simply because it refers to the memory location
that that fetched value now occupies. This makes using bound variables very efficient.
Some database drivers will allow you to bind columns prior to callingexecuteon that statement
handle the first time. For maximum portability between drivers, however, this is not
recommended.
Perl Training Australia (http://perltraining.com.au/) 123
Appendix D. Binding variables
Data types for column binding
Just as we can specify data types for parameter binding, data types can also be requested for bound
columns. For example you may wish to specify that a date column comes out in the standard format
forSQL_DATETIMEwhich isYYYY-MM-DD HH:MM:SSrather than the database s native formatting.
You d do this with the following:
$sth->bind_col(1, \$date, { TYPE => SQL_DATETIME });
# or using the short-cut
$sth->bind_col(1, \$date, SQL_DATETIME );
Setting the column output type affects the output type for that column for all subsequent calls to the
statement handle. Unbinding$datewill not change this.
You can specify data types for columns without mentioning any bind variable. Do this by passing in
undef:
$sth->bind_col(1, undef, SQL_DATETIME );
Binding to all columns
We can bind values to all of the columns in aSELECTby using thebind_columnsmethod. This takes
a list of references to scalar values and binds them to each column of the result in turn. This method
will die if the number of references does not match the number of fields.
my ( $staffid, $firstname, $lastname, $address, $city, $state);
my $sth = prepare("SELECT StaffId, FirstName, LastName, Address, City,
State
FROM Staff
WHERE City = ?");
$sth->execute( Melbourne );
$sth->bind_columns( \$staffid, \$firstname, \$lastname, \$address, \$city,
\$state );
# Alternately this can be written:
# $sth->bind_columns( \($staffid, $firstname, $lastname, $address, $city,
# $state) );
while( $sth->fetchrow_arrayref() ) {
print "$firstname $lastname lives at $address\n";
}
Exercise
1. Modify your previous script to bind the output columns to variables. Use eitherbind_color
bind_columns.
124 Perl Training Australia (http://perltraining.com.au/)
Appendix D. Binding variables
Avoiding premature optimisation
Binding values to placeholders and variables to columns is very rarely necessary. Although binding
variables to columns is the most efficient way to fetch data from the database it is not always the
most convenient.
DBIhas been written to be as fast as possible. Before rewriting your program to bind values and
variables make sure that you properly profile your script to ensure that such an optimisation will be
productive.
TheDBIdocumentation includes some hints on how to test whether it isDBIor your program that is
being slow.
Chapter summary
" We can bind the values of variables to placeholders with thebind_parammethod.
" bind_paramallows us to specify the types of our bind values if the database driver might get it
wrong.
" We can bind variables to the database fields output by using thebind_colandbind_columns
methods.
" Binding variables to database fields is the most efficient way to fetch data from the database.
" In many cases these abilities are not required.
Perl Training Australia (http://perltraining.com.au/) 125
Appendix D. Binding variables
126 Perl Training Australia (http://perltraining.com.au/)
Colophon
Said the Lama to the Llama:
u t i m e Found:
------
c n t r o s alarm
binmode
f s o c k e t i cate
chop
i s s e continue
exec
r c o n t i n u e m i exists
fileno
k s m s e e a t n getgrnam
getpwnam
n t r t m t k i l l n n gmtime
goto
i a s i a r u import
int
l l i t c o g h t g n e l kill
length
a x m t t o link
map
e g o e n next
oct
g g o e o redo
send
i m p o r t c l d sin
socket
t i g e t p w n a m sort
time
f r s e n d ucfirst
untie
Theperlmonkcode that appears on the cover of this book was written by Frank Booth. When
executed, it solves the output of thefind-a-funcpuzzle, written by Stephen B. Jenkins, and which
features on Perl Training Australia s Introduction to Perl notes.
Theperlmonkcode can be found in its native habitat on the PerlMonks website
(http://perlmonks.org/index.pl?node_id=123321). Thefind-a-funccode can also be found on
PerlMonks (http://perlmonks.org/index.pl?node_id=108730).
Frank Booth is the patron saint of red cabbage, and more information about him can be found on his
homenode (http://perlmonks.org/index.pl?node=frankus).
Perl Training Australia (http://perltraining.com.au/) 127
128 Perl Training Australia (http://perltraining.com.au/) [ Pobierz całość w formacie PDF ]
