Yolinux.com Tutorial

MySQL Programming: C API

This tutorial shows the use of the MySQL "C" programming API to call native MySQL functions to access the database.

For an introduction to installation, configuration and use of the MySQL database see the YoLinux MySQL tutorial.

Tutorial Table of Contents:

Related YoLinux Tutorials:

°Linux and MySQL

°MySQL, InnoDB, Transactions and Foreign Keys

°MySQL Workbench

°SQL Workbench/J

°List of Linux Databases

°PostgreSQL

°SQLite

°Apache, Tomcat, MySQL and Java

°YoLinux Tutorials Index




Free Information Technology Magazines and Document Downloads
TradePub link image


MySQL C Language 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:

Two examples:
Insert and select from an existing database:

This example will use the following example database schema and contents:

CREATE DATABASE bedrock;
USE bedrock;
CREATE TABLE employee (IDpk integer  NOT NULL auto_increment,
                       Employee_Name char(20),
                       Dept char(20),
                       JobTitle char(20),
                       PRIMARY KEY (IDpk));
INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Fred Flinstone','Worker','Rock Digger');
INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Wilma Flinstone','Finance','Analyst');
INSERT into employee (Employee_Name,Dept,JobTitle) VALUES ('Barney Rubble','Sales','Neighbor');

Example C/C++ program:

File: testMySqlAPI.cpp

#include <stdio.h>
#include <mysql.h>
#include <string>

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;
}
Note:
  • The query string is NOT terminated with a ";"
  • The function mysql_query() accepts a string query
  • The function mysql_real_query() accepts a binary query

Compile: g++ -o testMySqlAPI testMySqlAPI.cpp `mysql_config --cflags` `mysql_config --libs`

Run: ./testMySqlAPI
MySQL Connection Info: Localhost via UNIX socket 
MySQL Client Info: 5.1.47 
MySQL Server Info: 5.1.47 
Number of rows=4  Number of fields=4 
IDpk	Employee_Name	Dept	JobTitle	
1	Fred Flinstone	Worker	Rock Digger	
2	Wilma Flinstone	Finance	Analyst	
3	Barney Rubble	Sales	Neighbor	
4	Betty Rubble	IT	Neighbor

Generate a new database and table and insert a record:

File: genDatabase.cpp

#include <stdio.h>
#include <mysql.h>
#include <string>

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;

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

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

    MySQLConnection = mysql_init( NULL );

    try
    {
        MySQLConRet = mysql_real_connect( MySQLConnection,
                                          hostName.c_str(), 
                                          userId.c_str(), 
                                          password.c_str(), 
                                          NULL,  // No database specified
                                          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;
    }
      

    // --------------------------------------------------------------------
    //  Create database
    
    if (mysql_query(MySQLConnection, "CREATE DATABASE adams")) 
    {
        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
        return(1);
    }

    // --------------------------------------------------------------------
    //  Now that database has been created set default database

    if (mysql_query(MySQLConnection, "USE adams") )
    {
        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
        return(1);
    }

    // --------------------------------------------------------------------
    //  Create table and records

    if (mysql_query(MySQLConnection, "CREATE TABLE family (Name char(20),Room char(8),Phone char(24))") )
    {
        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
        return(1);
    }

    if (mysql_query(MySQLConnection, "INSERT INTO family VALUES ('Gomez Adams', 'master', '1-555-1212')") )
    {
        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
        return(1);
    }

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

    mysql_close(MySQLConnection);

    return 0;
}

Compile: g++ -o genDatabase genDatabase.cpp `mysql_config --cflags` `mysql_config --libs`

Run: ./genDatabase

Check MySQL for the database, table and data:
mysql> use adams;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> show tables;
+-----------------+
| Tables_in_adams |
+-----------------+
| family          |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from family;
+-------------+--------+------------+
| Name        | Room   | Phone      |
+-------------+--------+------------+
| Gomez Adams | master | 1-555-1212 |
+-------------+--------+------------+
1 row in set (0.00 sec)


Links:


Books:

book cover image "MySQL, Second edition"
by Paul DuBois
ISBN # 0735712123, New Riders Publishing

Amazon.com
book cover image "Managing and Using MySQL"
by George Reese, Randy Jay Yarger, Tim King
ISBN # 0596002114, O'Reilly

Amazon.com
book cover image "MySQL Cookbook"
by Paul DuBois
ISBN # 0596001452, O'Reilly

Amazon.com
book cover image "High Performance MySQL"
by Jeremy D. Zawodny, Derek J. Balling
ISBN # 0596003064, O'Reilly

Amazon.com
book cover image "The Definitive Guide to MySQL, Second Edition"
by Michael Kofler
ISBN # 1590591445, APress

Amazon.com
book cover image "MySQL Certification Study Guide"
by Paul Dubois, Stefan Hinz, Carsten Pedersen
ISBN # 0672326329, Sams

Amazon.com

   

    Bookmark and Share


Advertisements





Copyright © 2011 by Greg Ippolito