Execute MySQL statements in C language in Linux

The main API functions used to add, delete, modify, and query SQL statements are:

int mysql_query(MYSQL *connection, const char *query);

The function receives the parameter connection handle and a valid SQL statement in the string form (there is no ending semicolon, which is different from the mysql tool ). If successful, it returns 0.

To query binary data, use mysql_real_query.

Check the number of rows affected by the query:

my_ulonglong mysql_affected_rows(MYSQL *connection);

My_ulonglong is an unsigned long integer in % lu format.
This function returns the number of rows affected by the previous update, insert, or delete queries. Example

The database has a student table.

 

CREATE TABLE student (student_no varchar(12) NOT NULL PRIMARY KEY,student_name varchar(12) NOT NULL); 

Add, delete, and modify code:

# Include <stdio. h> # include <stdlib. h> # include <string. h> # include "mysql. h "# include" errmsg. h "# include" mysqld_error.h "MYSQL conn; void connection (const char * host, const char * user, const char * password, const char * database) {mysql_init (& conn ); // note that & if (mysql_real_connect (& conn, host, user, password, database, 0, NULL, 0) {printf ("Connection success! \ N ");} else {fprintf (stderr," Connection failed! \ N "); if (mysql_errno (& conn) {fprintf (stderr," Connection error % d: % s \ n ", mysql_errno (& conn ), mysql_error (& conn);} exit (EXIT_FAILURE);} void insert () {int res = mysql_query (& conn, "insert into student (student_no, student_name) VALUES ('20140901', 'ann ') "); if (! Res) {printf ("Inserted % lu rows \ n", (unsigned long) mysql_affected_rows (& conn);} else {fprintf (stderr, "Insert error % d: % s \ n ", mysql_errno (& conn), mysql_error (& conn) ;}} void update () {int res = mysql_query (& conn, "UPDATE student SET student_name = 'Anna 'WHERE student_no = '000000'"); if (! Res) {printf ("Update % lu rows \ n", (unsigned long) mysql_affected_rows (& conn);} else {fprintf (stderr, "Update error % d: % s \ n ", mysql_errno (& conn), mysql_error (& conn) ;}} void delete () {int res = mysql_query (& conn, "DELETE from student WHERE student_no = '000000'"); if (! Res) {printf ("Delete % lu rows \ n", (unsigned long) mysql_affected_rows (& conn);} else {fprintf (stderr, "Delete error % d: % s \ n ", mysql_errno (& conn), mysql_error (& conn) ;}} int main (int argc, char * argv []) {connection (" localhost ", "root", "shuang", "shuangde"); delete (); mysql_close (& conn); exit (EXIT_SUCCESS );}

Statement for returned data: select

The most common use of SQL is to extract data rather than insert or update data. The data is extracted using the select statement.

Generally, the C application requires four steps to extract data:

1. Execute the query

2. extract data

3. Process Data

4. necessary cleanup work

Just like insert and update, use mysql_query to send SQL statements, and then use mysql_store_result or mysql_use_result to extract data. The specific statement depends on how you want to extract data. Next, a series of mysql_fetch_row will be used to process data. Finally, use mysql_free_result to release the memory resources occupied by the query.

Extract all data at a time: mysql_store_result

// Related function: // this function is used after mysql_query is successfully called. This function will immediately save all the data returned from the client. It returns a pointer to the result set structure. If the result fails, NULLMYSQL_RES * mysql_store_result (MYSQL * connection) is returned. // This function accepts the result structure set returned by mysql_store_result, return the number of rows in the constructor my_ulonglong mysql_num_rows (MYSQL_RES * result); // This function extracts a row from the result structure obtained using mysql_store_result and places it in a row structure. When data is used up or an error occurs, NULL. MYSQL_ROW mysql_fetch_row (MYSQL_RES * resutl) is returned. // This function is used to jump to the result set. The setting will be returned by the next mysql_fetch_row operation. The offset parameter is a row number, which must be 0 ~ The total number of results rows is within the range of-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); // return an offset value, which indicates the current position of the result set. It is not a row number and cannot be used to define mysql_row_tell (MYSQL_RES * result); // This will move the current position in the result set and return the previous position MYSQL_ROW_OFFSET mysql_row_seek (MYSQL_RES * result, MYSQL_ROW_OFFSET offset); // after all data operations are completed, you must always call this method to handle void mysql_free_result (MYSQL_RES * result );

Sample Code:

# Include <stdio. h> # include <stdlib. h> # include <string. h> # include "mysql. h "# include" errmsg. h "# include" mysqld_error.h "MYSQL conn; MYSQL_RES * res_ptr; MYSQL_ROW sqlrow; void connection (const char * host, const char * user, const char * password, const char * database) {mysql_init (& conn); // take note of the address & if (mysql_real_connect (& conn, host, user, password, database, 0, NULL, 0 )) {printf ("Connection success! \ N ");} else {fprintf (stderr," Connection failed! \ N "); if (mysql_errno (& conn) {fprintf (stderr," Connection error % d: % s \ n ", mysql_errno (& conn ), mysql_error (& conn);} exit (EXIT_FAILURE);} int main (int argc, char * argv []) {connection ("localhost", "root ", "shuang", "shuangde"); int res = mysql_query (& conn, "SELECT * from student"); if (res) {fprintf (stderr, "SELECT error: % s \ n ", mysql_error (& conn) ;}else {res_ptr = mysql_store_result (& conn); if (res_ptr) {printf (" Retrieved % lu rows \ n ", (unsigned long) mysql_num_rows (res_ptr); while (sqlrow = mysql_fetch_row (res_ptr) {printf ("Fetched data... \ n ") ;}if (mysql_errno (& conn) {fprintf (stderr," Retrive error: % s \ n ", mysql_error (& conn ));} mysql_free_result (res_ptr) ;}} mysql_close (& conn); exit (EXIT_SUCCESS );}

Extract a row of data at a time: mysql_use_result

The usage is exactly the same as that of mysql_store_result. Change mysql_store_result in the above Code to mysql_use_result.

Mysql_use_result has substantial benefits in resource management, better balance the network load, and reduce the storage overhead caused by potentially large data volumes, however, it cannot be used with mysql_data_seek, mysql_row_seek, mysql_row_tell, and mysql_num_rows. If the data volume is small, it is better to use mysql_store_result.

Process returned data

// Related functions and definitions: // number of fields (columns) in the returned result set unsigned int mysql_field_count (MYSQL * connection ); // extract metadata and data to a new structure. MYSQL_FIELD * mysql_fetch_field (MYSQL * result); // This function overwrites the current field number, this number will automatically increase with each mysql_fetch_field call. If 0 is passed to offset, the system will jump back to MYSQL_FIELD_OFFSET mysql_field_seek (MYSQL * result, MYSQL_FIELD_OFFSET) in column 1st. // MYSQL_FIELD is defined in SQL. in h, It is a pointer to the field structure data and has column information. Member: char * name; // column name, which is a string char * table; // The name of the table to which the column belongs char * def; // if you call mysql_list_fields, it will contain the default enum enum_field_types type for this column; // The column type unsigned int length; // The column width unsigned int max_length; // If mysql_store_result is used, it will contain the length of the longest column value extracted in bytes. If mysql_use_result is used, it will not be set to unsigned int flags; // The column definition flag, it is irrelevant to the obtained data. common signs are: // NOT_NULL_FLAG/PRI_KEY_FLAG // UNSIGNED_FLAG/AUTO_INCREMENT_FLAG/BINARY_FLAG and other unsigned int Decimals; // number of digits after the decimal point. // The column type is quite extensive. For the complete list, see the header file mysql_com.h. Common examples include: // FIELD_TYPE_DECIMAL // FIELD_TYPE_LONG // FIELD_TYPE_STRING // FIELD_TYPE_VAR_STRING // a particularly useful predefined macro: IS_NUM. If the field type is a number, true is returned.

Sample Code:

# Include <stdio. h> # include <stdlib. h> # include <string. h> # include "mysql. h "# include" errmsg. h "# include" mysqld_error.h "MYSQL conn; MYSQL_RES * res_ptr; MYSQL_ROW sqlrow; void connection (const char * host, const char * user, const char * password, const char * database) {mysql_init (& conn); // take note of the address & if (mysql_real_connect (& conn, host, user, password, database, 0, NULL, 0 )) {printf ("Connection success! \ N ");} else {fprintf (stderr," Connection failed! \ N "); if (mysql_errno (& conn) {fprintf (stderr," Connection error % d: % s \ n ", mysql_errno (& conn ), mysql_error (& conn);} exit (EXIT_FAILURE);} void display_row () {unsigned int field_count = mysql_field_count (& conn); int I = 0; while (I <field_count) {if (sqlrow [I]) printf ("% s", sqlrow [I]); else printf ("NULL"); I ++ ;} printf ("\ n");} void display_header () {MYSQL_FIELD * field_ptr; printf ("Column details: \ n"); whil E (field_ptr = mysql_fetch_field (res_ptr ))! = NULL) {printf ("\ t Name: % s \ n", field_ptr-> name); printf ("\ t Table: % s \ n ", field_ptr-> table); printf ("\ t Type:"); if (IS_NUM (field_ptr-> type) {printf ("Numeric field \ n ");} else {switch (field_ptr-> type) {case FIELD_TYPE_VAR_STRING: printf ("VARCHAR \ n"); break; case FIELD_TYPE_LONG: printf ("LONG"); break; default: printf ("Type is % d, check in msyql_com.h \ n", field_ptr-> type) ;}} printf ("\ t Max width % ld \ n ", field_ptr-> length); if (field_ptr-> flags & AUTO_INCREMENT_FLAG) printf ("\ t Auto increments \ n"); printf ("\ n ");}} int main (int argc, char * argv []) {connection ("localhost", "root", "shuang", "shuangde"); int res = mysql_query (& conn, "SELECT * from student"); if (res) {fprintf (stderr, "SELECT error: % s \ n", mysql_error (& conn ));} else {res_ptr = mysql_use_result (& conn); if (res_ptr) {int first = 1; while (sqlrow = mysql_fetch_row (res_ptr) {if (first) {display_header (); first = 0;} display_row () ;}if (mysql_errno (& conn) {fprintf (stderr, "Retrive error: % s \ n ", mysql_error (& conn);} mysql_free_result (res_ptr);} mysql_close (& conn); exit (EXIT_SUCCESS );}