Database Reference
In-Depth Information
some exceptions, such as
USE
db_name
, which changes the default (current) database
for your session without making any changes to the database itself. The example
data-modifying statement used in this section is an
UPDATE
:
UPDATE
profile
SET
cats
=
cats
+
1
WHERE
name
=
'Sybil'
We'll cover how to execute this statement and determine the number of rows that
it affects.
• Statements that return a result set, such as
SELECT
,
SHOW
,
EXPLAIN
, or
DESCRIBE
. I
refer to such statements generically as
SELECT
statements, but you should under‐
stand that category to include any statement that returns rows. The example row-
retrieval statement used in this section is a
SELECT
:
SELECT
id
,
name
,
cats
FROM
profile
We'll cover how to execute this statement, fetch the rows in the result set, and
determine the number of rows and columns in the result set. (To get information
such as the column names or data types, access the result set metadata. That's
Recipe 10.2
.)
The first step in processing an SQL statement is to send it to the MySQL server for
execution. Some APIs (those for Perl, Ruby, and Java, for example) recognize a distinc‐
tion between the two categories of statements and provide separate calls for executing
them. Other APIs (such as the one for Python) have a single call used for all statements.
However, one thing all APIs have in common is that no special character indicates the
end of the statement. No terminator is necessary because the end of the statement string
terminates it. This differs from executing statements in the
mysql
program, where you
terminate statements using a semicolon (
;
) or
\g
. (It also differs from how this topic
usually includes semicolons in examples to make it clear where statements end.)
When you send a statement to the server, be prepared to handle errors if it did not
execute successfully.
Do not neglect this!
If a statement fails and you proceed on the basis
that it succeeded, your program won't work. For the most part, this section does not
show error-checking code, but that is for brevity. The sample scripts in the
recipes
distribution from which the examples are taken do include error handling, based on
the techniques illustrated in
Recipe 2.2
.
If a statement does execute without error, your next step depends on the statement type.
If it's one that returns no result set, there's nothing else to do, unless you want to check
how many rows were affected. If the statement does return a result set, fetch its rows,
then close the result set. In a context where you don't know whether a statement returns
a result set,
Recipe 10.3
discusses how to tell.
Perl
The Perl DBI module provides two basic approaches to SQL statement execution, de‐
pending on whether you expect to get back a result set. For a statement such as
IN
