Database Reference
In-Depth Information
The statement string generated by this code is the same as when you use placeholders.
Ruby
Ruby DBI uses
?
as the placeholder character in SQL statements and
nil
as the value
for binding an SQL
NULL
value to a placeholder.
To use placeholders with
do
, pass the statement string followed by the data values to
bind to the placeholders:
count
=
dbh
.
do
(
"INSERT INTO profile (name,birth,color,foods,cats)
VALUES(?,?,?,?,?)"
,
"De'Mont"
,
"1973-01-12"
,
nil
,
"eggroll"
,
4
)
Alternatively, pass the statement string to
prepare
to get a statement handle, then use
that handle to invoke
execute
with the data values:
sth
=
dbh
.
prepare
(
"INSERT INTO profile (name,birth,color,foods,cats)
VALUES(?,?,?,?,?)"
)
count
=
sth
.
execute
(
"De'Mont"
,
"1973-01-12"
,
nil
,
"eggroll"
,
4
)
Regardless of how you construct the statement, DBI includes properly escaped quotes
and a properly unquoted
NULL
value:
INSERT
INTO
profile
(
name
,
birth
,
color
,
foods
,
cats
)
VALUES
(
'De\'
Mont
','
1973
-
01
-
12
',NULL,'
eggroll
'
,
4
)
The Ruby DBI placeholder mechanism provides quotes around data values as necessary
when they are bound to the statement string, so don't put quotes around the
?
characters
in the string.
The approach that uses
prepare
plus
execute
is useful for a statement to be executed
multiple times with different data values. For a statement to be executed just once, you
can skip the
prepare
step. Pass the statement string and the data values to the database
handle
execute
method:
sth
=
dbh
.
execute
(
"SELECT * FROM profile WHERE cats > ?"
,
2
)
sth
.
fetch
do
|
row
|
printf
"id: %s, name: %s, cats: %s
\n
"
,
row
[
"id"
]
,
row
[
"name"
]
,
row
[
"cats"
]
end
sth
.
finish
The Ruby DBI
quote()
database-handle method is an alternative to placeholders. The
following example uses
quote()
to produce the
INSERT
statement for De'Mont. Write
the
%s
format specifiers without enclosing quotes because
quote()
provides them au‐
tomatically as necessary. Non-
nil
values are inserted with quotes, and
nil
values are
inserted as
NULL
without quotes:
stmt
=
sprintf
"INSERT INTO profile (name,birth,color,foods,cats)
VALUES(%s,%s,%s,%s,%s)"
,
dbh
.
quote
(
"De'Mont"
),
dbh
.
quote
(
"1973-01-12"
),
