Database Reference
In-Depth Information
Querying MySQL
In the previoussection, we examined the process for starting a simple Ruby program and
connecting to a MySQL server, and looked at how to disconnect from it. Let's now see
how to query a database while connected to MySQL or MariaDB with the Ruby API.
We'll do a very simple query to get a list of Avocet birds from the
birds
table. To do
this, we'll first create a variable to store the
SELECT
statement we want to execute. Then
we'll execute it with a
query()
call.Here's how that part of the program would look:
sql
=
"SELECT common_name, scientific_name
FROM birds
WHERE common_name LIKE '%Avocet%'"
rows
=
con
.
query
(
sql
)
rows
.
each
do
|
row
|
common_name
=
row
[
0
]
scientific_name
=
row
[
1
]
puts
common_name
+
' - '
+
scientific_name
end
After the
query()
, you can see that we're using an
each
statement to go through each
of the
rows
of the results, storing each row in an array called
row
. Then we're temporar-
ily storing each element of the
row
array in the
common_name
and
scientif-
ic_name
variables. We're using
puts
to display each variable with a hyphen between
them and a newline at the end.
Sample MySQL/Ruby Program
Although it'seasier to discuss the components of a program in separate pieces, it can be
confusing to see how they come together. A complete Ruby program follows that uses the
MySQL/Ruby module. This program has a very different purpose from the snippets we
showed earlier. It will check the backup directory for backup files in accordance with our
backup policy (this task was discussed in
Developing a Backup Policy
)
. The program will
display to the administrator a list of backup files for the past several days. It will also store
a report of its findings in the
backup_reports
table in the
server_admin
database
in MySQL:
#!/usr/bin/ruby
require
'mysql'
# create date variables
