Database Reference
In-Depth Information
This technique reduces database traffic to a single query. However, for a large lookup
table, that could still be a lot of traffic, and you might not want to hold the entire table
in memory.
Performing Lookups with Other Languages
The lookup example shown here uses a Perl hash to determine whether a given value is
present in a set of values:
$valid
=
exists
(
$members
{
$val
});
Similar data structures exist for other languages. In Ruby, use a hash, and check input
values using the
has_key?
method:
valid
=
members
.
has_key
?(
val
)
In PHP, use an associative array, and perform a key lookup with
isset()
:
$valid
=
isset
(
$members
[
$val
]);
In Python, use a dictionary, and check input values using the
has_key()
method:
valid
=
members
.
has_key
(
val
)
For lookups in Java, use a
HashMap
, and test values with the
containsKey()
method:
valid
=
members
.
containsKey
(
val
);
The
transfer
directory of the
recipes
distribution contains some sample code for lookup
operations in each language.
Remember already seen values to avoid database lookups
Another lookup technique mixes individual statements with a hash that stores lookup
value existence information. This approach can be useful if you have a very large lookup
table. Begin with an empty hash:
my
%members
;
# hash for lookup values
Then, for each value to be tested, check whether it's present in the hash. If not, execute
a query to check whether the value is present in the lookup table, and record the result
of the query in the hash. The validity of the input value is determined by the value
associated with the key, not by the existence of the key:
if
(
!
exists
(
$members
{
$val
}))
# haven't seen this value yet
{
my
$count
=
$dbh
->
selectrow_array
(
"SELECT COUNT(*) FROM $tbl_name WHERE val = ?"
,
undef
,
$val
);
# store true/false to indicate whether value was found
$members
{
$val
}
=
(
$count
>
0
);
