Access Mysql Data using C language-Execute SQL statements (3)

2011-05-16 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
(4) process returned data

(4) process returned data

Now that you know how to extract rows, you can learn how to process the actual data returned. Like most SQL databases, MySQL returnsTwo types of data
.
[1] information extracted from the table, that isColumn data
.
[2] data, the so-calledMetadata
(Metadata), such as the column name and type.

Problem
: How to convert the data itself into a useful form.

The mysql_field_count function provides basic information about the query results. It accepts the connection object and returns the number of fields (columns) in the result set:
Unsigned intMysql_field_count
(MySQL * connection );

In a more general way, you can use mysql_field_count to do other things, such as judging why the call to mysql_store_result fails. For example, if mysql_store_result returns NULL, but mysql_field_count returns a positive number, you can assume that this is an extraction error. However, if mysql_field_count returns 0, no columns can be extracted. This explains why the storage results fail.

Example:
Print unformatted data
Select3.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/> void display_row (); <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); <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/> display_row (); // print <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/> // print <br/> void display_row () <br/>{< br/> unsigned int field_count; <br/> field_count = 0; <br/> while (field_count <mysql_field_count (& my_connection) <br/>{< br/> printf ("% s ", sqlrow [field_count]); <br/> field_count ++; <br/>}< br/> printf ("/N"); <br/>}< br/>

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

Problem:
The above program can run, although the output is not particularly beautiful. However, possible null values in the result are not considered. If you want to print data in a more neat format (or table-based format), you need to get the data and metadata returned by MySQL at the same time. You can use mysql_fetch_field to extract metadata and data to a new structure at the same time.

Mysql_field *Mysql_fetch_field
(Mysql_res * result );
You need to call this function again until the return value of null indicates the end of the data. Then, you can use a pointer to the field structure data to obtain information about the column. The structure mysql_filed is defined in MySQL. h.

Example:
More complete printing
Select4.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/> void display_header (); <br/> void display_row (); <br/> int main (INT argc, char * argv []) <br/>{< br/> int res; <br/>/* used to ensure we display the row header exactly once when data is successfully retrieved */<br/> int first_row = 1; <br/> mysql_init (& my_co Nnection); <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_res Ult (& my_connection); <br/> res_ptr = mysql_use_result (& my_connection); <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/> If (first_row) <br/>{< br/> display_header (); <br/> first_row = 0; <br/>}< br/> display_row (); // print <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/> void display_header () <br/>{< br/> mysql_field * field_ptr; <br/> printf ("column details:/N "); <br/> while (field_ptr = mysql_fetch_field (res_ptr ))! = NULL) <br/>{< br/> printf ("/T name: % s/n", field_ptr-> name ); <br/> printf ("/T type:"); <br/> If (is_num (field_ptr-> type )) <br/>{< br/> printf ("numeric field/N "); <br/>}< br/> else <br/>{< br/> switch (field_ptr-> type) <br/>{< br/> case field_type_var_string: <br/> printf ("varchar/N"); <br/> break; </P> <p> case field_type_long: <br/> printf ("Long/N"); <br/> break; <br/> default: <br/> printf ("type is % d, check in mysql_com.h/N ", field_ptr-> type ); <br/>}/ * switch */<br/>}/* else */<br/> printf ("/T max width % LD/N ", field_ptr-> length); <br/> If (field_ptr-> flags & auto_increment_flag) <br/> printf ("/T auto increments/N "); <br/> printf ("/N"); <br/>}/ * While */<br/>}< br/> void display_row () <br/>{< br/> unsigned int field_count; <br/> field_count = 0; <br/> while (field_count <mysql_field_count (& my_connection )) <br/>{< br/> // printf ("% s", sqlrow [field_count]); </P> <p> If (sqlrow [field_count]) <br/> printf ("% s", sqlrow [field_count]); <br/> else <br/> printf ("null "); </P> <p> field_count ++; <br/>}< br/> printf ("/N"); <br/>}< br/>

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

This is still not very nice, but it demonstrates how to use data more effectively by simultaneously processing raw data and metadata.

For more instructions, see the MySQL manual.

Reference
:
Basic SQL statements

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