Get
Ready to Unleash the
Power of UCanCode
.NET

Database Programming with C/C++, with mySQL

Creating a database
application in C/C++
is a daunting tucancode.net, 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. 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.

An Overview

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 thelibmysqlclientlibrary.
If you have MySQL Server installed, most
probably the client API library is already
installed. (Otherwise, it may be downloaded
with the command, something likesudo
apt-get install libmysqlclient<version>
libmysqlclient<version>-devin
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.

C/C++ and MySQL

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:libmysqlclientas
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/mysqland/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 themakefile
automatically. You may choose any
other, even simplegedit,vi,
or any other simple text editor such
asnanois
also fine. In such a case. consult
the appropriate manual for the
configuration and settings and how
to create themakefile
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.

Figure 1:Transaction
Processing System

Configuration and Settings the in IDE: CodeLite

Make sure the following configurations are set
in theGlobal
SettingsofProject
Settings.

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

Options=
-lmysqlclient

Library Path=
.;/usr/lib/mysql

Figure 2:Project
settings

#ifndef
BANKACCOUNT_H

#define
BANKACCOUNT_H

#include
<string>

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
<string>

#include
<cstring>

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
<mysql.h>

#include
<string>

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
<cstdlib>

#include
<sstream>

#include
<iostream>

#include
<iomanip>

#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
<<
"SELECT * FROM bank_account WHERE acc_no="

<<
acno;

if(!mysql_query(db_conn,
sql.str().c_str()))
{

b
=
new
BankAccount();

rset
=
mysql_use_result(db_conn);

row
=
mysql_fetch_row(rset);

b->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
<<
"UPDATE bank_account SET balance="

<<
b->getBalance()

<<
" WHERE acc_no="
<<
acno;

if(!mysql_query(db_conn,
sql.str().c_str()))
{

message("Cash
withdraw successful.

Balance updated.");

}
else
{

message("Cash
deposit unsuccessful!

Update failed");

}

}

}

}

void
BankTransaction::deposit(int
acno,
double
amount)

{

stringstream sql;

sql
<<
"UPDATE bank_account SET balance=balance+"
<<
amount

<<
" WHERE acc_no="
<<
acno;

if(!mysql_query(db_conn,
sql.str().c_str()))
{

message("Cash
deposit successful. Balance updated.");

}
else
{

message("Cash
deposit unsuccessful! Update failed");

}

}

void
BankTransaction::createAccount(BankAccount*
ba)

{

stringstream ss;

ss
<<
"INSERT INTO bank_account(acc_no, fname,
lname,

balance)"

<<
"values ("
<<
ba->getAccountNumber()
<<
", '"

<<
ba->getFirstName()
+
"','"

<<
ba->getLastName()
<<
"',"

<<
ba->getBalance()
<<
")";

if(mysql_query(db_conn,
ss.str().c_str()))

message("Failed
to create account! ");

else

message("Account
creation successful.");

}

void
BankTransaction::closeAccount(int
acno)

{

stringstream ss;

ss
<<
"DELETE FROM bank_account WHERE acc_no="

<<
acno;

if(mysql_query(db_conn,
ss.str().c_str()))

message("Failed
to close account! ");

else

message("Account
close successful.");

}

void
BankTransaction::message(string
msg)

{

cout
<<
msg
<<
endl;

}

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
<<
left
<<
setw(10)
<<
setfill('-')
<<
left
<<
'+'

<<
setw(21)
<<
setfill('-')
<<
left
<<
'+'

<<
setw(21)

<<
setfill('-')
<<
left
<<
'+'
<<
setw(21)

<<
setfill('-')

<<
'+'
<<
'+'
<<
endl;

cout
<<
setfill('
')
<<
'|'
<<
left
<<
setw(9)

<<
"Account"

<<
setfill('
')
<<
'|'
<<
setw(20)
<<
"First Name"

<<
setfill('
')
<<
'|'
<<
setw(20)
<<
"Last Name"

<<
setfill('
')
<<
'|'
<<
right
<<
setw(20)

<<
"Balance"
<<
'|'
<<
endl;

cout
<<
left
<<
setw(10)
<<
setfill('-')
<<
left

<<
'+'
<<
setw(21)
<<
setfill('-')
<<
left
<<
'+'

<<
setw(21)

<<
setfill('-')
<<
left
<<
'+'
<<
setw(21)
<<
setfill('-')

<<
'+'
<<
'+'
<<
endl;

if(rset)
{

while((rows
=
mysql_fetch_row(rset)))
{

cout
<<
setfill('
')
<<
'|'
<<
left
<<
setw(9)
<<
rows[0]

<<
setfill('
')
<<
'|'
<<
setw(20)
<<
rows[1]

<<
setfill('
')
<<
'|'
<<
setw(20)
<<
rows[2]

<<
setfill('
')
<<
'|'
<<
right
<<
setw(20)

<<
rows[3]
<<
'|'
<<
endl;

}

cout
<<
left
<<
setw(10)
<<
setfill('-')
<<
left

<<
'+'
<<
setw(21)
<<
setfill('-')
<<
left
<<
'+'

<<
setw(21)

<<
setfill('-')
<<
left
<<
'+'
<<
setw(21)

<<
setfill('-')

<<
'+'
<<
'+'
<<
endl;

}

mysql_free_result(rset);

}

Listing 4:BankTransaction.cpp

#include
<iostream>

#include
<sstream>

#include
<iomanip>

#include
<cstdlib>

#include
<mysql.h>

#include
"BankAccount.h"

#include
"BankTransaction.h"

using
namespace
std;

enum
Options
{
PRINT
=
1,
NEW,
WITHDRAW,
DEPOSIT,

CLOSE,
END
};

int
mainMenu()

{

cout
<<
"\nMenu Options"
<<
endl

<<
"1 - Print All Account"

<<
endl
<<
"2 - Open New Account"
<<
endl

<<
"3 - Withdraw"
<<
endl
<<
"4 - Deposit"

<<
endl
<<
"5 - Close Account"
<<
endl

<<
"6 - End Transaction"
<<
endl;

int
ch;

cin
>>
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
<<
"\nEnter account no, first name,

last name, balance: "

<<
endl
<<
"? ";

cin
>>
acno;

cin
>>
fname;

cin
>>
lname;

cin
>>
bal;

if(acno
<
1)
{

cout
<<
"Invalid account number."
<<
endl;

break;

}

bt->createAccount(new
BankAccount(acno,
fname,
lname,

bal));

break;

case
WITHDRAW:

cout
<<
"\nEnter account no, amount to withdraw "

<<
endl
<<
"? ";

cin
>>
acno;

cin
>>
bal;

if(bal
<
0)
{

cout
<<
"Invalid amount."
<<
endl;

break;

}

bt->withdraw(acno,
bal);

break;

case
DEPOSIT:

cout
<<
"\nEnter account no, amount to deposit "

<<
endl
<<
"? ";

cin
>>
acno;

cin
>>
bal;

if(bal
<
0)
{

cout
<<
"Invalid amount."
<<
endl;

break;

}

bt->deposit(acno,
bal);

break;

case
CLOSE:

cout
<<
"\nEnter account no to close account "

<<
endl
<<
"? ";

cin
>>
acno;

bt->closeAccount(acno);

break;

default:

cerr
<<
"Invalid choice!"
<<
endl;

break;

}

}

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 thelibmysqlclientAPI
library. The minimal CRUD operations are
implemented so that it can be used as the basis
for further improvement.