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
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
[/
Copyright (c) 2019-2023 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
Distributed under the Boost Software License, Version 1.0. (See accompanying
file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
]
[section:prepared_statements Prepared statements]
This section covers using [mysqllink sql-prepared-statements.html
server-side prepared statements]. You should use them whenever a query
contains parameters not known at compile-time.
[heading Preparing a statement]
To prepare a statement, call [refmem connection prepare_statement]
or [refmem connection async_prepare_statement], passing your statement
as a string. This yields a [reflink statement] object:
[prepared_statements_prepare]
The question mark characters (`?`) represent parameters
(as described [mysqllink prepare.html here]).
When you execute the statement (next section), you
provide values for each of the parameters you declared, and the server
will use these values to run the statement.
[heading Executing a statement]
To execute a statement, use any of the following functions:
* [refmem connection execute] or [refmem connection async_execute],
which execute the statement and read the generated rows.
* [refmem connection start_execution] and [refmem connection async_start_execution], which initiate a
statement execution as a multi-function operation.
For example:
[prepared_statements_execute]
Some observations:
* You must pass in [*exactly as many parameters
as the statement has]. Failing to do so will result in an error.
* You don't need to sanitize the parameters anyhow. The server takes care of it.
* Actual parameters are matched to `?` placeholders by order.
* You can pass types like built-in integers, `float`, [reflink date] or `std::string`,
with the expected effects. [link mysql.prepared_statements.writable_field_reference This table]
contains a reference with all the allowed types.
* You can also use the static interface to execute statements by replacing [reflink results]
by [reflink static_results].
You can pass `std::optional` and `boost::optional` for parameters that may be `NULL`.
If the optional doesn't have a value, `NULL` will be sent to the server. For example:
[prepared_statements_execute_null]
[heading Type casting with statement parameters]
MySQL is quite permissive with the type of statement parameters. In most cases,
it will perform the required casts for you. For example, given this table definition:
[prepared_statements_casting_table]
We can write:
[prepared_statements_casting_execute]
MySQL expects a `TINYINT`, but we're sending an `int`, which is bigger.
As long as the value is in range, this won't cause any trouble.
If the value is out-of-range, `execute` will fail with an error.
[heading Executing a statement with a variable number of parameters]
The above approach works when you know at compile time how many parameters the statement has.
In some scenarios (e.g. a graphical interface), this may not be the case. For these cases, you can
`bind` a statement to a `field` or `field_view` iterator range:
[prepared_statements_execute_iterator_range]
[heading Closing a statement]
Prepared statements are created server-side, and thus consume server resources. If you don't need a
[reflink statement] anymore, you can call [refmem connection close_statement] or
[refmem connection async_close_statement] to instruct the server to deallocate it.
Prepared statements are managed by the server on a per-connection basis. Once you close your connection
with the server, all prepared statements you have created using this connection will be automatically
deallocated.
If you are creating your prepared statements at the beginning
of your program and keeping them alive until the connection
is closed, then there is no need to call `close_statement()`,
as closing the connection will do the cleanup
for you. If you are creating and destroying prepared statements
dynamically, then it is advised to use `close_statement()` to prevent excessive
resource usage in the server.
Finally, note that [reflink statement]'s destructor
does not perform any server-side deallocation of the statement.
This is because closing a statement involves a network
operation that may block or fail.
[heading Type mapping reference for prepared statement parameters]
The following table contains a reference of the types that can be used when binding a statement.
If a type can be used this way, we say to satisfy the `WritableField` concept.
The table shows how a parameter `v` in a expression `conn.execute(stmt.bind(v), result)`
is interpreted by MySQL, depeding on `v`'s type.
[table:writable_field_reference
[
[C++ type]
[MySQL type]
[Compatible with...]
]
[
[`signed char`, `short`, `int`, `long`, `long long`]
[`BIGINT`]
[Signed `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`]
]
[
[`unsigned char`, `unsigned short`, `unsigned int`, `unsigned long`, `unsigned long long`]
[`UNSIGNED BIGINT`]
[Unsigned `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`, `YEAR`, `BIT`]
]
[
[`bool`]
[`BIGINT` (`1` if `true`, `0` if `false`)]
[`TINYINT`]
]
[
[`std::basic_string<char, std::char_traits<char>, Allocator>` (including `std::string`), [reflink string_view], `std::string_view`, `const char*`]
[`VARCHAR`]
[`CHAR`, `VARCHAR`, `TEXT` (all sizes), `ENUM`, `SET`, `JSON`, `DECIMAL`, `NUMERIC`]
]
[
[`std::basic_vector<unsigned char, Allocator>` (including [reflink blob]), [reflink blob_view]]
[`BLOB`]
[`BINARY`, `VARBINARY`, `BLOB` (all sizes), `GEOMETRY`]
]
[
[`float`]
[`FLOAT`]
[`FLOAT`]
]
[
[`double`]
[`DOUBLE`]
[`DOUBLE`]
]
[
[[reflink date]]
[`DATE`]
[`DATE`]
]
[
[[reflink datetime]]
[`DATETIME`]
[`DATETIME`, `TIMESTAMP`]
]
[
[[reflink time]]
[`TIME`]
[`TIME`]
]
[
[`std::nullptr_t`]
[`NULL`]
[Any of the other types. Used to insert `NULL`s, for example.]
]
[
[`std::optional<T>`]
[
Applies `T`'s type mapping if the optional has a value.[br]
`NULL` otherwise
]
[]
]
[
[`boost::optional<T>`]
[
Applies `T`'s type mapping if the optional has a value.[br]
`NULL` otherwise
]
[]
]
[
[[reflink field_view]]
[Depends on the actual type stored by the field]
[]
]
[
[[reflink field]]
[Depends on the actual type stored by the field]
[]
]
]
[endsect]
|
