1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
/*! \page query Fetching Query Results
First read about \ref stat "how to prepare" statements
\section query_fetch Fetching a Results
The result is represented by cppdb::result class. It stores the query result. The prepared statement result can be fetched
using \ref cppdb::statement::query() "statement::query()" function.
\code
cppdb::statement st = sql << "SELECT name,age FROM students";
cppdb::result r = st.query();
\endcode
\note \ref cppdb::statement "statement" can be converted to \ref cppdb::result "result" automatically that actually causes calling \ref cppdb::statement::query() "query()" function.
So the same code above can be written using syntactic sugar as following:
\code
cppdb::result r = sql << "SELECT name,age FROM students";
\endcode
\section query_meta Fetching Meta-data on the Result:
Following meta-data can be fetched about the result:
- Number of columns using \ref cppdb::result::cols() "cols()" member function.
- The name of columns using \ref cppdb::result::name() "name()" member function. These names can be used
for retrieval of the data using \ref cppdb::result "fetch()" member functions.
\section query_iter Getting the Result Data
In order to iterate over rows of the result you should use \ref cppdb::result::next() "next()" function that
returns true if the next row exits and false otherwise. Once \ref cppdb::result::next() "next()" returned true
you can fetch the values using \ref cppdb::result "fetch()" family of functions.
There are 3 kinds of prototypes for \c fetch() functions.
-# <tt>bool fetch(int column,type &value)</tt> - fetch the value from column (index starts from 0) returning false if the value in NULL.
-# <tt>bool fetch(std::string const &column_name,type &value)</tt> - fetch the value from column using its name returning false if the value in NULL.
-# <tt>bool fetch(type &value)</tt> - fetch the value from the next column in current row (starting from 0) returning false if the value in NULL.
Where type is one of C++ numeric types, \c std::string for text, \c std::tm for date-time types and \c std::ostream for Blob types. Fetching
a value would try to do the best in casting between result type and the type you provide, for example fetching numeric or date-time types
would convert them to string representation, it would try to do the casting between string and std::tm and numeric types if possible.
If conversion fails, cppdb::bad_value_cast is thrown.
For example:
\code
cppdb::result r = sql << "SELECT name,age FROM users WHERE age > ?" << 18.0;
while(r.next()) {
std::string name;
double age;
r.fetch(0,name);
r.fetch(1,age);
std::cout << name << " is " << age << " years old" << std::endl;
}
\endcode
Another way to get values directly is by using cppdb::result::get(int) or cppdb::result::get(std::string const &) template member functions
that allow to fetch values from columns directly using column index or column name:
\code
cppdb::result r = sql << "SELECT name,age FROM users WHERE age > ?" << 18.0;
while(r.next()) {
std::cout << r.get<std::string>("name") << " is " << r.get<double>("age") << " years old" << std::endl;
}
\endcode
Unlike \c fetch() function, \c get() functions throw cppdb::null_value_fetch if the value was null.
\section query_syntacx Syntactic Sugar
There is also overloaded operator ">>" that provide syntactic sugar for fetching data and the example above an be written as:
\code
cppdb::result r = sql << "SELECT name,age FROM users WHERE age > ?" << 18.0;
while(r.next()) {
std::string name;
double age;
r >> name >> age;
std::cout << name << " is " << age << " years old" << std::endl;
}
\endcode
\note Operators \c >> function remain variable unchanged when the result is NULL value.
In order to fetch both meta-data and the value you can use \ref cppdb::into "into()" manipulator
that passes both reference to the value and a tag. For example
\code
cppdb::null_tag_type age_tag,name_tag;
std::string name;
double age;
r >> cppdb::into(name,name_tag) >> cppdb::into(age,age_tag);
\endcode
\section query_row Fetching a Single Row
Sometimes it is useful to fetch a single row of data and not iterate over it. This can be done using cppdb::statement::row()
function that works like \ref cppdb::statement::query() "query()" but also calls \ref cppdb::result::next() "next()" first time
and checks if more then one rows had been fetched (in which case it throws \ref cppdb::multiple_rows_query "multiple_rows_query" exception).
For example:
\code
cppdb::statement st = sql<<"SELECT password WHERE username=?" << user
cppdb::result r = st.row();
if(!r.empty()) {
if(pass == r.get<std::string>(0)) {
// ok
}
else {
// wrong password
}
}
else {
// no such user
}
\endcode
You can also use cppdb::row manipulator to make it shorter:
\code
cppdb::result r = sql<<"SELECT password WHERE username=?" << user << cppdb::row
if(!r.empty())
...
else
...
\endcode
You can also use operator \c >> right after \ref cppdb::row "row" manipulator, but in case
the empty result was fetched a \ref cppdb::empty_row_access "empty_row_access" exception would be thrown.
\code
double age;
sql<<"SELECT age WHERE username=?" << user << cppdb::row >> age;
\endcode
*/
|
