An example of the use of newer constructs (the classes Row, Object, Reference). It uses declarations and definitions generated by db2cpp.
// $Id: example-row.C,v 1.2 2006/09/17 10:18:17 dvermeir Exp $ #include <fstream> #include <dvmysql/mysqldb.h> // The database consists of three relations: customer, account and // transaction. The SQL definition is shown below. The corresponding C++ // declarations can be found in the file "test-database.h" which // was generated using the db2cpp tool (included in this package) // using the command. // // db2cpp --database=test --password=xyz --out=test-database // ///////////////////////////////////////////////////////////////////////// // SQL ddl // // create table customer ( // id int auto_increment primary key, // first_name varchar(200) not null, // last_name varchar(200) not null, // birth_date date, // sex char(1), // email varchar(200), // mod_date timestamp, // unique index(first_name, last_name) // ); // // create table account ( // code char(14) not null primary key, # e.g. IBAN code // description text, // customer_id int not null references customer(id) // ); // // create table transaction ( // id int auto_increment primary key, // amount int not null, // account_code char(14) not null references account(code), // executed timestamp, // description text // ); ///////////////////////////////////////////////////////////////////////// #include "test-database.h" /* The definition below is useful for defining a lists of columns, e.g. * as a parameter to a function. See the Reference definitions below * for an example. */ Dv::Sql::ColumnTag cols; /* A Reference that links an account to a customer. An account's * customer_id column is a reference to a customer's id column. */ const Dv::Sql::Reference acct_cust( Database::account, (cols = &Account::customer_id), Database::customer, (cols = &Customer::id) ); /* A Reference that links a transaction to an account. A transaction's * account_code column is a reference to an account's code column. */ const Dv::Sql::Reference trans_acct( Database::transaction, (cols = &Transaction::account_code), Database::account, (cols = &Account::code) ); int main() { try { // Open a connection to the database. The Database class // is derived from Dv::MySql::Db. In addition to opening // a connection, it also initializes the tables // of the database. Each table has its own namespace, // see test-database.h. E.g. the namespace corresponding // to the customer table is shown here: // // namespace Customer { // The type of a row in the customer table // typedef Dv::Sql::Row<Database::customer> Row; // typedef Row::Set Set; // the type of a set of customer table rows // The type of a column in the customer table // typedef Dv::Sql::Row<Database::customer>::Column Column; // The various columns of the customer table. // extern const Column id; // extern const Column first_name; // extern const Column last_name; // extern const Column birth_date; // extern const Column sex; // extern const Column email; // extern const Column mod_date; // }; // // The Database class contains static Dv::Sql::Table::Ref members for each table. // These static members are used as template parameters in the Row type definitions. // // class Database: public Dv::MySql::Db { // public: // Database(..) throw (std::runtime_error); // static Dv::Sql::Table::Ref account; // static Dv::Sql::Table::Ref customer; // static Dv::Sql::Table::Ref transaction; // }; // Connect to the database. Will throw upon failure. Database db("test", "root", "xyz"); std::string customer_name; // Find a customer with last_name = customer_name. Will throw if // not found. Customer::Row cust(Customer::last_name == customer_name); // Find all customers with last_name = customer_name and a // first_name starting with 'J'. Customer::Set cust_set(Customer::last_name == customer_name && Customer::first_name ^ "J%"); // An alternative way to find a customer with this last_name. cust.set(Customer::last_name, customer_name); // Some alternatives for setting the last_name column in cust cust["last_name"] = customer_name; cust[2] = customer_name; cust[Customer::last_name] = customer_name; cust.set("last_name", customer_name); cust.set(2, customer_name); // Find the first customer matching all "dirty" values in cust. cust.match_first(); // And erase the row. cust.erase(); // Also erase all customers whose last_name starts with 'Z' cust.erase(Customer::last_name ^ "Z%"); // Find a customer with another id. cust[Customer::id] = 102; cust.refresh(); // will select customer with matching primary key // Update this customer's first_name cust[Customer::first_name] = "Bill"; cust.update(); // Get all accounts for this customer. // This can be done using the acct_cust reference. // The first way uses a specific memberfunction. Account::Set accounts( cust, acct_cust); // A more general way (which can also be used for joins over many // tables, uses select expressions that can // be generated from a reference. Account::Set other_accounts(acct_cust.match()); // Account::Set other_accounts(acct_cust.match() && acct_cust.match_target(cust)); // The other way around, finding a customer owning a certain account. Account::Row account; Customer::Row owner(account, acct_cust); // Find all transactions for all accounts of the customer. Transaction::Set transactions( trans_acct && acct_cust && (Customer::id == cust.get<int>(Customer::id) ) ); // Alternatively, we just want the sum of all amounts of transactions // on an account of the customer with a code starting with 'A'. Dv::Sql::Object total(db, "select sum(transaction.amount) as total", trans_acct && acct_cust && ( Account::code ^ "A%" ) && (Customer::id == cust.get<int>(Customer::id)) ); int x = total["total"]; std::cout << x << std::endl; } catch (std::exception& e) { std::cout << e.what() << std::endl; return 1; } return 0; }
| dvmysql-1.0.3 | [17 November, 2010] |