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.
dbh | [RW] | |
raise_error | [RW] |
# 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
Get an attribute from the StatementHandle object.
# 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.
# 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
# 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.
# 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.
# 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.
# 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.
# 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.
# 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
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.
# 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.
# 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.
# 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.
# 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.
# 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
# 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.
# 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
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.
# 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.
# 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.)
# File lib/dbi/handles/statement.rb, line 195 195: def rows 196: sanity_check 197: @handle.rows 198: end
# 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
# 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
# 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
# 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
# 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