class SQLite3::Statement
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.
Attributes
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.
Public Class Methods
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.
# File lib/sqlite3/statement.rb, line 28 def initialize(db, sql) raise ArgumentError, "prepare called on a closed database" if db.closed? sql = sql.encode(Encoding::UTF_8) if sql && sql.encoding != Encoding::UTF_8 @connection = db @columns = nil @types = nil @remainder = prepare db, sql end
Public Instance Methods
Returns true if the statement is currently active, meaning it has an open result set.
# File lib/sqlite3/statement.rb, line 111 def active? !done? end
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
.
static VALUE bind_param(VALUE self, VALUE key, VALUE value) { sqlite3StmtRubyPtr ctx; int status; int index; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(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 || rb_enc_get_index(value) == rb_ascii8bit_encindex() ) { status = sqlite3_bind_blob( ctx->st, index, (const char *)StringValuePtr(value), (int)RSTRING_LEN(value), SQLITE_TRANSIENT ); } else { if (UTF16_LE_P(value) || UTF16_BE_P(value)) { status = sqlite3_bind_text16( ctx->st, index, (const char *)StringValuePtr(value), (int)RSTRING_LEN(value), SQLITE_TRANSIENT ); } else { if (!UTF8_P(value) || !USASCII_P(value)) { value = rb_str_encode(value, rb_enc_from_encoding(rb_utf8_encoding()), 0, Qnil); } status = sqlite3_bind_text( ctx->st, index, (const char *)StringValuePtr(value), (int)RSTRING_LEN(value), SQLITE_TRANSIENT ); } } break; case T_BIGNUM: { sqlite3_int64 num64; if (bignum_to_int64(value, &num64)) { status = sqlite3_bind_int64(ctx->st, index, num64); break; } } 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; }
Return the number of bind parameters
static VALUE bind_parameter_count(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); return INT2NUM(sqlite3_bind_parameter_count(ctx->st)); }
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
.
# File lib/sqlite3/statement.rb, line 52 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
Resets the statement. This is typically done internally, though it might occasionally be necessary to manually reset the statement.
static VALUE clear_bindings_bang(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); sqlite3_clear_bindings(ctx->st); ctx->done_p = 0; return self; }
Closes the statement by finalizing the underlying statement handle. The statement must not be used after being closed.
static VALUE sqlite3_rb_close(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_OPEN_STMT(ctx); sqlite3_finalize(ctx->st); ctx->st = NULL; return self; }
Returns true if the statement has been closed.
static VALUE closed_p(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); if (!ctx->st) { return Qtrue; } return Qfalse; }
Returns the number of columns to be returned for this statement
static VALUE column_count(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); return INT2NUM(sqlite3_column_count(ctx->st)); }
Get the column type at index
. 0 based.
static VALUE column_decltype(VALUE self, VALUE index) { sqlite3StmtRubyPtr ctx; const char *name; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); name = sqlite3_column_decltype(ctx->st, (int)NUM2INT(index)); if (name) { return rb_str_new2(name); } return Qnil; }
Get the column name at index
. 0 based.
static VALUE column_name(VALUE self, VALUE index) { sqlite3StmtRubyPtr ctx; const char *name; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); name = sqlite3_column_name(ctx->st, (int)NUM2INT(index)); VALUE ret = Qnil; if (name) { ret = interned_utf8_cstr(name); } return ret; }
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.
# File lib/sqlite3/statement.rb, line 118 def columns get_metadata unless @columns @columns end
Return the database name for the column at column_index
static VALUE database_name(VALUE self, VALUE index) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); return SQLITE3_UTF8_STR_NEW2( sqlite3_column_database_name(ctx->st, NUM2INT(index))); }
returns true if all rows have been returned.
static VALUE done_p(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); if (ctx->done_p) { return Qtrue; } return Qfalse; }
# File lib/sqlite3/statement.rb, line 123 def each loop do val = step break self if done? yield val end end
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!
.
# File lib/sqlite3/statement.rb, line 78 def execute(*bind_vars) reset! if active? || done? bind_params(*bind_vars) unless bind_vars.empty? results = @connection.build_result_set self step if column_count == 0 yield results if block_given? results end
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
.
# File lib/sqlite3/statement.rb, line 104 def execute!(*bind_vars, &block) execute(*bind_vars) block ? each(&block) : to_a end
Returns the SQL statement used to create this prepared statement, but with bind parameters substituted in to the statement.
static VALUE get_expanded_sql(VALUE self) { sqlite3StmtRubyPtr ctx; char *expanded_sql; VALUE rb_expanded_sql; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); expanded_sql = sqlite3_expanded_sql(ctx->st); rb_expanded_sql = rb_obj_freeze(SQLITE3_UTF8_STR_NEW2(expanded_sql)); sqlite3_free(expanded_sql); return rb_expanded_sql; }
Return the approximate number of bytes of heap memory used to store the prepared statement
static VALUE memused(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); return INT2NUM(sqlite3_stmt_status(ctx->st, SQLITE_STMTSTATUS_MEMUSED, 0)); }
Resets the statement. This is typically done internally, though it might occasionally be necessary to manually reset the statement.
static VALUE reset_bang(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); sqlite3_reset(ctx->st); ctx->done_p = 0; return self; }
Returns the SQL statement used to create this prepared statement
static VALUE get_sql(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); return rb_obj_freeze(SQLITE3_UTF8_STR_NEW2(sqlite3_sql(ctx->st))); }
Returns a Hash containing information about the statement. The contents of the hash are implementation specific and may change in the future without notice. The hash includes information about internal statistics about the statement such as:
- +fullscan_steps+: the number of times that SQLite has stepped forward
in a table as part of a full table scan
- +sorts+: the number of sort operations that have occurred - +autoindexes+: the number of rows inserted into transient indices
that were created automatically in order to help joins run faster
- +vm_steps+: the number of virtual machine operations executed by the
prepared statement
- +reprepares+: the number of times that the prepare statement has been
automatically regenerated due to schema changes or changes to bound parameters that might affect the query plan
- +runs+: the number of times that the prepared statement has been run - +filter_misses+: the number of times that the Bloom filter returned
a find, and thus the join step had to be processed as normal
- +filter_hits+: the number of times that a join step was bypassed
because a Bloom filter returned not-found
# File lib/sqlite3/statement.rb, line 167 def stat key = nil if key stat_for(key) else stats_as_hash end end
static VALUE step(VALUE self) { sqlite3StmtRubyPtr ctx; sqlite3_stmt *stmt; int value, length; VALUE list; rb_encoding *internal_encoding; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); if (ctx->done_p) { return Qnil; } internal_encoding = rb_default_internal_encoding(); stmt = ctx->st; value = sqlite3_step(stmt); if (rb_errinfo() != Qnil) { /* some user defined function was invoked as a callback during step and * it raised an exception that has been suppressed until step returns. * Now re-raise it. */ VALUE exception = rb_errinfo(); rb_set_errinfo(Qnil); rb_exc_raise(exception); } length = sqlite3_column_count(stmt); list = rb_ary_new2((long)length); switch (value) { case SQLITE_ROW: { int i; for (i = 0; i < length; i++) { VALUE val; switch (sqlite3_column_type(stmt, i)) { case SQLITE_INTEGER: val = LL2NUM(sqlite3_column_int64(stmt, i)); break; case SQLITE_FLOAT: val = rb_float_new(sqlite3_column_double(stmt, i)); break; case SQLITE_TEXT: { val = rb_utf8_str_new( (const char *)sqlite3_column_text(stmt, i), (long)sqlite3_column_bytes(stmt, i) ); if (internal_encoding) { val = rb_str_export_to_enc(val, internal_encoding); } rb_obj_freeze(val); } break; case SQLITE_BLOB: { val = rb_str_new( (const char *)sqlite3_column_blob(stmt, i), (long)sqlite3_column_bytes(stmt, i) ); rb_obj_freeze(val); } break; case SQLITE_NULL: val = Qnil; break; default: rb_raise(rb_eRuntimeError, "bad type"); } rb_ary_store(list, (long)i, val); } } break; case SQLITE_DONE: ctx->done_p = 1; return Qnil; break; default: sqlite3_reset(stmt); ctx->done_p = 0; CHECK(sqlite3_db_handle(ctx->st), value); } rb_obj_freeze(list); return list; }
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.
# File lib/sqlite3/statement.rb, line 134 def types must_be_open! get_metadata unless @types @types end
Private Instance Methods
A convenience method for obtaining the metadata about the query. Note that this will actually execute the SQL, which means it can be a (potentially) expensive operation.
# File lib/sqlite3/statement.rb, line 180 def get_metadata @columns = Array.new(column_count) do |column| column_name column end @types = Array.new(column_count) do |column| val = column_decltype(column) val&.downcase end end
static VALUE prepare(VALUE self, VALUE db, VALUE sql) { sqlite3RubyPtr db_ctx = sqlite3_database_unwrap(db); sqlite3StmtRubyPtr ctx; const char *tail = NULL; int status; StringValue(sql); TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); /* Dereferencing a pointer to the database struct will be faster than accessing it through the * instance variable @connection. The struct pointer is guaranteed to be live because instance * variable will keep it from being GCed. */ ctx->db = db_ctx; #ifdef HAVE_SQLITE3_PREPARE_V2 status = sqlite3_prepare_v2( #else status = sqlite3_prepare( #endif db_ctx->db, (const char *)StringValuePtr(sql), (int)RSTRING_LEN(sql), &ctx->st, &tail ); CHECK_PREPARE(db_ctx->db, status, StringValuePtr(sql)); timespecclear(&db_ctx->stmt_deadline); return rb_utf8_str_new_cstr(tail); }
Returns a Hash containing information about the statement.
static VALUE stat_for(VALUE self, VALUE key) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); if (SYMBOL_P(key)) { size_t value = stmt_stat_internal(key, ctx->st); return SIZET2NUM(value); } else { rb_raise(rb_eTypeError, "non-symbol given"); } }
Returns a Hash containing information about the statement.
static VALUE stats_as_hash(VALUE self) { sqlite3StmtRubyPtr ctx; TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx); REQUIRE_LIVE_DB(ctx); REQUIRE_OPEN_STMT(ctx); VALUE arg = rb_hash_new(); stmt_stat_internal(arg, ctx->st); return arg; }