A complete example of C connection to MySQL database development in Linux environment (add, delete, modify, and query)

I. Development Environment

ReadHat6.332-bit, Mysql5.6.15, gcc4.4.6

Ii. Compilation

Gcc-I/usr/include/mysql-L/usr/lib-lmysqlclient main. c-o main. out

-I: Specify the directory where the mysql header file is located (search for the header file used under the/usr/include directory by default)

-L: Specifies the directory of the mysql dynamic library file (from the/usr/lib directory by default)

-L: link libmysqlclient. so dynamic library

-O: generated Executable File Name

Iii. Complete example

//// Main. c // mysql database programming /// Created by YangXin on 14-5-22. // Copyright (c) 2014 yangxin. all rights reserved. // # include
 
  
# Include
  
   
# Include
   
    
# Include
    
     
MYSQL mysql; // query int query (); // modify int update (); // add data my_ulonglong add (); // parameterize add data my_ulonglong addByParams (); // delete data my_ulonglong delete (); // print the database server information void printMySqlInfo (); int main (int argc, const char * argv []) {/* Before the connection, use mysql_init to initialize the MYSQL connection handle */mysql_init (& mysql);/* use mysql_real_connect to connect to the server. The parameters are MYSQL handle, Server IP address, user name and password used to log on to mysql, database to be connected */if (! Mysql_real_connect (& mysql, "localhost", "root", "yangxin", "test", 0, NULL, 0) {printf ("connecting to Mysql error: % d from % s \ n ", mysql_errno (& mysql), mysql_error (& mysql); return-1 ;}else {printf (" Connected Mysql successful! \ N ") ;}printmysqlinfo (); // sets the encoding mysql_query (& mysql," set names utf8 "); // parameterized data addByParams (); // query (); // modify update (); // add (); // delete ();/* close connection */mysql_close (& mysql ); return 0;} // query int query () {int flag, I; const char * SQL = NULL; MYSQL_RES * res = NULL; MYSQL_ROW row = NULL; MYSQL_FIELD * fields = NULL; SQL = "select * from t_user"; flag = mysql_real_query (& mysql, SQL, (unsig Ned int) strlen (SQL); if (flag) {printf ("query error: % d from % s \ n", mysql_errno (& mysql ), mysql_error (& mysql); return-1 ;}// read the query result to the memory. If there is a lot of data, the memory res = mysql_store_result (& mysql) is consumed ); // res = mysql_use_result (& mysql); // when needed, each time a row is read from the server // number of fields unsigned int field_count = mysql_field_count (& mysql ); printf ("field_cout: % d \ n", field_count); // query the total number of my_ulonglong rows = mysql_num_rows (res); pri Ntf ("% lld \ n", rows); // obtain all fields = mysql_fetch_fields (res); for (int I = 0; I <mysql_num_fields (res ); I ++) {printf ("% s \ t", fields [I]. name);} printf ("\ n"); // traverses the result set while (row = mysql_fetch_row (res) {for (I = 0; I <mysql_num_fields (res); I ++) {printf ("% s \ t", row [I]);} printf ("\ n ");} // release the result set mysql_free_result (res); return 0 ;}// modify int update () {const char * SQL = NULL; int flag =-1; SQL = "Update t_user set name = 'lisi', age = 20 where id = 1"; // run the SQL command flag = mysql_real_query (& mysql, SQL, (unsigned int) strlen (SQL); if (flag) {printf ("update data error: % d from % s \ n", mysql_errno (& mysql), mysql_error (& mysql )); return-1;} printf ("update success. \ n "); return 0;} // add my_ulonglong add () {const char * SQL = NULL; int flag =-1; SQL =" insert into t_user (name, age, address) values ('hangsa N', 40, 'beijing') "; // run flag = mysql_real_query (& mysql, SQL, strlen (SQL); if (flag) {printf ("add data error: % d from % s \ n", mysql_errno (& mysql), mysql_error (& mysql); return-1 ;} // The number of deleted rows my_ulonglong affected_rows = ignore (& mysql); // the ID of the newly added record my_ulonglong newid = mysql_insert_id (& mysql); printf ("success add % lld record data, newid: % lld! \ N ", affected_rows, newid); return newid;} // parameterized addition of my_ulonglong addByParams () {const char * SQL = NULL; MYSQL_STMT * stmt = NULL; MYSQL_BIND bnd [3]; // Number of placeholder parameters memset (& bnd, 0, sizeof (bnd); // you can specify the number of manually committed transactions. // mysql_autocommit (& mysql, 0 ); // Execute SQL SQL = "insert into t_user (name, age, address) values (?,?,?) through parameter placeholders (?,?,?) "; Stmt = mysql_stmt_init (& mysql); // pre-processing SQL if (mysql_stmt_prepare (stmt, SQL, (unsigned int) strlen (SQL) {fprintf (stderr, "mysql_stmt_prepare faild: % d from % s \ n", mysql_stmt_errno (stmt), mysql_stmt_error (stmt); return-1 ;} // encapsulate the placeholder data const char * name = "hanzhiqiang"; bnd [0]. buffer = (void *) name; bnd [0]. buffer_type = MYSQL_TYPE_STRING; bnd [0]. buffer_length = strlen (name); int age = 30; bnd [1]. bu Ffer = (void *) & age; bnd [1]. buffer_length = sizeof (int); bnd [1]. buffer_type = MYSQL_TYPE_LONG; const char * addres = "heimuer"; bnd [2]. buffer = (void *) addres; bnd [2]. buffer_length = strlen (addres); bnd [2]. buffer_type = MYSQL_TYPE_STRING; // bind the placeholder parameter value if (mysql_stmt_bind_param (stmt, bnd) {fprintf (stderr, "failed faild: % d from % s \ n ", mysql_errno (& mysql), mysql_error (& mysql); return-2 ;} // Execute SQL if (mysql_stmt_execute (stmt) {fprintf (stderr, "mysql_stmt_execute faild: % d from % s \ n", mysql_errno (& mysql ), mysql_error (& mysql); return-3 ;}// close statement mysql_stmt_close (stmt); // submit the transaction // mysql_commit (& mysql ); // transaction rollback (before committing a transaction) // mysql_rollback (& mysql); // obtain the number of records affected by the database after the inserted data my_ulonglong newid = mysql_stmt_insert_id (stmt ); printf ("parameterized statement Insert new record id: % lld \ n", newid); // The number of affected rows my_ulo Nglong affectedrows = mysql_stmt_affected_rows (stmt); printf ("Number of affected rows inserted by parameterized statements: % lld \ n", affectedrows); return newid;} my_ulonglong delete () {const char * SQL = NULL; int flag =-1; SQL = "delete from t_user where id> 10"; flag = mysql_real_query (& mysql, SQL, strlen (SQL); if (flag) {printf ("delete data error: % d from % s \ n", mysql_errno (& mysql), mysql_error (& mysql )); return-1;} my_ulonglong affected_ro Ws = mysql_affected_rows (& mysql); printf ("Number of deleted rows: % lld \ n", affected_rows); printf ("success delete % lld record data! \ N ", affected_rows); return affected_rows;} void printMySqlInfo () {const char * stat = mysql_stat (& mysql); const char * server_info = mysql_get_server_info (& mysql ); const char * clientInfo = Response (); unsigned long version = mysql_get_client_version (); const char * hostinfo = mysql_get_host_info (& mysql); unsigned long serverversion = mysql_get_server_version (& mysql ); unsigned int protoinfo = Mysql_get_proto_info (& mysql); printf ("stat: % s \ n", stat); printf ("server_info: % s \ n", server_info); printf ("clientInfo: % s \ n ", clientInfo); printf (" version: % ld \ n ", version); printf (" hostinfo: % s \ n ", hostinfo ); printf ("serverversion: % ld \ n", serverversion); printf ("protoinfo: % d \ n", protoinfo); const char * charactername = mysql_character_set_name (& mysql ); printf ("client character set: % s \ n", charactername); if (! Mysql_set_character_set (& mysql, "utf8") {printf ("New client character set: % s \ n", mysql_character_set_name (& mysql ));}}