Next: 5. Class Reference
Up: Usage
Previous: Usage
Contents
Subsections
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.
-
- reset-db [host [user [password]]]
If you leave off host localhost is assumed. If you leave off user your current
username is assumed. If you leave of the password it is assumed that you don't
need one.
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:
- When the use_exceptions flag is set for a parent object it is also
set for all of its children the it created after the flag is set. For example
when the use_exceptions flag is set for the con object, it
is also set for the query object. Please note that the use_exceptions
flag is not linked, it is copied. This means that when you change the use_exceptions
flag only its new children are affected, not the ones it already created.
- ColData will always throw an exception when it encounters a bad conversion.
A bad conversion is defined as a conversion in which: a) All the charters from
the string are not read in and b) The remaining characters are something other
than whitespace, zeros (0), or periods (.). This means that when ``1.25'' is
converted into an int an exception will be thrown however not when ``1.00''
is converted into an int as the remaining characters are the period and the
zero.
To see how the exception work try creating an error. Some good things to try
would be misspelling the table name or changing the double to an int.
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.33This 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 (%0q, %1q, %2, %3, %4q)";
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;
}
}
Next: 5. Class Reference
Up: Usage
Previous: Usage
Contents
2000-05-16