Performs calculations on the MySQL result set,

6.8 Other topics
This section includes several topics that are not fully suited to any of the sections in the development of this chapter from CLIENT1 to CLIENT5:
After using the result set metadata to help verify that the data is appropriate for calculation, the result set data is used to compute the results.
How to handle data that is difficult to insert into a query.
How to work with graphic data.
How to get information about the table structure.
Common MySQL program design errors and how to avoid.
6.8.1 perform calculations on a result set
So far, we have focused and predominantly used the result set metadata to print row data, but it's clear that there are times when you need to use data to do something other than print. For example, to calculate statistics based on data values, the metadata is applied to ensure that the data is appropriate to the needs they are meeting. What kind of requirements? For the startup program, you might want to verify that the positive
Whether the column to perform a numeric calculation actually contains a number!
The following list shows a simple function summary _ stats (), which gets the result set and the column index, and produces a summary statistic of the column values. The function also lists the number of missing values that are detected by checking for null. These calculations include requirements that must be met by two of data, and Summary_stats () is validated with the result set meta data:
The specified column must exist (that is, the column index must be within the range of the result set column value).
This column must include numeric values.
If these conditions are not met, Summary_stats () prints only the error message and returns. The code is as follows:



Note the Mysql_ data _ Seek () called Before the Mysql_fetch_row () loop. To get the same result set, it allows multiple calls to summary _ stats () (assuming you want to count the statistics for several columns). Each Call Summary _ stats () is  dad 匦 Lu Di, the  of the head  trace ㄕ filled the Adze-kai 鑝 ysql_store_result () Create a result set, if you create a result set with Mysql_use_result (), you can only process the row in order, and only once. Summary_stats () is a relatively simple function, but it gives us a hint of how to write a more complex computational program, such as the least squares regression of two columns or standard statistics, such as T-Test.
6.8.2 to encode the data in question in a query
Data values, including quotes, nulls, and backslashes, can cause problems when you execute a query if you insert them into a query. The following discussion discusses these difficulties and introduces the solutions. Suppose you want to build a select query that is based on the contents of the empty end string pointed to by name:

If the value of name is similar to "0 ' Malley, Brian," then the query is illegal because the quotation marks appear in the quoted string:

You need to pay special attention to this quotation mark so that the server does not interpret it as the end of name. One way is to use double quotes within a string, which is the ANSI SQL convention. SQL supports this convention and also allows quotes to be used after backslashes:

Another problem is the use of any binary data in a query, such as in an application that stores graphics in a database. Because binary values contain some characters, it is not safe to put them in a query. To solve this problem, you can use the Mysql_ Escape _ String (), which encodes special characters so that they can be used in a quoted string. The special characters considered by Mysql_escape_string () refer to null characters, single quotes, double quotes, backslashes, line breaks, carriage returns, and C Ontrol-z (the last one appears in the Windows locale). When do you use Mysql_escape_string ()? The most insured answer is "always". However, if you are sure of the form of the data and know that it is correct-probably because of a prior validation check-you do not have to encode it. For example, if you work with a string of phone numbers, which consists entirely of numbers and short lines, then you do not have to call Mysql_ Escape _ String (), or you will call it.
Mysql_escape_string () encodes the problematic characters by converting them to a sequence of 2 characters that begin with a backslash. For example, the null character is converted to ' \ 0 ', where 0 is printable ASCII code 0, not empty. Backslashes, single quotes, and double quotes are converted to ' \ \ ', ' \ ' and ' \ ' respectively. The procedure for calling Mysql_escape_string () is as follows:

Mysql_escape_string () encodes the from_str, writes the result to the to _ STR, and adds a null endpoint, which is convenient because functions such as strcpy () and strlen () can be used to use the result string. From_str points to a char buffer that includes the string to be encoded, which may contain any content, including binary data. To_str points to an existing char buffer in which the encoded string can be written; do not pass an uninitialized pointer or null pointer, and want to allocate space by mysql_escape_string (). The length of the buffer pointed to by TO_STR is at least (from_len*2) + 1 bytes (it is likely that each character in FROM_STR needs to be encoded in 2 characters; the extra byte is the null end value).
Both From_len and to_len are unsigned int values, From_len represent the length of the data in the FROM_STR, and providing this length is necessary because the FROM_STR may contain null-valued bytes and cannot be used as an empty end string. The To_len value returned from mysql_escape_string () is the actual length of the encoded string that is the result, and no null endpoints are counted.
When mysql_escape_string () returns, the result encoded in the to _ str can be considered an empty end string because the null value in FROM_STR is encoded as ' \ 0 '.
To rewrite the code that constructs the select, so that the value of the name works even if it contains quotes, we do the following:

Processing of 6.8.3 image data
One of the basic functions of mysql_escape_string () is to load the image data into a table. This section describes how to do this (this discussion also applies to other forms of binary data). Suppose you want to read images from a file and store them in a table with a unique identifier. BLOB types are a good choice for binary data, so you can use the following table to describe:

In fact, to get the image from the file and put it into the images table, use the following function Load_image () to implement, giving an identifier number and a pointer to an open file that includes the image data:

Load_image () does not allocate a very large query buffer (0 K), so it can only handle relatively small graphics.
In a real-world application, you can dynamically allocate buffers based on the size of the graphics file. Processing graphical data (or any binary data) recovered from a database is not as problematic as starting to put it, because the original form of the data value in the variable mysql_row is valid, and this length is valid by invoking Mysql_ fetch _length (). You must treat a value as a count string, not as an empty end string.
6.8.4 Get table information
MySQL allows you to use the following query to get information about the structure of the table (the following are equivalent):

Similar to select, two statements return a result set. To find the columns in the table, all you need to do is process the rows in the result set to get useful information from them. For example, if you publish a describe images statement from a MySQL client, this information is returned:

If you execute the same query from your own client, you get the same information (no border). If you only want information for a single column, use the following query:
Show FIELDS from TBL _ name like "Col _ name"
This query returns the same column, but only one line (rows are not returned if the column does not exist).
6.8.5 client programming errors that need to be avoided
This section discusses some common MYSQLC API program design errors and how to avoid them (these problems occur periodically in the MySQL mailing list).
1. Error 1--using the uninitialized connection handler pointer in the example of this chapter, we have called Mysql_ i n i t () by passing the null argument, which is to assign and initialize the MySQL structure, and then return a pointer. Another approach is to pass the pointer to an existing MySQL structure. In this case, Mysql_init () initializes the structure and returns a pointer without having to allocate the structure itself. If you want to use the second method, be careful that there are some subtle problems. The following discussion points out some of the issues that need to be noted. If you pass a pointer to Mysql_ init (), it should actually point to something. Look at the following code snippet:

The problem is that mysql_init () gets a pointer, but the pointer does not point to anywhere it is known. Conn is a local variable, so when main () begins execution It is an uninitialized memory that can point anywhere, which means that mysql_init () will use pointers and can be abused in any area of memory. If you're lucky, Conn will point to the outside of your program's address space, so that the system will terminate immediately, allowing you to be aware of the problems that appear in your code as soon as possible.
If it's unfortunate, Conn will point to the inside of some data that is later used in the program until the problem is found again using that data. So the actual problem is much more problematic and more difficult to capture than when it comes to executing a program. The following is a problematic code:

At this point, Conn is a global variable, so initialize it to 0 (or null) before the program starts. Mysql_init () encounters a null parameter, so initializes and assigns a new connection handler. The system crashes as long as the conn is passed to a MYSQLCAPI function that requires a non-null connection handler. The modification of these snippets is to make sure that Conn has a knowable value. For example, you can initialize it to an already allocated MySQL structure address:

However, recommended (easier!) The solution is simply to pass NULL explicitly to MYSQL_ init (), let the function assign the MySQL structure, and assign the return value to conn:

In any case, do not forget to verify the return value of Mysql_init () to ensure that it is not null.
2. Error 2--valid result set test failure
Remember to check the invocation status of the desired result set. The following code does not do this:

Unfortunately, if Mysql_store_result () fails, Res_set is not executed for the Null,while loop, and the return value of the result set function should be tested to ensure that it is actually working.
3. Error 3--null failure caused by column values
Do not forget to check whether the column value in the array Mysql_row returned by Mysql_fetch_row () is a null pointer. If row[i] is null, then on some machines the following code can cause a crash:

The most damaging part of this error is that some versions of printf () have a permissive "(null)" Output to null pointers, which makes it easy to escape without positioning the error. If you give a program to a friend and he's not too tolerant of the printf version, the program crashes, and your friend thinks you're a useless programmer. The loop should be written in the following way:

The only one by one times that you do not need to check whether the column values are NULL is when you have determined that the IS-_ not _ NULL () is true from the column information structure.
4. Error 4--passing meaningless result buffer
A client library function that requires you to provide a buffer typically makes these buffers real, and the following code violates this rule:

What is the problem? The to_str must point to an existing buffer, which is not in the sample, so it points to a random location. Do not pass meaningless pointers to the mysql_escape_string as TO_STR parameters, otherwise it will trample the memory arbitrarily.