mysql| Data | database | statement | Execution is similar to PostgreSQL, and you can access MySQL in many different languages, including C, C + +, Java, and Perl. From the 5th chapter on MySQL in the professional Linux programming, Neil Matthew and Richard Stones used the detailed MySQL C interface to show us how to execute SQL statements in the MySQL database. They will discuss statements that return data, such as inserts and statements that do not return data, such as update and delete. They 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:
#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:
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:
Childnofnameage1
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:
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.
[1] [2] Next page
