Use C in MySQL database to execute SQL statement _ MySQL

The SQL statement executed using C in the MySQL database is similar to that in PostgreSQL. it can be accessed in many different languages, including C, C ++, Java, and Perl. From Chapter 5th of Professional Linux Programming for MySQL, Neil Matthew and Richard Stones use the detailed MySQL C interface to explain how to execute SQL statements in the MySQL database. They will look at 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)