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::refin 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()withSELECTqueries,dbixx::dbixx_erroris 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_erroris 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.
