Class: DuckDB::PreparedStatement

Inherits:
Object
  • Object
show all
Includes:
Converter
Defined in:
lib/duckdb/prepared_statement.rb,
ext/duckdb/prepared_statement.c

Overview

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

Constant Summary collapse

RANGE_INT16 = 
-32_768..32_767
RANGE_INT32 = 
-2_147_483_648..2_147_483_647
RANGE_INT64 = 
-9_223_372_036_854_775_808..9_223_372_036_854_775_807

Constants included from Converter

Converter::EPOCH, Converter::EPOCH_UTC, Converter::FLIP_HUGEINT, Converter::HALF_HUGEINT, Converter::HALF_HUGEINT_BIT

Instance Method Summary collapse

Methods included from Converter

_parse_date, _parse_time, _to_date, _to_decimal_from_hugeint, _to_decimal_from_value, _to_hugeint_from_vector, _to_infinity, _to_interval_from_vector, _to_query_progress, _to_time, _to_time_from_duckdb_time, _to_time_from_duckdb_time_tz, _to_time_from_duckdb_timestamp_ms, _to_time_from_duckdb_timestamp_ns, _to_time_from_duckdb_timestamp_s, _to_time_from_duckdb_timestamp_tz, _to_uuid_from_vector

Constructor Details

#initialize(con, query) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'ext/duckdb/prepared_statement.c', line 70

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);
    }
    return self;
}

Instance Method Details

#bind(index, value) ⇒ Object

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')



205
206
207
208
209
210
211
212
213
214
215
216
 
# File 'lib/duckdb/prepared_statement.rb', line 205

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

#bind_args(*args, **kwargs) ⇒ Object

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)



74
75
76
77
78
79
80
81
 
# File 'lib/duckdb/prepared_statement.rb', line 74

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

#bind_blob(vidx, blob) ⇒ Object 



267
268
269
270
271
272
273
274
275
276
277
 
# File 'ext/duckdb/prepared_statement.c', line 267

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(vidx, val) ⇒ Object 



162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'ext/duckdb/prepared_statement.c', line 162

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) ⇒ Object

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')



134
135
136
137
138
 
# File 'lib/duckdb/prepared_statement.rb', line 134

def bind_date(index, value)
  date = _parse_date(value)

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

#bind_double(vidx, val) ⇒ Object 



242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'ext/duckdb/prepared_statement.c', line 242

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(vidx, val) ⇒ Object 



229
230
231
232
233
234
235
236
237
238
239
240
 
# File 'ext/duckdb/prepared_statement.c', line 229

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) ⇒ Object

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)



95
96
97
98
99
100
101
102
 
# File 'lib/duckdb/prepared_statement.rb', line 95

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

#bind_hugeint_internal(index, value) ⇒ Object

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)



116
117
118
119
 
# File 'lib/duckdb/prepared_statement.rb', line 116

def bind_hugeint_internal(index, value)
  lower, upper = integer_to_hugeint(value)
  _bind_hugeint(index, lower, upper)
end

#bind_int16(vidx, val) ⇒ Object 



190
191
192
193
194
195
196
197
198
199
200
201
 
# File 'ext/duckdb/prepared_statement.c', line 190

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(vidx, val) ⇒ Object 



203
204
205
206
207
208
209
210
211
212
213
214
 
# File 'ext/duckdb/prepared_statement.c', line 203

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(vidx, val) ⇒ Object 



216
217
218
219
220
221
222
223
224
225
226
227
 
# File 'ext/duckdb/prepared_statement.c', line 216

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(vidx, val) ⇒ Object 



177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'ext/duckdb/prepared_statement.c', line 177

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) ⇒ Object

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')



189
190
191
192
 
# File 'lib/duckdb/prepared_statement.rb', line 189

def bind_interval(index, value)
  value = Interval.to_interval(value)
  _bind_interval(index, value.interval_months, value.interval_days, value.interval_micros)
end

#bind_null(vidx) ⇒ Object 



279
280
281
282
283
284
285
286
287
288
289
 
# File 'ext/duckdb/prepared_statement.c', line 279

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(name) ⇒ Object 



116
117
118
119
120
121
122
123
124
125
126
 
# File 'ext/duckdb/prepared_statement.c', line 116

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) ⇒ Object

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')



153
154
155
156
157
 
# File 'lib/duckdb/prepared_statement.rb', line 153

def bind_time(index, value)
  time = _parse_time(value)

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

#bind_timestamp(index, value) ⇒ Object

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')



172
173
174
175
176
 
# File 'lib/duckdb/prepared_statement.rb', line 172

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

#bind_varchar(vidx, str) ⇒ Object 



255
256
257
258
259
260
261
262
263
264
265
 
# File 'ext/duckdb/prepared_statement.c', line 255

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

clear all bindings of prepared statement.

Returns:



151
152
153
154
155
156
157
158
159
160
 
# File 'ext/duckdb/prepared_statement.c', line 151

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;
}

#execute ⇒ Object 



95
96
97
98
99
100
101
102
103
104
105
106
 
# File 'ext/duckdb/prepared_statement.c', line 95

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

    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);
    ctxr = get_struct_result(result);
    if (duckdb_execute_prepared(ctx->prepared_statement, &(ctxr->result)) == DuckDBError) {
        rb_raise(eDuckDBError, "%s", duckdb_result_error(&(ctxr->result)));
    }
    return result;
}

#nparams ⇒ Object 



88
89
90
91
92
 
# File 'ext/duckdb/prepared_statement.c', line 88

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) ⇒ Object

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



57
58
59
60
 
# File 'lib/duckdb/prepared_statement.rb', line 57

def param_type(index)
  i = _param_type(index)
  Converter::IntToSym.type_to_sym(i)
end

#parameter_name(vidx) ⇒ Object 



128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
 
# File 'ext/duckdb/prepared_statement.c', line 128

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 ⇒ Object 



25
26
27
 
# File 'lib/duckdb/prepared_statement.rb', line 25

def pending_prepared
  PendingResult.new(self)
end

#pending_prepared_stream ⇒ Object 



29
30
31
 
# File 'lib/duckdb/prepared_statement.rb', line 29

def pending_prepared_stream
  PendingResult.new(self, true)
end

#statement_type ⇒ Object

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



44
45
46
47
 
# File 'lib/duckdb/prepared_statement.rb', line 44

def statement_type
  i = _statement_type
  Converter::IntToSym.statement_type_to_sym(i)
end