8.3 Access MySQL data using the C language 8.3.3 the main API function that executes the SQL statement to execute the query executes the appropriate name:
int mysql_query (MySQL *connection, const char *query);
This routine
a valid SQL statement that accepts the form of a connection structure pointer and text stringIf successful, it returns 0.
1. SQL statements that do not return data for simplicity, first look at some SQL statements that do not return any data: Update,delete and insert.
The following function is used to
Check the number of rows affected by the query:
my_ulonglong mysql_affected_rows (MySQL *connection);
The return value type of this function is very uncommon, and it uses unsigned types for portability reasons. When using printf, it is best to use the%lu format to convert it to unsigned long. This function returns the update that was previously executed, The functions that are affected by the delete and insert queries. mysql returns the number of rows modified by an update operation.
Typically, for Mysql_ series functions, a return value of 0 indicates that no rows are affected, positive numbers are actual results, and generally represents the number of rows affected.
Write the program insert1.c, trying to insert a new row in the table.
Mysql_affected_rows returns the actual modification to the data or the number of rows inserted. In addition, when data is deleted from the database, if you use the WHERE clause to delete the data, Mysql_affected_rows returns the number of rows deleted. However, if you sell no WHERE clause in the DELETE statement, all rows in the table are deleted. However, the number of affected rows returned by the program is 0. This is because MySQL optimizes the operation to delete all rows, and it does not perform many single-line deletions.
2. Find the inserted content auto_increment type is automatically assigned by MySQL ID, this feature is very useful, especially when there are many users.
CREATE TABLE Children (
Childno int auto_increment NULL PRIMARY KEY,
fname varchar (30),
Age int
);
MySQL provides functions
last_insert_id () gives the value of the Auto_increment column.
Whenever MySQL inserts data into the Auto_increment column,
MySQL will track the last assigned value based on each userThe user program can discover this value by using the Select Private Function last_insert_id (), which functions like a virtual column in a table.
Writing a program insert2.c
3. Statements that return data the most common usage of SQL is, of course, extracting data instead of inserting or updating data. The data is extracted using the SELECT statement.
MySQL also supports the use of SQL statements Show,describe and explain to return results, which are not involved for the time being.
Extracting data in a C application typically requires the following 4 steps:
Execute Query
Extracting data
Working with Data
Necessary clean-up work
Just like the previous insert and DELETE statements, you will use the
mysql_query to send SQL statements to execute a query, and then use
Mysql_store_result or Mysql_use_result to extract data, which function to use depends on how you want to extract the data. A series of
Mysql_fetch_row calls to process data, and finally use
Mysql_free_result frees the memory resources that the query consumes.
The main difference between Mysql_use_result and Mysql_store_result is whether you want to return one row of data at a time, or return all results at once. When the statement result set is small, the latter is more appropriate.
A function that extracts all data at once
You can use Mysql_store_result to extract all of the data from select in a single call:
mysql_res *mysql_store_result (MYSQL *connection);
Obviously, this function needs to be used after a successful call to mysql_query. This function will
Save the data returned from the client immediately. It returns a pointer to the result set structure and returns NULL if it fails.
My_ulonglong mysql_num_rows (Mysql_res *result);
This function accepts the result returned by Mysql_store_result, returns the result structure, and returns the number of rows in the result set. If the Mysql_store_result call succeeds, Mysql_num_rows will always be successful.
If you are using a particularly large data set, it is a good idea to extract smaller, more manageable chunks of information as this will return control to the application more quickly and will not consume a large amount of network resources.
Use Mysql_fetch_row to handle it, or you can use Mysql_data_seek,mysql_row_seek and Mysql_row_tell to move back and forth through the dataset.
1.mysql_fetch_row: This function extracts a row from the result of using Mysql_store_result and puts it in a row structureReturns NULL when the data is exhausted or an error occurs.
Mysql_row mysql_fetch_row (Mysql_res *result);
2.mysql_data_seek: This function is used to jump in the result set, setting the row that will be returned by the next mysql_fetch_row operation. The value of the parameter offset is a line number, it must be within the return of 0 to the total number of rows in the result set minus 1. Passing 0 will cause the next Mysql_fetch_row call to return the first row in the result set.
void Mysql_data_seek (Mysql_res *result, my_ulonglong offset);
3.mysql_row_tell: This function returns an offset value that is used to represent the current position in the result set. It is not a line number and cannot be used for Mysql_data_seek.
Mysql_row_offset Mysql_row_tell (Mysql_res *result);
However, you can use its return value in this way:
Mysql_row_offset Mysql_row_tell (mysql_res *result, Mysql_row_offset OFFSET);
This moves the current position in the result set and returns the previous value.
This pair of functions is useful for moving between known points in the result set. But do not confuse the offsets used by Row_tell and Row_seek and the line numbers used by Data_seek.
4. After all the operations on the data have been completed, you must explicitly call
Mysql_free_result to get the MySQL library to complete the cleanup process..
Extracting data
Write the program select1.c extract all records older than 5
#include <stdlib.h> #include <stdio.h> #include "mysql.h" int main (int argc, char *argv[]) {int res; MYSQL my_connection; Mysql_res *res_ptr; Mysql_row Sqlrow;mysql_init (&my_connection);//Initialize the connection handle, return a pointer to the newly allocated connection handle, and simply assign and initialize a struct if (Mysql_real_connect ( &my_connection, "localhost", "Rick", "Secret", "Foo", 0, NULL, 0)) {//mysql_real_connect provides parameters to a connection, Pointer Connection points to the structure printf ("Connection success\n") that has been initialized by mysql_init; res = mysql_query (&my_connection, "select Childno, fname, age from children WHERE > 5 "),//mysql_query parameters are SQL statements in the form of struct pointers and text strings, and if successful execute string representations of SQL statements, return 0if (RES) {printf ("Select Error:%s\n", Mysql_error (&my_connection));} Else{res_ptr = Mysql_store_result (&my_connection);//mysql_store_result extracts all data from select in a single call, returning a pointer to the result set structure if ( RES_PTR) {printf ("Retrieved%lu rows\n", (unsigned long) mysql_num_rows (res_ptr));//mysql_num_rows gets the number of records returned, accepted by MySQL _store_result returns the resulting structure pointer while ((Sqlrow = Mysql_fetch_row (res_ptr))) {//mysql_fetch_row from mysql_store_ A row of printf ("Fet") is extracted from the resulting structure of the resultChed data...\n ");} if (Mysql_errno (&my_connection)) {//mysql_errno Returns an error code, not 0 fprintf (stderr, "Retrieve Error:%s\n", Mysql_error (& my_connection));//mysql_error returns the wrong text message}mysql_free_result (RES_PTR);//mysql_free_result frees the memory resource that the query consumes}}mysql_close ( &my_connection);//close 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;}
Linux program Design-Execute SQL statement (Eighth chapter)
