MySQL Programming: C API

The MySQL client "C" language API development libraries and "C" header files are available as MySQL development packages. On my Red Hat Enterprise 6 workstation it is mysql-devel-5.1.47-4.el6.x86_64
The MySQL client API has been ported to many platforms with many different compilers. To help developers determine the compiling and linking flags to use on their platform use the mysql_config utility program:
» C compile flags: mysql_config --cflags
» Result: -I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC -DUNIV_LINUX
» Linker flags: mysql_config --libs
» Result: -rdynamic -L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lssl -lcrypto
Example MySQL C API program:
Example C/C++ program:
File: testMySqlAPI.cpp

#include 
#include 
#include 

class FFError
{
public:
    std::string    Label;

    FFError( ) { Label = (char *)"Generic Error"; }
    FFError( char *message ) { Label = message; }
    ~FFError() { }
    inline const char*   GetMessage  ( void )   { return Label.c_str(); }
};

using namespace std;

main()
{
    // --------------------------------------------------------------------
    // Connect to the database
    
    MYSQL      *MySQLConRet;
    MYSQL      *MySQLConnection = NULL;

    string hostName = "localhost";
    string userId   = "user1";
    string password = "supersecret";
    string DB       = "bedrock";

    MySQLConnection = mysql_init( NULL );

    try
    {
        MySQLConRet = mysql_real_connect( MySQLConnection,
                                          hostName.c_str(), 
                                          userId.c_str(), 
                                          password.c_str(), 
                                          DB.c_str(), 
                                          0, 
                                          NULL,
                                          0 );

        if ( MySQLConRet == NULL )
            throw FFError( (char*) mysql_error(MySQLConnection) );
   
        printf("MySQL Connection Info: %s \n", mysql_get_host_info(MySQLConnection));
        printf("MySQL Client Info: %s \n", mysql_get_client_info());
        printf("MySQL Server Info: %s \n", mysql_get_server_info(MySQLConnection));

    }
    catch ( FFError e )
    {
        printf("%s\n",e.Label.c_str());
        return 1;
    }
      
    int             mysqlStatus = 0;
    MYSQL_RES      *mysqlResult = NULL;

    // --------------------------------------------------------------------
    // This block of code would be performed if this insert were in a loop 
    // with changing data. Of course it is not necessary in this example.

    if(mysqlResult)
    {
        mysql_free_result(mysqlResult);
        mysqlResult = NULL;
    }

    // --------------------------------------------------------------------
    // Perform a SQL INSERT

    try
    {
        string sqlInsStatement = "INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Betty Rubble','IT','Neighbor')";
        mysqlStatus = mysql_query( MySQLConnection, sqlInsStatement.c_str() );
        if (mysqlStatus)
        {
            throw FFError( (char*)mysql_error(MySQLConnection) );
        }
    }
    catch ( FFError e )
    {
        printf("%s\n",e.Label.c_str());
        mysql_close(MySQLConnection);
        return 1;
    }

    if(mysqlResult)
    {
        mysql_free_result(mysqlResult);
        mysqlResult = NULL;
    }
    
    // --------------------------------------------------------------------
    // Perform a SQL SELECT and retrieve data

    MYSQL_ROW       mysqlRow;
    MYSQL_FIELD    *mysqlFields;
    my_ulonglong    numRows;
    unsigned int    numFields;
    
    try
    {
        string sqlSelStatement = "SELECT * FROM employee";
        mysqlStatus = mysql_query( MySQLConnection, sqlSelStatement.c_str() );

        if (mysqlStatus)
            throw FFError( (char*)mysql_error(MySQLConnection) );
        else
            mysqlResult = mysql_store_result(MySQLConnection); // Get the Result Set

        if (mysqlResult)  // there are rows
        {
            // # of rows in the result set
            numRows = mysql_num_rows(mysqlResult);

            // # of Columns (mFields) in the latest results set
            numFields = mysql_field_count(MySQLConnection);

            // Returns the number of columns in a result set specified
            numFields = mysql_num_fields(mysqlResult);

            printf("Number of rows=%u  Number of fields=%u \n",numRows,numFields);
        }
        else
        {
           printf("Result set is empty");
        }
    
        // Print column headers

        mysqlFields = mysql_fetch_fields(mysqlResult);

        for(int jj=0; jj < numFields; jj++)
        {
            printf("%s\t",mysqlFields[jj].name);
        }
        printf("\n");
     
        // print query results

        while(mysqlRow = mysql_fetch_row(mysqlResult)) // row pointer in the result set
        {
            for(int ii=0; ii < numFields; ii++)
            {
                printf("%s\t", mysqlRow[ii] ? mysqlRow[ii] : "NULL");  // Not NULL then print
            }
            printf("\n");
        }

        if(mysqlResult)
        {
            mysql_free_result(mysqlResult);
            mysqlResult = NULL;
        }
    }
    catch ( FFError e )
    {
        printf("%s\n",e.Label.c_str());
        mysql_close(MySQLConnection);
        return 1;
    }

    // --------------------------------------------------------------------
    // Close datbase connection

    mysql_close(MySQLConnection);

    return 0;
}

Compile: g++ -o testMySqlAPI testMySqlAPI.cpp `mysql_config --cflags` `mysql_config --libs`
Run: ./testMySqlAPI
Source URL: http://www.yolinux.com/TUTORIALS/MySQL-Programming-C-API.html