Main / Edit version 8 / Edit version 9 / / Users Area

Difference "dbixx::session" ver. 8 versus ver. 9

Content:

## Description

This is general class that hold connection with database. It provides C++ interface for `dbi_conn` -- [connection infrastructure](http://libdbi.sourceforge.net/docs/programmers-guide/reference-conn.html) of libdbi.

- [Session Management](#session)

- [Preparing a query](#prep)

- [Executing queries and fetching results](#exec)

- [Retrieving information about query](#qinfo)

- [Accessing to low level libdbi features](#low)

## Session Management

### Constructor

session();

session(std::string const &backend);

Create connection object and optionaly load database driver (see, [dbi\_conn\_new](http://libdbi.sourceforge.net/docs/programmers-guide/reference-conn.html#DBI-CONN-NEW))

This object is non-copyable.

### Configuration Member Functions

void driver(std::string const &backend);

void param(std::string const &par,std::string const &val);

void param(std::string const &par,int);

`driver(...)` function provides an ability to load driver if it is not specified in construction.

`param(...)` overloaded function allows to setup different

backend parameters. They represent [dbi\_conn\_set\_option](http://libdbi.sourceforge.net/docs/programmers-guide/reference-conn.html#DBI-CONN-SET-OPTION) and [dbi\_conn\_set\_option\_numeric](http://libdbi.sourceforge.net/docs/programmers-guide/reference-conn.html#DBI-CONN-SET-OPTION-NUMERIC) . You should refer to [libdbi-drivers documentation](http://libdbi-drivers.sourceforge.net/docs.html) for specific parameters.

### Connection

void connect();

void reconnect();

void close();

These member functions are used for connecting, reconnecting and disconnecting configured backend to database.

For example:

session sql;

sql.driver("sqlite3");

sql.param("dbname","test.db");

sql.param("sqlite3_dbdir","./");

sql.connect();

## Preparing a query

void query(std::string const &query);

session &operator<<(std::string const &query);

Function `query` is used for assignment of the next query for execution. Operator `<<` is syntactic sugar for calling `query()`.

sql<<"SELECT name,value FROM options";

After we had defined the query we can bind additional parameters, if any.

bind(X const &value,bool isnull=false);

Where X is one of: `std::string`, `int`, `unsigned`, `long long`, `unsigned long long`, `double`, `std::tm` and `dbixx::null`.  The parameters are marked as "?" in the queries, and they are replaced with appropriate values.

sql.query("INSERT INTO t VALUES(?,?,?)"

sql.bind(10);

sql.bind("Your name is 'Smith'");

sql.bind(null());

Creates following query:

INSERT INTO t VALUES(10,'Your name is \'Smith\'',NULL)

Parameter `std::string` is always automatically escaped

and it is safe to bind any data to it.

There is a syntactic sugar available as well:

template<typename T>

session &operator,(T v);

template<typename T>

session &operator,(std::pair<T,bool> p);

So the above query can be set up as following:

sql<< "INSERT INTO t VALUES(?,?,?)",

10,"Your name is 'Smith'",null();

### Binding NULL values

There are several ways to bind NULL values:

1. Use `bind(T const &v,bool is_null)` directly.

2. Use `null()` syntactic sugar

3. User `use()` template function.

`use()` function is defined in namespace `dbixx` as following:

template<typename T>

std::pair<T,bool> use(T val,bool isnull=false)

It allows you bind such values in more friendly way:

int x=10;

bool tag=is_x_null();

sql<<"INSERT INTO tbl VALUES(?)",

use(x,tag),exec();

### Notes:

1. You should never use something like:

sql<< "INSERT INTO t VALUES('?')",message;

"?" symbol inside quotation marks '' would be interpreted

as "?" and not placeholder for value. Beginning and end quotes are automatically added for std::strings.

2. You should never create queries like this:

sql<< "INSERT INTO t VALUES('"+message+"')";

Such query is not escaped and can lead to SQL Injections.

3. Function `use()` receives it's parameters by value, you probably may want to use `boost::ref` in order to prevent

copying data:

string message;

sql<<"INSERT INTO t VALUES(?)",

use(boost::ref(message),flag),exec();

## Executing queries and fetching results.

There types of queries are distinguished by DbiXX:

1. Queries without output results. like INSERT or DELETE

2. Queries with single output row, like SELECT of single entry by primary key.

3. Queries with multiple row results.

### Queries without output

They are invoked using:

void exec();

void operator,(dbixx::exec const &);

The first one is normal execution and the second one is syntactic sugar.

sql<<"DELETE FROM users WHERE id=?",id;

sql.exec();

Can be written as well as:

sql<<"DELETE FROM users WHERE id=?",id,exec();

#### Notes:

1. You must call `exec()` in order to run the query. Binding parameters do no send request.

2. If you try use `exec()` with `SELECT` queries, [`dbixx::dbixx_error`](/wikipp/en/page/ref_dbixx_error) is thrown.

### Requesting single row

When single row is requested, its output

is stored in [`dbixx::row`](/wikipp/en/page/ref_dbixx_row) class.

bool single(dbixx::row &);

bool operator,(dbixx::row &r);

This function fetches zero or one row. If row is fetched, it returns true, otherwise false.

dbixx::row r;

sql<<"SELECT password FROM users "

"WHERE username=?",name;

if(sql.single(r)) {

// Do something

}

else {

throw error("No such user");

}

There is syntactic sugar available as well:

dbixx::row r;

sql<<"SELECT password FROM users "

"WHERE username=?",name,r;

if(r.isempty()) {

throw error("No such user");

}

#### Notes:

1. First syntax is recommended.

2. If query returns more then single row, [`dbixx::dbixx_error`](/wikipp/en/page/ref_dbixx_error) is thrown.

### Fetching multiple rows

The output of such queries can be stored in [`dbixx::result`](/wikipp/en/page/ref_dbixx_result)

The appropriate API is following:

void fetch(dbixx::result &);

void operator,(dbixx::result &);

For example:

dbixx::result res;

sql<<"SELECT id,name FROM person";

sql.fetch(res);

Or:

dbixx::result res;

sql<<"SELECT id,name FROM person",res;

## Retrieving information about query

RowID and number of affected rows can be retrieved using:

unsigned long long rowid(char const *seq=NULL);

unsigned long long affected();

Where `seq` is a sequence name, it is relevant for databases like PostgreSQL.

For example:

sql<<"INSERT INTO users(name,pass) VALUES(?,?)",

username,password,exec();

user_id=sql.rowid();

affected=sql.affected(); // = 1

## Accessing to low level libdbi features.

If some features are not supported by DbiXX C++ wrapper,

you can always access directly to underlying `dbi_conn`

and work directly with libdbi.

dbi_conn get_dbi_conn();

**Note:** The connection is still managed by `dbixx::session` object, thus do not close connection.

CppCMS - C++ Web Framework.

Difference "dbixx::session" ver. 8 versus ver. 9

Content:

About

Support This Project

Navigation

Main Page