---
layout: docu
redirect_from:
- /docs/api/c/prepared
- /docs/api/c/prepared/
- /docs/clients/c/prepared
title: Prepared Statements
---

<!-- markdownlint-disable MD001 -->

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/stable/clients/c/appender.md %}).

## API Reference Overview

<!-- This section is generated by scripts/generate_c_api_docs.py -->

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">duckdb_state</span> <a href="#duckdb_prepare"><span class="nf">duckdb_prepare</span></a>(<span class="kt">duckdb_connection</span> <span class="nv">connection</span>, <span class="kt">const</span> <span class="kt">char</span> *<span class="nv">query</span>, <span class="kt">duckdb_prepared_statement</span> *<span class="nv">out_prepared_statement</span>);
<span class="kt">void</span> <a href="#duckdb_destroy_prepare"><span class="nf">duckdb_destroy_prepare</span></a>(<span class="kt">duckdb_prepared_statement</span> *<span class="nv">prepared_statement</span>);
<span class="kt">const</span> <span class="kt">char</span> *<a href="#duckdb_prepare_error"><span class="nf">duckdb_prepare_error</span></a>(<span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>);
<span class="kt">idx_t</span> <a href="#duckdb_nparams"><span class="nf">duckdb_nparams</span></a>(<span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>);
<span class="kt">const</span> <span class="kt">char</span> *<a href="#duckdb_parameter_name"><span class="nf">duckdb_parameter_name</span></a>(<span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>, <span class="kt">idx_t</span> <span class="nv">index</span>);
<span class="kt">duckdb_type</span> <a href="#duckdb_param_type"><span class="nf">duckdb_param_type</span></a>(<span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>, <span class="kt">idx_t</span> <span class="nv">param_idx</span>);
<span class="kt">duckdb_logical_type</span> <a href="#duckdb_param_logical_type"><span class="nf">duckdb_param_logical_type</span></a>(<span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>, <span class="kt">idx_t</span> <span class="nv">param_idx</span>);
<span class="kt">duckdb_state</span> <a href="#duckdb_clear_bindings"><span class="nf">duckdb_clear_bindings</span></a>(<span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>);
<span class="kt">duckdb_statement_type</span> <a href="#duckdb_prepared_statement_type"><span class="nf">duckdb_prepared_statement_type</span></a>(<span class="kt">duckdb_prepared_statement</span> <span class="nv">statement</span>);
</code></pre></div></div>

#### `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

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">duckdb_state</span> <span class="nv">duckdb_prepare</span>(<span class="nv">
</span>  <span class="kt">duckdb_connection</span> <span class="nv">connection</span>,<span class="nv">
</span>  <span class="kt">const</span> <span class="kt">char</span> *<span class="nv">query</span>,<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> *<span class="nv">out_prepared_statement
</span>);
</code></pre></div></div>

##### 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.

<br>

#### `duckdb_destroy_prepare`

Closes the prepared statement and de-allocates all memory allocated for the statement.

##### Syntax

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">void</span> <span class="nv">duckdb_destroy_prepare</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> *<span class="nv">prepared_statement
</span>);
</code></pre></div></div>

##### Parameters

* `prepared_statement`: The prepared statement to destroy.

<br>

#### `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

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">const</span> <span class="kt">char</span> *<span class="nv">duckdb_prepare_error</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement
</span>);
</code></pre></div></div>

##### Parameters

* `prepared_statement`: The prepared statement to obtain the error from.

##### Return Value

The error message, or `nullptr` if there is none.

<br>

#### `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

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">idx_t</span> <span class="nv">duckdb_nparams</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement
</span>);
</code></pre></div></div>

##### Parameters

* `prepared_statement`: The prepared statement to obtain the number of parameters for.

<br>

#### `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

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">const</span> <span class="kt">char</span> *<span class="nv">duckdb_parameter_name</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>,<span class="nv">
</span>  <span class="kt">idx_t</span> <span class="nv">index
</span>);
</code></pre></div></div>

##### Parameters

* `prepared_statement`: The prepared statement for which to get the parameter name from.

<br>

#### `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

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">duckdb_type</span> <span class="nv">duckdb_param_type</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>,<span class="nv">
</span>  <span class="kt">idx_t</span> <span class="nv">param_idx
</span>);
</code></pre></div></div>

##### Parameters

* `prepared_statement`: The prepared statement.
* `param_idx`: The parameter index.

##### Return Value

The parameter type

<br>

#### `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

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">duckdb_logical_type</span> <span class="nv">duckdb_param_logical_type</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement</span>,<span class="nv">
</span>  <span class="kt">idx_t</span> <span class="nv">param_idx
</span>);
</code></pre></div></div>

##### Parameters

* `prepared_statement`: The prepared statement.
* `param_idx`: The parameter index.

##### Return Value

The logical type of the parameter

<br>

#### `duckdb_clear_bindings`

Clear the params bind to the prepared statement.

##### Syntax

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">duckdb_state</span> <span class="nv">duckdb_clear_bindings</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> <span class="nv">prepared_statement
</span>);
</code></pre></div></div>
<br>

#### `duckdb_prepared_statement_type`

Returns the statement type of the statement to be executed

##### Syntax

<div class="language-c highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="kt">duckdb_statement_type</span> <span class="nv">duckdb_prepared_statement_type</span>(<span class="nv">
</span>  <span class="kt">duckdb_prepared_statement</span> <span class="nv">statement
</span>);
</code></pre></div></div>

##### Parameters

* `statement`: The prepared statement.

##### Return Value

duckdb_statement_type value or DUCKDB_STATEMENT_TYPE_INVALID

<br>