class DuckDB::PreparedStatement

The DuckDB::PreparedStatement encapsulates connection with DuckDB prepared statement.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name, email FROM users WHERE email = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind(1, 'email@example.com')
stmt.execute

Constants

RANGE_INT16
RANGE_INT32
RANGE_INT64

Public Class Methods

new (p1, p2)

Source

static VALUE duckdb_prepared_statement_initialize(VALUE self, VALUE con, VALUE query) {
    rubyDuckDBConnection *ctxcon;
    rubyDuckDBPreparedStatement *ctx;

    if (!rb_obj_is_kind_of(con, cDuckDBConnection)) {
        rb_raise(rb_eTypeError, "1st argument should be instance of DackDB::Connection");
    }

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);
    ctxcon = get_struct_connection(con);

    if (duckdb_prepare(ctxcon->con, StringValuePtr(query), &(ctx->prepared_statement)) == DuckDBError) {
        const char *error = duckdb_prepare_error(ctx->prepared_statement);
        rb_raise(eDuckDBError, "%s", error ? error : "Failed to prepare statement(Database connection closed?).");
    }
    return self;
}

prepare (con, sql) { |stmt| ... }

Source

# File lib/duckdb/prepared_statement.rb, line 38
def prepare(con, sql)
  stmt = new(con, sql)
  return stmt unless block_given?

  begin
    yield stmt
  ensure
    stmt.destroy
  end
end

return DuckDB::PreparedStatement object. The first argument is DuckDB::Connection object. The second argument is SQL string. If block is given, the block is executed and the statement is destroyed.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connection
DuckDB::PreparedStatement.prepare(con, 'SELECT * FROM users WHERE id = ?') do |stmt|
  stmt.bind(1, 1)
  stmt.execute
end

Public Instance Methods

bind (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 249
def bind(index, value)
  case index
  when Integer
    bind_with_index(index, value)
  when String
    bind_with_name(index, value)
  when Symbol
    bind_with_name(index.to_s, value)
  else
    raise(ArgumentError, "1st argument `#{index}` must be Integer or String or Symbol.")
  end
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value is the value of prepared statement parameter.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name, email FROM users WHERE email = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind(1, 'email@example.com')

bind_args (*args, **kwargs)

Source

# File lib/duckdb/prepared_statement.rb, line 99
def bind_args(*args, **kwargs)
  args.each.with_index(1) do |arg, i|
    bind(i, arg)
  end
  kwargs.each do |key, value|
    bind(key, value)
  end
end

binds all parameters with SQL prepared statement.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name FROM users WHERE id = ?'
# or
# sql ='SELECT name FROM users WHERE id = $id'
stmt = PreparedStatement.new(con, sql)
stmt.bind_args([1])
# or
# stmt.bind_args(id: 1)

bind_blob (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_blob(VALUE self, VALUE vidx, VALUE blob) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_blob(ctx->prepared_statement, idx, (const void *)StringValuePtr(blob), (idx_t)RSTRING_LEN(blob)) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_bool (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_bool(VALUE self, VALUE vidx, VALUE val) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);
    if (val != Qtrue && val != Qfalse) {
        rb_raise(rb_eArgError, "binding value must be boolean");
    }

    if (duckdb_bind_boolean(ctx->prepared_statement, idx, (val == Qtrue)) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_date (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 159
def bind_date(index, value)
  date = _parse_date(value)

  _bind_date(index, date.year, date.month, date.day)
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value is to expected date.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name FROM users WHERE birth_day = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind(1, Date.today)
#  or you can specify date string.
# stmt.bind(1, '2021-02-23')

bind_decimal (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 231
def bind_decimal(index, value)
  decimal = _parse_deciaml(value)
  lower, upper = decimal_to_hugeint(decimal)
  width = decimal.to_s('F').gsub(/[^0-9]/, '').length
  _bind_decimal(index, lower, upper, width, decimal.scale)
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value is to expected BigDecimal value or any value that can be parsed into a BigDecimal.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT value FROM decimals WHERE decimal = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind_decimal(1, BigDecimal('987654.321'))

bind_double (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_double(VALUE self, VALUE vidx, VALUE val) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);
    double dbl = NUM2DBL(val);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_double(ctx->prepared_statement, idx, dbl) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_float (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_float(VALUE self, VALUE vidx, VALUE val) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);
    double dbl = NUM2DBL(val);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_float(ctx->prepared_statement, idx, (float)dbl) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_hugeint (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 120
def bind_hugeint(index, value)
  case value
  when Integer
    bind_varchar(index, value.to_s)
  else
    raise(ArgumentError, "2nd argument `#{value}` must be Integer.")
  end
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value is to expected Integer value. This method uses bind_varchar internally.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name FROM users WHERE bigint_col = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind_hugeint(1, 1_234_567_890_123_456_789_012_345)

bind_hugeint_internal (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 141
def bind_hugeint_internal(index, value)
  lower, upper = integer_to_hugeint(value)
  _bind_hugeint(index, lower, upper)
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value must be Integer value. This method uses duckdb_bind_hugeint internally.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name FROM users WHERE bigint_col = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind_hugeint_internal(1, 1_234_567_890_123_456_789_012_345)

bind_int16 (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_int16(VALUE self, VALUE vidx, VALUE val) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);
    int16_t i16val = NUM2INT(val);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_int16(ctx->prepared_statement, idx, i16val) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_int32 (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_int32(VALUE self, VALUE vidx, VALUE val) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);
    int32_t i32val = NUM2INT(val);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_int32(ctx->prepared_statement, idx, i32val) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_int64 (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_int64(VALUE self, VALUE vidx, VALUE val) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);
    int64_t i64val = NUM2LL(val);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_int64(ctx->prepared_statement, idx, i64val) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_int8 (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_int8(VALUE self, VALUE vidx, VALUE val) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);
    int8_t i8val = (int8_t)NUM2INT(val);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_int8(ctx->prepared_statement, idx, i8val) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_interval (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 214
def bind_interval(index, value)
  value = Interval.to_interval(value)
  _bind_interval(index, value.interval_months, value.interval_days, value.interval_micros)
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value is to expected ISO8601 time interval string.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT value FROM intervals WHERE interval = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind(1, 'P1Y2D')

bind_null (p1)

Source

static VALUE duckdb_prepared_statement_bind_null(VALUE self, VALUE vidx) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_null(ctx->prepared_statement, idx) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

bind_parameter_index (p1)

Source

static VALUE duckdb_prepared_statement_bind_parameter_index(VALUE self, VALUE name) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx;

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_parameter_index(ctx->prepared_statement, &idx, StringValuePtr(name)) == DuckDBError) {;
        rb_raise(rb_eArgError, "parameter '%s' not found", StringValuePtr(name));
    }
    return ULL2NUM(idx);
}

bind_time (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 178
def bind_time(index, value)
  time = _parse_time(value)

  _bind_time(index, time.hour, time.min, time.sec, time.usec)
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value is to expected time value.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name FROM users WHERE birth_time = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind(1, Time.now)
#  or you can specify time string.
# stmt.bind(1, '07:39:45')

bind_timestamp (index, value)

Source

# File lib/duckdb/prepared_statement.rb, line 197
def bind_timestamp(index, value)
  time = _parse_time(value)

  _bind_timestamp(index, time.year, time.month, time.day, time.hour, time.min, time.sec, time.usec)
end

binds i-th parameter with SQL prepared statement. The first argument is index of parameter. The index of first parameter is 1 not 0. The second argument value is to expected time value.

require 'duckdb'
db = DuckDB::Database.open('duckdb_database')
con = db.connect
sql ='SELECT name FROM users WHERE created_at = ?'
stmt = PreparedStatement.new(con, sql)
stmt.bind(1, Time.now)
#  or you can specify timestamp string.
# stmt.bind(1, '2022-02-23 07:39:45')

bind_varchar (p1, p2)

Source

static VALUE duckdb_prepared_statement_bind_varchar(VALUE self, VALUE vidx, VALUE str) {
    rubyDuckDBPreparedStatement *ctx;
    idx_t idx = check_index(vidx);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_bind_varchar(ctx->prepared_statement, idx, StringValuePtr(str)) == DuckDBError) {
        rb_raise(eDuckDBError, "fail to bind %llu parameter", (unsigned long long)idx);
    }
    return self;
}

clear_bindings → DuckDB::PreparedStatement

Source

static VALUE duckdb_prepared_statement_clear_bindings(VALUE self) {
    rubyDuckDBPreparedStatement *ctx;
    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    if (duckdb_clear_bindings(ctx->prepared_statement) == DuckDBError) {
        const char *error = duckdb_prepare_error(ctx->prepared_statement);
        rb_raise(eDuckDBError, "fail to clear bindings. %s", error);
    }
    return self;
}

clear all bindings of prepared statement.

execute ()

Source

static VALUE duckdb_prepared_statement_execute(VALUE self) {
    rubyDuckDBPreparedStatement *ctx;
    rubyDuckDBResult *ctxr;
    const char *error;
    VALUE result = rbduckdb_create_result();

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);
    ctxr = get_struct_result(result);

    duckdb_prepared_statement_execute_nogvl_args args = {
        .prepared_statement = ctx->prepared_statement,
        .out_result = &(ctxr->result),
        .retval = DuckDBError,
    };

    rb_thread_call_without_gvl((void *)duckdb_prepared_statement_execute_nogvl, &args, RUBY_UBF_IO, 0);
    duckdb_state state = args.retval;

    if (state == DuckDBError) {
        error = duckdb_result_error(args.out_result);
        if (error == NULL) {
            error = duckdb_prepare_error(args.prepared_statement);
        }

        rb_raise(eDuckDBError, "%s", error ? error : "Failed to execute prepared statement.");
    }

    return result;
}

nparams ()

Source

static VALUE duckdb_prepared_statement_nparams(VALUE self) {
    rubyDuckDBPreparedStatement *ctx;
    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);
    return ULL2NUM(duckdb_nparams(ctx->prepared_statement));
}

param_type (index)

Source

# File lib/duckdb/prepared_statement.rb, line 82
def param_type(index)
  i = _param_type(index)
  Converter::IntToSym.type_to_sym(i)
end

returns parameter type. The argument must be index of parameter.

require 'duckdb'
db = DuckDB::Database.open
con = db.connect
con.execute('CREATE TABLE users (id INTEGER, name VARCHAR(255))')
stmt = con.prepared_statement('SELECT * FROM users WHERE id = ?')
stmt.param_type(1) # => :integer

parameter_name (p1)

Source

static VALUE duckdb_prepared_statement_parameter_name(VALUE self, VALUE vidx) {
    rubyDuckDBPreparedStatement *ctx;
    VALUE vname;
    const char *name;
    idx_t idx = check_index(vidx);

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);

    name = duckdb_parameter_name(ctx->prepared_statement, idx);
    if (name == NULL) {
        rb_raise(eDuckDBError, "fail to get name of %llu parameter", (unsigned long long)idx);
    }
    vname = rb_str_new2(name);
    duckdb_free((void *)name);
    return vname;
}

pending_prepared ()

Source

# File lib/duckdb/prepared_statement.rb, line 50
def pending_prepared
  PendingResult.new(self)
end

pending_prepared_stream ()

Source

# File lib/duckdb/prepared_statement.rb, line 54
def pending_prepared_stream
  PendingResult.new(self, true)
end

statement_type ()

Source

# File lib/duckdb/prepared_statement.rb, line 69
def statement_type
  i = _statement_type
  Converter::IntToSym.statement_type_to_sym(i)
end

returns statement type. The return value is one of the following symbols:

:invalid, :select, :insert, :update, :explain, :delete, :prepare, :create,
:execute, :alter, :transaction, :copy, :analyze, :variable_set, :create_func,
:drop, :export, :pragma, :vacuum, :call, :set, :load, :relation, :extension,
:logical_plan, :attach, :detach, :multi

 require 'duckdb'
 db = DuckDB::Database.open('duckdb_database')
 con = db.connect
 stmt = con.prepared_statement('SELECT * FROM users')
 stmt.statement_type # => :select