--- layout: docu title: Prepared Statements --- A prepared statement is a parameterized query. The query is prepared with question marks (`?`) or dollar symbols (`$1`) indicating the parameters of the query. Values can then be bound to these parameters, after which the prepared statement can be executed using those parameters. A single query can be prepared once and executed many times. Prepared statements are useful to: * Easily supply parameters to functions while avoiding string concatenation/SQL injection attacks. * Speeding up queries that will be executed many times with different parameters. DuckDB supports prepared statements in the C API with the `duckdb_prepare` method. The `duckdb_bind` family of functions is used to supply values for subsequent execution of the prepared statement using `duckdb_execute_prepared`. After we are done with the prepared statement it can be cleaned up using the `duckdb_destroy_prepare` method. ## Example ```c duckdb_prepared_statement stmt; duckdb_result result; if (duckdb_prepare(con, "INSERT INTO integers VALUES ($1, $2)", &stmt) == DuckDBError) { // handle error } duckdb_bind_int32(stmt, 1, 42); // the parameter index starts counting at 1! duckdb_bind_int32(stmt, 2, 43); // NULL as second parameter means no result set is requested duckdb_execute_prepared(stmt, NULL); duckdb_destroy_prepare(&stmt); // we can also query result sets using prepared statements if (duckdb_prepare(con, "SELECT * FROM integers WHERE i = ?", &stmt) == DuckDBError) { // handle error } duckdb_bind_int32(stmt, 1, 42); duckdb_execute_prepared(stmt, &result); // do something with result // clean up duckdb_destroy_result(&result); duckdb_destroy_prepare(&stmt); ``` After calling `duckdb_prepare`, the prepared statement parameters can be inspected using `duckdb_nparams` and `duckdb_param_type`. In case the prepare fails, the error can be obtained through `duckdb_prepare_error`. It is not required that the `duckdb_bind` family of functions matches the prepared statement parameter type exactly. The values will be auto-cast to the required value as required. For example, calling `duckdb_bind_int8` on a parameter type of `DUCKDB_TYPE_INTEGER` will work as expected. > Warning Do **not** use prepared statements to insert large amounts of data into DuckDB. Instead it is recommended to use the [Appender]({% link docs/1.3/clients/c/appender.md %}). ## API Reference Overview

duckdb_state duckdb_prepare(duckdb_connection connection, const char *query, duckdb_prepared_statement *out_prepared_statement);
void duckdb_destroy_prepare(duckdb_prepared_statement *prepared_statement);
const char *duckdb_prepare_error(duckdb_prepared_statement prepared_statement);
idx_t duckdb_nparams(duckdb_prepared_statement prepared_statement);
const char *duckdb_parameter_name(duckdb_prepared_statement prepared_statement, idx_t index);
duckdb_type duckdb_param_type(duckdb_prepared_statement prepared_statement, idx_t param_idx);
duckdb_logical_type duckdb_param_logical_type(duckdb_prepared_statement prepared_statement, idx_t param_idx);
duckdb_state duckdb_clear_bindings(duckdb_prepared_statement prepared_statement);
duckdb_statement_type duckdb_prepared_statement_type(duckdb_prepared_statement statement);

#### `duckdb_prepare` Create a prepared statement object from a query. Note that after calling `duckdb_prepare`, the prepared statement should always be destroyed using `duckdb_destroy_prepare`, even if the prepare fails. If the prepare fails, `duckdb_prepare_error` can be called to obtain the reason why the prepare failed. ##### Syntax

duckdb_state duckdb_prepare(
  duckdb_connection connection,
  const char *query,
  duckdb_prepared_statement *out_prepared_statement
);

##### Parameters * `connection`: The connection object * `query`: The SQL query to prepare * `out_prepared_statement`: The resulting prepared statement object ##### Return Value `DuckDBSuccess` on success or `DuckDBError` on failure.
#### `duckdb_destroy_prepare` Closes the prepared statement and de-allocates all memory allocated for the statement. ##### Syntax

void duckdb_destroy_prepare(
  duckdb_prepared_statement *prepared_statement
);

##### Parameters * `prepared_statement`: The prepared statement to destroy.
#### `duckdb_prepare_error` Returns the error message associated with the given prepared statement. If the prepared statement has no error message, this returns `nullptr` instead. The error message should not be freed. It will be de-allocated when `duckdb_destroy_prepare` is called. ##### Syntax

const char *duckdb_prepare_error(
  duckdb_prepared_statement prepared_statement
);

##### Parameters * `prepared_statement`: The prepared statement to obtain the error from. ##### Return Value The error message, or `nullptr` if there is none.
#### `duckdb_nparams` Returns the number of parameters that can be provided to the given prepared statement. Returns 0 if the query was not successfully prepared. ##### Syntax

idx_t duckdb_nparams(
  duckdb_prepared_statement prepared_statement
);

##### Parameters * `prepared_statement`: The prepared statement to obtain the number of parameters for.
#### `duckdb_parameter_name` Returns the name used to identify the parameter The returned string should be freed using `duckdb_free`. Returns NULL if the index is out of range for the provided prepared statement. ##### Syntax

const char *duckdb_parameter_name(
  duckdb_prepared_statement prepared_statement,
  idx_t index
);

##### Parameters * `prepared_statement`: The prepared statement for which to get the parameter name from.
#### `duckdb_param_type` Returns the parameter type for the parameter at the given index. Returns `DUCKDB_TYPE_INVALID` if the parameter index is out of range or the statement was not successfully prepared. ##### Syntax

duckdb_type duckdb_param_type(
  duckdb_prepared_statement prepared_statement,
  idx_t param_idx
);

##### Parameters * `prepared_statement`: The prepared statement. * `param_idx`: The parameter index. ##### Return Value The parameter type
#### `duckdb_param_logical_type` Returns the logical type for the parameter at the given index. Returns `nullptr` if the parameter index is out of range or the statement was not successfully prepared. The return type of this call should be destroyed with `duckdb_destroy_logical_type`. ##### Syntax

duckdb_logical_type duckdb_param_logical_type(
  duckdb_prepared_statement prepared_statement,
  idx_t param_idx
);

##### Parameters * `prepared_statement`: The prepared statement. * `param_idx`: The parameter index. ##### Return Value The logical type of the parameter
#### `duckdb_clear_bindings` Clear the params bind to the prepared statement. ##### Syntax

duckdb_state duckdb_clear_bindings(
  duckdb_prepared_statement prepared_statement
);

#### `duckdb_prepared_statement_type` Returns the statement type of the statement to be executed ##### Syntax

duckdb_statement_type duckdb_prepared_statement_type(
  duckdb_prepared_statement statement
);

##### Parameters * `statement`: The prepared statement. ##### Return Value duckdb_statement_type value or DUCKDB_STATEMENT_TYPE_INVALID