123456789_123456789_123456789_123456789_123456789_

Class: SQLite3::Statement

Relationships & Source Files
Super Chains via Extension / Inclusion / Inheritance
Instance Chain:
self, Enumerable
Inherits: Object
Defined in: lib/sqlite3/statement.rb,
ext/sqlite3/backup.c,
ext/sqlite3/database.c,
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.

Class Method Summary

Instance Attribute Summary

Instance Method Summary

Constructor Details

.new(db, sql) ⇒ Statement

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.

Raises:

  • (ArgumentError)
[ GitHub ] 

  


# 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


  







Instance Attribute Details



  

    #active?  ⇒ Boolean  (readonly)
  



  

    

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


  





  


  [ GitHub ]


  

  


# File 'lib/sqlite3/statement.rb', line 111



def active?
  !done?
end


  








  

    #closed?  ⇒ Boolean  (readonly)  



  

    

Alias for Database#closed?.


  









  

    #done?  ⇒ Boolean  (readonly)  



  

    

returns true if all rows have been returned.


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 360



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

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


  








  

    #remainder   (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.


  



  [ GitHub ]


  

  


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



attr_reader :remainder


  







Instance Method Details



  

    #bind_param(key, value)    



  

    

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.


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 221



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


  








  

    #bind_parameter_count    



  

    

Return the number of bind parameters


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 451



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


  








  

    #bind_params(*bind_vars)  
  



  

    

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, #bind_param, and #bind_params.


  



  [ GitHub ]


  

  


# 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


  








  

    #clear_bindings!    



  

    

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


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 339



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


  








  

    #close  
  



  

    

Alias for Database#close.


  









  

    #column_count    



  

    

Returns the number of columns to be returned for this statement


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 374



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


  








  

    #column_decltype(index)    



  

    

Get the column type at index.  0 based.


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 430



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


  








  

    #column_name(index)    



  

    

Get the column name at index.  0 based.


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 405



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


  








  

    #columns  
  



  

    

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.


  



  [ GitHub ]


  

  


# File 'lib/sqlite3/statement.rb', line 118



def columns
  get_metadata unless @columns
  @columns
end


  








  

    #database_name(column_index)    



  

    

Return the database name for the column at column_index


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 620



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


  








  

    #each  
  

  [ GitHub ]


  

  


# File 'lib/sqlite3/statement.rb', line 123



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


  








  

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



  

    

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





  [ GitHub ]


  

  


# 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!(*bind_vars, &block)  
  



  

    

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.


  



  [ GitHub ]


  

  


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



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


  








  

    #expanded_sql    



  

    

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


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 656



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


  








  

    #get_metadata   (private)
  



  

    

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.


  



  [ GitHub ]


  

  


# 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


  








  

    #memory_used    



  

    

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


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 601



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


  








  

    #must_be_open!  
  

  

    This method is for internal use only.
  



  

    

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


  



  [ GitHub ]


  

  


# File 'lib/sqlite3/statement.rb', line 142



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


  








  

    #prepare(db, sql)   (private)
  

  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 52



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

/* call-seq: stmt.close
 *
 * 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)
{


  








  

    #reset!    



  

    

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


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 317



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


  








  

    #sql    



  

    

Returns the SQL statement used to create this prepared statement


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 639



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


  








  

    #stat(key = nil)  
  



  

    

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


  



  [ GitHub ]


  

  


# File 'lib/sqlite3/statement.rb', line 167



def stat key = nil
  if key
    stat_for(key)
  else
    stats_as_hash
  end
end


  








  

    #stmt_stat(hash_or_key)   (private)  



  

    

Returns a Hash containing information about the statement.


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 579



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


  








  

    #stats_as_hash(hash)   (private)  



  

    

Returns a Hash containing information about the statement.


  



  [ GitHub ]


  

  


# File 'ext/sqlite3/statement.c', line 560



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


  








  

    #step(nPage)    



  

    

Alias for Backup#step.


  









  

    #types  
  



  

    

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.


  



  [ GitHub ]


  

  


# File 'lib/sqlite3/statement.rb', line 134



def types
  must_be_open!
  get_metadata unless @types
  @types
end