Using C to execute SQL statements in the MySQL database (2)

Statements that return data

Now is the time to discuss the most common use of SQL, a SELECT statement that retrieves data from a database.

MySQL also supports show, describe, and explain SQL statements that return results, but they are not considered here. As a rule, the manual contains descriptions of these statements.

You will recall from the PostgreSQL chapter that you can retrieve the data from the SQL SELECT statement in pqexec, get all the data immediately, or use cursors to retrieve the data line-by-row from the database in order to handle large data.

For exactly the same reason, MySQL's retrieval method is almost identical, although it actually does not use the form of a cursor to describe line-by-row retrieval. However, it provides an API to narrow the differences between the two methods, and it usually makes it easier to swap the two methods if needed.

Typically, there are 4 stages of retrieving data from a MySQL database:

Issue a query

Retrieving data

Working with Data

Perform any required collation

As before, we use mysql_query to issue queries. Data retrieval is done using Mysql_store_result or mysql_use_result, depending on how you want to retrieve the data, and then use the Mysql_fetch_row call sequence to process the data. Finally, you must call Mysql_free_result to allow MySQL to perform any required collation.

Functions for all immediate data retrieval

You can retrieve all of the data from a SELECT statement (or another statement that returns data), using Mysql_store_result in a single call:

Mysql_res *mysql_store_result (MySQL *connection);

You must retrieve the data after mysql_query to call this function to store the data in the result set. This function retrieves all the data from the server and immediately stores it in the client. It returns a pointer to a struct (result set structure) that we have never encountered before. If the statement fails, NULL is returned.

When using equivalent PostgreSQL, it should be known that returning NULL means that an error has occurred and this is not the case for retrieving data. Even if the return value is not NULL, it does not mean that there is currently data to be processed.

If NULL is not returned, you can call mysql_num_rows and retrieve the actual number of rows returned, which of course may be 0.

My_ulonglong mysql_num_rows (Mysql_res *result);

It obtains the result structure returned from Mysql_store_result and returns the number of rows in the result set, which may be 0. If Mysql_store_result succeeds, then Mysql_num_rows is always successful.