|
|
Posted on 2012-12-06 17:42 盛勝 閱讀(479) 評論(0) 編輯 收藏 引用
http://blog.chinaunix.net/uid-677314-id-379541.html 摘在網上，對于編寫程序可以作為參考。首先，包括頭文件。#include <mysql/mysq.h>編譯方法：$ gcc -I/usr/include/mysql -L/usr/lib/mysql myapp.c -o myapp -lmysqlclient
程序：
connect_simplest.c#include <stdio.h> #include <mysql.h> MYSQL *conn; /* pointer to connection handler */int main ( int argc, char *argv[] ){//初始化mysql的句柄connconn = mysql_init ( NULL ); //連接mysql數據庫mysql_real_connect (conn, /* pointer to connection handler */"localhost", /* host to connect to */"user_name", /* user name */"password", /* password */"test", /* database to use */0, /* port (default 3306) */NULL, /* socket or /var/lib/mysql.sock */0 ); /* flags (none) *///關閉數據庫，句柄 mysql_close ( conn );return 0;}compilation: gcc -Wall connect_simplest.c -lmysqlclientIf mysql.h is not in a standard place: -I/path/to/the/mysql.If libmysqlclient.so or libmysqlclient.a is not in a standardplace: -L/path/to/the/mysqlclient/library.(match better use Makefile)For more secure connection you should use struct option from <getopt.h>conn.hMYSQL *conn;MYSQL_RES *res;MYSQL_ROW row;const char* groups[] = {"client",NULL};struct option long_options[] ={{"host",required_argument,NULL,'h'},{"user",required_argument,NULL,'u'},{"password",optional_argument,NULL,'p'},{"port",required_argument,NULL,'P'},{"socket",required_argument,NULL,'S'},{0,0,0,0}};unsigned long how_rows (void);void print_error(char *);conn.c#include <mysql.h> #include <stdio.h> #include <stdlib.h> #include <getopt.h> // for getopt_long #include "conn.h" int main (int argc, char* argv[]){int i, c, option_index;char databases[15][30];char *pass = NULL;char *user = NULL;char *host = NULL;char *sock = NULL;unsigned int port = 0;// needs for operations required by load_defaults().my_init();// reads /etc/.my.cnf or ${HOME}/.my.cnf fileload_defaults("my",groups,&argc,&argv);// allows assigning values for host, pass...while((c=getopt_long(argc,argv,"h:p:u:P:S:",long_options,&option_index)) != EOF){switch(c){case 'h':host = optarg;break;case 'u':user = optarg;break;case 'p':pass = optarg;break;case 'P':port = (unsigned int) atoi(optarg);break;case 'S':sock = optarg;break;}}//=================================================if ( (conn = mysql_init(NULL)) == NULL){print_error("mysql_init()");exit (EXIT_FAILURE);}if ( (mysql_real_connect(conn,host,user,pass,"",0,NULL,0))== NULL){print_error("mysql_real_connect()");exit (EXIT_FAILURE);}mysql_query(conn,"show databases");res = mysql_store_result(conn);i=0;system("clear");printf("\nThere are %lu databases\n",how_rows());puts("=====================");while ( row = mysql_fetch_row(res) ){strcpy(databases[i],row[0]);puts(databases[i]);i++;}mysql_close(conn);return 0;}//---------------------------------------------------------- unsigned long how_rows (void)//---------------------------------------------------------- {return (unsigned long) mysql_num_rows(res);}//---------------------------------------------------------- void print_error(char* msg)//---------------------------------------------------------- {fprintf(stderr,"%s\n",msg);if ( conn != NULL ){fprintf(stderr,"Error: %u %s\n",mysql_errno(conn),mysql_error(conn));}}OUTPUT:// There are 16 databases // ===================== // School // Student // TransAct // ana // bank // billing // helpfile // helpfile1 // mysql // phone // sales_analis // samp_db // student // studentdb // test // user1 Of course, you have to hide your password from the direct viewing.I usually use <password.hh> that I createdfor similar purposes.simp_conn_pass.c#include <mysql.h> #include <password.hh> MYSQL *conn;MYSQL_RES *res;MYSQL_ROW row;int main (){conn = mysql_init ( NULL );mysql_real_connect ( conn,"localhost","ana",password(),"bank",0,NULL,0);mysql_query(conn,"show tables");res = mysql_store_result(conn);while ( row = mysql_fetch_row(res) ){fprintf (stdout,"%s\n", row[0]);}fprintf (stdout,"\n%lu rows affected\n",(unsigned long) mysql_num_rows(res));mysql_close(conn);exit(0);}OUTPUT:// BANK_WORKER // BRANCHES // CHECKING_ACCT // CHECKING_REP // CLOSE_CHECK_ACCT // CLOSE_CUSTOMERS // CLOSE_SAVINGS_ACCT // CUSTOMERS // LOAN_ACCT // SAVINGS_ACCT // SAVINGS_REP // // 11 rows affected Some Usage some_usage.c #include <mysql.h> #include <stdlib.h> #include <stdio.h> #include <password.hh> MYSQL *conn;MYSQL_RES *res;MYSQL_ROW row;unsigned long how_rows (void);void print_data(void);char* to_upper (char*);void print_table(void);void print_line (void);//----------------------------------------------------------int main (int argc, char* argv[]){unsigned long ul;int i;char databases[25][30];char query[30];char temp[30], table[30];conn = mysql_init ( NULL );mysql_real_connect (conn,"","ana",password(),"",3306,NULNULL;mysql_query(conn,"show databases");res = mysql_store_result(conn);ul = how_rows();i=0;system("clear");printf("\nThere are %lu databases\n",ul);puts("=====================");while ( row = mysql_fetch_row(res) ){strcpy(databases[i],row[0]);puts(databases[i]);i++;}for ( i = 0; i < ul; i++ ){if ( strcmp(databases[i],"mysql") == 0 )continue;sprintf(query,"use %s",databases[i]);mysql_query(conn,query);strcpy(temp,databases[i]);fprintf(stdout,"\nThe database %s consists tables:\n",to_upper(temp));puts("==========================================");print_data();getchar();// usleep(900000); }while(1){fprintf(stdout,"What database do you want to use? (0-quit): ");fgets(temp,29,sstdin;temp[strlen(temp)-1] = '\0';if ( strcmp(temp,"0") == 0 )break;for ( i = 0; i < ul; i++ ){if ( strcmp(temp,databases[i]) == 0 ){while(1){fprintf(stdout,"What table do you want to see? ");fprintf(stdout,"(0-quit): ");fgets(table,29,sstdin;table[strlen(table)-1] = '\0';if ( strcmp(table,"0") == 0 )break;sprintf(query,"select * from %s.%s",temp,table);mysql_query(conn,query);print_table();}}}} mysql_close(conn);exit(0);}//---------------------------------------------------------- unsigned long how_rows (void)//---------------------------------------------------------- {return (unsigned long) mysql_num_rows(res);}//---------------------------------------------------------- void print_data(void)//---------------------------------------------------------- {mysql_query(conn,"show tables");res = mysql_store_result(conn);while ( row = mysql_fetch_row(res) ){fprintf(stdout,"%s\n",row[0]);}}//---------------------------------------------------------- char* to_upper (char* name)//---------------------------------------------------------- {char *p = name;while (*p){*p = toupper(*p);p++;}return name;}//---------------------------------------------------------- void print_table(void)//---------------------------------------------------------- {MYSQL_FIELD *field;unsigned int i, j, column_length;res = mysql_store_result(conn);mysql_field_seek(res,0);for ( i = 0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);column_length = strlen(field->name);if ( column_length < field->max_length )column_length = field->max_length;if ( column_length < 4 && !IS_NOT_NULL(field->flags) )column_length = 4;field->max_length = column_length;}print_line();fputc('\n',stdstdoutmysql_field_seek(res,0);for ( i = 0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);fprintf(stdout," %-*s ",field->max_length,field->name);}fputc('\n',stdstdout print_line();fputc('\n',stdstdoutwhile ((row = mysql_fetch_row(res)) != NULL){mysql_field_seek(res,0);for ( i = 0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);if ( row[i] == NULL )fprintf(stdout," %-*s ",field->max_length,"NULL");elseif ( IS_NUM(field->type) )fprintf(stdout," %*s ",field->max_length,row[i]);elsefprintf(stdout," %-*s ",field->max_length,row[i]);}fputc('\n',stdstdout }fputc('\n',stdstdout mysql_free_result(res);}//---------------------------------------------------------- void print_line (void)//---------------------------------------------------------- {MYSQL_FIELD *field;unsigned int i, j;mysql_field_seek(res,0);for ( i = 0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);for ( j = 0; j < field->max_length + 2; j++ )fputc('=',ststdout}}OUTPUT:There are 16 databases=====================SchoolStudentTransActanabankbillinghelpfilehelpfile1mysqlphonesales_analissamp_dbstudentstudentdbtestuser1The database SCHOOL consists tables:==========================================school_studentThe database STUDENT consists tables:==========================================studentsThe database TRANSACT consists tables:==========================================destinationpurposetesttranskepperThe database ANA consists tables:==========================================addition_infocasesstudent_infostudent_recordsteacherteacher_roomtemptemp_studenttemp_teachertemp_teacher_roomThe database BANK consists tables:==========================================BANK_WORKERBRANCHESCHECKING_ACCTCHECKING_REPCLOSE_CHECK_ACCTCLOSE_CUSTOMERSCLOSE_SAVINGS_ACCTCUSTOMERSLOAN_ACCTSAVINGS_ACCTSAVINGS_REPThe database BILLING consists tables:==========================================The database HELPFILE consists tables:==========================================datafilehelpfileThe database HELPFILE1 consists tables:==========================================datafileThe database PHONE consists tables:==========================================addressphoneThe database SALES_ANALIS consists tables:==========================================departmentsquotesalesThe database SAMP_DB consists tables:==========================================absenceeventmemberpresidentscorestudentThe database STUDENT consists tables:==========================================studentsubjectsThe database STUDENTDB consists tables:==========================================studentsubjectsThe database TEST consists tables:==========================================tt1testtest1test2The database USER1 consists tables:==========================================What database do you want to use? (0-quit): bankWhat table do you want to see? (0-quit): BRANCHES================================================================BRANCH_NAME BRANCH_NUM BRANCH_ADDRESS================================================================BROOKLYN_BRANCH 001 718 Ave N, Brooklyn, NY 11234MANHATTAN_BRANCH 002 291 5th Ave, New York, NY, 10070What table do you want to see? (0-quit): BANK_WORKER=============================================================B_SSN B_FNAME B_LNAME B_ID B_PHONE B_EXT=============================================================277-65-8877 John Smith 001 718-999-6655 313665-66-4423 Kevin Walters 001 718-999-6655 444775-99-7543 Peter Kats 002 212-999-8655 111777-65-6654 Anna Katsnelson 001 718-999-6655 222765-87-6540 Sam Jefferson 002 212-999-8655 123What table do you want to see? (0-quit): 0What database do you want to use? (0-quit): 0
//MYSQL m_sqlCon;extern CDBConnectDlg dbConnectDlg; dbConnectDlg.m_edit_dbip.GetWindowText(dbConnectDlg.m_strdbip); dbConnectDlg.m_edit_dbuser.GetWindowText(dbConnectDlg.m_strdbuser); dbConnectDlg.m_edit_dbpwd.GetWindowText(dbConnectDlg.m_strdbpwd); dbConnectDlg.m_edit_dbport.GetWindowText(dbConnectDlg.m_strdbport); dbConnectDlg.intport=atoi(dbConnectDlg.m_strdbport); bool testcon=false; MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row; conn=mysql_init(&dbConnectDlg.m_sqlCon); testcon=mysql_real_connect(&dbConnectDlg.m_sqlCon,dbConnectDlg.m_strdbip,dbConnectDlg.m_strdbuser,dbConnectDlg.m_strdbpwd,"",dbConnectDlg.intport,NULL,0); if (testcon) { mysql_query(conn,"show databases;"); res = mysql_store_result(conn); int i=0; //char m_databases[MAX_PATH][MAX_PATH]; memset(m_databases,0,MAX_PATH*MAX_PATH); while ( row = mysql_fetch_row(res) ) { strcpy(m_databases[i],row[0]); puts(m_databases[i]); i++; } m_dbcount=i; mysql_close(&dbConnectDlg.m_sqlCon); } else { memset(m_databases,0,MAX_PATH*MAX_PATH); }
|