A Simple Oracle Call Interface
by Maciej Sobczak     


Example 1:

(a) 

cout << i;

(b)

int count;
sql << "select count(*) from person", into(count);

(c)

int id = 7;
string name = "John";
sql << "insert into person(id, name) values(:id, :name)", use(id), use(name);

(d)

sql << "query", into(var1), use(var2), ...;




Listing One

class Session
{
public:
    // connect to the database
    Session(std::string const & serviceName,
        std::string const & userName,
        std::string const & password);
    ~Session();
    void commit();
    void rollback();
    // ...
};
class Statement
{
public:
    Statement(Session &s);
    ~Statement();
    // accumulate the binding info
    void exchange(/* ... */);
    // prepare the SQL statement
    void prepare(std::string const &query);
    // use the binding info to actually bind the variables
    void defineAndBind();
    // execute the query
    bool execute(int num = 0);
    // fetch the next row of data
    bool fetch();
    // ...
};


Listing Two

// helper functions for preparing binding and define info
template <typename T>
IntoTypePtr into(T &t)
{
    return IntoTypePtr(new IntoType<T>(t));
}
template <typename T>
UseTypePtr use(T &t)
{
    return UseTypePtr(new UseType<T>(t));
}
// type of the temporary object
class TempType
{
public:
    TempType(Session &s);
    // the destructor actually makes the dirty work
    ~TempType();
    // inserter for additional stream-formatted data
    template <typename T>
    TempType & operator<<(T const &t);
    // operators that accept and accumulate bind info
    TempType & operator,(IntoTypePtr const &);
    TempType & operator,(UseTypePtr const &);
    // ...
};
// type of object that is used to kick-start the temporary
class StarterType
{
public:
    StarterType(Session *s);

    template <typename T>
    TempType operator<<(T const &t)
    {
        TempType o(*session_);
        o << t;
        return o;
    }
private:
    Session *session_;
};
class Session
{
public:
    // the starter object
    StarterType once;
    // the inserter operator (first in the expression)
    template <typename T>
    TempType operator<<(T const &t)
    {
        return once << t;
    }
    // ...
};


Listing Three

// example program

#include "soci.h"
#include <iostream>

using namespace std;
using namespace SOCI;

int main()
{
    try
    {
        Session sql("DBNAME", "user", "password");
        // example 1. - basic query with one variable used
        int count;
        sql << "select count(*) from some_table", into(count);
        // example 2. - basic query with parameter
        int id = 7;
        string name;
        sql << "select name from person where id = " << id, into(name);
        // example 3. - the same, but with input variable
        sql << "select name from person where id = :id", into(name), use(id);
        // example 4. - statement with no output
        id = 8;
        name = "John";
        sql << "insert into person(id, name) values(:id, :name)",
                                                          use(id), use(name);
        // example 5. - statement used multiple (three) times
        Statement st1 = (sql.prepare <<
            "insert into country(id, name) values(:id, :name)",
            use(id), use(name));
        id = 1; name = "France";  st1.execute(1);
        id = 2; name = "Germany"; st1.execute(1);
        id = 3; name = "Poland";  st1.execute(1);
        // example 6. - statement used for fetching many rows
        Statement st2 = (sql.prepare <<
            "select name from country", into(name));
        st2.execute();
        while (st2.fetch())
        {
            cout << name << '\n';
        }
    }
    catch (exception const &e)
    {
        cerr << "Error: " << e.what() << '\n';
    }
}






1


