Database Programming with C/C++

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Creating a database application in C/C++ is a daunting task, especially for a novice programmer. Although the actually code is quite simple, it is the configuration issues such as importing right library, drivers to use, how to access them, and so forth, that make it an uphill battle. If you think you need to sharpen those C/C#/C++ skills, visit the TechRepublic Academy today. Modern high-level languages are pretty straightforward in these matters. They are quite simple and convenient with an all-in-one-place library with very few configuration troubles. One of the interesting aspects of using a high-level language is that you can almost start coding with even a little understanding of the underlying principles. C/C++, on the other hand, is a different breed. It is not easy to tread even a step superficially. This makes it more intriguing, challenging, that which tests your skills with every code you write. But, once you overcome the preliminary hurdles, there is nothing like C/C++. And the database programming? It’s quite fun, actually. Let’s get a first ride with the database code in C/C++ in this article.

How Do You Access Databases In C/C++?

There are many practical ways to access a database in C/C++. Except for ODBC; its APIs are not standard. Most database vendors provide a native client library to access a database. Client libraries are vendor specific; that means that the API provided by one is different from another although the underlying principles are the same. MySQL, for example, has its own client library and the API supplied by is are quite different from the API supplied by the client library of PostgreSQL. If you stick to one vendor-specific database, say MySQL, the driver options for database programming with C/C++ are:

MySQL Client library: It is a native C API library distributed with MySQL and implemented in the libmysqlclient library. If you have MySQL Server installed, most probably the client API library is already installed. (Otherwise, it may be downloaded with the command, something like sudo apt-get install libmysqlclient libmysqlclient-dev in Ubuntu/Mint.)
MySQL C/C++ Connector: It is an innovation in its prime to make database connectivity simple and convenient. The API is based partially on the JDBC4.0 API standard and perhaps will be the standard way to access a database as it matures. There is a separate connector for C and as well as for C++. While using the C++ connector, no C functions calls are required.
ODBC (Open Database Connectivity): Developed by Microsoft in the 90’s, it provides a vendor-neutral API to a access database system. Most database vendors provide at least an ODBC driver apart from its native support. ODBC is basically a driver model that contains logic to convert a standard set of commands into calls understood by the underlying system. It stands between the application and the database system to reciprocate the exchange of calls/responses among them. In recent years, due to the advent of thin client computing, the requirement of virtualization offered by ODBC has been reduced. Most Web development platforms work in layers with HTML as the intermediary between the user and the application. And, in most cases, the underlying database layers have a direct link with the target database. This made native library more preferable over ODBC. Due to these reasons, the development of ODBC has slacked down in the recent years.

How Do You Connect MySQL and C/C++?

Let’s try out a database application using a very basic, low-level MySQL client C API library. The database access routine more or less involves the following steps:

1. Initialize Connection Handle Structure

MYSQL *mysql_init(MYSQL *);

2. Make the Connection

MYSQL mysql_real_connect( MYSQL connection, const char *host, const char *username, const char *password, const char *database_name, unsigned int port, const char *unix_socket_name, unsigned int flags );

3. Execute SQL Statements

int mysql_query(MYSQL *connection, const char *query);

4. Functions to Use for Data Retrieval

MYSQL_RES *mysql_use_result(MYSQL *connection); MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

5. Error Handling

unsigned int mysql_errno(MYSQL *connection); char *mysql_error(MYSQL *connection);

6. Close the Connection

void mysql_close(MYSQL *connection);

There are many other functions, but these are the functions we shall use when creating the application down the line. Consult the MySQl C API manuals for more details on these and other APIs. To sum up, you’ll basically need at least the following software.

Application Name	Source	Details
Database	MySQL 5	MySQL Database Server will be our back-end database
Database API	MySQL client library	Provides native driver and library: libmysqlclient as an interface between the application and the database. Make sure that the client API library is installed. Once installed, the header files and the library are generally found in /usr/include/mysql and /usr/lib/mysql, respectively; otherwise, make sure of the correct path in your system.
Compiler	g++	GNU C++ compiler
IDE	CodeLite 9.1.8	It is not absolute necessary to use an IDE, but it is convenient to use one. There are many IDEs available in Linux for C/C++ programming. CodeLite seemed (to me) modern, simple, and intuitive. Also, it creates the make file automatically. You may choose any other, even simple gedit, vi, or any other simple text editor such as nano is also fine. In such a case. consult the appropriate manual for the configuration and settings and how to create the make file if you resort to do everything manually.

An Example: Transaction Processing System

This is a very simple and rudimentary implementation of a transaction processing system. The code could have been written in a C style (without classes) because the MySQL API functions calls are in C format. But, to get a feel of object-oriented database programming with C++, classes are used. In many cases, we had to force its parameters to go with the essence of C++ by casting and converting occasionally. The application processing can imagined as shown in Figure 1.

DB1

Figure 1: Transaction Processing System

Configuration and Settings in the IDE: CodeLite

Make sure the following configurations are set in the Global Settings of Project Settings.

Additional Include Paths = .;/usr/include/mysql

Library Path = .;/usr/lib/mysql

Figure 2: Project settings

#ifndef BANKACCOUNT_H #define BANKACCOUNT_H #include using std::string; class BankAccount < public: static const int MAX_SIZE = 30; BankAccount(int = 0, string = "", string = "", double = 0.0); ~BankAccount(); void setAccountNumber(int); void setLastName(string); void setFirstName(string); void setBalance(double); int getAccountNumber() const; string getFirstName() const; string getLastName() const; double getBalance() const; private: int accountNumber; char firstName[MAX_SIZE]; char lastName[MAX_SIZE]; double balance; >; #endif // BANKACCOUNT_H

Listing 1: BankAccount.h

#include "BankAccount.h" #include #include using std::string; BankAccount::BankAccount(int accno, string fname, string lname, double bal) < setAccountNumber(accno); setFirstName(fname); setLastName(lname); setBalance(bal); >void BankAccount::setAccountNumber(int accno) < accountNumber = accno; >void BankAccount::setLastName(string lname) < const char* ln = lname.data(); int len = lname.size(); len = (len < MAX_SIZE ? len : MAX_SIZE - 1); strncpy(lastName, ln, len); lastName[len] = '\0'; >void BankAccount::setFirstName(string fname) < const char* fn = fname.data(); int len = fname.size(); len = (len < MAX_SIZE ? len : MAX_SIZE - 1); strncpy(firstName, fn, len); firstName[len] = '\0'; >void BankAccount::setBalance(double bal) < balance = bal; >int BankAccount::getAccountNumber() const < return accountNumber; >string BankAccount::getFirstName() const < return firstName; >string BankAccount::getLastName() const < return lastName; >double BankAccount::getBalance() const < return balance; >BankAccount::~BankAccount()

Listing 2: BankAccount.cpp

#ifndef BANKTRANSACTION_H #define BANKTRANSACTION_H #include #include class BankAccount; using namespace std; class BankTransaction < public: BankTransaction(const string = "localhost", const string = "", const string = "", const string = ""); ~BankTransaction(); void createAccount(BankAccount*); void closeAccount(int); void deposit(int, double); void withdraw(int, double); BankAccount* getAccount(int); void printAllAccounts(); void message(string); private: MYSQL* db_conn; >; #endif // BANKTRANSACTION_H

Listing 3: BankTransaction.h

#include #include #include #include #include "BankTransaction.h" #include "BankAccount.h" BankTransaction::BankTransaction(const string HOST, const string USER, const string PASSWORD, const string DATABASE) < db_conn = mysql_init(NULL); if(!db_conn) message("MySQL initialization failed! "); db_conn = mysql_real_connect(db_conn, HOST.c_str(), USER.c_str(), PASSWORD.c_str(), DATABASE.c_str(), 0, NULL, 0); if(!db_conn) message("Connection Error! "); >BankTransaction::~BankTransaction() < mysql_close(db_conn); >BankAccount* BankTransaction::getAccount(int acno) < BankAccount* b = NULL; MYSQL_RES* rset; MYSQL_ROW row; stringstream sql; sql setAccountNumber(atoi(row[0])); b->setFirstName(row[1]); b->setLastName(row[2]); b->setBalance(atof(row[3])); > mysql_free_result(rset); return b; > void BankTransaction::withdraw(int acno, double amount) < BankAccount* b = getAccount(acno); if(b != NULL) < if(b->getBalance() < amount) message("Cannot withdraw. Try lower amount."); else < b->setBalance(b->getBalance() - amount); stringstream sql; sql getBalance() else < message("Cash deposit unsuccessful! Update failed"); >> > > void BankTransaction::deposit(int acno, double amount) < stringstream sql; sql else < message("Cash deposit unsuccessful! Update failed"); >> void BankTransaction::createAccount(BankAccount* ba) < stringstream ss; ss getAccountNumber() getFirstName() + "','" getLastName() << "'," getBalance() void BankTransaction::closeAccount(int acno) < stringstream ss; ss void BankTransaction::message(string msg) < cout void BankTransaction::printAllAccounts() < MYSQL_RES* rset; MYSQL_ROW rows; string sql = "SELECT * FROM bank_account"; if(mysql_query(db_conn, sql.c_str())) < message("Error printing all accounts! "); return; >rset = mysql_use_result(db_conn); cout cout mysql_free_result(rset); >

Listing 4: BankTransaction.cpp

#include #include #include #include #include #include "BankAccount.h" #include "BankTransaction.h" using namespace std; enum Options < PRINT = 1, NEW, WITHDRAW, DEPOSIT, CLOSE, END >; int mainMenu() < cout > ch; return ch; > int main(int argc, char** argv) < BankTransaction* bt = new BankTransaction("localhost", "root", "passwd123", "mybank"); int choice; int acno; string fname, lname; double bal; while(1) < choice = mainMenu(); if(choice == END) break; switch(choice) < case PRINT: bt->printAllAccounts(); break; case NEW: cout > acno; cin >> fname; cin >> lname; cin >> bal; if(acno < 1) < cout bt->createAccount(new BankAccount(acno, fname, lname, bal)); break; case WITHDRAW: cout > acno; cin >> bal; if(bal < 0) < cout bt->withdraw(acno, bal); break; case DEPOSIT: cout > acno; cin >> bal; if(bal < 0) < cout bt->deposit(acno, bal); break; case CLOSE: cout > acno; bt->closeAccount(acno); break; default: cerr > return 0; >

Listing 5: main.cpp

Build and Execute Project

Figure 3: The completed project

Conclusion

Many of the checks and validation are unimplemented to keep things as simple as possible. Only the absolute minimum number of functions are used from the libmysqlclient API library. The minimal CRUD operations are implemented so that it can be used as the basis for further improvement.