This tutorial is meant to give you a jump start into using my API. My Mysql++ API is a very complicated being with a lot of advance features that you can due without if all you want to do is execute simple queries.
This tutorial assumes you know C++ fairly well, inparticuler it assumes you know about the Standard Template Library (STL) and exceptions.
All of the example code form complete running programs. However in order to use them you need to first compile them my switching to the examples directory and typing in make. Then you need to set up the database by running reset-db. The usage of the reset-db program is as follows.
When you first run the program you need to give it an account with permission to create databases. Once the database is created you can use any account that has permission full permission to the database mysql_cpp_data.
You should also run the reset-db program between examples that modify the data or else things might not work right.
The following example demonstrates how to open a connection, execute a simple query, and display the results. The code can be found in the file simple1.cc which is located in the examples directory.
#include <iostream>
#include <iomanip>
#include <sqlplus.hh>
int main() {
Connection con("mysql_cpp_data");
// The full format for the Connection constructor is
// Connection(cchar *db, cchar *host="",
// cchar *user="", cchar
*passwd="")
// You may need to specify some of them if the database is not
on
// the local machine or you database username is not the same as
your
// login name, etc..
Query query = con.query();
// This creates a query object that is bound to con.
query << "select * from stock";
// You can write to the query object like you would any other ostrem
Result res = query.store();
// Query::store() executes the query and returns the results
cout << "Query: " <<
query.preview() << endl;
// Query::preview() simply returns a string with the current query
// string in it.
cout << "Records Found: "
<< res.size() << endl <<
endl;
Row row;
cout.setf(ios::left);
cout << setw(17) << "Item"
<< setw(4) <<
"Num"
<< setw(7) <<
"Weight"
<< setw(7) <<
"Price"
<< "Date" <<
endl
<< endl;
Result::iterator i;
// The Result class has a read-only Random Access Iterator
for (i = res.begin(); i != res.end(); i++) {
row = *i;
cout << setw(17) << row[0]
<< setw(4) <<
row[1]
<< setw(7) <<
row["weight"]
// you can use either the index number or column name when
// retrieving the colume data as demonstrated above.
<< setw(7) <<
row[3]
<< row[4] <<
endl;
}
return 0;
}
Everything here should be fairly obvious. Take particular notice of how we used an iterator with the result set.
This example is almost like the previous one however it uses exceptions and the automatic conversion feature of ColData. Pay particular notice to how exceptions are used. This file for this code is named complic1.cc.
#include <iostream>
#include <iomanip>
#include <sqlplus.hh>
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect("mysql_cpp_data");
// Here we broke making the connection into two calls.
// The first one creates the Connection object with the
// use exceptions option turned on and the second one
// makes the connection
Query query = con.query();
query << "select * from stock";
Result res = query.store();
cout << "Query: " <<
query.preview() << endl;
cout << "Records Found: "
<< res.size() << endl <<
endl;
Row row;
cout.setf(ios::left);
cout << setw(17) << "Item"
<< setw(4) <<
"Num"
<< setw(7) <<
"Weight"
<< setw(7) <<
"Price"
<< "Date" <<
endl
<< endl;
Result::iterator i;
cout.precision(3);
for (i = res.begin(); i != res.end(); i++) {
row = *i;
cout << setw(17) <<
row["item"] << setw(4) <<
row[1]
<< setw(7) <<
(double)row[2]
// This is converting the row to a double so that
we
// can set the precision of it.
// ColData has the nice feature that it will convert
to
// any of the basic c++ types. if there is a problem
// in the conversion it will throw an exception (which
we
// cache below). To test it try changing the 2 in
row[2]
// to row[0]
<< setw(7) <<
(double)row[3];
Date date = row["sdate"];
// The ColData is implicitly converted to a date here.
cout.setf(ios::right);
cout.fill('0');
cout << setw(2) <<
date.month << "-" <<
setw(2) << date.day << endl;
cout.fill(' ');
cout.unsetf(ios::right);
}
return 0;
} catch (BadQuery er) { // handle any connection or
// query errors
that may come up
cerr << "Error: " <<
er.error << endl;
return -1;
} catch (BadConversion er) { // handle bad conversions
cerr << "Error: Tried to convert
\"" << er.data
<< "\" to a \""
<< er.type_name <<
"\"." <<
endl;
return -1;
}
}
Everything should be fairly obvious. A few notes about exceptions, however:
The following example demonstrates how to get some basic information about the fields, including the name of the field and the SQL type. The file is called fieldinfo1.cc.
#include <iostream>
#include <iomanip>
#include <sqlplus.hh>
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect("mysql_cpp_data");
Query query = con.query();
query << "select * from stock";
Result res = query.store();
cout << "Query: " <<
query.preview() << endl;
cout << "Records Found: "
<< res.size() << endl <<
endl;
cout << "Query Info:\n";
cout.setf(ios::left);
for (unsigned int i = 0; i < res.size(); i++) {
cout << setw(2) <<
i
<< setw(15) <<
res.names(i).c_str()
// this is the name of the field
<< setw(15) <<
res.types(i).sql_name()
// this is the SQL identifier name
// Result::types(unsigned int) returns a mysql_type_info
which in many
// ways is like type_info except that it has additional
sql type
// information in it. (with one of the methods being
sql_name())
<< setw(20) <<
res.types(i).name()
// this is the C++ identifier name which most closely
resembles
// the sql name (its is implementation defined and
often not very readable)
<< endl;
}
cout << endl;
if (res.types(0) == typeid(string))
cout << "Field 'item' is of
an sql type which most closely resembles a\n"
<< "the c++ string
type\n";
// this is demonstrating how a mysql_type_info can be compared
with a c++
// type_info.
if (res.types(1) == typeid(short int))
cout << "Field 'num' is of
an sql type which most closely resembles a\n"
<< "the c++ short
int type\n";
else if (res.types(1).base_type() == typeid(short int))
cout << "Field 'num' base type
is of an sql type which most closely \n"
<< "resembles a the
c++ short int type\n";
// However you have to be careful as if it can be null the
actual type is
// Null<TYPE> not TYPE. So you should always use the base_type
method
// to get at the underlying type. If the type is not null
than this base
// type would be the same as its type.
return 0;
} catch (BadQuery er) {
cerr << "Error: " <<
er.error << endl;
return -1;
} catch (BadConversion er) { // handle bad conversions
cerr << "Error: Tried to convert
\"" << er.data
<< "\" to a \""
<< er.type_name <<
"\"." <<
endl;
return -1;
}
}
The next example demonstrates a fairly interesting concept known as Specialized SQL Structures (SSQLS). The file name for this code is custom1.cc.
#include <iostream>
#include <iomanip>
#include <vector>
#include <sqlplus.hh>
#include <custom.hh>
sql_create_5 (stock, // struct name,
1, 5, // I'll
explain these latter
string, item, // type, id
int, num,
double, weight,
double, price,
Date, sdate)
// this is calling a very complex macro which will create a custom
// struct "stock" which has the variables:
// string item
// int num
// ...
// Date sdate
// defined as well methods to help populate the class from a mysql
row
// among other things that I'll get too in a latter example
int main () {
try { // its
in one big try block
Connection con (use_exceptions);
con.connect ("mysql_cpp_data");
Query query = con.query ();
query << "select * from stock";
vector < stock > res;
query.storein (res);
// this is storing the results into a vector of the custom
struct
// "stock" which was created my the macro
above.
cout.setf (ios::left);
cout << setw (17) <<
"Item"
<< setw (4) <<
"Num"
<< setw (7) <<
"Weight"
<< setw (7) <<
"Price"
<< "Date" <<
endl
<< endl;
// Now we we iterate through the vector using an iterator and
// produce output similar to that using Row
// Notice how we call the actual variables in i and not an
index
// offset. This is because the macro at the begging of the
file
// set up an *actual* struct of type stock which contains
the
// variables item, num, weight, price, and data.
cout.precision(3);
vector <stock>::iterator i;
for (i = res.begin (); i != res.end (); i++) {
cout << setw (17) <<
i->item.c_str ()
// unfortunally the gnu string class does not respond
to format
// modifers so we have to convert it to a conat char
*.
<< setw (4) <<
i->num
<< setw (7) <<
i->weight
<< setw (7) <<
i->price
<< i->sdate
<< endl;
}
return 0;
} catch (BadQuery er){ // handle any connection
// or query errors
that may come up
cerr << "Error: " <<
er.error << endl;
return -1;
} catch (BadConversion er) {
// we still need to cache bad conversions incase something
goes
// wrong when the data is converted into stock
cerr << "Error: Tried to convert
\"" << er.data
<< "\" to a \""
<< er.type_name <<
"\"." <<
endl;
return -1;
}
}
As you can see. SSQLS are very powerful things.
SSQLS can also be used to add data to a table. The file name for this code is custom2.cc
#include <iostream>
#include <vector>
#include <sqlplus.hh>
#include <custom.hh>
#include "util.hh"
// util.hh/cc contains the print_stock_table function
sql_create_5(stock, 1, 5, string, item, int, num,
double, weight, double, price, Date, sdate)
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect("mysql_cpp_data");
Query query = con.query();
stock row;
// create an empty stock object
/* row.item = "Hot Dogs";
row.num = 100;
row.weight = 1.5;
row.price = 1.75;
row.sdate = "1998-09-25"; */
row.set("Hot Dogs", 100, 1.5, 1.75, "1998-09-25");
// populate stock
query.insert(row);
// form the query to insert the row
// the table name is the name of the struct by default
cout << "Query : " <<
query.preview() << endl;
// show the query about to be executed
query.execute();
// execute a query that does not return a result set
print_stock_table(query);
// now print the new table;
} catch (BadQuery er) {
cerr << "Error: " <<
er.error << endl;
return -1;
} catch (BadConversion er) {
cerr << "Error: Tried to convert
\"" << er.data
<< "\" to a \""
<< er.type_name <<
"\"." <<
endl;
return -1;
}
}
That's all there is to it. Because this example modifies the data you should run reset-db after running the example code.
And it almost as easy to modify data with SSQLS. The file name is custom3.cc.
#include <iostream>
#include <vector>
#include <sqlplus.hh>
#include <custom.hh>
#include "util.hh"
// util.hh/cc contains the print_stock_table function
sql_create_5(stock, 1, 5, string, item, int, num,
double, weight, double, price, Date, sdate)
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect("mysql_cpp_data");
Query query = con.query();
query << "select * from stock
where item = \"Hotdogs' Buns\"
";
Result res = query.store();
if (res.empty())
throw BadQuery("Hotdogs' Buns not found in table,
run reset-db");
// here we are testing if the query was successful, if not
throw a bad query
stock row = res[0];
// because there should only be one row in this query we don't
// need to use a vector. Just store the first row directly
in
// "row". We can do this because one of
the constructors for
// stock takes a Row as an parameter.
stock row2 = row;
// Now we need to create a copy so that the replace query knows
// what the original values are.
row.item = "Hotdog Buns"; // now change item
query.update(row2, row);
// form the query to replace the row
// the table name is the name of the struct by default
cout << "Query : " <<
query.preview() << endl;
// show the query about to be executed
query.execute();
// execute a query that does not return a result set
print_stock_table(query);
// now print the new table;
} catch (BadQuery er) {
cerr << "Error: " <<
er.error << endl;
return -1;
} catch (BadConversion er) {
cerr << "Error: Tried to convert
\"" << er.data
<< "\" to a \""
<< er.type_name <<
"\"." <<
endl;
return -1;
}
}
When you run the example you will notice that in the where clause only the item field is checked for. This is because SSQLS also also less-than-comparable.
Don't forget to run reset-db after running the example.
SSQLS are can also be made less-than-comparable. This means that they can be sorted and stored in sets as demonstrated in the next example. The file name is custom4.cc
#include <iostream>
#include <iomanip>
#include <vector>
#include <sqlplus.hh>
#include <custom.hh>
sql_create_5(stock,
1, // This number is used to make a SSQLS
less-than-comparable.
// If this number is n then if the
first n elements are the
// same the two SSQLS are the same.
// In this case if two two stock's
"item" are the same then
// the two stock are the same.
5, // this number should generally be the
same as the number of
// elements in the list unless you
have a good reason not to.
string,item, int,num, double,weight,
double,price, Date,sdate)
int main() {
try { // its in one big try block
Connection con(use_exceptions);
con.connect("mysql_cpp_data");
Query query = con.query();
query << "select * from stock";
set<stock> res;
query.storein(res);
// here we are storing the elements in a set not a vector.
cout.setf (ios::left);
cout << setw (17) <<
"Item"
<< setw (4) <<
"Num"
<< setw (7) <<
"Weight"
<< setw (7) <<
"Price"
<< "Date" <<
endl
<< endl;
// Now we we iterate through the set. Since it is a set the
list will
// naturally be in order.
set<stock>::iterator i;
cout.precision(3);
for (i = res.begin (); i != res.end (); i++) {
cout << setw (17) <<
i->item.c_str ()
<< setw (4) <<
i->num
<< setw (7) <<
i->weight
<< setw (7) <<
i->price
<< i->sdate
<< endl;
}
i = res.find(stock("Hamburger Buns"));
if (i != res.end())
cout << "Hamburger Buns found.
Currently " << i->num <<
" in stock.\n";
else
cout << "Sorry no Hamburger
Buns found in stock\n";
// Now we are using the set's find method to find out how many
// Hamburger Buns are in stock.
return 0;
} catch (BadQuery er) {
cerr << "Error: " <<
er.error << endl;
return -1;
} catch (BadConversion er) {
cerr << "Error: Tried to convert
\"" << er.data
<< "\" to a \""
<< er.type_name <<
"\"." <<
endl;
return -1;
}
}
Beginning with MySQl++ 1.6 we have introduced three new examples, whose aim is to demonstrate some of the strongest features of MySQL++, whose primary objective is not just to demonstrate power and ease of use of MySQL++, but also to provide a solution to some of the most frequent problems presented by MySQL users. These examples exemplify a superiority of C++ over other existing languages. Those examples take very few effective MySQL++ / C++ commands to produce highly efficient code, such that each of those examples resovles some very common problems that face MySQL users, especially beginners.
As these examples are meant to tbe applied, and are applied by many MySQL users, constants that can differ from one case to another have been grouped in order to simplify editing. Also , all of this examples contain full error checking code. This is one of the areaa where C++ exception handling, fully applied in MySQL++, truly shines.
This function is solved in MySQL version 3.23 , but as of this writing many users are still using older versions. Beside that this examples demonstrates several features of MySQL++. This program requires one argument, which is a full path of the binary file.
#include <sys/stat.h>
#include <fstream>
#include <mysql++>
extern int errno;
const char MY_DATABASE[]="telcent";
const char MY_TABLE[]="fax";
const char MY_HOST[]="localhost";
const char MY_USER[]="root";
const char MY_PASSWORD[]="";
const char MY_FIELD[]="fax"; // BLOB field
int main(int argc, char *argv[]) {
if (argc < 2) {
cerr << "Usage
: load_file full_file_path" << endl
<< endl;
return -1;
}
Connection con(use_exceptions);
try {
con.real_connect (MY_DATABASE,MY_HOST,MY_USER,MY_PASSWORD,3306,(int)0,60,NULL);
Query query = con.query(); ostrstream
strbuf;
ifstream In (argv[1],ios::in
| ios::binary); struct stat for_len;
if ((In.rdbuf())->is_open()) {
if (stat (argv[1],&for_len)
== -1) return -1;
unsigned int blen
= for_len.st_size; if (!blen) return -1;
char *read_buffer
= new char[blen]; In.read(read_buffer,blen); string fill(read_buffer,blen);
strbuf <<
"INSERT INTO " << MY_TABLE <<
" (" << MY_FIELD <<
") VALUES(\"" <<
escape << fill << "\")";
query.exec(strbuf.str());
delete[] read_buffer;
}
else
cerr <<
"Your binary file " << argv[1]
<< "could not be open, errno = "
<< errno;
return 0;
} catch (BadQuery er) {
cerr << "Error: " <<
er.error << " " <<
con.errnum() << endl;
return -1;
}
}
One of the features that is displayed in this example is escape manipulator. Although automatic quoting and escaping is introduced in version 1.6, it is applicable to ColData classes only, as they contain info on data type. We could also make quoting and escaping on general data type string, but it would require to scan entire string to find out if quoting and escaping is applicable. As this feature would slow down code, we DEFINITELY NEED USER'S FEEDBACK on this matter.
This example is also very short one, considering a function that it performs. Although since 3.23.3, there is a command that dumps data from BLOB column in a binary file, this program can be used not only by users still utilizing older versions, but by users that do not wish to have this middle step of saving image to disk.
#include <sqlplus.hh>
#define MY_DATABASE "telcent"
#define MY_TABLE "fax"
#define MY_HOST "localhost"
#define MY_USER "root"
#define MY_PASSWORD ""
#define MY_FIELD "fax" // BLOB field
#define MY_KEY "datet" // PRIMARY
KEY
int main (int argc, char *argv[]) {
if (argc < 2) {
cerr << "Usage
: cgi_image primary_key_value" << endl
<< endl;
return -1;
}
cout << "Content-type:
image/jpeg" << endl;
Connection con(use_exceptions);
try {
con.real_connect (MY_DATABASE,MY_HOST,MY_USER,MY_PASSWORD,3306,(int)0,60,NULL);
Query query = con.query();
query << "SELECT
" << MY_FIELD << "
FROM " << MY_TABLE <<
" WHERE " << MY_KEY <<
" = " << argv[1];
ResUse res = query.use(); Row row=res.fetch_row();
long unsigned int *jj = res.fetch_lengths();
cout << "Content-length:
" << *jj << endl
<< endl;
fwrite(row.raw_data(0),1,*jj,stdout); return
0;
} catch (BadQuery er) {
cerr << "Error: " <<
er.error << " " <<
con.errnum() << endl;
return -1;
}
}
This example demonstrates MySQL++ handling of binary data, which was introduced in version 1.6. Flexible usage of streams enables utilization of this program in many applications.
This feature is asked for by many users, but until it is done, this program can be used instead. it is a small program, which also demonstrates few MySQL++ features.
#include <sqlplus.hh>
#define MY_DATABASE "telcent"
#define MY_TABLE "nazivi"
#define MY_HOST "localhost"
#define MY_USER "root"
#define MY_PASSWORD ""
#define MY_FIELD "naziv"
#define MY_QUERY "SELECT URL from my_table as t1,
my_table as t2 where t1.field = t2.field"
int main (void) {
Connection con(use_exceptions);
try {
ostrstream strbuf; unsigned int i=0;
con.real_connect (MY_DATABASE,MY_HOST,MY_USER,MY_PASSWORD,3306,(int)0,60,NULL);
Query query = con.query(); query
<< MY_QUERY;
ResUse res = query.use(); Row row;
strbuf << "delete
from " << MY_TABLE <<
" where " << MY_FIELD <<
" in (";
// for UPDATE just replace the above DELETE FROM with UPDATE statement
for(;row=res.fetch_row();i++) strbuf
<< row[0] << ","; if
(!i) return 0;
string output(strbuf.str()); output.erase(output.size()-1,1);
output += ")";
query.exec((const string&)output);
// cout << output << endl;
return 0;
} catch (BadQuery er) {
cerr << "Error: " <<
er.error << " " <<
con.errnum() << endl;
return -1;
}
}
Please do notify that in query construction command field MY_FIELD list of values is inserted unquoted and unescaped. This way a new feature, introduced in MySQL++ since version 1.6, is exemplified. Namely field values will be quoted or not depending on it's type. Users should not explicitely quote or quote + escape, as this will result in error. This way some more burden is taken from a programmer. Programmer may disable this feature by setting a corresponding global veriable to false. 5.1This example is written to perform DELETE. UPDATE requires some changes.
All users of this examples should beware that one more check is required in order to run this query safely. Namely, in some extreme cases, size of query might grow larger then max_allowed packet. Thterefore this check should be added.
This is only scratching the surface of what SSQLS can do for more information see the chapter on them (7).
Another powerful feature of Mysql++ is being able to set up template queries. The following example demonstrates how to use them. This code is the actual code used to set up and/or reset the sample database. It can be found under reset-db.cc. we hope to come up with some better examples soon.
#include <iostream>
#include <sqlplus.hh>
int main (int argc, char *argv[]) {
Connection connection(use_exceptions);
try { // the entire main block is one big try block;
if (argc == 1) connection.connect("");
else if (argc == 2) connection.connect("",argv[1]);
else if (argc == 3) connection.connect("",argv[1],argv[2]);
else if (argc <= 4) connection.connect("",argv[1],argv[2],argv[3]);
// create a new object and connect based on any (if any) arguments
// passed to main();
try {
connection.select_db("mysql_cpp_data");
} catch (BadQuery er) {
// if it couldn't connect to the database assume that it
doesn't exist
// and try created it. If that does not work exit with
an error.
connection.create_db("mysql_cpp_data");
connection.select_db("mysql_cpp_data");
}
Query query = connection.query(); // create a new query object
try { // ignore any errors here
// we hope to make this simpler soon
query.execute("drop table stock");
} catch (BadQuery er) {}
query << "create table stock
(item char(20) not null, num smallint,"
<< "weight double,
price double, sdate date)";
query.execute(RESET_QUERY);
// send the query to create the table and execute it. The
// RESET_QUERY tells the query object to reset it self after
// execution
query << "insert into %5:table
values (%q0, %q1, %2, %3, %q4)";
query.parse();
// set up the template query we will use to insert the data.
The
// parse method call is important as it is what lets the query
// know that this is a template and not a literal string
query.def["table"] = "stock";
// This is setting the parameter named table to stock.
query.execute ("Hamburger Buns", 56, 1.25,
1.1, "1998-04-26");
query.execute ("Hotdogs' Buns" ,65, 1.1
, 1.1, "1998-04-23");
query.execute ("Dinner Roles" , 75, .95,
.97, "1998-05-25");
query.execute ("White Bread" , 87, 1.5,
1.75, "1998-09-04");
// The last parameter "table" is not specified
here. Thus
// the default value for "table" is used
which is "stock".
} catch (BadQuery er) { // handle any errors that may come up
cerr << "Error: " <<
er.error << endl;
return -1;
}
}