The Battle for Wesnoth  1.15.1+dev
mysql_prepared_statement.ipp
Go to the documentation of this file.
1 /*
2  Copyright (C) 2016 by Sergey Popov <loonycyborg@gmail.com>
3  Part of the Battle for Wesnoth Project https://www.wesnoth.org
4 
5  This program is free software; you can redistribute it and/or modify
6  it under the terms of the GNU General Public License 2
7  the Free Software Foundation; either version 2 of the License, or
8  (at your option) any later version.
9  This program is distributed in the hope that it will be useful,
10  but WITHOUT ANY WARRANTY.
11 
12  See the COPYING file for more details.
13 */
14 
15 #ifndef MYSQL_PREPARED_STATEMENT_IPP
16 #define MYSQL_PREPARED_STATEMENT_IPP
17 
18 #include <array>
19 #include <utility>
20 #include <memory>
21 #include <string>
22 #include <string.h>
23 
24 #define BOOST_SCOPE_EXIT_CONFIG_USE_LAMBDAS
25 #include <boost/scope_exit.hpp>
26 
27 #include <mysql/mysql.h>
28 
29 #include "exceptions.hpp"
30 
31 struct sql_error : public game::error
32 {
33  sql_error(const std::string& message, const std::string& sql)
34  : game::error("Error evaluating SQL statement: '" + sql + "': " + message) {}
35  sql_error(const std::string& message) : game::error(message) {}
36 };
37 
38 // make_bind functions embed pointers to their arguments in the
39 // MYSQL_BIND structure returned. It's caller's responsibility
40 // to ensure that argument's lifetime doesn't end before mysql
41 // is done with those MYSQL_BINDs
42 static MYSQL_BIND make_bind(const std::string& str, my_bool* is_null = 0)
43 {
44  MYSQL_BIND result;
45  memset(&result, 0, sizeof (MYSQL_BIND));
46  result.buffer_type = MYSQL_TYPE_STRING;
47  result.buffer = const_cast<void*>(static_cast<const void*>(str.c_str()));
48  result.buffer_length = str.size();
49  result.is_unsigned = 0;
50  result.is_null = is_null;
51  result.length = 0;
52  return result;
53 }
54 
55 static MYSQL_BIND make_bind(char* str, std::size_t* len, my_bool* is_null = 0)
56 {
57  MYSQL_BIND result;
58  memset(&result, 0, sizeof (MYSQL_BIND));
59  result.buffer_type = MYSQL_TYPE_STRING;
60  result.buffer = static_cast<void*>(str);
61  result.buffer_length = *len;
62  result.is_unsigned = 0;
63  result.is_null = is_null;
64  result.length = len;
65  return result;
66 }
67 
68 static MYSQL_BIND make_bind(int& i, my_bool* is_null = 0)
69 {
70  MYSQL_BIND result;
71  memset(&result, 0, sizeof (MYSQL_BIND));
72  result.buffer_type = MYSQL_TYPE_LONG;
73  result.buffer = static_cast<void*>(&i);
74  result.is_unsigned = 0;
75  result.is_null = is_null;
76  return result;
77 }
78 
79 static MYSQL_BIND make_bind(unsigned int& i, my_bool* is_null = 0)
80 {
81  MYSQL_BIND result;
82  memset(&result, 0, sizeof (MYSQL_BIND));
83  result.buffer_type = MYSQL_TYPE_LONG;
84  result.buffer = static_cast<void*>(&i);
85  result.is_unsigned = 1;
86  result.is_null = is_null;
87  return result;
88 }
89 
90 template<typename... Args> constexpr auto make_binds(Args&&... args)
91  -> std::array<MYSQL_BIND, sizeof...(Args)>
92 {
93  return { { (make_bind(std::forward<Args>(args))) ... } };
94 }
95 
96 template<typename T> T fetch_result(MYSQL_STMT* stmt, const std::string& sql);
97 template<> std::string fetch_result<std::string>(MYSQL_STMT* stmt, const std::string& sql)
98 {
99  char* buf = nullptr;
100  std::string result;
101  std::size_t len = 0;
102  my_bool is_null;
103  MYSQL_BIND result_bind[1] = { make_bind(buf, &len, &is_null) };
104 
105  if(mysql_stmt_bind_result(stmt, result_bind) != 0)
106  throw sql_error(mysql_stmt_error(stmt), sql);
107 
108  BOOST_SCOPE_EXIT(&stmt) {
109  mysql_stmt_free_result(stmt);
110  } ;
111 
112  mysql_stmt_store_result(stmt);
113 
114  int res = mysql_stmt_fetch(stmt);
115  if(res == MYSQL_NO_DATA)
116  throw sql_error("no data returned", sql);
117  if(is_null)
118  throw sql_error("null value returned", sql);
119  if(res != MYSQL_DATA_TRUNCATED)
120  throw sql_error(mysql_stmt_error(stmt), sql);
121  if(len > 0) {
122  buf = new char[len];
123  result_bind[0].buffer = buf;
124  result_bind[0].buffer_length = len;
125  res = mysql_stmt_fetch_column(stmt, result_bind, 0, 0);
126  result = std::string(buf, len);
127  delete[] buf;
128  }
129  if(res == MYSQL_NO_DATA)
130  throw sql_error("no data returned", sql);
131  if(res != 0)
132  throw sql_error("mysql_stmt_fetch_column failed", sql);
133  return result;
134 }
135 
136 template<typename T> T fetch_result_long_internal_(MYSQL_STMT* stmt, const std::string& sql)
137 {
138  T result;
139  my_bool is_null;
140  MYSQL_BIND result_bind[1] = { make_bind(result, &is_null) };
141 
142  if(mysql_stmt_bind_result(stmt, result_bind) != 0)
143  throw sql_error(mysql_stmt_error(stmt), sql);
144 
145  BOOST_SCOPE_EXIT(&stmt) {
146  mysql_stmt_free_result(stmt);
147  } ;
148 
149  int res = mysql_stmt_fetch(stmt);
150  if(res == MYSQL_NO_DATA)
151  throw sql_error("no data returned", sql);
152  if(is_null)
153  throw sql_error("null value returned", sql);
154  if(res != 0)
155  throw sql_error(mysql_stmt_error(stmt), sql);
156  return result;
157 }
158 
159 template<> int fetch_result<int>(MYSQL_STMT* stmt, const std::string& sql)
160 {
161  return fetch_result_long_internal_<int>(stmt, sql);
162 }
163 
164 template<> unsigned int fetch_result<unsigned int>(MYSQL_STMT* stmt, const std::string& sql)
165 {
166  return fetch_result_long_internal_<unsigned int>(stmt, sql);
167 }
168 
169 template<> bool fetch_result<bool>(MYSQL_STMT* stmt, const std::string& sql)
170 {
171  int result;
172  my_bool is_null;
173  MYSQL_BIND result_bind[1] = { make_bind(result, &is_null) };
174 
175  if(mysql_stmt_bind_result(stmt, result_bind) != 0)
176  throw sql_error(mysql_stmt_error(stmt), sql);
177 
178  BOOST_SCOPE_EXIT(&stmt) {
179  mysql_stmt_free_result(stmt);
180  } ;
181 
182  int res = mysql_stmt_fetch(stmt);
183  if(res == MYSQL_NO_DATA)
184  return false;
185  if(is_null)
186  throw sql_error("null value returned", sql);
187  if(res != 0)
188  throw sql_error(mysql_stmt_error(stmt), sql);
189  return true;
190 }
191 
192 template<> void fetch_result<void>(MYSQL_STMT*, const std::string&)
193 {
194 }
195 
196 /**
197  * Execute an sql query using mysql prepared statements API
198  * This function can convert its arguments and results to appropriate
199  * MYSQL_BIND structures automatically based on their C++ type
200  * though each type requires explicit support. For now only ints and
201  * std::strings are supported.
202  * Setting return type to bool causes this function to do a test query
203  * and return true if there is any data in result set, false otherwise
204  */
205 template<typename R, typename... Args>
206 R prepared_statement(MYSQL* conn, const std::string& sql, Args&&... args)
207 {
208  auto arg_binds = make_binds(args...);
209 
210  std::unique_ptr<MYSQL_STMT, decltype(&mysql_stmt_close)> stmt{mysql_stmt_init(conn), mysql_stmt_close};
211  if(stmt == nullptr)
212  throw sql_error("mysql_stmt_init failed", sql);
213 
214  if(mysql_stmt_prepare(stmt.get(), sql.c_str(), sql.size()) != 0)
215  throw sql_error(mysql_stmt_error(stmt.get()), sql);
216 
217  if(mysql_stmt_bind_param(stmt.get(), arg_binds.data()) != 0)
218  throw sql_error(mysql_stmt_error(stmt.get()), sql);
219 
220  if(mysql_stmt_execute(stmt.get()) != 0)
221  throw sql_error(mysql_stmt_error(stmt.get()), sql);
222 
223  return fetch_result<R>(stmt.get(), sql);
224 }
225 
226 #endif // MYSQL_PREPARED_STATEMENT_IPP
void fetch_result< void >(MYSQL_STMT *, const std::string &)
bool fetch_result< bool >(MYSQL_STMT *stmt, const std::string &sql)
static MYSQL_BIND make_bind(const std::string &str, my_bool *is_null=0)
sql_error(const std::string &message)
R prepared_statement(MYSQL *conn, const std::string &sql, Args &&... args)
Execute an sql query using mysql prepared statements API This function can convert its arguments and ...
T fetch_result_long_internal_(MYSQL_STMT *stmt, const std::string &sql)
int fetch_result< int >(MYSQL_STMT *stmt, const std::string &sql)
std::size_t i
Definition: function.cpp:933
unsigned int fetch_result< unsigned int >(MYSQL_STMT *stmt, const std::string &sql)
Base class for all the errors encountered by the engine.
Definition: exceptions.hpp:29
std::string message
Definition: exceptions.hpp:31
constexpr auto make_binds(Args &&... args) -> std::array< MYSQL_BIND, sizeof...(Args)>
T fetch_result(MYSQL_STMT *stmt, const std::string &sql)
sql_error(const std::string &message, const std::string &sql)