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

remainder [R]

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

SQLite3::Statement.new(db, sql)

Source

# 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

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.

Public Instance Methods

active? ()

Source

# File lib/sqlite3/statement.rb, line 145
def active?
  !done?
end

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

bind_param(key, value)

Source

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),
                             RSTRING_LENINT(value),
                             SQLITE_TRANSIENT
                         );
            } else {


                if (UTF16_LE_P(value) || UTF16_BE_P(value)) {
                    status = sqlite3_bind_text16(
                                 ctx->st,
                                 index,
                                 (const char *)StringValuePtr(value),
                                 RSTRING_LENINT(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),
                                 RSTRING_LENINT(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;
}

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.

Type mapping from Ruby to SQLite3:

nil → NULL
Integer → INTEGER (or REAL when outside the signed int64 range)
Float → REAL
SQLite3::Blob → BLOB
String with Encoding::ASCII_8BIT (a.k.a. BINARY) → BLOB
String with Encoding::UTF_16LE or Encoding::UTF_16BE → TEXT (bound as UTF-16)
String (all other encodings) → TEXT (re-encoded to UTF-8 if necessary)
Any other type → raises RuntimeError

Note: if you have a string with only ASCII characters but ASCII-8BIT encoding and want it bound as TEXT rather than BLOB, re-encode it first:

stmt.bind_param(1, my_binary_str.encode(Encoding::UTF_8))

The value that this method binds stays bound when you execute or reset the statement. To change the value, bind a new value to the same parameter. To clear all values, call clear_bindings!.

See also bind_params.

bind_parameter_count

Source

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

Return the number of bind parameters

bind_params (*bind_vars)

Source

# File lib/sqlite3/statement.rb, line 74
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

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

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

Type mapping from Ruby to SQLite3:

nil → NULL
Integer → INTEGER (or REAL when outside the signed int64 range)
Float → REAL
SQLite3::Blob → BLOB
String with Encoding::ASCII_8BIT (a.k.a. BINARY) → BLOB
String with Encoding::UTF_16LE or Encoding::UTF_16BE → TEXT (bound as UTF-16)
String (all other encodings) → TEXT (re-encoded to UTF-8 if needed)
Any other type → raises RuntimeError

Note: a String with Encoding::ASCII_8BIT (BINARY) is always bound as a BLOB, even when its bytes are all valid ASCII. If you want such a string compared or stored as TEXT, re-encode it before binding:

stmt.bind_params(my_binary_str.encode(Encoding::UTF_8))

Example:

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

The values that this method binds stay bound when you execute or reset the statement. To change a value, bind a new value to the same parameter. To clear all values, call clear_bindings!.

clear_bindings!

Source

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

Sets the value of each parameter in the statement to NULL.

The values that you bind with bind_param, bind_params, or execute stay bound when you execute or reset the statement. Call this method to clear them.

Source

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

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

closed?

Source

static VALUE
closed_p(VALUE self)
{
    sqlite3StmtRubyPtr ctx;
    TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx);

    if (!ctx->st) { return Qtrue; }

    return Qfalse;
}

Returns true if the statement has been closed.

column_count

Source

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

Returns the number of columns to be returned for this statement

column_decltype(index)

Source

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_utf8_str_new_cstr(name); }
    return Qnil;
}

Get the declared type of the column at index, as a UTF-8 string. 0 based. Returns nil if the column has no declared type, such as an expression.

column_name(index)

Source

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

Get the column name at index. 0 based.

columns ()

Source

# File lib/sqlite3/statement.rb, line 152
def columns
  get_metadata unless @columns
  @columns
end

Return a frozen array of the column names for this statement, as frozen UTF-8 strings. Note that this may execute the statement in order to obtain the metadata; this makes it a (potentially) expensive operation.

database_name(column_index)

Source

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

Return the database name for the column at column_index

done?

Source

static VALUE
done_p(VALUE self)
{
    sqlite3StmtRubyPtr ctx;
    TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx);

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

returns true if all rows have been returned.

each () { |val| ... }

Source

# File lib/sqlite3/statement.rb, line 157
def each
  loop do
    val = step
    break self if done?
    yield val
  end
end

execute (*bind_vars) { |results| ... }

Source

# File lib/sqlite3/statement.rb, line 110
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. 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.

This method binds each argument to the statement with bind_params. The values stay bound after this method returns. If a later call to execute does not give a value for a parameter, the statement uses the value from the earlier call. To set all parameters to NULL before you execute the statement again, call clear_bindings!:

stmt = db.prepare( "select :a, :b" )
stmt.execute( a: 1, b: 2 ).to_a  # => [[1, 2]]
stmt.execute( a: 3 ).to_a        # => [[3, 2]]
stmt.clear_bindings!
stmt.execute( a: 3 ).to_a        # => [[3, nil]]

Example:

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

execute! (*bind_vars, &block)

Source

# File lib/sqlite3/statement.rb, line 138
def execute!(*bind_vars, &block)
  execute(*bind_vars)
  block ? each(&block) : to_a
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.

This method binds each argument to the statement with bind_params. The values stay bound after this method returns. For more information, see execute.

Example:

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

See also bind_params, execute.

expanded_sql

Source

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

Returns the SQL statement used to create this prepared statement, but with bind parameters substituted in to the statement.

memused

Source

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

Return the approximate number of bytes of heap memory used to store the prepared statement

named_params

Source

static VALUE
named_params(VALUE self)
{
    sqlite3StmtRubyPtr ctx;
    TypedData_Get_Struct(self, sqlite3StmtRuby, &statement_type, ctx);

    REQUIRE_LIVE_DB(ctx);
    REQUIRE_OPEN_STMT(ctx);

    int param_count = sqlite3_bind_parameter_count(ctx->st);
    VALUE params = rb_ary_new2(param_count);

    // The first host parameter has an index of 1, not 0.
    for (int i = 1; i <= param_count; i++) {
        const char *name = sqlite3_bind_parameter_name(ctx->st, i);
        // We ignore positional and anonymous parameters, and also null values, since there can be
        // gaps in the list.
        if (name && *name != '?') {
            VALUE param = interned_utf8_cstr(name + 1);
            rb_ary_push(params, param);
        }
    }
    return rb_obj_freeze(params);
}

Return the list of named parameters in the statement. This returns a frozen array of strings (without the leading prefix character). The values of this list can be used to bind parameters to the statement using bind_param. Positional (?NNN) and anonymous (?) parameters are excluded.

reset!

Source

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

Resets the statement. The library usually calls this method for you, but you can also call it yourself.

This method does not clear the values that you bound to the parameters. To clear them, call clear_bindings!.

sql

Source

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 the SQL statement used to create this prepared statement

stat (key = nil)

Source

# File lib/sqlite3/statement.rb, line 203
def stat key = nil
  if key
    stat_for(key)
  else
    stats_as_hash
  end
end

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

step ()

Source

static VALUE
step(VALUE self)
{
    sqlite3StmtRubyPtr ctx;
    sqlite3_stmt *stmt;
    rb_sqlite3_callback_frame_t frame;
    int value, length, callback_state;
    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;

    if (!sqlite3_stmt_busy(stmt)) {
        timespecclear(&ctx->db->stmt_deadline);
    }

    rb_sqlite3_push_callback_frame(&frame);
    value = sqlite3_step(stmt);
    if ((callback_state = rb_sqlite3_pop_callback_frame(&frame))) { rb_jump_tag(callback_state); }

    if (value == SQLITE_DONE) {
        ctx->done_p = 1;
        return Qnil;
    }

    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;
        default:
            sqlite3_reset(stmt);
            ctx->done_p = 0;
            CHECK(sqlite3_db_handle(ctx->st), value);
    }

    rb_obj_freeze(list);

    return list;
}

types ()

Source

# File lib/sqlite3/statement.rb, line 170
def types
  must_be_open!
  get_metadata unless @types
  @types
end

Return a frozen array of the declared types of the columns in this statement, as frozen UTF-8 strings with ASCII letters downcased. A column with no declared type, such as an expression, has a nil type. Note that this may execute the statement in order to obtain the metadata; this makes it a (potentially) expensive operation.