Class: DBI::StatementHandle

Inherits:
Handle
  • Object
show all
Includes:
Enumerable
Defined in:
lib/dbi/handles/statement.rb

Overview

StatementHandle is the interface the consumer sees after successfully issuing a DatabaseHandle#prepare. They may also be exposed through other methods that send statements to the database.

Almost all methods in this class will raise InterfaceError if the statement is already finished.

Instance Attribute Summary collapse

Attributes inherited from Handle

#convert_types, #handle, #trace_mode, #trace_output

Instance Method Summary collapse

Methods inherited from Handle

#func, #trace

Constructor Details

#initialize(handle, fetchable = false, prepared = true, convert_types = true, executed = false) ⇒ StatementHandle

Returns a new instance of StatementHandle.



17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
# File 'lib/dbi/handles/statement.rb', line 17

def initialize(handle, fetchable=false, prepared=true, convert_types=true, executed=false)
    super(handle)
    @fetchable = fetchable
    @prepared  = prepared     # only false if immediate execute was used
    @executed  = executed     # only true if the statement was already executed.
    @cols = nil
    @coltypes = nil
    @convert_types = convert_types

    if @fetchable
        @row = DBI::Row.new(column_names, column_types, nil, @convert_types)
    else
        @row = nil
    end
end

Instance Attribute Details

#dbh ⇒ Object

Returns the value of attribute dbh.



14
15
16
 
# File 'lib/dbi/handles/statement.rb', line 14

def dbh
  @dbh
end

#raise_error ⇒ Object

Returns the value of attribute raise_error.



15
16
17
 
# File 'lib/dbi/handles/statement.rb', line 15

def raise_error
  @raise_error
end

Instance Method Details

#[](attr) ⇒ Object

Get an attribute from the StatementHandle object.



358
359
360
361
 
# File 'lib/dbi/handles/statement.rb', line 358

def [] (attr)
    sanity_check
    @handle[attr]
end

#[]=(attr, val) ⇒ Object

Set an attribute on the StatementHandle object.



364
365
366
367
 
# File 'lib/dbi/handles/statement.rb', line 364

def []= (attr, val)
    sanity_check
    @handle[attr] = val
end

#bind_coltype(pos, type) ⇒ Object

Instruct successive calls to #fetch to cast the type returned into ‘type`, for row position `pos`. Like all bind_* calls, `pos` indexes starting at 1.

‘type` is an object with the DBI::Type calling convention.

This call must be called after #execute has successfully ran, otherwise it will raise InterfaceError.

Example:

# `foo` is an integer and this statement will return two rows. 
sth = dbh.prepare("select foo from bar") 
# would raise InterfaceError if called here
sth.execute

sth.bind_coltype(1, DBI::Type::Varchar) 
# would normally use DBI::Type::Integer and return a Fixnum. We'll make it a string.
sth.fetch => ["1"]

# Here we coerce it to Float.
sth.bind_coltype(1, DBI::Type::Float)
sth.fetch => [1.0]
sth.finish



72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/dbi/handles/statement.rb', line 72

def bind_coltype(pos, type)
    sanity_check({:prepared => true, :executed => true})
    
    coltypes = column_types

    if (pos - 1) < 0
        raise InterfaceError, "bind positions index starting at 1"
    end

    coltypes[pos-1] = type
    @row = DBI::Row.new(column_names, coltypes, nil, @convert_types)
end

#bind_param(param, value, attribs = nil) ⇒ Object

Just like BaseStatement#bind_param, but will attempt to convert the type if it’s supposed to, adhering to the DBD’s current ruleset.



89
90
91
92
93
94
95
96
97
 
# File 'lib/dbi/handles/statement.rb', line 89

def bind_param(param, value, attribs=nil)
    sanity_check({ :prepared => true })

    if @convert_types
        value = DBI::Utils::ConvParam.conv_param(dbh.driver_name, value)[0]
    end

    @handle.bind_param(param, value, attribs)
end

#cancel ⇒ Object

Cancel the query, closing any open result cursors and truncating any result sets.

The difference between this and #finish is that cancelled statements may be re-executed.



146
147
148
149
150
 
# File 'lib/dbi/handles/statement.rb', line 146

def cancel
    sanity_check
    @handle.cancel if @fetchable
    @fetchable = false
end

#column_info ⇒ Object

See BaseStatement#column_info.



183
184
185
186
 
# File 'lib/dbi/handles/statement.rb', line 183

def column_info
    sanity_check
    @handle.column_info.collect {|col| ColumnInfo.new(col) }
end

#column_names ⇒ Object

Obtains the column names for this query as an array.



155
156
157
158
159
 
# File 'lib/dbi/handles/statement.rb', line 155

def column_names
    sanity_check
    return @cols unless @cols.nil?
    @cols = @handle.column_info.collect {|col| col['name'] }
end

#column_types ⇒ Object

Obtain the type mappings for the columns in this query based on ColumnInfo data on the query.

The result will be a position-dependent array of objects that conform to the DBI::Type calling syntax.



168
169
170
171
172
173
174
175
176
177
178
 
# File 'lib/dbi/handles/statement.rb', line 168

def column_types
    sanity_check
    return @coltypes unless @coltypes.nil?
    @coltypes = @handle.column_info.collect do |col| 
        if col['dbi_type']
            col['dbi_type']
        else
            DBI::TypeUtil.type_name_to_module(col['type_name'])
        end
    end
end

#each(&p) ⇒ Object

Synonym for #fetch with a block.

Raises:



236
237
238
239
240
241
 
# File 'lib/dbi/handles/statement.rb', line 236

def each(&p)
    sanity_check({:fetchable => true, :prepared => true, :executed => true})
    raise InterfaceError, "No block given" unless block_given?

    fetch(&p)
end

#execute(*bindvars) ⇒ Object

Execute the statement.

This generally means that the statement will be sent to the database and some form of result cursor will be obtained, but is ultimately driver-dependent.

If arguments are supplied, these are fed to #bind_param.



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/dbi/handles/statement.rb', line 107

def execute(*bindvars)
    cancel     # cancel before 
    sanity_check({:prepared => true })

    if @convert_types
        bindvars = DBI::Utils::ConvParam.conv_param(dbh.driver_name, *bindvars)
    end

    @handle.bind_params(*bindvars)
    @handle.execute
    @fetchable = true
    @executed = true

    # TODO:?
    #if @row.nil?
    @row = DBI::Row.new(column_names, column_types, nil, @convert_types)
    #end
    return nil
end

#fetch(&p) ⇒ Object

See BaseStatement#fetch.

fetch can also take a block which will be applied to each row in a similar fashion to Enumerable#collect. See #each.



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
 
# File 'lib/dbi/handles/statement.rb', line 207

def fetch(&p)
    sanity_check({ :fetchable => true, :prepared => true, :executed => true })

    if block_given? 
        while (res = @handle.fetch) != nil
            @row = @row.dup
            @row.set_values(res)
            yield @row
        end
        @handle.cancel
        @fetchable = false
        return nil
    else
        res = @handle.fetch
        if res.nil?
            @handle.cancel
            @fetchable = false
        else
            @row = @row.dup
            @row.set_values(res)
            res = @row
        end
        return res
    end
end

#fetch_all ⇒ Object

Fetch the entire result set. Result is array of DBI::Row.



321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
 
# File 'lib/dbi/handles/statement.rb', line 321

def fetch_all
    sanity_check({:fetchable => true, :prepared => true, :executed => true})

    cols = column_names
    fetched_rows = []

    begin
        while row = fetch do
            fetched_rows.push(row.dup)
        end
    rescue Exception
    end

    @handle.cancel
    @fetchable = false

    return fetched_rows
end

#fetch_array ⇒ Object

Similar to #fetch, but returns Array of Array instead of Array of DBI::Row objects (and therefore does not perform type mapping). This is basically a way to get the raw data from the DBD.



248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
 
# File 'lib/dbi/handles/statement.rb', line 248

def fetch_array
    sanity_check({:fetchable => true, :prepared => true, :executed => true})

    if block_given? 
        while (res = @handle.fetch) != nil
            yield res
        end
        @handle.cancel
        @fetchable = false
        return nil
    else
        res = @handle.fetch
        if res.nil?
            @handle.cancel
            @fetchable = false
        end
        return res
    end
end

#fetch_hash ⇒ Object

Map the columns and results into an Array of Hash resultset.

No type conversion is performed here. Expect this to change in 0.6.0.



273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/dbi/handles/statement.rb', line 273

def fetch_hash
    sanity_check({:fetchable => true, :prepared => true, :executed => true})

    cols = column_names

    if block_given? 
        while (row = @handle.fetch) != nil
            hash = {}
            row.each_with_index {|v,i| hash[cols[i]] = v} 
            yield hash
        end
        @handle.cancel
        @fetchable = false
        return nil
    else
        row = @handle.fetch
        if row.nil?
            @handle.cancel
            @fetchable = false
            return nil
        else
            hash = {}
            row.each_with_index {|v,i| hash[cols[i]] = v} 
            return hash
        end
    end
end

#fetch_many(cnt) ⇒ Object

Fetch ‘cnt` rows. Result is array of DBI::Row



304
305
306
307
308
309
310
311
312
313
314
315
316
 
# File 'lib/dbi/handles/statement.rb', line 304

def fetch_many(cnt)
    sanity_check({:fetchable => true, :prepared => true, :executed => true})

    cols = column_names
    rows = @handle.fetch_many(cnt)
    if rows.nil? or rows.empty?
        @handle.cancel
        @fetchable = false
        return []
    else
        return rows.collect{|r| tmp = @row.dup; tmp.set_values(r); tmp }
    end
end

#fetch_scroll(direction, offset = 1) ⇒ Object

See BaseStatement#fetch_scroll.



343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/dbi/handles/statement.rb', line 343

def fetch_scroll(direction, offset=1)
    sanity_check({:fetchable => true, :prepared => true, :executed => true})

    row = @handle.fetch_scroll(direction, offset)
    if row.nil?
        #@handle.cancel
        #@fetchable = false
        return nil
    else
        @row.set_values(row)
        return @row
    end
end

#fetchable? ⇒ Boolean

Returns true if the statement is believed to return data upon #fetch.

The current reliability of this (and the concept in general) is suspect.

Returns:

  • (Boolean) 


43
44
45
 
# File 'lib/dbi/handles/statement.rb', line 43

def fetchable?
    @fetchable
end

#finish ⇒ Object

Finish the statement, causing the database to release all assets related to it (any result cursors, normally).

StatementHandles that have already been finished will normally be inoperable and unavailable for further use.



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

def finish
    sanity_check
    @handle.finish
    @handle = nil
end

#finished? ⇒ Boolean

Returns true if the StatementHandle has had #finish called on it, explicitly or otherwise.

Returns:

  • (Boolean) 


35
36
37
 
# File 'lib/dbi/handles/statement.rb', line 35

def finished?
    @handle.nil?
end

#rows ⇒ Object

Should return the row modified count as the result of statement execution.

However, some low-level drivers do not supply this information or supply misleading information (> 0 rows for read-only select statements, f.e.)



195
196
197
198
 
# File 'lib/dbi/handles/statement.rb', line 195

def rows
    sanity_check
    @handle.rows
end