As we saw in the previous section, the server returns some
important information about each query, displaying some of it directly in
the MySQL client and making some of it easy to obtain through commands
SHOW WARNINGS. When SQL is
called from an application, it’s just as important to retrieve this
information and check to make sure nothing suspicious is going on. All
programming APIs for MySQL support functions that retrieve the query
information returned by the server. In this section, we will discuss these
functions. I refer just to the C API because I had to choose one language,
and most of the other APIs are based on the C API.
This means the query executed fine and changed
This returns a positive number of rows if there were changes, 0 if nothing changed, or –1 in case of error.
UPDATE statements, if
the client flag
was set, this function returns the number of rows that matched the
WHERE condition, which is not
always the same as those that were actually changed.
Using affected rows is turned off by default in Connector/J
because this feature is not JDBC-compliant and will break most
applications that rely on
(matched) rows instead of
affected rows for DML statements. But it
does cause correct update counts from
INSERT ... ON DUPLICATE KEY UPDATE
statements to be returned by the server. The
useAffectedRows connection string
property tells Connector/J whether to set the
CLIENT_FOUND_ROWS flag when connecting
to the server.
which shows how many rows satisfy the
returns additional information about the most recent query in
the string format. For an
it returns a string like:
Rows matched: # Changed: # Warnings: #
# represents the number of
matched rows, changed rows, and warnings, correspondingly. You
should parse the line for “matched: #” to find out how many
corresponding rows were found.
which shows how many rows were actually changed. Note that
M (rows matched) and
P (rows changed) can be different.
Perhaps the columns you wanted to change already contained the
values you specified; in that case, the columns appear in the
“Matched” value but not the “Changed” value.
In an application, retrieve this information using
mysql_info() as before, but in this case,
parse for “Changed: #.”
You get warnings if the server detected something unusual and worth reporting during the query, but the query could still be executed and the rows could be modified. Be sure to check for warnings anyway, because they will let you know about potential problems.
A value called
is defined by the ANSI SQL standard to indicate the status of a
statement. The states are set to status codes, defined in the
standard, that indicate whether a call completed successfully or
returned an exception. The
SQLSTATE is returned as a character
string. To find out which values the MySQL server can return,
refer to “Server
Error Codes and Messages” in the MySQL Reference
This returns the MySQL number of the latest error. For instance, a syntax error will generate the number 1064, whereas 0 means no error.
This returns a text representation of the latest error. For a syntax error, it would be something like:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRO t1 WHERE f1 IN (1,2,1)' at line 1
This can be convenient for storing messages in a separate logfile that you can examine at any time.