//*************************************************
//  Testcase for using MYSQL C API
//
//  Simple create table, insert data, update and
//   select (with bind)
//*************************************************

#include <iostream>
#include <mysql.h>

using std::cout;
using std::endl;
using std::cin;

const char* user   = "USER";
const char* passwd = "PASSWORD";
const char* dbase  = "DATABASE";
const char* server = "10.68.3.129";


#define TABLENAME   "foo_dfr"

#define CREATETABLE "create table foo_dfr(col1 int, col2 int, col3 int, col4 int)"
#define DROPTABLE   "DROP TABLE " TABLENAME
#define SELECTSTMT  "select * from foo_dfr"
#define INSERTSTMT  "insert into foo_dfr values( ?,?,?,? )"

inline void printMsg(const char* str)
{
    cout << endl;
    cout << "--------------------------------------------------" << endl;
    cout << "       " << str << endl;
    cout << "--------------------------------------------------" << endl << endl;
}

inline void doFatalError(const char* str)
{
    cout << "FATAL ERROR : " << str << endl;
    exit(1);
}


inline void doFetchError(MYSQL* pMysql)
{
    cout << "MYSQL ERROR # " << mysql_errno(pMysql) << " : " << mysql_error(pMysql) << endl;
}


void doExecuteDirect(MYSQL* pMysql, const char * sql)
{
    cout << "EXECUTING SQL >>  " << sql << endl;
    if (mysql_real_query(pMysql, sql, strlen(sql))) {
        doFetchError(pMysql);
    }
}

void doBindInsert(MYSQL* pMysql)
{
    printMsg("BIND INSERT");
    MYSQL_STMT* stmt;
    MYSQL_BIND bind[4];  // For two variables
    int intVal = 10;
    my_bool isNull;
    my_bool isNull2;
    stmt = mysql_prepare(pMysql, INSERTSTMT, strlen(INSERTSTMT));
    if(!stmt) {
        doFetchError(pMysql);
        return;
    }
    if (mysql_param_count(stmt) != 4) {
        cout << "Incorrect param count understood by Mysql. Its going nuts." << endl;
        return;
    }

    // Now lets bind
    bind[0].buffer_type = MYSQL_TYPE_LONG;
    bind[0].buffer = (char*)&intVal;
    bind[0].is_null = &isNull;
    bind[0].length = 0;  // Not needed for int type

    bind[1].buffer_type = MYSQL_TYPE_LONG;
    bind[1].buffer = (char*)&intVal;
    bind[1].is_null = &isNull;
    bind[1].length = 0;  // Not needed for int type

    bind[2].buffer_type = MYSQL_TYPE_LONG;
    bind[2].buffer = (char*)&intVal;
    bind[2].is_null = &isNull;
    bind[2].length = 0;  // Not needed for int type

    bind[3].buffer_type = MYSQL_TYPE_LONG;
    bind[3].buffer = (char*)&intVal;
    bind[3].is_null = &isNull2;
    bind[3].length = 0;  // Not needed for int type
    
    // Now bind them
    if (mysql_bind_param(stmt, bind)) {
        doFetchError(pMysql);
        return;
    }

    // Set the data
    isNull = 0;
    isNull2 = 0;
    intVal = 22;

    if(mysql_execute(stmt)) {
        doFetchError(pMysql);
        return;
    }

    isNull = 1;
    isNull2 = 1;
    intVal = 29;

    if(mysql_execute(stmt)) {
        doFetchError(pMysql);
        return;
    }
    
    isNull = 0;
    isNull2 = 0;
    intVal = 88;

    if(mysql_execute(stmt)) {
        doFetchError(pMysql);
        return;
    }
    
    cout << "Execution succeded. Rows affected = " << (int)mysql_stmt_affected_rows(stmt) << endl;
    
    if(mysql_stmt_close(stmt)) {
        doFetchError(pMysql);
        return;
    }
    
}

void doBoundSelect(MYSQL* mysql, int)
{
  printMsg("Bound Select");
  MYSQL_STMT    *stmt;
  MYSQL_BIND*    bind;
  MYSQL_RES     *prepare_meta_result;
  unsigned long length[4];
  int           column_count, row_count;
  int           int_data1;
  int           int_data2;
  int           int_data3;
  int           int_data4;
  my_bool       is_null[4];
  
  bind = new MYSQL_BIND[4];

  /* Prepare a SELECT query to fetch data from test_table */
  stmt = mysql_prepare(mysql,  SELECTSTMT, strlen(SELECTSTMT));
  if (!stmt)
  {
    fprintf(stderr, " mysql_prepare(), SELECT failed\n");
    fprintf(stderr, " %s\n", mysql_error(mysql));
    exit(0);
  }
  fprintf(stdout, " prepare, SELECT successful\n");

  // Fetch result set meta information
  prepare_meta_result = mysql_get_metadata(stmt);
  if (!prepare_meta_result)
  {
    fprintf(stderr, " mysql_prepare_result(), returned no meta information\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }

/* Get total columns in the query */
  column_count= mysql_num_fields(prepare_meta_result);
  fprintf(stdout, " total columns in SELECT statement: %d\n", column_count);
  
  /* Execute the SELECT query */
  if (mysql_execute(stmt))
  {
    fprintf(stderr, " mysql_execute(), failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }


  // Now buffer all results to client 
  if (mysql_stmt_store_result(stmt))
  {
    fprintf(stderr, " mysql_stmt_store_result() failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }

  MYSQL_RES* result = mysql_get_metadata(stmt);
  if (result) { // Yes. Process data
      // Describe the result set.
      int nFields = mysql_num_fields(result);
      MYSQL_FIELD* fields = mysql_fetch_fields(result);  
    cout << "Result set schema has " << nFields << " fields" << endl
         << "=========================================" << endl;
    for (int i=0; i < nFields; ++i) {   // For each field, dump its output
       cout << fields[i].name 
            << "      " << fields[i].table
            //<< "      " << fields[i].def  // CAUTION: Not without mysql_list_fields
            << "      " << fields[i].type
            << "      " << fields[i].length
            << "      " << fields[i].max_length // Not for use_result()
            << "      " << (IS_NOT_NULL(fields[i].flags)?"NOT NULL":"NULLable")
            << "      " << (IS_PRI_KEY(fields[i].flags)?"PRIMARY KEY":"")
            << "      " << fields[i].decimals
            << endl;
    }
  }
  
  // Bind the result buffers for all 2 columns before fetching them 

  // INTEGER COLUMN 
  bind[0].buffer_type= MYSQL_TYPE_LONG;
  bind[0].buffer= (char *)&int_data1;
  bind[0].is_null= &is_null[0];
  bind[0].length= &length[0];  

  // INTEGER COLUMN 
  bind[1].buffer_type= MYSQL_TYPE_LONG;
  bind[1].buffer= (char *)&int_data2;
  bind[1].is_null= &is_null[1];
  bind[1].length= &length[1];  

  // INTEGER COLUMN 
  bind[2].buffer_type= MYSQL_TYPE_LONG;
  bind[2].buffer= (char *)&int_data3;
  bind[2].is_null= &is_null[2];
  bind[2].length= &length[2];  

  // INTEGER COLUMN 
  bind[3].buffer_type= MYSQL_TYPE_LONG;
  bind[3].buffer= (char *)&int_data4;
  bind[3].is_null= &is_null[3];
  bind[3].length= &length[3];    
 
  /* Bind the result buffers */
  if (mysql_bind_result(stmt, bind))
  {
    fprintf(stderr, " mysql_bind_result() failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }
  
  /* Fetch all rows */
  row_count= 0;
  fprintf(stdout, "Fetching results ...\n");
  while (!mysql_fetch(stmt))
  {
    row_count++;
    fprintf(stdout, "  row %d\n", row_count);
  
    fprintf(stdout, "   column1 (integer)  : ");
    if (is_null[0])
      fprintf(stdout, " NULL\n");
    else
      fprintf(stdout, " %d (%ld)\n", int_data1, length[0]);

    fprintf(stdout, "   column1 (integer)  : ");
    if (is_null[1])
      fprintf(stdout, " NULL\n");
    else
      fprintf(stdout, " %d (%ld)\n", int_data2, length[1]);      

    fprintf(stdout, "   column1 (integer)  : ");
    if (is_null[2])
      fprintf(stdout, " NULL\n");
    else
      fprintf(stdout, " %d (%ld)\n", int_data3, length[2]);      

    fprintf(stdout, "   column1 (integer)  : ");
    if (is_null[3])
      fprintf(stdout, " NULL\n");
    else
      fprintf(stdout, " %d (%ld)\n", int_data4, length[3]);        
    fprintf(stdout, "\n");
    
  }

  /* Validate rows fetched */
  fprintf(stdout, " total rows fetched: %d\n", row_count);

  /* Free the prepared result metadata */
  mysql_free_result(prepare_meta_result);
  mysql_free_result(result);
  
  /* Close the statement */
  if (mysql_stmt_close(stmt))
  {
    fprintf(stderr, " failed while closing the statement\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
  }
   
}


int main()
{
    printMsg("STARTING");
    // Initialization
    MYSQL* myconn = mysql_init(NULL);
    if (!myconn) doFatalError("Could not get MYSQL*");
    // Set the options.
    if (mysql_options(myconn, MYSQL_OPT_CONNECT_TIMEOUT, "30")) {
        doFetchError(myconn);
    } else {
        cout << "Connection timeout set to 30" << endl;
    }

    if (!mysql_real_connect(myconn, server, user, passwd, dbase, 0, NULL, 0))
    {
        doFetchError(myconn);
        doFatalError("Failed to connect to server");
    }

    // First create table
    printMsg("CREATE TABLE");
    doExecuteDirect(myconn, CREATETABLE);

    doBindInsert(myconn);

    // Select with binding
    doBoundSelect(myconn, 0);

    // Drop the table.
    cout << "Ready to drop table. Press any key to continue..." << endl;
    cin.get();
    doExecuteDirect(myconn, DROPTABLE);


    // Close the connection
    mysql_close(myconn);
    return 0;
}