Invoke API to implement additions and deletions to MySQL database and transaction implementation

Use the API to implement additions and deletions to change client code:

#include <stdio.h> #include <mysql.h> #include <string.h> #include <unistd.h> #define _host_ Name_ "127.0.0.1" #define _ACT_NAME_ "root" #define _ACT_PWD_ "123" #define _DB_NAME_ "Scott" void Showresult (Mysql_res * Result) {Mysql_field *field; int numfield = Mysql_num_fields (Result), while (FIELD = Mysql_fetch_field (Result)) {printf (" %-7s\t ", field->name);  } printf ("\ n--------------------------------------------------------------\ n"); Mysql_row ROW;  while (row = mysql_fetch_row (result)) {int i; for (i = 0;i < numfield;i++) {printf ("%-7s\t", Row[i]);} printf ("\ n"); } printf ("--------------------------------------------------------------\ n");} int main () {//Initialize Mysqlmysql *mysql = Mysql_init (null); if (mysql = = NULL) {printf ("init err\n"); return-1;} Build connection MySQL = Mysql_real_connect (mysql,_host_name_,_act_name_,_act_pwd_,_db_name_,0,null,0); if (mysql = = NULL) { fprintf (stderr, "Failed to connect to Database:error:%s\n", Mysql_error (MySQL)); return-2;} Set the character set mysql_set_Character_set (MySQL, "UTF8");p rintf ("Connected ok!\n"); char rsql[512] ={0};while (1) {Write (Stdout_fileno, "sql>", 4); memset (Rsql,0,sizeof (rsql)); Read (stdin_fileno,rsql,sizeof (rsql));//exit If the judgment is quit (strncasecmp (Rsql, "Quit", 4) = = 0) {printf ("bye!\n"); break;} else if (mysql_query (Mysql,rsql)) {fprintf (stderr, "Failed to query to Database:error:%s\n", Mysql_error (MySQL) ); continue;} Get recordset Mysql_res *result = Mysql_store_result (mysql); if (Result! = NULL) {Showresult (result);//Release Recordset Mysql_free_result (result);} else{printf ("Query OK,%ld row affected \ n", (long) mysql_affected_rows (MySQL));}} Mysql_close (MySQL); return 0;}

　

Using the API to manipulate transactions:

Transactions in MySQL # include <stdio.h> #include <stdlib.h> #include <string.h> #include "mysql.h" #define Set_ TRAN "Set autocommit=0"//Manual commit ———— manual Commit#define Unset_tran "set autocommit=1"//Auto Commit#define _host_ "127.0.0.1 "#define _USER_" root "#define _PASSWD_" 123 "#define _DBNAME_" Scott "//Set up a transaction to manually commit int Mysql_operationtran (MySQL *mysql) {/  /--open transaction int ret = mysql_query (mysql, "Start transaction"); Open transaction start transactionif (ret! = 0) {printf ("Mysql_operationtran query start err:%s\n", Mysql_error (MySQL)); return re t;} --Set the transaction to manually submit ret = mysql_query (mysql, Set_tran);//set autocommmit = 0if (ret! = 0) {printf ("Mysql_operationtran query SET ERR:%s\n ", Mysql_error (MySQL)); return ret;} return ret;}  Set TRANSACTION to auto commit int Mysql_autotran (MySQL *mysql) {//--open transaction int ret = mysql_query (mysql, "Start transaction"); if (ret! = 0) {printf ("Mysql_autotran query start err:%s\n", Mysql_error (MySQL)); return ret;}  --Set transaction to auto submit ret = mysql_query (mysql, Unset_tran); "Set autocommit = 1" if (ret! = 0){printf ("Mysql_autotran query Set ERR:%s\n", Mysql_error (MySQL)); return ret;} return ret;}  Execute commit, manually commit transaction int mysql_commit (MySQL *mysql) {int ret = mysql_query (MySQL, "commit");//Submit if (ret! = 0) {printf ("Commit ERR:%s\n ", Mysql_error (MySQL)); return ret;} return ret;} Execute Rollback, rollback TRANSACTION int mysql_rollback (MySQL *mysql) {int ret = mysql_query (mysql, "Rollback"), if (ret! = 0) {printf (" Rollback ERR:%s\n ", Mysql_error (MySQL)); return ret;} return ret;}                                                  #define DROP_SAMPLE_TABLE "DROP table IF EXISTS test_table" #define create_sample_table "CREATE table test_table (col1 INT, Col2 VARCHAR (Ten), Col3 VA Rchar (Ten)) "#define SQL01" INSERT in To Test_table (col1,col2,col3) VALUES (' AAA ', ' A1 ') "#define SQL02" INSERT into test_table (col1,col2,col3) VALUES (20, ' BBB ', ' B2 ') "#define SQL03" INSERT into test_table (col1,col2,col3) VALues ("CCC", ' C3 ') "#define SQL04" INSERT into test_table (col1,col2,col3) VALUES (+, ' DDD ', ' D4 ') "int main (void) {int re t = 0; MySQL *mysql = mysql_init (null); mysql = Mysql_real_connect (mysql, _host_, _user_, _passwd_, _dbname_, 0, NULL, 0); if (mysq L = = NULL) {ret = Mysql_errno (mysql);p rintf ("Func mysql_real_connect () err:%d\n", ret); return ret;} printf ("---connect o  K......\n ");//execute Delete table if (mysql_query (MySQL, drop_sample_table)) {fprintf (stderr," DROP table failed\n ");  fprintf (stderr, "%s\n", Mysql_error (MySQL)); Exit (0);}  Execute CREATE TABLE if (mysql_query (MySQL, create_sample_table)) {fprintf (stderr, "CREATE TABLE failed\n");  fprintf (stderr, "%s\n", Mysql_error (MySQL)); Exit (0);} ret = Mysql_operationtran (MySQL); Open the transaction and modify the transaction properties to manual commit if (ret! = 0) {printf ("Mysql_operationtran () err:%d\n", ret); return ret;} ret = mysql_query (mysql, sql01);//Insert the first row of data into the table ' AAA ' if (ret! = 0) {printf ("mysql_query () err:%d\n", ret); return ret;} ret = mysql_query (mysql, sql02);//Insert second row of data into table ' BBB ' if (ret! = 0) {printf ("mysql_query () err:%d\n", ret); return ret;} ret = mysql_commit (MySQL); Manually commit the transaction if (ret! = 0) {printf ("Mysql_commit () err:%d\n", ret); return ret;} AAA BBB went in. #if 1ret = Mysql_autotran (mysql); = again = Modify the transaction property to "Auto" commitif (ret! = 0) {printf ("Mysql_operationtran () err:%d\n", ret); return ret;} #else ret = Mysql_operationtran (MySQL); = again = Modify the transaction property to "manual" Commitif (ret! = 0) {printf ("Mysql_operationtran () err:%d\n", ret); return ret;} #endifret = mysql_query (MySQL, sql03);//Insert the third row of data into the table ' CCC ' if (ret! = 0) {printf ("mysql_query () err:%d\n", ret); return ret;} ret = mysql_query (mysql, sql04);//Insert the fourth row of data into the table ' DDD ' if (ret! = 0) {printf ("mysql_query () err:%d\n", ret); return ret;} ret = Mysql_rollback (mysql);//Direct Rollback operation if (ret! = 0) {printf ("Mysql_rollback () err:%d\n", ret); return ret;} Whether the rollback operation can return the value of CCC, DDD, depends on the transaction properties. Mysql_close (MySQL); return 0;}

　　

Invoke API to implement additions and deletions to MySQL database and transaction implementation