C++ database access
John Schwartzman shows how to write a C++ program that interrogates and commands a MYSQL database to do his bidding.
John Schwartzman explains how to code C++ programs that can interrogate and command those MYSQL databases.
Have you installed MYSQL on your computer as part of a LAMP (Linux, Apache, MYSQL and PHP) server, and have never been quite sure what to do with it? This tutorial shows you how to use MYSQL with the C++ programming language to query and alter a relational database.
You may think that programming in C++ is rather a gruesome undertaking, especially when combined with the rigours of SQL programming. It really isn’t that bad, though, and by using C++ we can abstract away lots of the gory details and present a clean, well structured programming interface that’s fairly easy to understand.
SQL Stands for Structured Query Language and it’s used to interrogate and command Relational Database Management Systems (RDBMS) like MYSQL, Mariadb, and so on. Both MYSQL and Mariadb will work with the MYSQL C++/connnector, which is available as a separate download from Mysql/oracle.
Mariadb is available under the GNU General Public Licence (GPL) and MYSQL is available under the GPL and also under a proprietary licence. When we write MYSQL in this tutorial, we are referring to either MYSQL or Mariadb – your system will behave in the same way no matter which one you have installed.
Class division
The following code illustrates the way a typical C++ program would access a database using MYSQL connector. The namespace sql is where the database components reside. Try / catch is the way C++ handles code that might produce errors. This code comes with the MYSQL C++/connector, but we think it has way too many variables to keep track of and is too confusing! main(int argc, char* argv[])
{ try
{ sql::driver* pdriver = get_driver_instance(); sql::connection* pconn pdriver->connect(“tcp://127. 0.0.1:3306”,“worlduser”, “worlduser123”); sql::statement* pstmt = pconn->createstatement(); pconn->setschema(“world”);
sql::resultset* PRS = pstmt->executequery(“select
Name, Countrycode, District, Population FROM city”); ... delete PRS; delete pstmt; delete pconn; return EXIT_SUCCESS; } catch (const sql::sqlexception*&e) {
... return EXIT_FAILURE; }
What we propose instead is a programming idiom known as Resource Acquisition Is Initialization (RAII). In RAII, we define a class named Database to hold/hide all the database components. The act of bringing the class into existence (instantiation) is what causes all of the preliminary actions shown above to be performed. Instantiating the Database class connects the user to the database. Using the Database class (see Figure 1
above), our main program would now look like this: #include “Database.h”
int main(int argc, char* argv[])
{ try
{ std::unique_ptr
Database(“worlduser”, “worlduser123”)); sql::resultset *PRS = pdb->executequery(“select Name, Countrycode,
District, Population FROM city”);
... return EXIT_SUCCESS;
} catch (const sql::sqlexception(&e) {
... return EXIT_FAILURE;
}
}
We know this looks like more typing instead of less, but once the Database class is defined, it becomes like a part of the C++ language. We’ve abstracted away all of the inner workings of Connector/c++ and placed them in the Database class, and our program just looks like the 18-line main function shown above. Our main program uses helper classes called Place and
Language to manage and display SQL queries of
world ’s tables.
Place and Language use a utility class called
Strstrmbuf , which was written to, among other things, pad data into constant-width columns and to commaseparate numbers so that they’re more easily human readable (say, 1,538,123,000 instead of 1538123000). The Strstrmbuf class was inspired by Java’s Stringbuilder class. We have found Stringbuilder to be extremely useful and wanted the same functionality and more in C++.
Once the Place , Language and Strstrmbuf classes are defined, they become almost like a part of the C++ language and you can just use them when and where you need them without worrying too much about the details of the classes. You could even build them into a shared library.
Unzip db.zip into your home directory or wherever you place your development files. Take a look in the db/src directory for Database.h, Database.cpp, Place.h, Place.cpp, Language.h, Language.cpp, Strstrmbuf.h, Strstrmbuf.cpp, Terminal.h, Terminal.cpp and db.cpp. These files are included on the DVD and are also available at https://github.com/ jschwartzman/db. The db.cpp program contains our main function and it’s here that the action is orchestrated. As you proceed through main() , you’ll perform various selects, inserts, deletes and updates on world ’s tables. The main function also provides a running commentary on the various SQL statements you’ll execute.
Let’s look closely at Database.h (Figure 1). We invoke the Database constructor method ( ctor ) when we invoke new Database(“user”, “password”); in our main function (line 32 of db.cpp). Every class has a ctor and the ctor has the same name as the class. It also has a destructor ( dtor ) function with the class name prefixed by a tilde ~ character. When Database ’s constructor is called using the C++ new operator, we pass the strings suser , spassword , sschema and saddress to the ctor .
You must, at least, provide a username and password and you may pass the other two arguments as well. If you choose not to provide sschema and
saddress , Database will provide its own default values. The Database constructor calls a few inlined methods: _pdriver = get_driver_instance(); _pconnection = _pdriver->connect(_saddress, _ suser, _spassword); _pconnection->setschema(_sschema); _pstatement = _pconnection->createstatement();
These methods are inlined because they live in the interface file Database.h rather than the implementation file Database.cpp. Our class member variables are distinguished from other variables by prefixing them with an underscore character.
The Database ctor logs you into the RDBMS and sets itself up so that it’s ready for you to ask your first question about the world database. This database is just an argument that’s passed into the Database
constructor. You could substitute another database or even a database that you’ve created yourself.
Database ’s member variables are kept in an area of memory called the heap, along with functions. The variables and functions stay in memory as long as the class exists. How long is that? The Database instance exists until PDB , the pointer variable that references it, goes out of scope. PDB is declared and defined inside the try block of db.cpp, so the lifetime of the Database instance is the lifetime of the try block. When we reach the closing brace of the try block the Database instance pointed to by PDB goes out of scope. We could have written:
Database* PDB = new Database(“worlduser”,
“worlduser123”);
// use the Database instance delete PDB;
Instead, we used a newer feature of C++ and wrote: std::unique_ptr
Database(“worlduser”, “worlduser123”));
Now when PDB goes out of scope, the destructor of the Database instance is automatically called and we don’t need to delete PDB . The notation std::unique_ptr means that unique_ptr is a member of the std namespace. The namespace is a feature of C++ that helps to avoid collisions between different objects in different modules with the same name.
In order to build this code, you’ll need C++, MYSQL or Mariadb, MYSQL Connnector/c++ 8.0, Boost and the sample database schema world.sql. See the box on page 93 for instructions. Once you have the world database schema installed, you’ll need to do a little administrative work. Start MYSQL with the command
$ mysql -u root -p
MYSQL will then prompt you for the root password. This is MYSQL’S root password and not necessarily the Linux root password. Once you’ve successfully logged into MYSQL or Mariadb as the root user, issue the following commands:
Mariadb [(none)]> CREATE DATABASE world; Mariadb [(none)]> USE world;
Mariadb [world]> SOURCE path_to_world/world.sql;
path_to_world refers to the location where you unzipped world.sql. For example, the last statement might read:
Mariadb [world]> SOURCE ~/Downloads/world.sql;
We’ve capitalised the SQL commands, but SQL doesn’t care about capitalisation and it doesn’t care about white space in your commands.
I am root
As the root user of MYSQL, you need to create a new, non-root user of the world database and assign the nonroot user appropriate privileges. Type the following SQL statement and substitute your own choice of password for worlduser123 :
Mariadb [world]> GRANT SELECT, INSERT, DELETE,
UPDATE ON world.*
-> To‘worlduser’@’localhost’ IDENTIFIED BY ‘worlduser123’;
You don’t have to type everything on one line. You can hit the Return key at any point and MYSQL will show the continuation symbol -> on a new line. Be sure to enter a semicolon as the last character of your statement. What this statement means is that we want to grant all of the CRUD (Create, Read, Update and Delete) privileges for all of world ’s tables ( world.* ) to the new user worlduser@localhost .
Once you’ve created the new user worlduser and assigned its password and its privileges, quit MYSQL/ Mariadb by typing quit at the prompt:
Mariadb [world]> quit;
Now log into MYSQL/MARIADB as worlduser and tell it that you want to use the world database: mysql -D world -u worlduser -p
and enter the newly assigned password for worlduser
at the prompt. For a scenic tour of Great Britain type the SQL statement:
Mariadb [world]> SELECT name, district FROM city
WHERE countrycode=’gbr’;
If you see a list of cities and countries then you’re ready to go. Let’s look at the functions of the Database class (Database.h and Database.cpp). Aside from the ctor and dtor, we have member functions to perform various CRUD tasks.
Inside Database you’ll find functions like executequery() . This will execute a SQL SELECT statement against the world database. executequery() returns a pointer to a Resultset which is a list of ROWS from the database.you can call the static helper function Place::display(prs) to display the results.
The Place helper class is designed to display four COLUMN result ROWS from the database, where the first three COLUMNS are strings and the last COLUMN
is a long. The Language helper class is designed to display two COLUMN result ROWS from the database, where the first COLUMN is a string and the second COLUMN is a long double. It is very similar to Place . The simple Place and Language classes can serve as models for your own helper classes with any size and type of ROWS that you need.
Database provides the Boolean execute() method to execute SQL CRUD statements like INSERT, UPDATE and DELETE that do not return a list of Resultset objects, but instead directly modify the database. Finally, Database provides queryhasrecords() and executecountquery() , both of which perform a SELECT query that tells you how many records are returned by your select statement. You can perform decision making in your application based on whether or not your SELECT query returns any records, or whether it returns a specific number of records.
A load of CRUD
INSERT is the Create in CRUD, while SELECT is the Read, UPDATE is the Update and DELETE is the Delete.
The db class (db.cpp) is a tutorial which will walk you through a variety of CRUD operations using the Database class to access the world database. It uses various helper classes ( Place , Language , Terminal and Strstrmbuf ) to display the results. db.cpp is set up as a set of screens. Each screen preforms a different operation or set of operations and describes what’s happening in each SQL statement. Consider db.cpp (and the Linux executable db) as a gentle tutorial introduction to SQL.
The functions that perform SELECT operations return long integers to tell you how many ROWS were returned by your queries. The execute() command returns a Boolean, but all of Database ’s methods and functions will throw an exception if they fail. The exception number and message will tell you why something might have failed. The usual reason is that the SQL statement that you tried to execute was not formed correctly. You might want to open up the MYSQL console to experiment with various CRUD statements if they’re giving you trouble, using the command mysql -u worlduser -p .
C++ is an object-orientated language which was
developed by Bjarne Stroustrup at Bell Labs. It first appeared in 1985. C++ started with the procedural language C and extended it to use the concept of classes. In C++, a class is an abstraction of some realworld problem. A class should basically do one thing well, and it shouldn’t have to know about the inner workings of other classes to do that one thing.
Every class that we add essentially expands the C++ language. One of the goals of C++ is that operations on a class should be as efficient as operations on built-in objects. The real-world problem that we’re addressing with the Database class is connection to a database server. This connection is established by Database ’s constructor method which is invoked when a Database object is instantiated using the C++ new method.
Once that connection is made, you can perform various SQL operations on that database from a menu which is published in Database ’s header file, Database.h. The number of items is relatively small, but each can use many varieties of SQL statements.
Basically, Database is an extension to C++ that
encapsulates connection to, and operations on, a database. When the Database object is no longer needed, it is destroyed, and Database ’s destructor method causes the release of all of the Database
object’s resources (memory, sockets, pipes, files, threads, tasks and so on) back to Linux so that they are available for other programs needing to be run.
The C++ new operator is sort of like the C function malloc , in that memory is allocated for the object – but the comparison stops there, as we also have a special active constructor method that is invoked by the C++ new operator to do all the housekeeping that is required for a Database object to establish a connection to an RDBMS database.
Likewise, the C++ delete operator is sort of like the C function free , in that the memory used by the Database object is freed – but again, that’s as far as we can go with the comparison. There is a special active destructor method that is invoked by the C++ delete
operator to do all of the housekeeping that is required to sever the connection to the RDBMS.
In Database ’s destructor, we delete any outstanding Resultset , and then delete the Statement object and finally, the Connection object. Once that is done, the resources used by those objects are released and the memory used by the Database object itself is returned.
The first code we looked at dealt with the creation of a database connection and database statement as a series of discreet operations. That example was very C-like. The C++ Database class treats those operations as a single process that must be run once in order for you to use a database.
Java bad
Java, which is based on C++, has immutable strings. Operations that modify a string are expensive to perform in Java as they always create a new string and delete an old string or strings. The Stringbuilder class was created to make operations on Java string objects simpler, easier and less resource-intensive.
C++ has the standard library stringstream class to make modification of strings easier, but it’s not as straightforward as Java’s Stringbuilder . Strstrmbuf inherits all the capabilities of stringstrm and attempts to make it easier to use and to add some useful features to it. You’ll see Strstrmbuf used to format Resultset objects in the Place and Language classes. Place and Language hide the complexity of Strstrmbuf from our application.
The Place class (see Figures 2 and 3 on page 93 and 94) is designed to be initialised with four COLUMNS from a single ROW in a Resultset returned by a SQL query. It displays the results of all of the four COLUMN SQL examples.
Once the Place class is created, you can use the display() class method to display a single ROW of the Resultset . We need the static method Place::display(sql::resultset, bool bdisplay) to repeatedly create Place objects as we iterate over the multiple ROWS in a Resultset . Notice that we’ve overloaded the function name display() . That’s perfectly legal in C++ as long as the methods being overloaded have different signatures.
void Place::display() is one method signature. static long Place::display(sql::resultset* PRS, bool bdisplay = true) is the other signature. The bdisplay = true means that the second argument is optional. If you want bdisplay to be true, you don’t need to supply the second argument, if you want bdisplay to be false, you need to pass two arguments. That’s a little bit of ‘syntactic sugar’ to make C++ easier to use.
Notice that the static display function creates, displays and destroys one Place object for each ROW present in a Resultset .
Member variables and functions of a class are marked as public , private or protected . That indicates whether we want code from outside our class to be able to examine or change our member variables or invoke our functions. This is a feature of C++ that provides hiding of information.
If we want code from outside of our class to be able to examine our member variables, then we provide accessor (getter) functions. If we want code from outside of our class to be able to change our member variables, then we provide mutator (setter) functions. The less everyone knows about our classes’ business, the less confusing it is for humans to read and understand our code. C++ can largely do away with global variables and functions and methods that anyone can call, which are a feature of C.
Terminal is the other class used. Terminal was created because we wanted writing and colouring the screen to be performed in one place only, so that if we wanted to change the way captions or labels are displayed, we’d only have to make a change in one place, instead of in the dozens of places in db.cpp
where captions and labels are written.
See the box on page 95 to build and run the db application (./db), and read the commentary and the SQL statements, to make sure the query results make sense. Modify the SQL statements and see how your changes modify the output.
Think about creating your own database to describe the details of the physical computers and virtual machines in your own environment. Examine world.sql
as an example of how to automate the process of creating a database. Have fun!