|
Common questions and problems when using the C API
It is possible for
mysql_store_result() to
return NULL following a
successful call to
mysql_query(). When this
happens, it means one of the following conditions occurred:
- There was a malloc()
failure (for example, if the result set was too large).
- The data couldn't be read (an error occurred on the connection).
- The query returned no data (for example, it was an
INSERT,
UPDATE, or
DELETE).
You can always check whether or not the statement should have
produced a non-empty result by calling
mysql_field_count(). If
mysql_field_count()
returns zero, the result is empty and the last query was a statement that does
not return values (for example, an
INSERT or a
DELETE). If
mysql_field_count()
returns a non-zero value, the statement should have produced a non-empty result.
See the description of the
mysql_field_count()
function for an example.
You can test for an error by calling
mysql_error() or
mysql_errno().
In addition to the result set returned by a query, you can also get the
following information:
- mysql_affected_rows()
returns the number of rows affected by the last query when doing an
INSERT,
UPDATE, or
DELETE. An exception is
that if DELETE is used
without a WHERE clause,
the table is re-created empty, which is much faster! In this case,
mysql_affected_rows()
returns zero for the number of records affected.
- mysql_num_rows()
returns the number of rows in a result set. With
mysql_store_result(),
mysql_num_rows() may be
called as soon as
mysql_store_result()
returns. With
mysql_use_result(),
mysql_num_rows() may be
called only after you have fetched all the rows with
mysql_fetch_row().
- mysql_insert_id()
returns the ID generated by the last query that inserted a row into a table with
an AUTO_INCREMENT index.
- Some queries (LOAD DATA INFILE
..., INSERT INTO ...
SELECT ...,
UPDATE) return
additional information. The result is returned by
mysql_info(). See the
description for
mysql_info() for the
format of the string that it returns.
mysql_info() returns a
NULL pointer if there is
no additional information.
If you insert a record in a table containing a column that has the
AUTO_INCREMENT
attribute, you can get the most recently generated ID by calling the
mysql_insert_id()
function.
You can also retrieve the ID by using the
LAST_INSERT_ID()
function in a query string that you pass to
mysql_query().
You can check if an
AUTO_INCREMENT index is
used by executing the following code. This also checks if the query was an
INSERT with an
AUTO_INCREMENT index:
if (mysql_error(&mysql)[0]
== 0 &&
mysql_num_fields(result) ==
0 &&
mysql_insert_id(&mysql)
!= 0)
{
used_id =
mysql_insert_id(&mysql);
}
The most recently generated ID is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not even
be changed if you update another
AUTO_INCREMENT column
with a non-magic value (that is, a value that is not
NULL and not
0).
If you want to use the ID that was generated for one table and insert it
into a second table, you can use SQL statements like this:
INSERT INTO foo
(auto,text)
VALUES(NULL,'text');
# generate ID by inserting NULL
INSERT INTO foo2
(id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
When linking with the C API, the following errors may occur on some
systems:
gcc -g -o client test.o
-L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl
Undefined first
referenced
symbol in
file
floor
/usr/local/lib/mysql/libmysqlclient.a(password.o)
ld: fatal: Symbol referencing
errors. No output written to client
If this happens on your system, you must include the math library by adding
-lm to the end of the
compile/link line
|
