C API - 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

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_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_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 that connection.

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