Database Reference
In-Depth Information
vary in number, construct a list of placeholder characters. In Perl, the following state‐
ment creates a string consisting of
n
placeholder characters separated by commas:
$str
=
join
(
","
, (
"?"
)
x
n
);
The
x
repetition operator, when applied to a list, produces
n
copies of the list, so the
join()
call joins these lists to produce a single string containing
n
comma-separated
instances of the
?
character. This is handy for binding an array of data values to a list of
placeholders in a statement string because the size of the array is the number of place‐
holders needed:
$str
=
join
(
","
, (
"?"
)
x
@values
);
In Ruby, use the
*
operator to similar effect:
str
=
(
[
"?"
]
*
values
.
size
)
.
join
(
","
)
A less cryptic method is to use a loop approach, here illustrated in Python:
str
=
""
if
len
(
values
)
>
0
:
str
=
"?"
for
i
in
range
(
1
,
len
(
values
)):
str
+=
",?"
Perl
To use placeholders with Perl DBI, put a
?
in your SQL statement string at each data
value location. Then bind the values to the statement by passing them to
do()
or
exe
cute()
, or by calling a DBI method specifically intended for placeholder substitution.
Use
undef
to bind a
NULL
value to a placeholder.
With
do()
, add the
profile
row for De'Mont by passing the statement string and the
data values in the same call:
my
$count
=
$dbh
->
do
(
"INSERT INTO profile (name,birth,color,foods,cats)
VALUES(?,?,?,?,?)"
,
undef
,
"De'Mont"
,
"1973-01-12"
,
undef
,
"eggroll"
,
4
);
The arguments following the statement string are
undef
, then one data value for each
placeholder. The
undef
argument is a historical artifact, but must be present.
Alternatively, pass the statement string to
prepare()
to get a statement handle, then use
that handle to pass the data values to
execute()
:
my
$sth
=
$dbh
->
prepare
(
"INSERT INTO profile (name,birth,color,foods,cats)
VALUES(?,?,?,?,?)"
);
my
$count
=
$sth
->
execute
(
"De'Mont"
,
"1973-01-12"
,
undef
,
"eggroll"
,
4
);
In either case, DBI generates this statement:
