Database Reference
In-Depth Information
With that change, the result is a more compact query result display:
+--+-------+----------+
|id|name |birth |
+--+-------+----------+
|1 |Sybil |1970-04-13|
|2 |Nancy |1969-09-30|
|3 |Ralph |1973-11-02|
|4 |Lothair|1963-07-04|
|5 |Henry |1965-02-14|
|6 |Aaron |1968-09-17|
|7 |Joanna |1952-08-20|
|8 |Stephen|1960-05-01|
|9 |Amabel |NULL |
+--+-------+----------+
Number of rows selected: 9
Before writing your own function, check whether your API already provides one. For
example, the Ruby DBI::Utils::TableFormatter module has an
ascii
method that pro‐
duces a formatted display much like that just described. Use it like this:
dbh
.
execute
(
stmt
)
do
|
sth
|
DBI
:
:Utils
::
TableFormatter
.
ascii
(
sth
.
column_names
,
sth
.
fetch_all
)
end
10.5. Listing or Checking Existence of Databases or Tables
Problem
You want to list the databases hosted by the MySQL server or the tables in a database.
Or you want to check whether a particular database or table exists.
Solution
Use
INFORMATION_SCHEMA
to get this information. The
SCHEMATA
table contains a row
for each database, and the
TABLES
table contains a row for each table in each database.
Discussion
To retrieve the list of databases hosted by the server, use this statement:
SELECT
SCHEMA_NAME
FROM
INFORMATION_SCHEMA
.
SCHEMATA
;
To sort the result, add an
ORDER
BY
SCHEMA_NAME
clause.
To check whether a specific database exists, use a
WHERE
clause with a condition that
names the database. If you get a row back, the database exists. The following Ruby
method shows how to perform an existence test for a database:
