Class: SQLite3::Statement

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

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

#initialize(db, sql) ⇒ Statement

call-seq: SQLite3::Statement.new(db, sql)

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:



28
29
30
31
32
33
34
35
36
37
 
# 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

#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:



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

def active?
  !done?
end

#bind_param(key, value) ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/sqlite3/ffi/statement.rb', line 87

def bind_param(key, value)
  require_live_db
  require_open_stmt

  case key
  when Symbol, String
    key = key.to_s
    key = ":" + key if key[0] != ":"
    index = FFI::CApi.sqlite3_bind_parameter_index(@stmt, key)
  else
    index = key.to_i
  end

  if index == 0
    raise SQLite3::Exception, "no such bind parameter"
  end

  case value
  when String
    if value.is_a?(Blob) || value.encoding == Encoding::BINARY
      status = FFI::CApi.sqlite3_bind_blob(@stmt, index, value, value.bytesize, FFI::CApi::SQLITE_TRANSIENT)
    elsif value.encoding == Encoding::UTF_16LE || value.encoding == Encoding::UTF_16BE
      status = FFI::CApi.sqlite3_bind_text16(@stmt, index, value, value.bytesize, FFI::CApi::SQLITE_TRANSIENT)
    else
      if value.encoding != Encoding::UTF_8 && value.encoding != Encoding::US_ASCII
        value = value.encode(Encoding::UTF_8)
      end
      status = FFI::CApi.sqlite3_bind_text(@stmt, index, value, value.bytesize, FFI::CApi::SQLITE_TRANSIENT)
    end
  when Float
    status = FFI::CApi.sqlite3_bind_double(@stmt, index, value)
  when Integer
    status = FFI::CApi.sqlite3_bind_int64(@stmt, index, value)
  when NilClass
    status = FFI::CApi.sqlite3_bind_null(@stmt, index)
  else
    raise RuntimeError, "can't prepare #{value.class.name}"
  end

  FFI.check(FFI::CApi.sqlite3_db_handle(@stmt), status)

  self
end

#bind_parameter_count ⇒ Object 



174
175
176
177
178
179
 
# File 'lib/sqlite3/ffi/statement.rb', line 174

def bind_parameter_count
  require_live_db
  require_open_stmt

  FFI::CApi.sqlite3_bind_parameter_count(@stmt)
end

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



52
53
54
55
56
57
58
59
60
61
62
 
# 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! ⇒ Object 



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

def clear_bindings!
  require_live_db
  require_open_stmt

  FFI::CApi.sqlite3_clear_bindings(@stmt)
  @done = false
end

#close ⇒ Object 



19
20
21
22
23
24
 
# File 'lib/sqlite3/ffi/statement.rb', line 19

def close
  require_open_stmt

  FFI::CApi.sqlite3_finalize(@stmt)
  @stmt = nil
end

#closed? ⇒ Boolean

Returns:



26
27
28
 
# File 'lib/sqlite3/ffi/statement.rb', line 26

def closed?
  @stmt.nil? || @stmt.null?
end

#column_count ⇒ Object 



152
153
154
155
156
157
 
# File 'lib/sqlite3/ffi/statement.rb', line 152

def column_count
  require_live_db
  require_open_stmt

  FFI::CApi.sqlite3_column_count(@stmt)
end

#column_decltype(index) ⇒ Object 



167
168
169
170
171
172
 
# File 'lib/sqlite3/ffi/statement.rb', line 167

def column_decltype(index)
  require_live_db
  require_open_stmt

  FFI::CApi.sqlite3_column_decltype(@stmt, index)
end

#column_name(index) ⇒ Object 



159
160
161
162
163
164
165
 
# File 'lib/sqlite3/ffi/statement.rb', line 159

def column_name(index)
  require_live_db
  require_open_stmt

  name = FFI::CApi.sqlite3_column_name(@stmt, index)
  name ? FFI.interned_utf8_cstr(name) : nil
end

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



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

def columns
  get_metadata unless @columns
  @columns
end

#done? ⇒ Boolean

Returns:



148
149
150
 
# File 'lib/sqlite3/ffi/statement.rb', line 148

def done?
  @done
end

#each ⇒ Object 



123
124
125
126
127
128
129
 
# 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| ... } ⇒ 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) 


78
79
80
81
82
83
84
85
86
87
88
 
# 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) ⇒ 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.



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

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

#expanded_sql ⇒ Object 



238
239
240
241
242
243
 
# File 'lib/sqlite3/ffi/statement.rb', line 238

def expanded_sql
  require_live_db
  require_open_stmt

  FFI::CApi.sqlite3_expanded_sql(@stmt).force_encoding(Encoding::UTF_8).freeze
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

#named_params ⇒ Object 



181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
 
# File 'lib/sqlite3/ffi/statement.rb', line 181

def named_params
  require_live_db
  require_open_stmt

  param_count = FFI::CApi.sqlite3_bind_parameter_count(@stmt)
  params = []

  1.upto(param_count) do |i|
    name = FFI::CApi.sqlite3_bind_parameter_name(@stmt, i)
    if name && name[0] != "?"
      param = FFI.interned_utf8_cstr(name[1..])
      params << param
    end
  end
  params.freeze
end

#prepare(db, sql) ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
# File 'lib/sqlite3/ffi/statement.rb', line 3

def prepare(db, sql)
  sql = FFI.string_value(sql)

  @db = db # so can check if discarded
  db = db.instance_variable_get(:@db)
  stmt = ::FFI::MemoryPointer.new(:pointer)
  tail = ::FFI::MemoryPointer.new(:pointer)
  status = FFI::CApi.sqlite3_prepare_v2(db, sql, sql.bytesize, stmt, tail)
  FFI.check_prepare(db, status, sql)

  @stmt = stmt.read_pointer
  @db.instance_variable_set(:@stmt_deadline, nil)

  tail.read_pointer.read_string.force_encoding(Encoding::UTF_8)
end

#reset! ⇒ Object 



131
132
133
134
135
136
137
138
 
# File 'lib/sqlite3/ffi/statement.rb', line 131

def reset!
  require_live_db
  require_open_stmt

  FFI::CApi.sqlite3_reset(@stmt)
  @done = false
  self
end

#sql ⇒ Object 



231
232
233
234
235
236
 
# File 'lib/sqlite3/ffi/statement.rb', line 231

def sql
  require_live_db
  require_open_stmt

  FFI::CApi.sqlite3_sql(@stmt).force_encoding(Encoding::UTF_8).freeze
end

#stat(key = nil) ⇒ Object

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



167
168
169
170
171
172
173
 
# File 'lib/sqlite3/statement.rb', line 167

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

#stat_for(key) ⇒ Object 



215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/sqlite3/ffi/statement.rb', line 215

def stat_for(key)
  require_live_db
  require_open_stmt

  if !key.is_a?(Symbol)
    raise TypeError, "non-symbol given"
  end

  stat_type = STMT_STAT_SYMBOLS[key]
  if !stat_type
    raise ArgumentError, "unknown key: #{key}"
  end

  FFI::CApi.sqlite3_stmt_status(@stmt, stat_type, 0)
end

#stats_as_hash ⇒ Object 



206
207
208
209
210
211
212
213
 
# File 'lib/sqlite3/ffi/statement.rb', line 206

def stats_as_hash
  require_live_db
  require_open_stmt

  STMT_STAT_SYMBOLS.to_h do |k, stat_type|
    [k, FFI::CApi.sqlite3_stmt_status(@stmt, stat_type, 0)]
  end
end

#step ⇒ Object 



30
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/sqlite3/ffi/statement.rb', line 30

def step
  require_live_db
  require_open_stmt

  return nil if @done

  stmt = @stmt

  value = FFI::CApi.sqlite3_step(stmt)
  if FFI.rb_errinfo
    exception = FFI.rb_errinfo
    FFI.rb_errinfo = nil
    raise exception
  end

  length = FFI::CApi.sqlite3_column_count(stmt)
  list = []

  case value
  when FFI::CApi::SQLITE_ROW
    length.times do |i|
      case FFI::CApi.sqlite3_column_type(stmt, i)
      when FFI::CApi::SQLITE_INTEGER
        val = FFI::CApi.sqlite3_column_int64(stmt, i)
      when FFI::CApi::SQLITE_FLOAT
        val = FFI::CApi.sqlite3_column_double(stmt, i)
      when FFI::CApi::SQLITE_TEXT
        len = FFI::CApi.sqlite3_column_bytes(stmt, i)
        val = FFI::CApi.sqlite3_column_text(stmt, i).read_bytes(len).force_encoding(Encoding::UTF_8)
        if Encoding.default_internal
          val = val.encode(Encoding.default_internal)
        end
        val.freeze
      when FFI::CApi::SQLITE_BLOB
        len = FFI::CApi.sqlite3_column_bytes(stmt, i)
        val = FFI::CApi.sqlite3_column_blob(stmt, i).read_bytes(len)
        val.freeze
      when FFI::CApi::SQLITE_NULL
        val = nil
      else
        raise RuntimeError, "bad type"
      end
      list << val
    end
  when FFI::CApi::SQLITE_DONE
    @done = true
    return nil
  else
    FFI::CApi.sqlite3_reset(stmt)
    @done = false
    FFI.check(FFI::CApi.sqlite3_db_handle(stmt), value)
  end

  list.freeze
  list
end

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



134
135
136
137
138
 
# File 'lib/sqlite3/statement.rb', line 134

def types
  must_be_open!
  get_metadata unless @types
  @types
end