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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
#include "cmdline.h"
#include "printdata.h"
#include <mysql++.h>
#include <iostream>
#include <iomanip>
#include <vector>
using namespace std;
using namespace mysqlpp;
typedef vector<size_t> IntVectorType;
static void
print_header(IntVectorType& widths, StoreQueryResult& res)
{
cout << " |" << setfill(' ');
for (size_t i = 0; i < res.field_names()->size(); i++) {
cout << " " << setw(widths.at(i)) << res.field_name(int(i)) << " |";
}
cout << endl;
}
static void
print_row(IntVectorType& widths, Row& row)
{
cout << " |" << setfill(' ');
for (size_t i = 0; i < row.size(); ++i) {
cout << " " << setw(widths.at(i)) << row[int(i)] << " |";
}
cout << endl;
}
static void
print_row_separator(IntVectorType& widths)
{
cout << " +" << setfill('-');
for (size_t i = 0; i < widths.size(); i++) {
cout << "-" << setw(widths.at(i)) << '-' << "-+";
}
cout << endl;
}
static void
print_result(StoreQueryResult& res, int index)
{
// Show how many rows are in result, if any
StoreQueryResult::size_type num_results = res.size();
if (res && (num_results > 0)) {
cout << "Result set " << index << " has " << num_results <<
" row" << (num_results == 1 ? "" : "s") << ':' << endl;
}
else {
cout << "Result set " << index << " is empty." << endl;
return;
}
// Figure out the widths of the result set's columns
IntVectorType widths;
size_t size = res.num_fields();
for (size_t i = 0; i < size; i++) {
widths.push_back(max(
res.field(i).max_length(),
res.field_name(i).size()));
}
// Print result set header
print_row_separator(widths);
print_header(widths, res);
print_row_separator(widths);
// Display the result set contents
for (StoreQueryResult::size_type i = 0; i < num_results; ++i) {
print_row(widths, res[i]);
}
// Print result set footer
print_row_separator(widths);
}
static void
print_multiple_results(Query& query)
{
// Execute query and print all result sets
StoreQueryResult res = query.store();
print_result(res, 0);
for (int i = 1; query.more_results(); ++i) {
res = query.store_next();
print_result(res, i);
}
}
int
main(int argc, char *argv[])
{
// Get connection parameters from command line
mysqlpp::examples::CommandLine cmdline(argc, argv);
if (!cmdline) {
return 1;
}
try {
// Enable multi-queries. Notice that you almost always set
// MySQL++ connection options before establishing the server
// connection, and options are always set using this one
// interface. If you're familiar with the underlying C API,
// you know that there is poor consistency on these matters;
// MySQL++ abstracts these differences away.
Connection con;
con.set_option(new MultiStatementsOption(true));
// Connect to the database
if (!con.connect(mysqlpp::examples::db_name, cmdline.server(),
cmdline.user(), cmdline.pass())) {
return 1;
}
// Set up query with multiple queries.
Query query = con.query();
query << "DROP TABLE IF EXISTS test_table; " <<
"CREATE TABLE test_table(id INT); " <<
"INSERT INTO test_table VALUES(10); " <<
"UPDATE test_table SET id=20 WHERE id=10; " <<
"SELECT * FROM test_table; " <<
"DROP TABLE test_table";
cout << "Multi-query: " << endl << query << endl;
// Execute statement and display all result sets.
print_multiple_results(query);
#if MYSQL_VERSION_ID >= 50000
// If it's MySQL v5.0 or higher, also test stored procedures, which
// return their results the same way multi-queries do.
query << "DROP PROCEDURE IF EXISTS get_stock; " <<
"CREATE PROCEDURE get_stock" <<
"( i_item varchar(20) ) " <<
"BEGIN " <<
"SET i_item = concat('%', i_item, '%'); " <<
"SELECT * FROM stock WHERE lower(item) like lower(i_item); " <<
"END;";
cout << "Stored procedure query: " << endl << query << endl;
// Create the stored procedure.
print_multiple_results(query);
// Call the stored procedure and display its results.
query << "CALL get_stock('relish')";
cout << "Query: " << query << endl;
print_multiple_results(query);
#endif
return 0;
}
catch (const BadOption& err) {
cerr << err.what() << endl;
cerr << "This example requires MySQL 4.1.1 or later." << endl;
return 1;
}
catch (const ConnectionFailed& err) {
cerr << "Failed to connect to database server: " <<
err.what() << endl;
return 1;
}
catch (const Exception& er) {
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return 1;
}
}
|
