Class DBI::StatementHandle
In: lib/dbi/handles/statement.rb
Parent: Handle

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.

Methods

Included Modules

Enumerable

Attributes

dbh  [RW] 
raise_error  [RW] 

Public Class methods

[Source]

    # File lib/dbi/handles/statement.rb, line 17
17:         def initialize(handle, fetchable=false, prepared=true, convert_types=true, executed=false)
18:             super(handle)
19:             @fetchable = fetchable
20:             @prepared  = prepared     # only false if immediate execute was used
21:             @executed  = executed     # only true if the statement was already executed.
22:             @cols = nil
23:             @coltypes = nil
24:             @convert_types = convert_types
25: 
26:             if @fetchable
27:                 @row = DBI::Row.new(column_names, column_types, nil, @convert_types)
28:             else
29:                 @row = nil
30:             end
31:         end

Public Instance methods

Get an attribute from the StatementHandle object.

[Source]

     # File lib/dbi/handles/statement.rb, line 358
358:         def [] (attr)
359:             sanity_check
360:             @handle[attr]
361:         end

Set an attribute on the StatementHandle object.

[Source]

     # File lib/dbi/handles/statement.rb, line 364
364:         def []= (attr, val)
365:             sanity_check
366:             @handle[attr] = val
367:         end

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

[Source]

    # File lib/dbi/handles/statement.rb, line 72
72:         def bind_coltype(pos, type)
73:             sanity_check({:prepared => true, :executed => true})
74:             
75:             coltypes = column_types
76: 
77:             if (pos - 1) < 1
78:                 raise InterfaceError, "bind positions index starting at 1"
79:             end
80: 
81:             coltypes[pos-1] = type
82:             @row = DBI::Row.new(column_names, coltypes, nil, @convert_types)
83:         end

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

[Source]

    # File lib/dbi/handles/statement.rb, line 89
89:         def bind_param(param, value, attribs=nil)
90:             sanity_check({ :prepared => true })
91: 
92:             if @convert_types
93:                 value = DBI::Utils::ConvParam.conv_param(dbh.driver_name, value)[0]
94:             end
95: 
96:             @handle.bind_param(param, value, attribs)
97:         end

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.

[Source]

     # File lib/dbi/handles/statement.rb, line 146
146:         def cancel
147:             sanity_check
148:             @handle.cancel if @fetchable
149:             @fetchable = false
150:         end

See BaseStatement#column_info.

[Source]

     # File lib/dbi/handles/statement.rb, line 183
183:         def column_info
184:             sanity_check
185:             @handle.column_info.collect {|col| ColumnInfo.new(col) }
186:         end

Obtains the column names for this query as an array.

[Source]

     # File lib/dbi/handles/statement.rb, line 155
155:         def column_names
156:             sanity_check
157:             return @cols unless @cols.nil?
158:             @cols = @handle.column_info.collect {|col| col['name'] }
159:         end

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.

[Source]

     # File lib/dbi/handles/statement.rb, line 168
168:         def column_types
169:             sanity_check
170:             return @coltypes unless @coltypes.nil?
171:             @coltypes = @handle.column_info.collect do |col| 
172:                 if col['dbi_type']
173:                     col['dbi_type']
174:                 else
175:                     DBI::TypeUtil.type_name_to_module(col['type_name'])
176:                 end
177:             end
178:         end

Synonym for fetch with a block.

[Source]

     # File lib/dbi/handles/statement.rb, line 236
236:         def each(&p)
237:             sanity_check({:fetchable => true, :prepared => true, :executed => true})
238:             raise InterfaceError, "No block given" unless block_given?
239: 
240:             fetch(&p)
241:         end

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.

[Source]

     # File lib/dbi/handles/statement.rb, line 107
107:         def execute(*bindvars)
108:             cancel     # cancel before 
109:             sanity_check({:prepared => true })
110: 
111:             if @convert_types
112:                 bindvars = DBI::Utils::ConvParam.conv_param(dbh.driver_name, *bindvars)
113:             end
114: 
115:             @handle.bind_params(*bindvars)
116:             @handle.execute
117:             @fetchable = true
118:             @executed = true
119: 
120:             # TODO:?
121:             #if @row.nil?
122:             @row = DBI::Row.new(column_names, column_types, nil, @convert_types)
123:             #end
124:             return nil
125:         end

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.

[Source]

     # File lib/dbi/handles/statement.rb, line 207
207:         def fetch(&p)
208:             sanity_check({ :fetchable => true, :prepared => true, :executed => true })
209: 
210:             if block_given? 
211:                 while (res = @handle.fetch) != nil
212:                     @row = @row.dup
213:                     @row.set_values(res)
214:                     yield @row
215:                 end
216:                 @handle.cancel
217:                 @fetchable = false
218:                 return nil
219:             else
220:                 res = @handle.fetch
221:                 if res.nil?
222:                     @handle.cancel
223:                     @fetchable = false
224:                 else
225:                     @row = @row.dup
226:                     @row.set_values(res)
227:                     res = @row
228:                 end
229:                 return res
230:             end
231:         end

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

[Source]

     # File lib/dbi/handles/statement.rb, line 321
321:         def fetch_all
322:             sanity_check({:fetchable => true, :prepared => true, :executed => true})
323: 
324:             cols = column_names
325:             fetched_rows = []
326: 
327:             begin
328:                 while row = fetch do
329:                     fetched_rows.push(row)
330:                 end
331:             rescue Exception
332:             end
333: 
334:             @handle.cancel
335:             @fetchable = false
336: 
337:             return fetched_rows
338:         end

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.

[Source]

     # File lib/dbi/handles/statement.rb, line 248
248:         def fetch_array
249:             sanity_check({:fetchable => true, :prepared => true, :executed => true})
250: 
251:             if block_given? 
252:                 while (res = @handle.fetch) != nil
253:                     yield res
254:                 end
255:                 @handle.cancel
256:                 @fetchable = false
257:                 return nil
258:             else
259:                 res = @handle.fetch
260:                 if res.nil?
261:                     @handle.cancel
262:                     @fetchable = false
263:                 end
264:                 return res
265:             end
266:         end

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.

[Source]

     # File lib/dbi/handles/statement.rb, line 273
273:         def fetch_hash
274:             sanity_check({:fetchable => true, :prepared => true, :executed => true})
275: 
276:             cols = column_names
277: 
278:             if block_given? 
279:                 while (row = @handle.fetch) != nil
280:                     hash = {}
281:                     row.each_with_index {|v,i| hash[cols[i]] = v} 
282:                     yield hash
283:                 end
284:                 @handle.cancel
285:                 @fetchable = false
286:                 return nil
287:             else
288:                 row = @handle.fetch
289:                 if row.nil?
290:                     @handle.cancel
291:                     @fetchable = false
292:                     return nil
293:                 else
294:                     hash = {}
295:                     row.each_with_index {|v,i| hash[cols[i]] = v} 
296:                     return hash
297:                 end
298:             end
299:         end

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

[Source]

     # File lib/dbi/handles/statement.rb, line 304
304:         def fetch_many(cnt)
305:             sanity_check({:fetchable => true, :prepared => true, :executed => true})
306: 
307:             cols = column_names
308:             rows = @handle.fetch_many(cnt)
309:             if rows.nil? or rows.empty?
310:                 @handle.cancel
311:                 @fetchable = false
312:                 return []
313:             else
314:                 return rows.collect{|r| tmp = @row.dup; tmp.set_values(r); tmp }
315:             end
316:         end

See BaseStatement#fetch_scroll.

[Source]

     # File lib/dbi/handles/statement.rb, line 343
343:         def fetch_scroll(direction, offset=1)
344:             sanity_check({:fetchable => true, :prepared => true, :executed => true})
345: 
346:             row = @handle.fetch_scroll(direction, offset)
347:             if row.nil?
348:                 #@handle.cancel
349:                 #@fetchable = false
350:                 return nil
351:             else
352:                 @row.set_values(row)
353:                 return @row
354:             end
355:         end

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

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

[Source]

    # File lib/dbi/handles/statement.rb, line 43
43:         def fetchable?
44:             @fetchable
45:         end

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.

[Source]

     # File lib/dbi/handles/statement.rb, line 134
134:         def finish
135:             sanity_check
136:             @handle.finish
137:             @handle = nil
138:         end

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

[Source]

    # File lib/dbi/handles/statement.rb, line 35
35:         def finished?
36:             @handle.nil?
37:         end

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

[Source]

     # File lib/dbi/handles/statement.rb, line 195
195:         def rows
196:             sanity_check
197:             @handle.rows
198:         end

Protected Instance methods

[Source]

     # File lib/dbi/handles/statement.rb, line 398
398:         def check_executed
399:             raise InterfaceError, "Statement hasn't been executed yet." unless @executed
400:         end

[Source]

     # File lib/dbi/handles/statement.rb, line 392
392:         def check_fetchable
393:             if !@fetchable and @raise_error
394:                 raise InterfaceError, "Statement does not have any data for fetching." 
395:             end
396:         end

[Source]

     # File lib/dbi/handles/statement.rb, line 388
388:         def check_prepared
389:             raise InterfaceError, "Statement wasn't prepared before." unless @prepared
390:         end

basic sanity checks for statements

[Source]

     # File lib/dbi/handles/statement.rb, line 403
403:         def check_statement(stmt)
404:             raise InterfaceError, "Statement is empty, or contains nothing but whitespace" if stmt !~ /\S/
405:         end

[Source]

     # File lib/dbi/handles/statement.rb, line 371
371:         def sanity_check(params={})
372:             raise InterfaceError, "Statement was already closed!" if @handle.nil?
373: 
374:             params.each_key do |key|
375:                 case key
376:                 when :fetchable
377:                     check_fetchable
378:                 when :executed
379:                     check_executed
380:                 when :prepared
381:                     check_prepared
382:                 when :statement
383:                     check_statement(params[:statement])
384:                 end
385:             end
386:         end

[Validate]