Access Mysql Data using C language -- execute SQL statement (2)

2011-05-15 wcdj

 

(1) SQL statements that do not return data-Update, delete, and insert
(2) discover the inserted content
(3) Statements for returning data
(1) functions for extracting all data at a time
(2) functions for extracting a row of data at a time

(3) Statements for returning data


The most common use of SQL is to extract data rather than insert or update data. Data is extracted using the SELECT statement.
Description
: MySQL also supports the use of SQL statements show, describe, and explain to return results, but we will not involve them here. By convention, the Manual contains an explanation of these statements.

Data extraction in c applications generally requires the following:4 steps
:
[1] Query
[2] Data Extraction
[3] process data
[4] necessary cleanup work
Use mysql_query to send SQL statements, use mysql_store_result or mysql_use_result to extract data, and then use mysql_fetch_row to process data. Finally, use mysql_free_result to release the memory resources occupied by the query.
Description
: What is the difference between mysql_use_result and mysql_store_result?
: Mainly,Do you want to return a row of data at a time or all the results at a time. When you predict that the result set is small, the latter will be more appropriate.


(1) functions for extracting all data at a time

You can use mysql_store_result to extract all data from select (or other statements that return data) in one call:
Mysql_res * mysql_store_result (MySQL * connection );

You need to use this function after mysql_query is successfully called. This function will immediately save all the data returned in the client. It returns a pointer to the result set structure. If the result set fails, null is returned.
After mysql_store_result is successfully called, you need to call mysql_num_rows to obtain the number of returned records. We hope this is a positive number, but if no row is returned, this value will be 0.
My_ulonglong mysql_num_rows (mysql_res * result );

This function accepts the result structure returned by mysql_store_result and returns the number of rows in the result set. If mysql_store_result is successfully called, mysql_num_rows will always be successful.

Note:
:
If you happen to be using a special dataset, you can finally extract smaller and easier-to-manage information blocks because it will return control to the application more quickly, it does not occupy a large amount of network resources. For details, refer to the usage of mysql_use_result.

Now, you can use mysql_fetch_row to process it, or use mysql_data_seek, mysql_row_seek, and mysql_row_tell to move back and forth in the dataset.

[1] mysql_fetch_row

This function extracts a row from the result structure obtained using mysql_store_result and places it in a row structure. Return null when data is used up 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. The setting will be returned by the next mysql_fetch_row operation. The value of the offset parameter is a row number, which must be within the range from 0 to the total number of rows in the result set minus 1. Passing 0 will cause the first row in the returned result set of the next mysql_fetch_row call.
Void mysql_data_seek (mysql_res * result, my_ulonglong offset );

[3] mysql_row_tell

This function returns an offset value indicating the current position in the result set. It is not a row number. You cannot use it for mysql_data_seek.
Mysql_row_offset mysql_row_tell (mysql_res * result );

[4] mysql_row_seek

You can use the return value of mysql_row_tell in mysql_row_seek.
Mysql_row_offset mysql_row_seek (mysql_res * result, mysql_row_offset offset );
This will move the current position in the result set and return the previous position.
Note:
This function is useful for moving between known points in the result set. Be careful not to confuse the offset used by row_tell and row_seek with the row number used by data_seek. Otherwise, the results become unpredictable.

[5] mysql_free_result

Void mysql_free_result (mysql_res * result );
After you complete the dataset operation, you must always call this function to let the MySQL database clean up the objects it allocates.

Example:
Select All records whose ages are greater than 100.
Select1.c

Log on to MySQL using wcdj
$ Mysql-u wcdj-P newdatabase
Enter password:
Welcome to the MySQL monitor. commands end with; or/g.
Displays all data in a table.
Mysql> select * from children;

# Include <stdlib. h> <br/> # include <stdio. h> <br/> # include "MySQL. H "<br/> MySQL my_connection; <br/> mysql_res * res_ptr; <br/> mysql_row sqlrow; <br/> int main (INT argc, char * argv []) <br/>{< br/> int res; <br/> mysql_init (& my_connection); <br/> If (mysql_real_connect (& my_connection, "localhost ", "wcdj", "123", "newdatabase", 0, null, 0) <br/>{< br/> printf ("connection success/N "); <br/> res = mysql_query (& my_connection, "select childno, fname, age from children where age> 100"); <br/> If (RES) <br/>{< br/> printf ("select error: % s/n", mysql_error (& my_connection )); <br/>}< br/> else // OK <br/>{< br/> res_ptr = mysql_store_result (& my_connection); <br/> If (res_ptr) // OK <br/>{< br/> printf ("retrieved % lu rows/N", (unsigned long) mysql_num_rows (res_ptr )); // OK <br/> while (sqlrow = mysql_fetch_row (res_ptr) <br/>{< br/> printf ("fetched data... /n "); // OK <br/>}< br/> If (mysql_errno (& my_connection) <br/>{< br/> fprintf (stderr, "retrive error: % s/n", mysql_error (& my_connection); <br/>}< br/> mysql_free_result (res_ptr ); <br/>}< br/> mysql_close (& my_connection ); <br/>}< br/> else <br/> {<br/> fprintf (stderr, "Connection Failed/N "); <br/> If (mysql_errno (& my_connection) <br/>{< br/> fprintf (stderr, "connection error % d: % s/n ", mysql_errno (& my_connection), <br/> mysql_error (& my_connection); <br/>}< br/> return exit_success; <br/>}< br/>

Compile the program:
$ Gcc-I/usr/include/MySQL select1.c-L/usr/lib/MySQL-lmysqlclient-O select1

 

(2) functions for extracting a row of data at a time

Mysql_res * mysql_use_result (MySQL * connection );

Like the mysql_store_result function, mysql_use_result returns NULL in case of an error. If successful, it returns a pointer to the result set object. However, the difference is that it does not place the extracted data in its initialization result set.
Note:
:
To obtain data, you must call mysql_fetch_row repeatedly until all data is extracted. If no data is obtained from mysql_use_result, subsequent data extraction operations in the program may return corrupted information.

What is the difference between calling mysql_use_result and calling mysql_store_result?
What about it?
[1] mysql_use_resultDisadvantages

1. The former has substantial benefits in resource management, but it cannot be used with mysql_data_seek, mysql_row_seek, or mysql_row_tell, and will not take effect until all data is extracted, the use of mysql_num_rows is also limited.
2. In addition, latency is increased because each row request and result return must go through the network.
3. Another possibility is that the network connection may fail in the middle of the operation, leaving incomplete data to you.
[2] mysql_use_resultAdvantages

However, none of the above disadvantages will erase the benefits of mysql_use_result: better balance the network load and reduce the storage overhead of potentially large datasets.

Example:
The mysql_use_result function is used here.
Select2.c

 

# Include <stdlib. h> <br/> # include <stdio. h> <br/> # include "MySQL. H "<br/> MySQL my_connection; <br/> mysql_res * res_ptr; <br/> mysql_row sqlrow; <br/> int main (INT argc, char * argv []) <br/>{< br/> int res; <br/> mysql_init (& my_connection); <br/> If (mysql_real_connect (& my_connection, "localhost ", "wcdj", "123", "newdatabase", 0, null, 0) <br/>{< br/> printf ("connection success/N "); <br/> res = mysql_query (& my_connection, "select childno, fname, age from children where age> 100"); <br/> If (RES) <br/>{< br/> printf ("select error: % s/n", mysql_error (& my_connection )); <br/>}< br/> else // OK <br/> {<br/> // res_ptr = mysql_store_result (& my_connection ); <br/> res_ptr = mysql_use_result (& my_connection); // mysql_use_result <br/> If (res_ptr) // OK <br/>{< br/> printf ("retrieved % lu rows/N", (unsigned long) mysql_num_rows (res_ptr); // OK, 0 <br/> while (sqlrow = mysql_fetch_row (res_ptr) <br/>{< br/> printf ("fetched data... /n "); // OK <br/>}< br/> If (mysql_errno (& my_connection) <br/>{< br/> fprintf (stderr, "retrive error: % s/n", mysql_error (& my_connection); <br/>}< br/> mysql_free_result (res_ptr ); <br/>}< br/> mysql_close (& my_connection ); <br/>}< br/> else <br/> {<br/> fprintf (stderr, "Connection Failed/N "); <br/> If (mysql_errno (& my_connection) <br/>{< br/> fprintf (stderr, "connection error % d: % s/n ", mysql_errno (& my_connection), <br/> mysql_error (& my_connection); <br/>}< br/> return exit_success; <br/>}< br/>

Compile the program:
$ Gcc-I/usr/include/MySQL select2.c-L/usr/lib/MySQL-lmysqlclient-O select2

Note:
:
Before extracting the last result, you still cannot get the number of rows (test, mysql_nuw_rows returns 0 at this time ). However, through early and regular error checks, it is easier to adjust the program to use mysql_use_result. Writing code in this way can reduce the trouble of modifying many programs later.

Reference
:
Basic SQL statements
Http://www.longen.org/s-z/details ~ Z/sqlsentence.htm

Linux programming (version 4th) Chapter 8th p.293