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
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.
Do not use prepared statements to insert large amounts of data into DuckDB. Instead it is recommended to use the Appender.
API Reference
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);
duckdb_type duckdb_param_type(duckdb_prepared_statement prepared_statement, idx_t param_idx);
duckdb_state duckdb_clear_bindings(duckdb_prepared_statement prepared_statement);
duckdb_state duckdb_bind_boolean(duckdb_prepared_statement prepared_statement, idx_t param_idx, bool val);
duckdb_state duckdb_bind_int8(duckdb_prepared_statement prepared_statement, idx_t param_idx, int8_t val);
duckdb_state duckdb_bind_int16(duckdb_prepared_statement prepared_statement, idx_t param_idx, int16_t val);
duckdb_state duckdb_bind_int32(duckdb_prepared_statement prepared_statement, idx_t param_idx, int32_t val);
duckdb_state duckdb_bind_int64(duckdb_prepared_statement prepared_statement, idx_t param_idx, int64_t val);
duckdb_state duckdb_bind_hugeint(duckdb_prepared_statement prepared_statement, idx_t param_idx, duckdb_hugeint val);
duckdb_state duckdb_bind_decimal(duckdb_prepared_statement prepared_statement, idx_t param_idx, duckdb_decimal val);
duckdb_state duckdb_bind_uint8(duckdb_prepared_statement prepared_statement, idx_t param_idx, uint8_t val);
duckdb_state duckdb_bind_uint16(duckdb_prepared_statement prepared_statement, idx_t param_idx, uint16_t val);
duckdb_state duckdb_bind_uint32(duckdb_prepared_statement prepared_statement, idx_t param_idx, uint32_t val);
duckdb_state duckdb_bind_uint64(duckdb_prepared_statement prepared_statement, idx_t param_idx, uint64_t val);
duckdb_state duckdb_bind_float(duckdb_prepared_statement prepared_statement, idx_t param_idx, float val);
duckdb_state duckdb_bind_double(duckdb_prepared_statement prepared_statement, idx_t param_idx, double val);
duckdb_state duckdb_bind_date(duckdb_prepared_statement prepared_statement, idx_t param_idx, duckdb_date val);
duckdb_state duckdb_bind_time(duckdb_prepared_statement prepared_statement, idx_t param_idx, duckdb_time val);
duckdb_state duckdb_bind_timestamp(duckdb_prepared_statement prepared_statement, idx_t param_idx, duckdb_timestamp val);
duckdb_state duckdb_bind_interval(duckdb_prepared_statement prepared_statement, idx_t param_idx, duckdb_interval val);
duckdb_state duckdb_bind_varchar(duckdb_prepared_statement prepared_statement, idx_t param_idx, const char *val);
duckdb_state duckdb_bind_varchar_length(duckdb_prepared_statement prepared_statement, idx_t param_idx, const char *val, idx_t length);
duckdb_state duckdb_bind_blob(duckdb_prepared_statement prepared_statement, idx_t param_idx, const void *data, idx_t length);
duckdb_state duckdb_bind_null(duckdb_prepared_statement prepared_statement, idx_t param_idx);
duckdb_state duckdb_execute_prepared(duckdb_prepared_statement prepared_statement, duckdb_result *out_result);
duckdb_state duckdb_execute_prepared_arrow(duckdb_prepared_statement prepared_statement, duckdb_arrow *out_result);
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
returns
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.
returns
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_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.
returns
The parameter type
duckdb_clear_bindings
Clear the params bind to the prepared statement.
Syntax
duckdb_state duckdb_clear_bindings(
duckdb_prepared_statement prepared_statement
);
duckdb_bind_boolean
Binds a bool value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_boolean(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
bool val
);
duckdb_bind_int8
Binds an int8_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_int8(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
int8_t val
);
duckdb_bind_int16
Binds an int16_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_int16(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
int16_t val
);
duckdb_bind_int32
Binds an int32_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_int32(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
int32_t val
);
duckdb_bind_int64
Binds an int64_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_int64(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
int64_t val
);
duckdb_bind_hugeint
Binds an duckdb_hugeint value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_hugeint(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
duckdb_hugeint val
);
duckdb_bind_decimal
Binds a duckdb_decimal value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_decimal(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
duckdb_decimal val
);
duckdb_bind_uint8
Binds an uint8_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_uint8(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
uint8_t val
);
duckdb_bind_uint16
Binds an uint16_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_uint16(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
uint16_t val
);
duckdb_bind_uint32
Binds an uint32_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_uint32(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
uint32_t val
);
duckdb_bind_uint64
Binds an uint64_t value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_uint64(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
uint64_t val
);
duckdb_bind_float
Binds an float value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_float(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
float val
);
duckdb_bind_double
Binds an double value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_double(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
double val
);
duckdb_bind_date
Binds a duckdb_date value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_date(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
duckdb_date val
);
duckdb_bind_time
Binds a duckdb_time value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_time(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
duckdb_time val
);
duckdb_bind_timestamp
Binds a duckdb_timestamp value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_timestamp(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
duckdb_timestamp val
);
duckdb_bind_interval
Binds a duckdb_interval value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_interval(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
duckdb_interval val
);
duckdb_bind_varchar
Binds a null-terminated varchar value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_varchar(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
const char *val
);
duckdb_bind_varchar_length
Binds a varchar value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_varchar_length(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
const char *val,
idx_t length
);
duckdb_bind_blob
Binds a blob value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_blob(
duckdb_prepared_statement prepared_statement,
idx_t param_idx,
const void *data,
idx_t length
);
duckdb_bind_null
Binds a NULL value to the prepared statement at the specified index.
Syntax
duckdb_state duckdb_bind_null(
duckdb_prepared_statement prepared_statement,
idx_t param_idx
);
duckdb_execute_prepared
Executes the prepared statement with the given bound parameters, and returns a materialized query result.
This method can be called multiple times for each prepared statement, and the parameters can be modified between calls to this function.
Syntax
duckdb_state duckdb_execute_prepared(
duckdb_prepared_statement prepared_statement,
duckdb_result *out_result
);
Parameters
prepared_statement
The prepared statement to execute.
out_result
The query result.
returns
DuckDBSuccess on success or DuckDBError on failure.
duckdb_execute_prepared_arrow
Executes the prepared statement with the given bound parameters, and returns an arrow query result.
Syntax
duckdb_state duckdb_execute_prepared_arrow(
duckdb_prepared_statement prepared_statement,
duckdb_arrow *out_result
);
Parameters
prepared_statement
The prepared statement to execute.
out_result
The query result.
returns
DuckDBSuccess on success or DuckDBError on failure.