Use C language to call mysql database programming practices and skills, mysql Database Programming

Use C language to call mysql database programming practices and skills, mysql Database Programming

Today, I have compiled and used C language to call mysql database programming practices and skills for reference by other IT peers. If there are any errors, I can leave a message and learn together.

I. mysql database C language common interface API
1. First of all, it is connected to the database mysql_real_connect. The prototype is as follows:
MYSQL * STDCALL mysql_real_connect (
MYSQL * mysql, const char * host,
Const char * user,
Const char * passwd,
Const char * db,
Unsigned int port,
Const char * unix_socket,
Unsigned long clientflag );
The first parameter mysql is a very important variable in the C language api. It has a rich array of memory, including port, dbname, charset, and other connection basic parameters. It also contains a struct variable named st_mysql_methods, which stores many function pointers, which will be called in various data operations after the database connection is successful. The parameters in the mysql_real_connect function are mostly deliberate.

2. After successfully connecting to the database, you can use mysql_query to execute the SQL statement. The prototype is as follows:
Int STDCALL mysql_query (MYSQL * mysql, const char * q );
The first parameter has been described above. The second parameter is the SQL statement to be executed. It is mainly used to execute functions such as adding, deleting, modifying, and querying SQL statements.
This function takes two steps as a whole:
(1) When sending SQL statements, an SQL statement is actually sent using a socket, coupled with a fixed mysql protocol header.
(2) then accept the result. Here, the read_query_result function pointer in st_mysql_methods in MYSQL is called.

A if the query contains binary data, use mysql_real_query.

B. 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.

#include    <stdio.h>#include    <string.h>#include    <stdlib.h>#include    <math.h>#include "/usr/local/mysql/include/mysql.h"static MYSQL mysql, *sock;char sql[1024];MYSQL_RES *res=NULL;MYSQL_ROW row ;int num_fields;int num_rows = 0;int main(){    memset(sql, 0x00, sizeof(sql));    mysql_init(&mysql);       if(!(sock = mysql_real_connect(&mysql, \        (char *)"localhost", (char *)"ebipcs", \        (char *)"Dcep2vUnAX", (char *)"ebipcs",\         0, NULL, 0))){        printf( "Couldn't connect to DB!\n\n%s\n\n", mysql_error(&mysql));        return 0 ;    }    if(sock){        printf( "SUCCESS\n" );    }else{        printf( "FAIL\n" );        return 0;    }    sprintf(sql,"update cisaddressinfo set cisaddressinfo.addressseqno = '2' where cisaddressinfo.customid='199999900000000000015';");    if(mysql_query(sock, sql)) {         printf("mysql_query[%d] [%s]!\n", mysql_errno(sock), mysql_error(sock));         return -1;     }    if( !(mysql_affected_rows( sock )) ){        printf("update OK\n");    }else{        printf("update Fail\n");    }    if(mysql_errno(sock)){        printf("mysql_affected_rows[%d] [%s]!\n", mysql_errno(sock), mysql_error(sock));    }    return 0;}

3. Store execution results
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.

A. Extract all data at a time: mysql_store_result
If mysql_query returns a successful result, we can use the mysql_store_result function to read the result. The prototype is as follows:
MYSQL_RES * STDCALL mysql_store_result (MYSQL * mysql );
This function calls the read_rows function pointer in st_mysql_methods of the MYSQL variable to obtain the query result. At the same time, this function will return a variable such as MYSQL_RES, which is mainly used to save the query results. At the same time, this function malloc stores a piece of memory space to store the queried data, so we must note the free (result), otherwise it will certainly cause memory leakage. After mysql_store_result is executed, the data is already in the MYSQL_RES variable.
Related functions:
// This function is used 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 fails, NULL is returned.
MYSQL_RES * mysql_store_result (MYSQL * connection );
// This function accepts the result structure set returned by mysql_store_result and returns the number of rows in the structure set.
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. Return NULL when data is used up or an error occurs.
MYSQL_ROW mysql_fetch_row (MYSQL_RES * resutl );
// This function is used to jump in 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. Transfer
// 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 );
// Returns an offset value that indicates the current position in the result set. It is not a row number and cannot be used for mysql_data_seek.
MYSQL_ROW_OFFSET 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 you complete all data operations, you must always call this operation to handle the problem.
Void mysql_free_result (MYSQL_RES * result );

# 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 );}

B. 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.

Functions and definitions related to processing returned data:
// Number of fields (columns) in the returned result set
Unsigned int mysql_field_count (MYSQL * connection );
// Extract metadata and data into a new structure
MYSQL_FIELD * mysql_fetch_field (MYSQL * result );
// This function is used to overwrite the current field number. This number will automatically increase with each call to mysql_fetch_field. If 0 is passed to offset, it will jump back to column 1st
MYSQL_FIELD_OFFSET mysql_field_seek (MYSQL * result, MYSQL_FIELD_OFFSET offset );
// MYSQL_FIELD is defined in SQL. 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; // name of the table to which the column belongs
Char * def; // If mysql_list_fields is called, it will contain the default value of this column
Enum enum_field_types type; // column type
Unsigned int length; // column width
Unsigned int max_length; // If mysql_store_result is used, it will contain the length of the extracted longest column value in bytes. If mysql_use_result is used, it will not be set
Unsigned int flags; // The column-defined flag is irrelevant to the obtained data. Common flags have the following meanings:
// NOT_NULL_FLAG
// PRI_KEY_FLAG
// UNSIGNED_FLAG
// AUTO_INCREMENT_FLAG
// BINARY_FLAG
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 types 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.

# 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 de Tails: \ n "); while (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 );}

TIPS:
A. About MYSQL MYSQL_RES variable definition and free.
It is better to use the following method.
Definition: MYSQL_RES * res = NULL;
Use: res = mysql_store_result (m_sock)
Release: if (res)
{Mysql_free_result (res); res = NULL}
Based on the above rules, we define a macro:
* # Define MYSQLFREE ()\
If (! = NULL )\
{\
Mysql_free_result ();\
A = NULL ;\
}
MYSQLFREE (res)

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.