ResultSet Tutorial
-
What it is:
The ResultSet library is a simple (really simple) library to connect to a MySQL server and run queries,
reading back basic data. It is a C++ library, which will build out of the box on the g++ 3.x, and has
been tested on Cygwin, Slackware Linux, and Macintosh OS X.
-
What it does:
The library has functions for connecting to MySQL databases using the C library mysqlclient (usually packaged with MySQL)
as well as the ResultSet class which is used both for sending queries and reading data. It uses a secondary class, Variant,
for returning data, which can be automatically cast to commonly used types.
-
Installing:
ResultSet requires the MySQL client library libmysqlclient, the compression library libz (required by mysqlclient), and
the STL. It looks for mysqlclient includes and libraries by default in /usr/local/mysql/lib and /usr/local/mysql/include,
but these paths can be changed in the Makefile. ResultSet will by default install itself in /usr/local/resultset.
To compile ResultSet, run "make all", followed by "make install" to install it.
-
Using:
-
Compiling with:
To compile a program with ResultSet, include resultset.h (by default in /usr/local/resultset/include) and link
against libresultset.a, libmysqlclient.a and libz.a (flags for gcc/g++ would be "-lresultset -lmysqlclient -lz").
-
Connecting to the database server:
To connect to a database server, there is a static function in the ResultSet class, makeConn(). This returns a
MYSQL object, which the MySQL C API uses to hold connection data to the database. For example, to connect to a
database "website" on a server at "192.168.0.1", say
MYSQL connection=ResultSet::makeConn("192.168.0.1","yourusername","yourpassword","website");
-
Running queries:
To run any query using ResultSet, you just need to use one class, ResultSet.
ResultSet has several constructors, but the one most commonly used takes a MYSQL& as an argument, and executes
queries on the database that connection is opened on.
You can write to a ResultSet's query buffer using standard stream operators, and then call ResultSet::store()
to execute the query.
ResultSet res(&connection);
int idx=3;
res.query<<"delete from comments where story="<<idx;
res.store();
-
Reading data:
If a ResultSet returned any rows from its query, you can access them with the bracket operator.
Several functions are useful here:
- int size(): Returns how many rows are contained in the ResultSet.
- bool next(): Move to the next row. Returns false if there is no row after this one, otherwise true.
- bool prev(): Move to the row before this one, or return false if on the first row.
- bool seek(int index): Move to a specified row, or return false if it does not exist.
- void rewind(): Move to the first row.
- int tell(): Return the number of the current row.
On a row, either the column number (starting with 0 for the leftmost column) or the name of the column
can be used with the bracket operators to return data for a field.
Example:
ResultSet employees(&connection);
employees.query<<"select name from employees where building=\"4S\"";
employees.store();
while(employees.next())
{
cout<<employees["name"]<<'\n';
}
If you're curious about the number of a field, or don't want to have to look it up from the name every time, you can use
the fieldNumber() function to do so.
-
What it won't do:
There are many things that the MySQL C and C++ libraries can do that ResultSet won't. For example, stored queries, direct
data manipulation, some of the more esoteric data types like binary objects. Stored queries will probably never be
implemented, direct data manipulation might someday be implemented in the form of a function to update rows in a table with
the contents of a ResultSet if a primary key is specified, and binary data retrieval will likely be added soon, but it might
be syntax-heavy.
-
Where to get help:
randrews@ems.jsc.nasa.gov