Quick Start Guide

The Code

The best is to start from this example

//                                                                             
//  Copyright (C) 2010-2011  Artyom Beilis (Tonkikh) <artyomtnk@yahoo.com>     
//                                                                             
//  Distributed under:
//
//                   the Boost Software License, Version 1.0.
//              (See accompanying file LICENSE_1_0.txt or copy at 
//                     http://www.boost.org/LICENSE_1_0.txt)
//
//  or (at your opinion) under:
//
//                               The MIT License
//                 (See accompanying file MIT.txt or a copy at
//              http://www.opensource.org/licenses/mit-license.php)
//
#include <cppdb/frontend.h>
#include <iostream>
#include <ctime>


int main()
{
        try {
                cppdb::session sql("sqlite3:db=db.db");
                
                sql << "DROP TABLE IF EXISTS test" << cppdb::exec;

                sql<<   "CREATE TABLE test ( "
                        "   id   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                        "   n    INTEGER,"
                        "   f    REAL, "
                        "   t    TIMESTAMP,"
                        "   name TEXT "
                        ")  " << cppdb::exec;
                
                std::time_t now_time = std::time(0);
                
                std::tm now = *std::localtime(&now_time);

                cppdb::statement stat;
                
                stat = sql << 
                        "INSERT INTO test(n,f,t,name) "
                        "VALUES(?,?,?,?)"
                        << 10 << 3.1415926565 << now <<"Hello 'World'";

                stat.exec();
                std::cout<<"ID: "<<stat.last_insert_id() << std::endl;
                std::cout<<"Affected rows "<<stat.affected()<<std::endl;
                
                stat.reset();

                stat.bind(20);
                stat.bind_null();
                stat.bind(now);
                stat.bind("Hello 'World'");
                stat.exec();


                cppdb::result res = sql << "SELECT id,n,f,t,name FROM test";

                while(res.next()) {
                        double f=-1;
                        int id,k;
                        std::tm atime;
                        std::string name;
                        res >> id >> k >> f >> atime >> name;
                        std::cout <<id << ' '<<k <<' '<<f<<' '<<name<<' '<<asctime(&atime)<< std::endl;
                }

                res = sql << "SELECT n,f FROM test WHERE id=?" << 1 << cppdb::row;
                if(!res.empty()) {
                        int n = res.get<int>("n");
                        double f=res.get<double>(1);
                        std::cout << "The values are " << n <<" " << f << std::endl;
                }
        }
        catch(std::exception const &e) {
                std::cerr << "ERROR: " << e.what() << std::endl;
                return 1;
        }
        return 0;
}

First we connect to the database using cppdb connection string. Then we execute a simple sql query.

When you write

        sql << "DROP TABLE IF EXISTS test" << cppdb::exec;

Description

You actually first prepare a statement (using first operator "<<") and then execute it using a cppdb::exec manipulator. In the same way we create the table we need.

Then we create a statement that we will use multiple times. At first we prepare a statement using operator "<<" and then we bind parameters to it. Note, the string is passed as is without escaping it in any way. Then we execute a statement calling stat.exec(). Note it could be done using manipulator as well in previous line, but this is just a more verbose way and probably more clean way to do it.

Then calling stat.last_insert_id() and stat.affected() we fetch the data about last executed statement.

We can use our statement again after calling cppdb::statement::reset() function. In the next statement execution we would use more "verbose" variant of binding parameters - using bind() functions of statement, and then executing it.

Then fetch the results we created. We prepare a query and assign it into res variable efficiently fetching the query result.

Then we iterate over result's rows using res.next(), for each row we fetch the data using operator ">>".

In the next query we select several values into a single row. We prepare a statement, bind a key 1 for the placeholder "?" and then we check if the row was actually fetched and we fetch values. We can fetch values using operator ">>" as above, however we can also fetch them using column names or indexes.