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.
Warning Do not use prepared statements to insert large amounts of data into DuckDB. Instead it is recommended to use the Appender.
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_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 objectquery: The SQL query to prepareout_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_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