The Battle for Wesnoth  1.15.2+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 #if !defined(MARIADB_VERSION_ID) && defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 80000
29 using my_bool = bool;
30 #endif
31 
32 #include "exceptions.hpp"
33 
34 struct sql_error : public game::error
35 {
36  sql_error(const std::string& message, const std::string& sql)
37  : game::error("Error evaluating SQL statement: '" + sql + "': " + message) {}
38  sql_error(const std::string& message) : game::error(message) {}
39 };
40 
41 // make_bind functions embed pointers to their arguments in the
42 // MYSQL_BIND structure returned. It's caller's responsibility
43 // to ensure that argument's lifetime doesn't end before mysql
44 // is done with those MYSQL_BINDs
45 static MYSQL_BIND make_bind(const std::string& str, my_bool* is_null = 0)
46 {
47  MYSQL_BIND result;
48  memset(&result, 0, sizeof (MYSQL_BIND));
49  result.buffer_type = MYSQL_TYPE_STRING;
50  result.buffer = const_cast<void*>(static_cast<const void*>(str.c_str()));
51  result.buffer_length = str.size();
52  result.is_unsigned = 0;
53  result.is_null = is_null;
54  result.length = 0;
55  return result;
56 }
57 
58 static MYSQL_BIND make_bind(char* str, std::size_t* len, my_bool* is_null = 0)
59 {
60  MYSQL_BIND result;
61  memset(&result, 0, sizeof (MYSQL_BIND));
62  result.buffer_type = MYSQL_TYPE_STRING;
63  result.buffer = static_cast<void*>(str);
64  result.buffer_length = *len;
65  result.is_unsigned = 0;
66  result.is_null = is_null;
67  result.length = len;
68  return result;
69 }
70 
71 static MYSQL_BIND make_bind(int& i, my_bool* is_null = 0)
72 {
73  MYSQL_BIND result;
74  memset(&result, 0, sizeof (MYSQL_BIND));
75  result.buffer_type = MYSQL_TYPE_LONG;
76  result.buffer = static_cast<void*>(&i);
77  result.is_unsigned = 0;
78  result.is_null = is_null;
79  return result;
80 }
81 
82 static MYSQL_BIND make_bind(unsigned int& i, my_bool* is_null = 0)
83 {
84  MYSQL_BIND result;
85  memset(&result, 0, sizeof (MYSQL_BIND));
86  result.buffer_type = MYSQL_TYPE_LONG;
87  result.buffer = static_cast<void*>(&i);
88  result.is_unsigned = 1;
89  result.is_null = is_null;
90  return result;
91 }
92 
93 template<typename... Args> constexpr auto make_binds(Args&&... args)
94  -> std::array<MYSQL_BIND, sizeof...(Args)>
95 {
96  return { { (make_bind(std::forward<Args>(args))) ... } };
97 }
98 
99 template<typename T> T fetch_result(MYSQL_STMT* stmt, const std::string& sql);
100 template<> std::string fetch_result<std::string>(MYSQL_STMT* stmt, const std::string& sql)
101 {
102  char* buf = nullptr;
103  std::string result;
104  std::size_t len = 0;
105  my_bool is_null;
106  MYSQL_BIND result_bind[1] = { make_bind(buf, &len, &is_null) };
107 
108  if(mysql_stmt_bind_result(stmt, result_bind) != 0)
109  throw sql_error(mysql_stmt_error(stmt), sql);
110 
111  BOOST_SCOPE_EXIT(&stmt) {
112  mysql_stmt_free_result(stmt);
113  } ;
114 
115  mysql_stmt_store_result(stmt);
116 
117  int res = mysql_stmt_fetch(stmt);
118  if(res == MYSQL_NO_DATA)
119  throw sql_error("no data returned", sql);
120  if(is_null)
121  throw sql_error("null value returned", sql);
122  if(res != MYSQL_DATA_TRUNCATED)
123  throw sql_error(mysql_stmt_error(stmt), sql);
124  if(len > 0) {
125  buf = new char[len];
126  result_bind[0].buffer = buf;
127  result_bind[0].buffer_length = len;
128  res = mysql_stmt_fetch_column(stmt, result_bind, 0, 0);
129  result = std::string(buf, len);
130  delete[] buf;
131  }
132  if(res == MYSQL_NO_DATA)
133  throw sql_error("no data returned", sql);
134  if(res != 0)
135  throw sql_error("mysql_stmt_fetch_column failed", sql);
136  return result;
137 }
138 
139 template<typename T> T fetch_result_long_internal_(MYSQL_STMT* stmt, const std::string& sql)
140 {
141  T result;
142  my_bool is_null;
143  MYSQL_BIND result_bind[1] = { make_bind(result, &is_null) };
144 
145  if(mysql_stmt_bind_result(stmt, result_bind) != 0)
146  throw sql_error(mysql_stmt_error(stmt), sql);
147 
148  BOOST_SCOPE_EXIT(&stmt) {
149  mysql_stmt_free_result(stmt);
150  } ;
151 
152  int res = mysql_stmt_fetch(stmt);
153  if(res == MYSQL_NO_DATA)
154  throw sql_error("no data returned", sql);
155  if(is_null)
156  throw sql_error("null value returned", sql);
157  if(res != 0)
158  throw sql_error(mysql_stmt_error(stmt), sql);
159  return result;
160 }
161 
162 template<> int fetch_result<int>(MYSQL_STMT* stmt, const std::string& sql)
163 {
164  return fetch_result_long_internal_<int>(stmt, sql);
165 }
166 
167 template<> unsigned int fetch_result<unsigned int>(MYSQL_STMT* stmt, const std::string& sql)
168 {
169  return fetch_result_long_internal_<unsigned int>(stmt, sql);
170 }
171 
172 template<> bool fetch_result<bool>(MYSQL_STMT* stmt, const std::string& sql)
173 {
174  int result;
175  my_bool is_null;
176  MYSQL_BIND result_bind[1] = { make_bind(result, &is_null) };
177 
178  if(mysql_stmt_bind_result(stmt, result_bind) != 0)
179  throw sql_error(mysql_stmt_error(stmt), sql);
180 
181  BOOST_SCOPE_EXIT(&stmt) {
182  mysql_stmt_free_result(stmt);
183  } ;
184 
185  int res = mysql_stmt_fetch(stmt);
186  if(res == MYSQL_NO_DATA)
187  return false;
188  if(is_null)
189  throw sql_error("null value returned", sql);
190  if(res != 0)
191  throw sql_error(mysql_stmt_error(stmt), sql);
192  return true;
193 }
194 
195 template<> void fetch_result<void>(MYSQL_STMT*, const std::string&)
196 {
197 }
198 
199 /**
200  * Execute an sql query using mysql prepared statements API
201  * This function can convert its arguments and results to appropriate
202  * MYSQL_BIND structures automatically based on their C++ type
203  * though each type requires explicit support. For now only ints and
204  * std::strings are supported.
205  * Setting return type to bool causes this function to do a test query
206  * and return true if there is any data in result set, false otherwise
207  */
208 template<typename R, typename... Args>
209 R prepared_statement(MYSQL* conn, const std::string& sql, Args&&... args)
210 {
211  auto arg_binds = make_binds(args...);
212 
213  std::unique_ptr<MYSQL_STMT, decltype(&mysql_stmt_close)> stmt{mysql_stmt_init(conn), mysql_stmt_close};
214  if(stmt == nullptr)
215  throw sql_error("mysql_stmt_init failed", sql);
216 
217  if(mysql_stmt_prepare(stmt.get(), sql.c_str(), sql.size()) != 0)
218  throw sql_error(mysql_stmt_error(stmt.get()), sql);
219 
220  if(mysql_stmt_bind_param(stmt.get(), arg_binds.data()) != 0)
221  throw sql_error(mysql_stmt_error(stmt.get()), sql);
222 
223  if(mysql_stmt_execute(stmt.get()) != 0)
224  throw sql_error(mysql_stmt_error(stmt.get()), sql);
225 
226  return fetch_result<R>(stmt.get(), sql);
227 }
228 
229 #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)