MySQL Programming: C API

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 and table from a C/C++ program
# Generate a new database and table and insert a record from a C/C++ program

Insert and select from an existing database:

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

01CREATE DATABASE bedrock;
02USE bedrock;
03CREATE TABLE employee (IDpk integer  NOT NULL auto_increment,
04                       Employee_Name char(20),
05                       Dept char(20),
06                       JobTitle char(20),
07                       PRIMARY KEY (IDpk));
08INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Fred Flinstone','Worker','Rock Digger');
09INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Wilma Flinstone','Finance','Analyst');
10INSERT into employee (Employee_Name,Dept,JobTitle) VALUES ('Barney Rubble','Sales','Neighbor');

Example C/C++ program:

File: testMySqlAPI.cpp

001#include <stdio.h>
002#include <mysql.h>
003#include <string>
004 
005class FFError
006{
007public:
008    std::string    Label;
009 
010    FFError( ) { Label = (char *)"Generic Error"; }
011    FFError( char *message ) { Label = message; }
012    ~FFError() { }
013    inline const char*   GetMessage  ( void )   { return Label.c_str(); }
014};
015 
016using namespace std;
017 
018main()
019{
020    // --------------------------------------------------------------------
021    // Connect to the database
022     
023    MYSQL      *MySQLConRet;
024    MYSQL      *MySQLConnection = NULL;
025 
026    string hostName = "localhost";
027    string userId   = "user1";
028    string password = "supersecret";
029    string DB       = "bedrock";
030 
031    MySQLConnection = mysql_init( NULL );
032 
033    try
034    {
035        MySQLConRet = mysql_real_connect( MySQLConnection,
036                                          hostName.c_str(), 
037                                          userId.c_str(), 
038                                          password.c_str(), 
039                                          DB.c_str(), 
040                                          0, 
041                                          NULL,
042                                          0 );
043 
044        if ( MySQLConRet == NULL )
045            throw FFError( (char*) mysql_error(MySQLConnection) );
046    
047        printf("MySQL Connection Info: %s \n", mysql_get_host_info(MySQLConnection));
048        printf("MySQL Client Info: %s \n", mysql_get_client_info());
049        printf("MySQL Server Info: %s \n", mysql_get_server_info(MySQLConnection));
050 
051    }
052    catch ( FFError e )
053    {
054        printf("%s\n",e.Label.c_str());
055        return 1;
056    }
057       
058    int             mysqlStatus = 0;
059    MYSQL_RES      *mysqlResult = NULL;
060 
061    // --------------------------------------------------------------------
062    // This block of code would be performed if this insert were in a loop 
063    // with changing data. Of course it is not necessary in this example.
064 
065    if(mysqlResult)
066    {
067        mysql_free_result(mysqlResult);
068        mysqlResult = NULL;
069    }
070 
071    // --------------------------------------------------------------------
072    // Perform a SQL INSERT
073 
074    try
075    {
076        string sqlInsStatement = "INSERT INTO employee (Employee_Name,Dept,JobTitle) VALUES ('Betty Rubble','IT','Neighbor')";
077        mysqlStatus = mysql_query( MySQLConnection, sqlInsStatement.c_str() );
078        if (mysqlStatus)
079        {
080            throw FFError( (char*)mysql_error(MySQLConnection) );
081        }
082    }
083    catch ( FFError e )
084    {
085        printf("%s\n",e.Label.c_str());
086        mysql_close(MySQLConnection);
087        return 1;
088    }
089 
090    if(mysqlResult)
091    {
092        mysql_free_result(mysqlResult);
093        mysqlResult = NULL;
094    }
095     
096    // --------------------------------------------------------------------
097    // Perform a SQL SELECT and retrieve data
098 
099    MYSQL_ROW       mysqlRow;
100    MYSQL_FIELD    *mysqlFields;
101    my_ulonglong    numRows;
102    unsigned int    numFields;
103     
104    try
105    {
106        string sqlSelStatement = "SELECT * FROM employee";
107        mysqlStatus = mysql_query( MySQLConnection, sqlSelStatement.c_str() );
108 
109        if (mysqlStatus)
110            throw FFError( (char*)mysql_error(MySQLConnection) );
111        else
112            mysqlResult = mysql_store_result(MySQLConnection); // Get the Result Set
113 
114        if (mysqlResult)  // there are rows
115        {
116            // # of rows in the result set
117            numRows = mysql_num_rows(mysqlResult);
118 
119            // # of Columns (mFields) in the latest results set
120            numFields = mysql_field_count(MySQLConnection);
121 
122            // Returns the number of columns in a result set specified
123            numFields = mysql_num_fields(mysqlResult);
124 
125            printf("Number of rows=%u  Number of fields=%u \n",numRows,numFields);
126        }
127        else
128        {
129           printf("Result set is empty");
130        }
131     
132        // Print column headers
133 
134        mysqlFields = mysql_fetch_fields(mysqlResult);
135 
136        for(int jj=0; jj < numFields; jj++)
137        {
138            printf("%s\t",mysqlFields[jj].name);
139        }
140        printf("\n");
141      
142        // print query results
143 
144        while(mysqlRow = mysql_fetch_row(mysqlResult)) // row pointer in the result set
145        {
146            for(int ii=0; ii < numFields; ii++)
147            {
148                printf("%s\t", mysqlRow[ii] ? mysqlRow[ii] : "NULL");  // Not NULL then print
149            }
150            printf("\n");
151        }
152 
153        if(mysqlResult)
154        {
155            mysql_free_result(mysqlResult);
156            mysqlResult = NULL;
157        }
158    }
159    catch ( FFError e )
160    {
161        printf("%s\n",e.Label.c_str());
162        mysql_close(MySQLConnection);
163        return 1;
164    }
165 
166    // --------------------------------------------------------------------
167    // Close datbase connection
168 
169    mysql_close(MySQLConnection);
170 
171    return 0;
172}

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

01#include <stdio.h>
02#include <mysql.h>
03#include <string>
04 
05class FFError
06{
07public:
08    std::string    Label;
09 
10    FFError( ) { Label = (char *)"Generic Error"; }
11    FFError( char *message ) { Label = message; }
12    ~FFError() { }
13    inline const char*   GetMessage  ( void )   { return Label.c_str(); }
14};
15 
16using namespace std;
17 
18int main()
19{
20    // --------------------------------------------------------------------
21    // Connect to the database
22     
23    MYSQL      *MySQLConRet;
24    MYSQL      *MySQLConnection = NULL;
25 
26    string hostName = "localhost";
27    string userId   = "user1";
28    string password = "supersecret";
29 
30    MySQLConnection = mysql_init( NULL );
31 
32    try
33    {
34        MySQLConRet = mysql_real_connect( MySQLConnection,
35                                          hostName.c_str(), 
36                                          userId.c_str(), 
37                                          password.c_str(), 
38                                          NULL,  // No database specified
39                                          0, 
40                                          NULL,
41                                          0 );
42 
43        if ( MySQLConRet == NULL )
44            throw FFError( (char*) mysql_error(MySQLConnection) );
45    
46        printf("MySQL Connection Info: %s \n", mysql_get_host_info(MySQLConnection));
47        printf("MySQL Client Info: %s \n", mysql_get_client_info());
48        printf("MySQL Server Info: %s \n", mysql_get_server_info(MySQLConnection));
49 
50    }
51    catch ( FFError e )
52    {
53        printf("%s\n",e.Label.c_str());
54        return 1;
55    }
56       
57 
58    // --------------------------------------------------------------------
59    //  Create database
60     
61    if (mysql_query(MySQLConnection, "CREATE DATABASE adams")) 
62    {
63        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
64        return(1);
65    }
66 
67    // --------------------------------------------------------------------
68    //  Now that database has been created set default database
69 
70    if (mysql_query(MySQLConnection, "USE adams") )
71    {
72        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
73        return(1);
74    }
75 
76    // --------------------------------------------------------------------
77    //  Create table and records
78 
79    if (mysql_query(MySQLConnection, "CREATE TABLE family (Name char(20),Room char(8),Phone char(24))") )
80    {
81        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
82        return(1);
83    }
84 
85    if (mysql_query(MySQLConnection, "INSERT INTO family VALUES ('Gomez Adams', 'master', '1-555-1212')") )
86    {
87        printf("Error %u: %s\n", mysql_errno(MySQLConnection), mysql_error(MySQLConnection));
88        return(1);
89    }
90 
91    // --------------------------------------------------------------------
92    // Close datbase connection
93 
94    mysql_close(MySQLConnection);
95 
96    return 0;
97}

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:

MySQL.com:
- C API documentation (MySQL 5.7)

Books:

	"MySQL, Second edition" by Paul DuBois ISBN # 0735712123, New Riders Publishing
	"Managing and Using MySQL" by George Reese, Randy Jay Yarger, Tim King ISBN # 0596002114, O'Reilly
	"MySQL Cookbook" by Paul DuBois ISBN # 0596001452, O'Reilly
	"High Performance MySQL" by Jeremy D. Zawodny, Derek J. Balling ISBN # 0596003064, O'Reilly
	"The Definitive Guide to MySQL, Second Edition" by Michael Kofler ISBN # 1590591445, APress
	"MySQL Certification Study Guide" by Paul Dubois, Stefan Hinz, Carsten Pedersen ISBN # 0672326329, Sams