Database Reference
In-Depth Information
which you can use to access individual column elements (for example,
$ref->{12}-
>{name}
). The result set row and column counts are available as follows:
my
@keys
=
defined
(
$ref
) ?
keys
(
%
{
$ref
}) : ();
my
$nrows
=
scalar
(
@keys
);
my
$ncols
=
$nrows
?
keys
(
%
{
$ref
->
{
$keys
[
0
]}}) :
0
;
The
selectall_
XXX
()
methods are useful when you need to process a result set more
than once because Perl DBI provides no way to “rewind” a result set. By assigning the
entire result set to a variable, you can iterate through its elements multiple times.
Take care when using the high-level methods if you have
RaiseError
disabled. In that
case, a method's return value may not enable you to distinguish an error from an empty
result set. For example, if you call
selectrow_array()
in scalar context to retrieve a
single value, an
undef
return value is ambiguous because it may indicate any of three
things: an error, an empty result set, or a result set consisting of a single
NULL
value. To
test for an error, check the value of
$DBI::errstr
,
$DBI::err
, or
$DBI::state
.
Ruby
As with Perl DBI, Ruby DBI provides two approaches to SQL statement execution. With
either approach, if a statement-execution method fails with an error, it raises an excep‐
tion.
For statements such as
INSERT
or
UPDATE
that return no result set, invoke the
do
database-
handle method. Its return value indicates the number of rows affected:
count
=
dbh
.
do
(
"UPDATE profile SET cats = cats+1 WHERE name = 'Sybil'"
)
puts
"Number of rows updated:
#{
count
}
"
For statements such as
SELECT
that return a result set, invoke the
execute
database-
handle method.
execute
returns a statement handle for fetching result set rows. The
statement handle has several methods of its own that enable row fetching in different
ways. After you are done with the statement handle, invoke its
finish
method. (Call
finish
for every statement handle that you create, unlike Perl DBI where
finish
need
be invoked only if you fetch a partial result set.) To determine the number of rows in
the result set, count them as you fetch them.
The following example executes a
SELECT
statement and uses the statement handle's
fetch
method in a
while
loop:
count
=
0
sth
=
dbh
.
execute
(
"SELECT id, name, cats FROM profile"
)
while
row
=
sth
.
fetch
do
printf
"id: %s, name: %s, cats: %s
\n
"
,
row
[
0
]
,
row
[
1
]
,
row
[
2
]
count
+=
1
end
sth
.
finish
puts
"Number of rows returned:
#{
count
}
"
