Similar to PostgreSQL, MySQL can be accessed in many different languages, including C, C ++, Java, and Perl. In the following sections, Neil Matthew and Richard Stones use the detailed MySQL C interface to show us how to execute SQL statements in the MySQL database. They will discuss the statements that return data, such as INSERT and statements that do not return data, such as UPDATE and DELETE. Then they will write a simple program to retrieve data from the database.
Execute SQL statements
Now we have a connection and know how to handle the error. It is time to discuss how to use our database for practical work. The primary keyword for executing all types of SQL statements is mysql_query:
int mysql_query(MYSQL *connection, const char *query)
|
As you can see, it is very simple. It takes a pointer to the connection structure and a text string containing the SQL statement to be executed. Unlike the command line tool, the end Semicolon is not used. 0 is returned. In special cases where binary data needs to be included, you can use the related function, mysql_real_query. For the purposes of this chapter, we only need to discuss mysql_query.
SQL statement that does not return data
We will first discuss the UPDATE, DELETE, and INSERT statements. Because they do not return data, it is easier to use.
Another important function we will introduce here is to check the affected number of rows:
my_ulonglong mysql_affected_rows(MYSQL *connection);
|
Perhaps the most obvious thing about this function is its extraordinary return results. This is a special unsigned type due to portability. To use it in printf, we recommend that you forcibly convert it to an unsigned long integer using the % lu format specification. This function returns the number of rows affected by the previous UPDATE, INSERT, or DELETE queries. These queries are executed using mysql_query.
For the mysql _ function, return code 0 indicates that no rows are affected. Positive numbers indicate actual results, usually the number of affected rows.
As mentioned above, unexpected results may occur when mysql_affected_rows is used. Let's first discuss the number of rows affected by the INSERT statement, which will be operated as expected. Add the following code to connect2.c and call it insert1.c:
#include
#include
#include "mysql.h"int main(int argc, char *argv[]) {MYSQL my_connection;int res;mysql_init(&my_connection); if (mysql_real_connect(&my_connection, "localhost", "rick", "bar", "rick", 0, NULL, 0)) { printf("Connection success\n"); res = mysql_query(&my_connection, "INSERT INTO children(fname,age), VALUES('Ann',3)"); if (!res) { printf("Inserted %lu rows\n", (unsigned long)mysql_affected_rows(&my_connection)); } else { fprintf(stderr, "Insert error %d: s\n",mysql_errno , (&my_connection), mysql_error(&my_connection)); } mysql_close(&my_connection); } else { fprintf(stderr, "Connection failed\n"); if (mysql_errno(&my_connection)) {fprintf(stderr, "Connection error %d: %s\n",mysql_errno(&my_connection),mysql_error(&my_connection)); } } return EXIT_SUCCESS; }
|
As expected, the number of inserted rows is 1.
Now, we change the code, so the 'insert' part is replaced:
mysql_errno(&my_connection), mysql_error(&my_connection)); } } res = mysql_query(&my_connection, "UPDATE children SET AGE = 4 WHERE fname = 'Ann'"); if (!res) { printf("Updated %lu rows\n", (unsigned long)mysql_affected_rows(&my_connection)); } else { fprintf(stderr, "Update error %d: %s\n",mysql_errno(&my_connection), mysql_error(&my_connection)); }
|
Assume that the data in the sub-table is as follows:
| Childno |
Fname |
Age |
| 1 2 3 4 5 6 7 8 9 10 11 |
Jenny Andrew Gavin Duncan Emma Alex Adrian Ann Ann Ann Ann |
14 10 4 2 0 11 5 3 4 3 4 |
If update1 is executed, the number of affected rows to be reported is 4, but the program actually reports 2 because it only has to change 2 rows, although the WHERE clause identifies 4 rows. If you want the result of mysql_affected_rows report to 4, which may be expected by people familiar with other databases, remember to pass the CLIENT_FOUND_ROWS flag to mysql_real_connect. The program in update2.c is as follows:
if (mysql_real_connect(&my_connection, "localhost", "rick", "bar", "rick", 0, NULL, CLIENT_FOUND_ROWS)) {
|
If we repeat the data in the database and run the program with this modification, the number of rows reported by the program is 4.
The mysql_affected_rows function is the last strange thing that occurs when data is deleted from the database. If the WHERE clause is used, mysql_affected_rows returns the number of rows deleted as expected. However, if there is no WHERE clause, all rows are deleted, and the number of affected rows is 0. This is because the entire table is optimized to be deleted due to efficiency reasons. This behavior is not affected by the CLIENT_FOUND_ROWS option flag.
Statements for returning data
Now it is time to discuss the most common use of SQL, the SELECT statement for retrieving data from the database.
MySQL also supports SHOW, DESCRIBE, and explain SQL statements for returned results, but they are not considered here. By convention, the Manual contains descriptions of these statements.
You will remember from the PostgreSQL chapter that you can retrieve data from the SQL SELECT statement in PQexec. Here, you can retrieve all data immediately, or use a cursor to retrieve data from the database row by row, so as to get big data.
For the same reason, the retrieval methods of MySQL are almost identical, although it does not actually use a cursor to describe row-by-row retrieval. However, it provides an API to reduce the differences between the two methods. If needed, it usually makes interchange between the two methods easier.
Generally, there are four phases to retrieve data from the MySQL database:
Send Query
Retrieve Data
Process Data
Any organization required for execution
As before, we use mysql_query to issue a query. Data retrieval is completed using mysql_store_result or mysql_use_result, depending on how you want to retrieve data, and then using mysql_fetch_row to call the sequence to process data. Finally, you must call mysql_free_result to allow MySQL to execute any required sorting.
All functions for immediate data retrieval
All data can be retrieved from the SELECT statement or other statements that return data. In a single call, use mysql_store_result:
MYSQL_RES *mysql_store_result(MYSQL *connection);
|
You must retrieve data in mysql_query before calling this function to store the data in the result set. This function retrieves all data from the server and stores it in the client. It returns a pointer to the structure result set structure we have never encountered before. If the statement fails, NULL is returned.
When using the equivalent PostgreSQL, you should know that NULL returned means an error has occurred, and this is different from the case where no data is retrieved. Even if the returned value is not NULL, it does not mean that the current data needs to be processed.
If NULL is not returned, you can call mysql_num_rows and retrieve the actual number of returned rows. Of course, it may be 0.
my_ulonglong mysql_num_rows(MYSQL_RES *result);
|
It obtains the returned result structure from mysql_store_result and returns the number of rows in the result set. The number of rows may be 0. If mysql_store_result is successful, mysql_num_rows always succeeds.
This combination of mysql_store_result and mysql_num_rows is a simple and direct method for data retrieval. Once mysql_store_result is returned successfully, all query data is stored on the client and we know that it can be retrieved from the result structure without worrying about database or network errors, because all data in the program is local. You can also immediately find the number of returned rows, which makes the encoding easier. As described above, it immediately sends all results back to the client. For a large result set, it may consume a large amount of server, network, and client resources. For these reasons, it is best to retrieve only the required data when a larger data set is used. Soon we will discuss how to use the mysql_use_result function to complete this operation.
Once the data is retrieved, you can use mysql_fetch_row to retrieve it, and use mysql_data_seek, mysql_row_seek, and mysql_row_tell to operate the result set. Before you start the data retrieval phase, let's discuss these functions first.
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
|
This function uses the result structure obtained from the stored results, retrieves a single row from it, and returns the data allocated to you in the row structure. If there is no more data or an error occurs, NULL is returned. Later, we will return to process the data in this row.
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);
|
This function allows you to enter the result set and sets the rows returned by the next get operation. Offset is the row number, which must be within the range from 0 to 1 in the result set. Passing 0 will cause the first row to be returned when mysql_fetch_row is called the next time.
MYSQL_ROW_OFFEST mysql_row_tell(MYSQL_RES *result);
|
This function returns an offset value indicating the current position in the result set. It is not a row number and cannot be used for mysql_data_seek. However, it can be used:
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);
|
It moves the current position in the result set and returns the previous position.
Sometimes, this pair of functions is useful for jump between known points in the result set. Note that do not confuse the offset values used by row tell and row seek with the row number used by data_seek. These are not interchangeable and the results will be what you want to see.
void mysql_free_result(MYSQL_RES *result);
|
When the result set is complete, you must always call this function to allow the MySQL database to organize the objects allocated to it.
Retrieve Data
Now, write the first program to retrieve data from the database. We will select the contents of all rows older than 5. Unfortunately, we do not know how to process this data, so we can only search it cyclically. This is select1.c:
#include
#include
#include "mysql.h" MYSQL my_connection; MYSQL_RES *res_ptr; MYSQL_ROW sqlrow; int main(int argc, char *argv[]) { int res; mysql_init(&my_connection); if (mysql_real_connect(&my_connection, "localhost", "rick", "bar", "rick", 0, NULL, 0)) { printf("Connection success\n"); res = mysql_query(&my_connection, "SELECT childno, fname, age FROM children WHERE age > 5"); if (res) { printf("SELECT error: %s\n", mysql_error(&my_connection)); } else { res_ptr = mysql_store_result(&my_connection); if (res_ptr) { printf("Retrieved %luows\n",(unsignedlong)mysql_num_rows(res_ptr)); while ((sqlrow = mysql_fetch_row(res_ptr))) { printf("Fetched data...\n"); } if (mysql_errno(&my_connection)) { fprintf(stderr, "Retrive error: s\n",mysql_error(&my_connection)); } } mysql_free_result(res_ptr); } mysql_close(&my_connection); } else { fprintf(stderr, "Connection failed\n"); if (mysql_errno(&my_connection)) { fprintf(stderr, "Connection error %d: %s\n", mysql_errno(&my_connection),mysql_error(&my_connection)); } } return EXIT_SUCCESS; }
|
The important parts of the retrieved result set and the retrieved data are highlighted.
Retrieve a row of data at a time
Instead of getting all the data immediately and storing it in the client, you can replace mysql_store_result with mysql_use_result:
MYSQL_RES *mysql_use_result(MYSQL *connection);
|
This function also gets a connection object and returns results combined with pointers, or returns NULL if an error occurs. Similar to mysql_store_result, it returns a pointer to the result set object. The key difference is that no data is actually retrieved to the result set when the result set is returned, but the result set is initialized to prepare the data for retrieval.
