/*
  dbexample.c
  Ira Ray Jenkins

  This simple example demonstrates connecting to a MySQL database and
  executing a query.
 */
#include <stdio.h>                           // for fprintf() and printf()
#include <stdlib.h>                          // for exit()
#include <mysql.h>                           // for mysql functionality

// db connection defines
#define SERVER   "sunapee.cs.dartmouth.edu"  // db server to connect to
#define USERNAME "user"                      // user to connect as
#define PASSWORD "pass"                      // user's password
#define DATABASE "db"                        // db to use
#define QUERY    "SELECT * FROM instructor;" // query statement

int main() {
  // local variables
  MYSQL *con = NULL;                         // db connection pointer
  MYSQL_RES *res = NULL;                     // result set
  MYSQL_ROW row;                             // single row of result
  int numColumns = 0;                        // number of columns

  // attempt to initialize db connection
  if(!(con = mysql_init(NULL))) {
    fprintf(stderr, "ERROR: %s\n", mysql_error(con));
    exit(1);
  }

  // attempt to connect to db
  if(!mysql_real_connect(con, SERVER, USERNAME, PASSWORD, DATABASE, 0, NULL, 0)) {
    fprintf(stderr, "Error: Cannot connect to database server.");
    exit(1);
  }
  
  printf("Connection established.\n");

  // attempt to query db
  if(mysql_query(con, QUERY)) {
    fprintf(stderr, "Error: Cannot query database.");
    exit(1);
  }
  
  printf("Query executed: '%s'\n\nResults:\n", QUERY);

  // store query result
  if(!(res = mysql_store_result(con))) {
    fprintf(stderr, "Error: Cannot store query result.");
    exit(1);
  }

  // store number of columns in result set
  numColumns = mysql_num_fields(res);

  // print table header
  int i;
  for(i = 0; i < numColumns; i++) {
    printf("%-12s", mysql_fetch_field(res)->name);
  }
  printf("\n--------------------------------------------\n");

  // iterate through results
  while((row = mysql_fetch_row(res))) {
    for(i = 0; i < numColumns; i++) {
      printf("%-12s", row[i] ? row[i] : "NULL");
    }
    printf("\n");
  }

  // cleanup
  mysql_free_result(res);
  mysql_close(con);

  printf("Connection terminated.");

  return 0;
}