[/
    Copyright (c) 2019-2024 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][br]
            Any `std::chrono::duration` convertible to `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]