They will discuss statements that return data, such as inserts and statements that do not return data, such as update and delete. They will then write a simple program that retrieves data from the database
Execute SQL statement
Now that we have a connection and know how to handle the error, it's time to discuss using our database for some practical work. The primary key for executing all types of SQL is mysql_query:
int mysql_query (MySQL *connection, const char *query)
As you can see, it's very simple. It takes a pointer to the connection structure and a text string containing the SQL to execute; Unlike command-line tools, end semicolons are not used. After success, return 0. In special cases where binary data needs to be included, you can use the associated function, Mysql_real_query. Although for the purposes of this chapter, we only need to discuss mysql_query.
SQL statements that do not return data
We'll discuss update, delete, and INSERT statements first. Because they do not return data, they are easier to use.
Another important function we'll look at here is the function to check the number of rows affected:
My_ulonglong mysql_affected_rows (MySQL *connection);
Perhaps the most obvious thing about this function is the extraordinary return of the result. For portability reasons, this is a special unsigned type. For use in printf, it is recommended that you cast them to unsigned long integers that use the%LU format specification. This function returns the number of rows affected by the previous update, insert, or delete query that was executed using mysql_query.
Typically, for the MYSQL_ function, return code 0 means that no rows are affected, and a positive number represents the actual result, usually the count of rows affected.
As mentioned earlier, unexpected results may occur when using mysql_affected_rows. Let's first discuss the number of rows affected by the INSERT statement, which will operate as expected. Add the following code to the program connect2.c and call it insert1.c:
Copy Code code as follows:
#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 rows inserted is 1.
Now we change the code, so the ' Insert ' section is replaced by:
Copy Code code as follows:
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));
}
Now assume that there are some data in the child table, 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 we execute update1, we want the number of affected rows reported to be 4, but in fact the program reports 2 because it only has to change 2 rows, although the WHERE clause identifies 4 rows. If you want the results of the Mysql_affected_rows report to be 4, which may be expected by someone familiar with other databases, you need to remember to pass the CLIENT_FOUND_ROWS flag to Mysql_real_connect, The procedures in UPDATE2.C are as follows:
Copy Code code as follows:
if (Mysql_real_connect (&my_connection, "localhost"),
"Rick", "Bar", "Rick", 0, NULL, client_found_rows) {
If we reset the data in the database and then run the program with this modification, it reports a number of rows of 4.
The last odd thing about function mysql_affected_rows is that it happens when you delete data from a database. If you use a WHERE clause, 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 rows affected is reported to be 0. This is because optimization deletes an entire table for efficiency reasons. This behavior is not affected by the CLIENT_FOUND_ROWS option flag.
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:
Copy Code code as follows:
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.
Copy Code code as follows:
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.
This combination of Mysql_store_result and mysql_num_rows is a simple and straightforward way to retrieve data. Once Mysql_store_result returns successfully, all query data is already stored on the client and we know that it can be retrieved from the result structure without fear of a database or network error, because all data is local to the program. You can also immediately discover the number of rows returned, which makes encoding easier. As mentioned earlier, it sends all results back to the client immediately. For large result sets, it can consume a large number of server, network, and client resources. For these reasons, it is best to retrieve only the data you need when you use a larger dataset. Soon, we'll discuss how to use the Mysql_use_result function to do the operation.
Once the data has been retrieved, you can use Mysql_fetch_row to retrieve it and use Mysql_data_seek, Mysql_row_seek, Mysql_row_tell to manipulate the result set. Before we start retrieving the data phase, let's talk about these functions first.
Copy Code code as follows:
Mysql_row mysql_fetch_row (Mysql_res *result);
This function takes the result structure obtained from the storage result and retrieves a single line from it, returning the data assigned to you in the row structure. Returns NULL when there is no more data or if an error occurs. Later, we'll come back to work on the data in this line.
Copy Code code as follows:
void Mysql_data_seek (Mysql_res *result, my_ulonglong offset);
This function allows you to enter the result set and set the row that will be returned by the next fetch operation. Offset is the line number, which must be in the range from 0 to the number of rows in the result set minus 1. Passing 0 causes the first row to be returned the next time Mysql_fetch_row is called.
Copy Code code as follows:
Mysql_row_offest Mysql_row_tell (Mysql_res *result);
This function returns an offset value that represents the current position in the result set. It is not a line number and cannot be used for Mysql_data_seek. However, it can be used to:
Copy Code code as follows:
Mysql_row_offset Mysql_row_seek (mysql_res *result, Mysql_row_offset OFFSET);
It moves the current position in the result set and returns to the previous position.
Sometimes, this pair of functions is useful for jumping between known points in the result set. Note that you do not confuse the offset value used by row tell and row seek with the line number used by Data_seek. These are not interchangeable and the result will be what you want to see.
Copy Code code as follows:
void Mysql_free_result (Mysql_res *result);
When you complete a result set, you must always call this function to allow the MySQL library to defragment the objects that are assigned to it.
Retrieving Data
Now start writing the first program that retrieves data from a database. We will select the contents of all rows older than 5. Unfortunately we don't yet know how to handle this data, so all we can do is loop through it. This is select1.c:
Copy Code code as follows:
#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;
}
Retrieving the result set and looping through important parts of the retrieved data has been highlighted.
retrieve one row of data at a time
To retrieve the data row by line, instead of getting all the data immediately and storing it in the client, you can replace the Mysql_store_result call with Mysql_use_result:
Copy Code code as follows:
Mysql_res *mysql_use_result (MySQL *connection);
This function also obtains a connection object and returns the result binding pointer, or returns NULL when an error occurs. Similar to Mysql_store_result, it returns a pointer to a result set object; The key difference is that when you return, you actually don't retrieve any data to the result set, just initialize the result set to be ready to retrieve the data.
reference materials
You can refer to the English version of this article at the Developerworks Global site.
This article is taken from the 5th chapter of the professional Linux book published by Wrox Press Ltd.
about the author
Rick Stones is a system designer who works for the IT department of a large pan-European pharmaceutical distribution and distribution company. Since 1985, he has been using various forms of UNIX and found Linux with an early. CD-ROM release. He writes software for UNIX, Linux, and other platforms in a variety of languages, as well as installing and managing several Linux servers. In a very limited spare time, he tried to improve his piano playing skills.
Neil Matthew has had more than 20 years of experience with UNIX and Linux since the beginning of the college era in contact with the C and Unix V6. Since then he has worked in the IT industry, primarily in the development of communications software, and has maintained a passion for esoteric programming languages and development techniques. Now, as a system designer, he advises on technology strategy and QA. He is keen to build Linux business cases within the company's enterprise. In his spare time, writing or riding for pleasure, Neil tried to persuade his wife and two children to walk with him in the country. Neil was involved in writing several books published by Wrox Press, most notably the beginning Linux programming and professional Linux programming.
