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::FLIP_HUGEINT, Converter::HALF_HUGEINT, Converter::HALF_HUGEINT_BIT

Instance Method Summary collapse

Methods included from Converter

_to_date, _to_decimal_from_vector, _to_hugeint_from_vector, _to_interval_from_vector, _to_time, _to_uuid_from_vector

Constructor Details

#initialize(con, query) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'ext/duckdb/prepared_statement.c', line 50

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(i, 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]')



170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
 
# File 'lib/duckdb/prepared_statement.rb', line 170

def bind(i, value)
  case value
  when NilClass
    bind_null(i)
  when Float
    bind_double(i, value)
  when Integer
    case value
    when RANGE_INT64
      bind_int64(i, value)
    else
      bind_varchar(i, value.to_s)
    end
  when String
    blob?(value) ? bind_blob(i, value) : bind_varchar(i, value)
  when TrueClass, FalseClass
    bind_bool(i, value)
  when Time
    bind_varchar(i, value.strftime('%Y-%m-%d %H:%M:%S.%N'))
  when Date
    bind_varchar(i, value.strftime('%Y-%m-%d'))
  when BigDecimal
    bind_varchar(i, value.to_s('F'))
  else
    raise(DuckDB::Error, "not supported type `#{value}` (#{value.class})")
  end
end

#bind_blob(vidx, blob) ⇒ Object 



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

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 



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

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(i, 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')



72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/duckdb/prepared_statement.rb', line 72

def bind_date(i, value)
  case value
  when Date, Time
    date = value
  else
    begin
      date = Date.parse(value)
    rescue => e
      raise(ArgumentError, "Cannot parse argument value to date. #{e.message}")
    end
  end

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

#bind_double(vidx, val) ⇒ Object 



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

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 



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

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(i, 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)



34
35
36
37
38
39
40
41
 
# File 'lib/duckdb/prepared_statement.rb', line 34

def bind_hugeint(i, value)
  case value
  when Integer
    bind_varchar(i, 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)



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

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

#bind_int16(vidx, val) ⇒ Object 



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

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 



137
138
139
140
141
142
143
144
145
146
147
148
 
# File 'ext/duckdb/prepared_statement.c', line 137

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 



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

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 



111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'ext/duckdb/prepared_statement.c', line 111

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(i, 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')



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

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

#bind_null(vidx) ⇒ Object 



213
214
215
216
217
218
219
220
221
222
223
 
# File 'ext/duckdb/prepared_statement.c', line 213

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_time(i, 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')



100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/duckdb/prepared_statement.rb', line 100

def bind_time(i, value)
  case value
  when Time
    time = value
  else
    begin
      time = Time.parse(value)
    rescue => e
      raise(ArgumentError, "Cannot parse argument value to time. #{e.message}")
    end
  end

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

#bind_timestamp(i, 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')



128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/duckdb/prepared_statement.rb', line 128

def bind_timestamp(i, value)
  case value
  when Time
    time = value
  else
    begin
      time = Time.parse(value)
    rescue => e
      raise(ArgumentError, "Cannot parse argument value to time. #{e.message}")
    end
  end

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

#bind_varchar(vidx, str) ⇒ Object 



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

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

#execute ⇒ Object 



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

static VALUE duckdb_prepared_statement_execute(VALUE self) {
    rubyDuckDBPreparedStatement *ctx;
    rubyDuckDBResult *ctxr;
    VALUE result = 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 



68
69
70
71
72
 
# File 'ext/duckdb/prepared_statement.c', line 68

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