## 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) |
|
|
## <span id="session"></span>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(); |
|
## <span id="prep"></span>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(); |
|
### 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. |
|
## <span id="exec"></span>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` is thrown. |
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` is thrown. |
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; |
|
## <span id="qinfo"></span>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 |
|
## <span id="low"></span>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. |
|
|
