/** 
 * @FileName    linux_c_mysql.c
 * @Describe    A simple example for operating mysql using c programming in linux system.
 * @Author      vfhky 2015.12.29 15:40 https://typecodes.com/cseries/linuxgccgconnectmysql.html
 * @Compile     gcc -I/usr/local/mysql/include  -L/usr/local/mysql/lib -lmysqlclient  linux_c_mysql.c -o linux_c_mysql
 * @Reference   http://dev.mysql.com/doc/refman/5.7/en/c-api-function-overview.html
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>


//MySQL connection.
MYSQL *pMysqlConn;
//result set.
MYSQL_RES *pMysqlRes;
//an instance of a row from the result.
MYSQL_ROW MysqlRow;

#define MAX_BUF_SIZE 1024

const char *pHostName = "localhost";    //or set the remote ip address.
const char *pUserName = "root";
const char *pPassword = "password";
const char *pDbName = "typecodes";      //database name of my typecho blog.
const unsigned int iDbPort = 3306;

/* print the last error message. */
void finish_with_error(const char *msg)
{
    if( msg )
        printf("Error message[%s]: [%s].\n", msg, mysql_error(pMysqlConn) );
    else
        printf( "Error message[%s].\n", mysql_error(pMysqlConn) );
    mysql_close(pMysqlConn);
    /**
     * When errors such as MySQL server has gone away hapended, the program should be end immeidately.
     * Otherwise, we should encounter the error below.
     * *** Error in `./linux_c_mysql': double free or corruption (!prev): 0x0000000001223560 ***
     */
    exit(-1);
}

/**
 * mysql_query() cannot be used for statements that contain binary data; you must use mysql_real_query() instead. 
 * Binary data may contain the “\0” character, which mysql_query() interprets as the end of the statement string.
 * In addition, mysql_real_query() is faster than mysql_query() because it does not call strlen() on the statement string. 
 */
int executesql( const char * sql )
{
    if( mysql_real_query( pMysqlConn, sql, strlen(sql) ) )
        return -1;
    return 0;
}

/* init the mysql connection. */
int init_mysql()
{
    pMysqlConn = mysql_init(NULL);
    if( pMysqlConn == NULL ) 
        return -1;
    if( !mysql_real_connect( pMysqlConn, pHostName, pUserName, pPassword, pDbName, iDbPort, NULL, 0 ) )
        return -2;
    //set the language for the results excuted.
    if( executesql("set names utf8") )
        return -3;
    return 0;
}


int main( int argc, char ** argv )
{
    int x=0, i=0;
    printf( "A example for connecting mysql using c program in linux.\n" );
    if( init_mysql() )
        finish_with_error(NULL);

    char cSqlData[MAX_BUF_SIZE] = {0x00};

    memcpy( cSqlData, "SELECT * FROM typecodes_users", strlen("SELECT * FROM vfhkytpvfhky_users") );
    if( executesql( cSqlData ) )
        finish_with_error(NULL);

    //pMysqlRes = mysql_use_result(pMysqlConn);
    pMysqlRes = mysql_store_result(pMysqlConn);

    int iNum_rows = mysql_num_rows(pMysqlRes);
    int iNum_fields = mysql_num_fields(pMysqlRes);

    printf( "Table have [%d] records containing [%d] fields in each one.\n", iNum_rows, iNum_fields );
    printf( "+------------------------------------------------+\n" );

    while( ( MysqlRow = mysql_fetch_row(pMysqlRes) ) )
    {
        i = 0;
        while( i < iNum_fields )
        {
            printf( "| %s", MysqlRow[i]?MysqlRow[i] : "NULL" );
            x = strlen(MysqlRow[i++]);
            for( ; x < 21; x++ )
                printf(" ");
        }
        printf("|\n");
    }

    printf( "+------------------------------------------------+\n" );
    mysql_free_result(pMysqlRes);
    mysql_close(pMysqlConn);
    return 0;
}