dbixx::session
Description
This is general class that hold connection with database. It provides C++ interface for dbi_conn
-- connection infrastructure of libdbi.
- Session Management
- Preparing a query
- Executing queries and fetching results
- Retrieving information about query
- Accessing to low level libdbi features
Session Management
Constructor
- session();
- session(std::string const &backend);
Create connection object and optionaly load database driver (see, 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 and dbi_conn_set_option_numeric . You should refer to libdbi-drivers documentation 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:
- Use
bind(T const &v,bool is_null)
directly. - Use
null()
syntactic sugar - 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:
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.
You should never create queries like this:
- sql<< "INSERT INTO t VALUES('"+message+"')";
Such query is not escaped and can lead to SQL Injections.
Function
use()
receives it's parameters by value, you probably may want to useboost::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:
- Queries without output results. like INSERT or DELETE
- Queries with single output row, like SELECT of single entry by primary key.
- 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:
- You must call
exec()
in order to run the query. Binding parameters do no send request. - If you try use
exec()
withSELECT
queries,dbixx::dbixx_error
is thrown.
Requesting single row
When single row is requested, its output
is stored in 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:
- First syntax is recommended.
- If query returns more then single row,
dbixx::dbixx_error
is thrown.
Fetching multiple rows
The output of such queries can be stored in 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.