How to handle MySQL queries

6.6 Processing Query
We already know how to start and end a session with the server and should now look at how to control the session. This section describes how to communicate with the server to handle queries. Each query executed should include the following steps:
1) construct the query. The query's construction depends on the content of the query-especially if it contains binary data.
2 Publish the query by sending the query to the server for execution.
3) Process the query results. This depends on the type of publication query. For example, the SELECT statement returns rows of data waiting for processing, which is not the case for the INSERT statement. One element of constructing a query is using which function to send the query to the server. The more general publishing query routine is Mysql_ real _ query (). The routine provides a count string (string plus length) to the query. You must understand the length of the query string and pass it along with the string itself to Mysql_real_query (). Because the query is a counted string,
So its content may be anything, including binary data or empty bytes. The query cannot be an empty end string. Another function that publishes the query, Mysql_ query (), has more restrictions on what the query string allows, but it is easier to use. The query passed to mysql_query () should be a null end string, which means that the query cannot contain empty bytes (a query containing empty bytes can cause the error to be interrupted, which is shorter than the actual query content). Generally, if a query contains arbitrary binary data, it may contain null bytes, so do not use Mysql_ query (). On the other hand, when dealing with empty end strings, constructing queries using familiar standard C library string functions is very resource-intensive, such as strcpy () and sprintf ().
Another element of constructing a query is whether you want to perform an overflow character operation. If you are constructing a query with two
You need to use this action when you enter data or other complex character values, such as quotes, backslashes, and so on. These will be in
6.8.2 section, "Encode the data in question in a query."
Here is a simple outline of the processing query:

Queries for mysql_query () and Mysql_real_query () return a value of 0, and the query fails to return a value other than 0. The success of the query refers to the server's belief that the query is valid and acceptable and capable of execution, and does not refer to the results of the query. For example, it does not refer to the row selected by the Select query, or to the row deleted by the DELETE statement. Check the actual results of the query to include additional processing.
There may be several reasons for a query failure, and some common causes are as follows:
Contains syntax errors.
Semantically illegal-for example, a query that involves a column that does not exist in the table.
There is not enough right to access the data referenced by the query.
Queries can be grouped into two broad categories: queries that do not return results, and queries that return results. Statements such as INSERT, delete, and update are queries that do not return the results class, and they do not return any rows even for queries that modify the database. The only information that can be returned is the number of rows that are affected. The SELECT and show statements are queries that belong to the return results class, and the purpose of publishing these statements is to return some information. The collection of rows generated by a query that returns data is called a result set, and is represented in MySQL as the Mysql_res data type, which is a structure that contains the data values of rows and the metadata about those values, such as the length of column names and data values. An empty result set (that is, a result that contains 0 rows) is separated from the "no results" area.
6.6.1 processing queries that do not return result sets
Process queries that do not return a result set, and publish the query with mysql_query () or mysql_real_query (). If the query succeeds, you can find out how many rows need to be inserted, deleted, or modified by calling Mysql_ a ffected_rows (). The following sample shows how to handle a query that does not return a result set:


Notice how the result of Mysql_ a ffected_rows () is converted to a unsigned long when printing, and this function returns a value of My_ulonglong type, but on some systems the value of that type cannot be printed directly (for example, The author observes that it can work under FreeBSD, but it cannot work under Solaris. Converting the value to the unsigned long type and using the '% l U ' print format can solve the problem. Also consider other functions that return my_ulonglong values, such as mysql_num_rows () and Mysql_ Insert _ ID (). Keep this in mind if you want the client program to be ported across the system.
Mysql_ rows _ Affected () returns the number of rows the query acts on, but the meaning of the "affected row" depends on the type of query. For inserts, deletes, and updates, the number of rows inserted, deleted, or updated, that is, the number of rows that MySQL actually modifies. If the content of the row is the same as what you want to update, MySQL will no longer update the row. This means that although a row may be selected to update (through the WHERE clause of the UPDATE statement), the row may not actually change.
For update, the meaning of the "affected row" is actually a point of contention because people want to think of it as a "matched row"-that is, to select the number of rows to update, even if the update does not actually change the value. If the application requires this information, it can be used to request it when connected to the server to implement this functionality. Pass the flags value of the client_found_rows to Mysql_ Real _ Connect (). You can also pass the client _ FOUND _rows as the flags parameter to do _ Connect (), and it will pass the value to Mysql_ real _ Connect ().
6.6.2 processing queries that return result sets
After you publish the query by calling mysql_query () and Mysql_real_query (), the query that returns the data is in the form of a result set. It is important to implement it in MySQL, and the SELECT is not the only statement that returns rows, and show, DESCRIBE, and explain all need to return rows. For all of these statements, you must perform additional processing row operations after the query is published.
Processing the result set includes the following steps:
Produces a result set by calling Mysql_store_result () or Mysql_use_result (). These functions return the Mysql_res pointer if successful, and the failure returns n U LL. Later we'll look at the difference between Mysql_store_result () and Mysql_use_result (), and the case when you select one without selecting another. Our examples make
With Mysql_ Store _ result (), it can immediately return rows from the server and store them on the client.
Call Mysql_ Fetch _ rows () for each row of the result set. This function returns the Mysql_row value, which is a pointer to an array of strings that represents the value of each column in the row. To operate on a row based on an application. You can print only the column values, perform statistical calculations, or do other things. Mysql_fetch_rows () returns NULL when there is no longer a row in the result set.
When the result set is processed, the call to Mysql_free_result () frees the memory used. If this is omitted, the application will leak out memory (it is extremely important to properly resolve the result set for long-running applications), otherwise it will be noted that the system will be replaced by a number of processes that consume the amount of system resources that are constantly growing.
The following sample outline describes how to handle a query that returns a result set:

We do each row by calling the function Process_result_set (), and here's the trick because we don't define this function, so we need to do this. Typically, the processing set function of the result is based on the following loop:

The Mysql_row value returned from Mysql_fetch_row () is a pointer to an array of values, so accessing each value is accessing row[i], where I ranges from 0 to the number of columns in the row minus 1. Here are a few important points to note about the Mysql_row data types:
Mysql_row is a pointer type, so you must declare the type variable to be Mysql_row ROW instead of Mysql_row *row.
The string in the Mysql_row array is null-terminated. However, the column may contain binary data, so that the data may contain empty bytes, so the value should not be considered null-terminated. The length of the column indicates how long the column value is.
The values for all data types are returned as strings, even if they are numeric. If you want the value to be numeric, you must convert the string yourself.
In the mysql_row array, null pointers represent NULL, unless the declaration is listed as NOT NULL, the column value should often be checked for null pointers.
An application can use the content of each line to do whatever it wants, to illustrate this, we print only the rows that are separated by a tab from the column value, and we need another function, Mysql_num_fields (), which comes from the client library; This function tells us how many values (columns) the row contains.
The following is the code for Process_result_set ():


Process_result_set () Prints each row in tab-delimited form (displaying the null value as the word "null"), followed by the count of rows retrieved, calculated by calling Mysql_num_rows (). Like Mysql_ affected_rows (), mysql_num_rows () returns the My_ulonglong value, so the value is converted to
The unsigned long type is printed in the '% l U ' format.
The loop of the fetch row is immediately after an error check, and if you want to create the result set with Mysql_store_result (),
The null value returned by mysql_fetch_row () usually means "no more rows." However, if you create a result set with Mysql_ use _ Results (), the null value returned by mysql_fetch_row () usually means "no more rows" or an error occurred. Regardless of how the result set is created, this test only allows Process_result_set () to detect errors.
This version of Process_result_set () is the lowest-performing method for printing column values, and each method has some drawbacks, such as assuming the following query:

We can make the output look good by providing some information such as column labels, and by arranging these values vertically. To do this, we need the label and the widest value each column needs. This information is valid, but is not part of the column data value, but is part of the metadata of the result set (data about the data). After a short summary of the query processing program, we will be in 6. 6. The 6 section, "Using result set metadata," gives a more beautiful display format.
Print binary data
For column values that contain binary data that may contain null bytes, the '% s ' printf () format identifier cannot print it correctly; printf () expects an empty end string, and the column value is not printed until the first empty byte. For binary data, it is best to use the length of the column to print the full value, such as fwrite () or PUTC ().
6.6.3 Common target Query handler
The processing query sample described earlier has been written using the knowledge that the statement should return some data. This is possible because the query is anchored inside the code: When the INSERT statement is used, it does not return the result, and the show tables statement is used to return the result.
However, it is not always possible to know which statement the query uses, for example, if you perform a query that is typed from the keyboard or from a file, it may be any statement. It is not possible to know in advance whether it will return rows. Of course, do not want to do parsing the query to determine what kind of statements it is, in short, not as simple as it seems. It is not enough to look at the first word, because the query may also start with an annotation statement, for example:
/* Comment */SELECT
Fortunately, you don't have to know the query type too early to handle it properly. The MYSQLC API enables you to write a generic target query handler that handles any type of statement well, regardless of whether it returns results or not. Before you write the code for the query handler, let's briefly describe how it works:
Publish the query, and if it fails, it ends.
If the query succeeds, call Mysql_store_result () retrieve rows from the server and create the result set.
If Mysql_store_result () fails, the query does not return a result set, or an error occurred while retrieving the result set. You can distinguish between these two situations by passing the connection handler to the Mysql_field_count () and detecting its value, as follows:
If Mysql_field_count () is Non-zero, there is an error, because the query should return the result set, but not. This happens for a number of reasons. For example, the result set may be too large, memory allocation fails, or a network outage occurs between the client and the server when fetching rows.
The slightly more complicated part of this process is that Mysql_ field _ count () is not present in earlier versions of MySQL3.22.24, and they use Mysql_ num _ Fields (). The following code block is included in the file that invokes Mysql_field_count () in order to write a program that can run in any version of MySQL:

This will treat some calls to Mysql_field_count () as calls to Mysql_num_fields () in earlier versions of MySQL3.22.24.
If Mysql_field_count () returns 0, it means that the query does not return results (this indicates that the query is a statement similar to insert, DELETE, or update).
If Mysql_store_result () succeeds, the query returns a result set, and the row is processed by calling Mysql_fetch_row () until it returns NULL.
The following list shows the functions for handling arbitrary queries, giving the connection handler and the null finalization query string:



6.6.4-Selectable Query processing method
This version of Process_query () has three features:
Publish the query with mysql_query ().
Retrieves a result set with Mysql_store_query ().
When a result set is not obtained, the error event is distinguished from the unwanted result set with Mysql_field_count (). For these features of query processing, there are three ways to do this:
You can use the Count query string and Mysql_ Real _ query () instead of the null finalization query string and MYSQL_ query ().
You can create a result set by calling Mysql_use_result () instead of calling Mysql_store_result ().
You can call Mysql_error () instead of calling Mysql_field_count () to determine whether the result set is a retrieval failure or simply does not set the retrieval.
You can use some or all of these methods instead of process _ query (). The following is a process_real_query () function that is similar to Process_query () but uses all three methods:


Comparison of 6.6.5 Mysql_store_result () with Mysql_use_result ()
function Mysql_store_result () is similar to Mysql_use_result () and they all have connection handler parameters and return the result set. But in fact the difference between the two is still very large. The primary difference between two functions is to retrieve the rows of the result set from the server. When called, Mysql_store_result () retrieves all rows immediately, while Mysql_use_result () initiates the query, but does not actually get any rows, Mysql_store_result () assumes that Mysql_ fetch _ is subsequently invoked Row () Retrieves records. The different ways in which these rows are retrieved cause the two to differ in other ways. This section compares to learn how to choose the method that best suits your application.
When Mysql_store_result () retrieves a result set from the server, the row is fetched and allocated memory, stored on the client, and then called Mysql_fetch_row () will never return an error because it simply leaves the row out of the data structure that has the result set. Mysql_fetch_row () returns null always indicates that the end of the result set has been reached. Instead, Mysql_use_result () itself does not retrieve any rows, but simply initiates a line-by-row retrieval, which means that each row must be called mysql_fetch_row () to complete itself. This being the case, although mysql_ fetch _ row () returns null at this point in time to the end of the result set, it may also indicate an error while communicating with the server. You can distinguish between the two by calling Mysql_errno () and Mysql_error ().
Compared to Mysql_use_result (), Mysql_store_result () has a higher memory and processing requirements because it maintains the entire result set on the client, so the cost of allocating memory and creating data structures is enormous, To retrieve a large result set at the risk of overflowing memory, if you want to retrieve more than one row at a time, use Mysql_ using _result (). Mysql_use_result () has a lower memory requirement because it only needs to allocate enough space for each processing line. This is faster because there is no need to create a complex data structure for the result set. On the other hand, Mysql_use_result () adds a larger load to the server, and it must keep the rows in the result set until the client looks appropriate to retrieve all the rows. This makes certain types of client programs unsuitable for mysql_ use _ result ():
An interactive client program that takes place ahead of time at the request of a user (without having to wait for the next line to be sent by the server just because the user needs a cup of coffee).
There are a number of client programs that do a lot of processing between row retrievals. In all these cases, the client program cannot quickly retrieve all rows of the result set, which limits the server and negatively affects other client programs because the tables that retrieve the data are read-locked during the query. The client that is trying to update the table or any client program to insert the row is blocked.
Offsetting the additional memory requirements caused by Mysql_store_result () is a significant benefit to accessing the entire result set at once. All rows in the result set are valid and therefore can be accessed arbitrarily: Mysql_ data _ Seek (), Mysql_ Rowseek (), and Mysql_row_tell () functions allow rows to be accessed in any order. Mysql_use_result () can only access rows in the order retrieved by Mysql_fetch_row (). If you want to process rows in any order, rather than in the order returned from the server, you must use Mysql_ store _ result (). For example, if you allow users to navigate back and forth through the selected rows of the query, it is best to use Mysql_ store _ result ().
Use Mysql_store_result () to obtain certain types of column information that is not valid when using Mysql_use_result (). by calling Mysql_num_rows () to get the number of rows for the result set, the maximum width values of these values in each column are stored in the Max_width member of the Mysql_field column information structure. Using Mysql_ Use _ Results (), until all rows are fetched, mysql_num_rows () returns the correct value, and Max_width is invalid because only the data in each row is displayed.
Because Mysql_use_result () performs fewer operations than Mysql_store_result (), Mysql_ imposes a mysql_store_result () No requirement: The client must call Mysql_ Fetch _ row () for each row in the result set, otherwise the remaining records in the result set become part of the next query result set, and an "unsynchronized" error occurs. This situation does not occur when using Mysql_store_result (), because when the function returns
Back, all the rows have been fetched. In fact, using Mysql_store_result () eliminates the need to call Mysql_ Fetch _ row () again. It's useful for all the things that interest you to get a non-empty result, rather than a query that contains the content of the result. For example, to know if a table my_tbl exists, you can execute the following query:
Show TABLES like "My_tb1"
If, after calling Mysql_store_result (), the value of Mysql_num_rows () is Non-zero, the table exists, and the mysql_fetch_row () is no longer required () (of course, the call to Mysql_ free _ result ()). If you want to provide maximum flexibility, give the user the option to use any of the result set processing methods. MySQL and mysqldump are the two programs that perform this operation, by default, using Mysql_ Store _ result (), but if the--quick option is specified, the Mysql_ use _ result () is used.
6.6.6 using result set meta data
The result set includes not only the column values of the data rows, but also the data information that becomes the metadata result set, including:
The number of rows and columns in the result set, implemented by calling Mysql_num_rows () and Mysql_num_fields ().
The length of each column value in the row, implemented by calling Mysql_fetch_lengths ().
Information about each column, such as the column name and type, the maximum width of each column value, and the table from which the column originates.
The Mysql_field structure stores this information and obtains it by calling Mysql_fetch_fields (). Appendix F describes the Mysql_field structure in detail and lists all the functions that provide access to the column information. The validity portion of the metadata depends on how the result set is processed, as mentioned in the previous section, and if you want to use the row count or the maximum length of the column, you must create the result set with Mysql_store_result () instead of Mysql_use_result (). The result set metadata is helpful in determining how the result set is handled:
Column name and width information is useful for nicely generating formatted output with column headings and vertically arranged.
Use the column count to determine the number of iterations of a loop that handles consecutive column values of data rows. If you want to assign a data structure that depends on the number of rows or columns known in the result set, you can use the row or column count.
You can determine the data type of the column. You can see whether the column is numeric, whether it might include binary data, and so on. In the previous 6.6.1 section, "Processing a query that returns a result set," we wrote the Process_result_set () program that prints the result in a tab-delimited form in the row of the result set. This is good for some purposes (for example, to enter data into spreadsheet software), but for visual inspection or printout, it's not a pretty display format. Recall the previous process_result_set () version, which produced this output:

Let's add a title and a border around each column to make some changes to Process_result_set () to produce tabular output. The revised version looks more beautiful and the output is the same, as shown here:

The basic point of the display algorithm is this:
1 Determine the display width of each column.
2 print a column header with a border (separated by a vertical bar and a dashed line before and after it).
3 Print the values of each row of the result set, columns with borders (separated by vertical bars), and vertically arranged, in addition to printing
The correct number, which prints the null value as the word "null."
4 Finally, print the count of the retrieved rows. This exercise provides a good demonstration of the use of result set metadata. In order to display the output described, we need to know a lot about the result set, in addition to the data values contained in the row. You might think, "This description sounds strikingly similar to the output that MySQL shows." Yes, welcome to the MySQL source code and revised version of the Process_result_set () code comparison, they are different, you can find that the same problem using two methods is instructive.
First, we need to determine the display width of each column, and here's how to do it. You can observe that these calculations are based entirely on the result set metadata, and they are not referenced regardless of the row value:

The column widths are computed by iterating through the Mysql_field structure of the columns in the result set, calling Mysql_ Fetch _ Seek () to locate the first structure, and subsequent Mysql_fetch_field () calls return a pointer to the structure of the contiguous column. The displayed column width is the maximum of the following three values, each of which depends on the metadata in the column information structure:
The length of field-> Name, which is the length of the column headings.
Field-> Max _ length, the length of the longest data value in the column.
If the column may include a null value, the length of the string "NULL", Field->flag indicates whether the column contains null. Note that after we know the width of the column to display, we assign this value to Max _ Length,max_length is a member of the structure obtained from the client library. Is this access allowed? Or should the contents of the Mysql_field structure be read-only? In general, it is "read-only", but some client programs in the MySQL split package change the value of the max_length in the same way, so suppose this is true (if you prefer a method that does not change the Max_length value, assign an array of unsigned int values, Stores the width of the calculation in this array). The calculation of the display width includes a description, recalling that Max_length does not make sense when you create a result set using Mysql_use_result (). Because we need max_length to determine the display width of the column values, the correct operation of the algorithm requires the result set produced by Mysql_store_result () (the length member of the MYSQL _ field structure tells the column value the maximum value that can be obtained. This can be a useful working environment if you use Mysql_store_result () instead of mysql_ using _ result ().
Once you know the width of the column, you can prepare to print, it is easy to work with the caption, for a given column, you print the name member with the calculated width using the column information structure pointed to by field.

For data, we loop through the rows in the result set, printing the column values of the current row at each iteration. It is tricky to print column values from a row, because the value may be null, or it may represent a number (in either case it is printed truthfully). The column values are printed as follows, where Row[i] includes a data value and a field pointer to the column information:

If the column type specified by Field->type is numeric, such as int, float, or decimal, then the value of the macro is-_ num is true. The final code that displays the result set is shown below. Note that because we need to print the dotted line multiple times, this code is encapsulated in its own function, and the function print_dashes () is this:



The MySQL client library provides several ways to access the column information structure, for example, the code of the preceding sample uses multiple loops to access these constructs in the following ways:

However, the combination of Mysql_field_seek () and Mysql_fetch_field () is the only way to obtain the Mysql_field structure, and you can view the Mysql_fetch_field () function and Mysql_fetch_ in Appendix F Field_direct () function to find other ways to get the structure of column information.