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

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Converter

_parse_date, _parse_deciaml, _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 



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'ext/duckdb/prepared_statement.c', line 81

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

Class Method Details

.prepare(con, sql) ⇒ Object

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



38
39
40
41
42
43
44
45
46
47
 
# 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

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



249
250
251
252
253
254
255
256
257
258
259
260
 
# 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

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



99
100
101
102
103
104
105
106
 
# 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

#bind_blob(vidx, blob) ⇒ Object 



295
296
297
298
299
300
301
302
303
304
305
 
# File 'ext/duckdb/prepared_statement.c', line 295

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 



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

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



159
160
161
162
163
 
# 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

#bind_decimal(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 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'))



231
232
233
234
235
236
 
# 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

#bind_double(vidx, val) ⇒ Object 



270
271
272
273
274
275
276
277
278
279
280
281
 
# File 'ext/duckdb/prepared_statement.c', line 270

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 



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

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)



120
121
122
123
124
125
126
127
 
# 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

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



141
142
143
144
 
# 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

#bind_int16(vidx, val) ⇒ Object 



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

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 



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

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 



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

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 



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

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



214
215
216
217
 
# 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

#bind_null(vidx) ⇒ Object 



307
308
309
310
311
312
313
314
315
316
317
 
# File 'ext/duckdb/prepared_statement.c', line 307

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 



144
145
146
147
148
149
150
151
152
153
154
 
# File 'ext/duckdb/prepared_statement.c', line 144

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



178
179
180
181
182
 
# 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

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



197
198
199
200
201
 
# 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

#bind_varchar(vidx, str) ⇒ Object 



283
284
285
286
287
288
289
290
291
292
293
 
# File 'ext/duckdb/prepared_statement.c', line 283

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:



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

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

#destroy ⇒ Object

:nodoc:



126
127
128
129
130
131
132
133
134
 
# File 'ext/duckdb/prepared_statement.c', line 126

static VALUE duckdb_prepared_statement_destroy(VALUE self) {
    rubyDuckDBPreparedStatement *ctx;
    TypedData_Get_Struct(self, rubyDuckDBPreparedStatement, &prepared_statement_data_type, ctx);
    destroy_prepared_statement(ctx);
    /*
    ctx->prepared_statement = NULL;
    */
    return Qnil;
}

#execute ⇒ Object 



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
# File 'ext/duckdb/prepared_statement.c', line 105

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

    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) {
        p = duckdb_result_error(&(ctxr->result));
        if (p == NULL) {
            p = duckdb_prepare_error(ctx->prepared_statement);
        }
        rb_raise(eDuckDBError, "%s", p ? p : "Failed to execute prepared statement.");
    }
    return result;
}

#nparams ⇒ Object 



99
100
101
102
103
 
# File 'ext/duckdb/prepared_statement.c', line 99

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



82
83
84
85
 
# File 'lib/duckdb/prepared_statement.rb', line 82

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

#parameter_name(vidx) ⇒ Object 



156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
# File 'ext/duckdb/prepared_statement.c', line 156

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 



50
51
52
 
# File 'lib/duckdb/prepared_statement.rb', line 50

def pending_prepared
  PendingResult.new(self)
end

#pending_prepared_stream ⇒ Object 



54
55
56
 
# File 'lib/duckdb/prepared_statement.rb', line 54

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



69
70
71
72
 
# File 'lib/duckdb/prepared_statement.rb', line 69

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