使用 C/C++ 存取 MySQL範例

/*
 ============================================================================
 Name        : mysql_sample.c
 Author      : Peter
 Version     :
 Copyright   : 
 Description : 
 ============================================================================
 */

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

struct connection_details {
 char *server;
 char *user;
 char *password;
 char *database;
};

MYSQL* mysql_connection_setup(struct connection_details mysql_details) {
 // first of all create a mysql instance and initialize the variables within
 MYSQL *connection = mysql_init(NULL);

 // connect to the database with the details attached.
 if (!mysql_real_connect(connection, mysql_details.server,
   mysql_details.user, mysql_details.password, mysql_details.database,
   0, NULL, 0)) {
  printf("Conection error : %s\n", mysql_error(connection));
  exit(1);
 }
 return connection;
}

MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query) {
 // send the query to the database
 if (mysql_query(connection, sql_query)) {
  printf("MySQL query error : %s\n", mysql_error(connection));
  exit(1);
 }

 return mysql_use_result(connection);
}

int main(void) {
 MYSQL *conn; // the connection
 MYSQL_RES *res; // the results
 MYSQL_ROW row; // the results row (line by line)

 struct connection_details mysqlD;
 mysqlD.server = "localhost"; // where the mysql database is
 mysqlD.user = "root"; // the root user of mysql
 mysqlD.password = "ds630805"; // the password of the root user in mysql
 mysqlD.database = "test_db"; // the databse to pick

 // connect to the mysql database
 conn = mysql_connection_setup(mysqlD);

 // assign the results return to the MYSQL_RES pointer
 res = mysql_perform_query(conn, "show tables");

 printf("MySQL Tables in mysql database:\n");
 while ((row = mysql_fetch_row(res)) != NULL)
  printf("%s\n", row[0]);

 printf("\n\n");;
 res = mysql_perform_query(conn, "select * from employee");
 printf("employee data in mysql database:\n");
 while ((row = mysql_fetch_row(res)) != NULL)
  printf("%s,%s,%s,%s \n", row[0],row[1],row[2],row[3]);

 // delete
 if (mysql_perform_query(conn,"insert into employee values(NULL,'Peter','RD','RD')")==0)
  printf("inert ok\n");
 else
  printf("inert error %d: %sn",mysql_errno(conn),mysql_error(conn));

 // delete
 if (mysql_perform_query(conn,"delete from employee where IDpk=4")==0)
  printf("delete ok\n");
 else
  printf("delete error %d: %sn",mysql_errno(conn),mysql_error(conn));
 //mysql_commit(conn);
 /* clean up the database result set */
 mysql_free_result(res);
 /* clean up the database link */
 mysql_close(conn);

 return EXIT_SUCCESS;
}