(1)編譯
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ gcc t17.c `mysql_config --cflags --libs` -o t17
(2)轉入用資料檔cust.csv:
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ cat cust.csv cardid,uname,remain 922001,鼠,6000 922002,牛,5000 922003,虎,4000 922004,兔,3000 922005,龍,2000 922006,蛇,1000
(3)執行
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ ./t17 cust.csv 0[922001] 1[鼠] 2[6000] SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922001','鼠' ,'xxxx',6000.00)] 執行成功!
0[922002] 1[牛] 2[5000] SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922002','牛' ,'xxxx',5000.00)] 執行成功!
0[922003] 1[虎] 2[4000] SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922003','虎' ,'xxxx',4000.00)] 執行成功!
0[922004] 1[兔] 2[3000] SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922004','兔' ,'xxxx',3000.00)] 執行成功!
0[922005] 1[龍] 2[2000] SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922005','龍' ,'xxxx',2000.00)] 執行成功!
0[922006] 1[蛇] 2[1000] SQL指令: [insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'922006','蛇' ,'xxxx',1000.00)] 執行成功! (4)程式碼
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ cat t17.c #include <my_global.h> #include <mysql.h> #define HOST "localhost" #define USER "treehrt" #define PWD "abc123abc123" #define DB "ilvssell"
typedef struct CUSTREC { //char uid[10]; double remain; //char inyear[5]; //char dept[10]; //char grade[10]; //char class[10]; char cardid[10]; char uname[10]; //char pid[10]; //char addr[80]; //char tel[15]; //char oldclass[10]; //char sno[2]; //int lostc; //char gen_code; //char in_use; //char cid[4];
} custRec ; void finish_with_error(MYSQL *con) { fprintf(stderr, "%s\n", mysql_error(con)); mysql_close(con); exit(1); } char tokens[100][60]; //最多100個存放token int tokensc; //tokens計數
void insert_new_cust_rec(custRec cR){ // printf("待寫入的資料:cR.cardid=%s,cR.uname=%s cR.remain=%.2f\n",cR.cardid,cR.uname,cR.remain); char st[500]; sprintf(st,"insert into new_cust (inyear,cardid,uname,cid4,remain) values (107,'%s','%s' ,'xxxx',%.2f)",cR.cardid,cR.uname,cR.remain) ; MYSQL *con = mysql_init(NULL); if (con == NULL) finish_with_error(con); if (mysql_real_connect(con, HOST , USER, PWD, DB, 0, NULL, 0) == NULL) finish_with_error(con); mysql_query(con, "set names utf8"); if (mysql_query(con, st)) finish_with_error(con); printf("SQL指令: [%s] 執行成功! \n\n", st); }
void parsecsvline(char *st ,char deli){ char tk[80]; tokensc=0; strcpy(tk,""); for (int i=0;i<strlen(st);i++) { if((st[i]==deli) || (st[i]==0x0a)) { strcpy(tokens[tokensc],tk); tokensc++; strcpy(tk,""); } else sprintf(tk,"%s%c",tk,st[i]); } for (int i=0;i<tokensc;i++) { printf("%d[%s] ",i,tokens[i]); } printf("\n"); }
int main(int argc, char **argv) { char st[100]; FILE *fp; custRec cR;
fp = fopen(argv[1] , "r"); //argv[1] use for csv filename ,ex cust.csv. if(fp == NULL) { perror("Error opening file"); return(-1); } fgets(st,100,fp); while (1){ if( fgets(st, 100, fp)!=NULL ) { parsecsvline(st,','); strcpy(cR.cardid,tokens[0]); strcpy(cR.uname,tokens[1]); cR.remain=atof(tokens[2]); insert_new_cust_rec(cR); } else break; } fclose(fp);
exit(0); } |