Class: SQLite3::Statement

Inherits:
Object
  • Object
show all
Includes:
Enumerable
Defined in:
lib/sqlite3/statement.rb,
ext/sqlite3/statement.c

Overview

A statement represents a prepared-but-unexecuted SQL query. It will rarely (if ever) be instantiated directly by a client, and is most often obtained via the Database#prepare method.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#SQLite3::Statement.new(db, sql) ⇒ Object

Create a new statement attached to the given Database instance, and which encapsulates the given SQL text. If the text contains more than one statement (i.e., separated by semicolons), then the #remainder property will be set to the trailing text.



31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'ext/sqlite3/statement.c', line 31

static VALUE initialize(VALUE self, VALUE db, VALUE sql)
{
  sqlite3RubyPtr db_ctx;
  sqlite3StmtRubyPtr ctx;
  const char *tail = NULL;
  int status;

  StringValue(sql);

  Data_Get_Struct(db, sqlite3Ruby, db_ctx);
  Data_Get_Struct(self, sqlite3StmtRuby, ctx);

  if(!db_ctx->db)
    rb_raise(rb_eArgError, "prepare called on a closed database");

#ifdef HAVE_RUBY_ENCODING_H
  if(!UTF8_P(sql)) {
    sql               = rb_str_export_to_enc(sql, rb_utf8_encoding());
  }
#endif

  status = sqlite3_prepare_v2(
      db_ctx->db,
      (const char *)StringValuePtr(sql),
      (int)RSTRING_LEN(sql),
      &ctx->st,
      &tail
  );

  CHECK(db_ctx->db, status);

  rb_iv_set(self, "@connection", db);
  rb_iv_set(self, "@remainder", rb_str_new2(tail));
  rb_iv_set(self, "@columns", Qnil);
  rb_iv_set(self, "@types", Qnil);

  return self;
}

Instance Attribute Details

#remainder ⇒ Object (readonly)

This is any text that followed the first valid SQL statement in the text with which the statement was initialized. If there was no trailing text, this will be the empty string.



20
21
22
 
# File 'lib/sqlite3/statement.rb', line 20

def remainder
  @remainder
end

Instance Method Details

#active? ⇒ Boolean

Returns true if the statement is currently active, meaning it has an open result set.

Returns:

  • (Boolean) 


94
95
96
 
# File 'lib/sqlite3/statement.rb', line 94

def active?
  !done?
end

#bind_param(key, value) ⇒ Object

Binds value to the named (or positional) placeholder. If param is a Fixnum, it is treated as an index for a positional placeholder. Otherwise it is used as the name of the placeholder to bind to.

See also #bind_params.



201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
 
# File 'ext/sqlite3/statement.c', line 201

static VALUE bind_param(VALUE self, VALUE key, VALUE value)
{
  sqlite3StmtRubyPtr ctx;
  int status;
  int index;

  Data_Get_Struct(self, sqlite3StmtRuby, ctx);
  REQUIRE_OPEN_STMT(ctx);

  switch(TYPE(key)) {
    case T_SYMBOL:
      key = rb_funcall(key, rb_intern("to_s"), 0);
    case T_STRING:
      if(RSTRING_PTR(key)[0] != ':') key = rb_str_plus(rb_str_new2(":"), key);
      index = sqlite3_bind_parameter_index(ctx->st, StringValuePtr(key));
      break;
    default:
      index = (int)NUM2INT(key);
  }

  if(index == 0)
    rb_raise(rb_path2class("SQLite3::Exception"), "no such bind parameter");

  switch(TYPE(value)) {
    case T_STRING:
      if(CLASS_OF(value) == cSqlite3Blob
#ifdef HAVE_RUBY_ENCODING_H
              || rb_enc_get_index(value) == rb_ascii8bit_encindex()
#endif
        ) {
        status = sqlite3_bind_blob(
            ctx->st,
            index,
            (const char *)StringValuePtr(value),
            (int)RSTRING_LEN(value),
            SQLITE_TRANSIENT
            );
      } else {
#ifdef HAVE_RUBY_ENCODING_H
        if(!UTF8_P(value)) {
              VALUE db          = rb_iv_get(self, "@connection");
              VALUE encoding    = rb_funcall(db, rb_intern("encoding"), 0);
              rb_encoding * enc = rb_to_encoding(encoding);
              value = rb_str_export_to_enc(value, enc);
          }
#endif

        status = sqlite3_bind_text(
            ctx->st,
            index,
            (const char *)StringValuePtr(value),
            (int)RSTRING_LEN(value),
            SQLITE_TRANSIENT
            );
      }
      break;
    case T_BIGNUM:
#if SIZEOF_LONG < 8
      if (RBIGNUM_LEN(value) * SIZEOF_BDIGITS <= 8) {
          status = sqlite3_bind_int64(ctx->st, index, (sqlite3_int64)NUM2LL(value));
          break;
      }
#endif
    case T_FLOAT:
      status = sqlite3_bind_double(ctx->st, index, NUM2DBL(value));
      break;
    case T_FIXNUM:
      status = sqlite3_bind_int64(ctx->st, index, (sqlite3_int64)FIX2LONG(value));
      break;
    case T_NIL:
      status = sqlite3_bind_null(ctx->st, index);
      break;
    default:
      rb_raise(rb_eRuntimeError, "can't prepare %s",
          rb_class2name(CLASS_OF(value)));
      break;
  }

  CHECK(sqlite3_db_handle(ctx->st), status);

  return self;
}

#bind_parameter_count ⇒ Object

Return the number of bind parameters



371
372
373
374
375
376
377
378
 
# File 'ext/sqlite3/statement.c', line 371

static VALUE bind_parameter_count(VALUE self)
{
  sqlite3StmtRubyPtr ctx;
  Data_Get_Struct(self, sqlite3StmtRuby, ctx);
  REQUIRE_OPEN_STMT(ctx);

  return INT2NUM((long)sqlite3_bind_parameter_count(ctx->st));
}

#bind_params(*bind_vars) ⇒ Object

Binds the given variables to the corresponding placeholders in the SQL text.

See Database#execute for a description of the valid placeholder syntaxes.

Example:

stmt = db.prepare( "select * from table where a=? and b=?" )
stmt.bind_params( 15, "hello" )

See also #execute, #bind_param, Statement#bind_param, and Statement#bind_params.



35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/sqlite3/statement.rb', line 35

def bind_params( *bind_vars )
  index = 1
  bind_vars.flatten.each do |var|
    if Hash === var
      var.each { |key, val| bind_param key, val }
    else
      bind_param index, var
      index += 1
    end
  end
end

#close ⇒ Object

Closes the statement by finalizing the underlying statement handle. The statement must not be used after being closed.



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'ext/sqlite3/statement.c', line 75

static VALUE sqlite3_rb_close(VALUE self)
{
  sqlite3StmtRubyPtr ctx;
  sqlite3 * db;

  Data_Get_Struct(self, sqlite3StmtRuby, ctx);

  REQUIRE_OPEN_STMT(ctx);

  db = sqlite3_db_handle(ctx->st);
  CHECK(db, sqlite3_finalize(ctx->st));

  ctx->st = NULL;

  return self;
}

#closed? ⇒ Boolean

Returns true if the statement has been closed.

Returns:

  • (Boolean) 


96
97
98
99
100
101
102
103
104
 
# File 'ext/sqlite3/statement.c', line 96

static VALUE closed_p(VALUE self)
{
  sqlite3StmtRubyPtr ctx;
  Data_Get_Struct(self, sqlite3StmtRuby, ctx);

  if(!ctx->st) return Qtrue;

  return Qfalse;
}

#column_count ⇒ Object

Returns the number of columns to be returned for this statement



322
323
324
325
326
327
328
329
 
# File 'ext/sqlite3/statement.c', line 322

static VALUE column_count(VALUE self)
{
  sqlite3StmtRubyPtr ctx;
  Data_Get_Struct(self, sqlite3StmtRuby, ctx);
  REQUIRE_OPEN_STMT(ctx);

  return INT2NUM((long)sqlite3_column_count(ctx->st));
}

#column_decltype(index) ⇒ Object

Get the column type at index. 0 based.



353
354
355
356
357
358
359
360
361
362
363
364
365
 
# File 'ext/sqlite3/statement.c', line 353

static VALUE column_decltype(VALUE self, VALUE index)
{
  sqlite3StmtRubyPtr ctx;
  const char * name;

  Data_Get_Struct(self, sqlite3StmtRuby, ctx);
  REQUIRE_OPEN_STMT(ctx);

  name = sqlite3_column_decltype(ctx->st, (int)NUM2INT(index));

  if(name) return rb_str_new2(name);
  return Qnil;
}

#column_name(index) ⇒ Object

Get the column name at index. 0 based.



335
336
337
338
339
340
341
342
343
344
345
346
347
 
# File 'ext/sqlite3/statement.c', line 335

static VALUE column_name(VALUE self, VALUE index)
{
  sqlite3StmtRubyPtr ctx;
  const char * name;

  Data_Get_Struct(self, sqlite3StmtRuby, ctx);
  REQUIRE_OPEN_STMT(ctx);

  name = sqlite3_column_name(ctx->st, (int)NUM2INT(index));

  if(name) return rb_str_new2(name);
  return Qnil;
}

#columns ⇒ Object

Return an array of the column names for this statement. Note that this may execute the statement in order to obtain the metadata; this makes it a (potentially) expensive operation.



101
102
103
104
 
# File 'lib/sqlite3/statement.rb', line 101

def columns
  get_metadata unless @columns
  return @columns
end

#database_name(column_index) ⇒ Object

Return the database name for the column at column_index



386
387
388
389
390
391
392
393
394
 
# File 'ext/sqlite3/statement.c', line 386

static VALUE database_name(VALUE self, VALUE index)
{
  sqlite3StmtRubyPtr ctx;
  Data_Get_Struct(self, sqlite3StmtRuby, ctx);
  REQUIRE_OPEN_STMT(ctx);

  return SQLITE3_UTF8_STR_NEW2(
      sqlite3_column_database_name(ctx->st, NUM2INT(index)));
}

#done? ⇒ Boolean

returns true if all rows have been returned.

Returns:

  • (Boolean) 


309
310
311
312
313
314
315
316
 
# File 'ext/sqlite3/statement.c', line 309

static VALUE done_p(VALUE self)
{
  sqlite3StmtRubyPtr ctx;
  Data_Get_Struct(self, sqlite3StmtRuby, ctx);

  if(ctx->done_p) return Qtrue;
  return Qfalse;
}

#each ⇒ Object 



106
107
108
109
110
111
112
 
# File 'lib/sqlite3/statement.rb', line 106

def each
  loop do
    val = step
    break self if done?
    yield val
  end
end

#execute(*bind_vars) {|@results| ... } ⇒ Object

Execute the statement. This creates a new ResultSet object for the statement’s virtual machine. If a block was given, the new ResultSet will be yielded to it; otherwise, the ResultSet will be returned.

Any parameters will be bound to the statement using #bind_params.

Example:

stmt = db.prepare( "select * from table" )
stmt.execute do |result|
  ...
end

See also #bind_params, #execute!.

Yields:

  • (@results) 


61
62
63
64
65
66
67
68
69
70
71
 
# File 'lib/sqlite3/statement.rb', line 61

def execute( *bind_vars )
  reset! if active? || done?

  bind_params(*bind_vars) unless bind_vars.empty?
  @results = ResultSet.new(@connection, self)

  step if 0 == column_count

  yield @results if block_given?
  @results
end

#execute!(*bind_vars, &block) ⇒ Object

Execute the statement. If no block was given, this returns an array of rows returned by executing the statement. Otherwise, each row will be yielded to the block.

Any parameters will be bound to the statement using #bind_params.

Example:

stmt = db.prepare( "select * from table" )
stmt.execute! do |row|
  ...
end

See also #bind_params, #execute.



87
88
89
90
 
# File 'lib/sqlite3/statement.rb', line 87

def execute!( *bind_vars, &block )
  execute(*bind_vars)
  block_given? ? each(&block) : to_a
end

#must_be_open! ⇒ Object

Performs a sanity check to ensure that the statement is not closed. If it is, an exception is raised.



142
143
144
145
146
 
# File 'lib/sqlite3/statement.rb', line 142

def must_be_open! # :nodoc:
  if closed?
    raise SQLite3::Exception, "cannot use a closed statement"
  end
end

#reset! ⇒ Object

Resets the statement. This is typically done internally, though it might occassionally be necessary to manually reset the statement.



289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
 
# File 'ext/sqlite3/statement.c', line 289

static VALUE reset_bang(VALUE self)
{
  sqlite3StmtRubyPtr ctx;
  int status;

  Data_Get_Struct(self, sqlite3StmtRuby, ctx);
  REQUIRE_OPEN_STMT(ctx);

  status = sqlite3_reset(ctx->st);
  CHECK(sqlite3_db_handle(ctx->st), status);

  ctx->done_p = 0;

  return self;
}

#step ⇒ Object 



106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
 
# File 'ext/sqlite3/statement.c', line 106

static VALUE step(VALUE self)
{
  sqlite3StmtRubyPtr ctx;
  sqlite3_stmt *stmt;
  int value, length;
  VALUE list;
#ifdef HAVE_RUBY_ENCODING_H
  rb_encoding * internal_encoding;
  int enc_index;
#endif

  Data_Get_Struct(self, sqlite3StmtRuby, ctx);

  REQUIRE_OPEN_STMT(ctx);

  if(ctx->done_p) return Qnil;

#ifdef HAVE_RUBY_ENCODING_H
  {
      VALUE db          = rb_iv_get(self, "@connection");
      VALUE encoding    = rb_funcall(db, rb_intern("encoding"), 0);
      enc_index = NIL_P(encoding) ? rb_utf8_encindex() : rb_to_encoding_index(encoding);
      internal_encoding = rb_default_internal_encoding();
  }
#endif

  stmt = ctx->st;

  value = sqlite3_step(stmt);
  length = sqlite3_column_count(stmt);
  list = rb_ary_new2((long)length);

  switch(value) {
    case SQLITE_ROW:
      {
        int i;
        for(i = 0; i < length; i++) {
          switch(sqlite3_column_type(stmt, i)) {
            case SQLITE_INTEGER:
              rb_ary_push(list, LL2NUM(sqlite3_column_int64(stmt, i)));
              break;
            case SQLITE_FLOAT:
              rb_ary_push(list, rb_float_new(sqlite3_column_double(stmt, i)));
              break;
            case SQLITE_TEXT:
              {
                VALUE str = rb_tainted_str_new(
                    (const char *)sqlite3_column_text(stmt, i),
                    (long)sqlite3_column_bytes(stmt, i)
                );
#ifdef HAVE_RUBY_ENCODING_H
                rb_enc_associate_index(str, enc_index);
                if(internal_encoding)
                  str = rb_str_export_to_enc(str, internal_encoding);
#endif
                rb_ary_push(list, str);
              }
              break;
            case SQLITE_BLOB:
              {
                VALUE str = rb_tainted_str_new(
                    (const char *)sqlite3_column_blob(stmt, i),
                    (long)sqlite3_column_bytes(stmt, i)
                );
                rb_ary_push(list, str);
              }
              break;
            case SQLITE_NULL:
              rb_ary_push(list, Qnil);
              break;
            default:
              rb_raise(rb_eRuntimeError, "bad type");
          }
        }
      }
      break;
    case SQLITE_DONE:
      ctx->done_p = 1;
      return Qnil;
      break;
    default:
      CHECK(sqlite3_db_handle(ctx->st), value);
  }

  return list;
}

#types ⇒ Object

Return an array of the data types for each column in this statement. Note that this may execute the statement in order to obtain the metadata; this makes it a (potentially) expensive operation.



117
118
119
120
121
 
# File 'lib/sqlite3/statement.rb', line 117

def types
  must_be_open!
  get_metadata unless @types
  @types
end