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
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
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;
}
- 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: ./testMySqlAPIMySQL 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
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: ./genDatabaseCheck 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)
- 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
|
|
