- dbh = DBI.connect('dbi:OCI8:test','user','passwd')
sth = dbh.execute("select 'hello,world' from dual")
sth.fetch do |row|
p row
end
sth.finish
dbh.disconnect
二、DBI::StatementHandle 解析
参考:http://ruby-dbi.rubyforge.org/rdoc/index.html
1、fetch() {|@row| ...} , each(&p)
提取一条记录,如果后面跟有一个 block,则会循环调用 fetch 对结果集合进行遍历迭代。each 为 fetch 同义词。
源代码:
- # File lib/dbi/handles/statement.rb, line 207
- def fetch(&p)
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- 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
例子:
- sth = dbh.execute(...)
- sth.fetch do |row|
- p row.by_field('xxxx')
- end
- #以下代码等价
- while ( row = sth.fetch ) != nil {
- p row.by_field(xxxx'')
- }
提取所有查询结果,本质上就是对 fetch 的循环调用
- # File lib/dbi/handles/statement.rb, line 325
- def fetch_all
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- raise InterfaceError, "Statement must first be executed" unless @fetchable
- cols = column_names
- fetched_rows = []
- begin
- while row = fetch do
- fetched_rows.push(row)
- end
- rescue Exception
- end
- @handle.cancel
- @fetchable = false
- return fetched_rows
- end
3、fetch_array() {|res| ...}
同 fetch 类似,只是返回结果是 array 。实际上我们只要使用 fetch 就可以了,DBI::ROW 本身也有 to_a() 方法。
源代码:
- # File lib/dbi/handles/statement.rb, line 249
- def fetch_array
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- raise InterfaceError, "Statement must first be executed" unless @fetchable
- 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
4、fetch_hash() {|hash| ...}
同 fetch_array 类似,只是返回结果是 hash 结构。同理,也可以用 fetch 取代,DBI::ROW 也有 to_h 方法。
源代码:
- # File lib/dbi/handles/statement.rb, line 275
- def fetch_hash
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- raise InterfaceError, "Statement must first be executed" unless @fetchable
- 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
提取 cnt 条记录,返回结果为 DBI::ROW 的数组。网页编程应该用处比较大
源代码:
- # File lib/dbi/handles/statement.rb, line 307
- def fetch_many(cnt)
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- raise InterfaceError, "Statement must first be executed" unless @fetchable
- cols = column_names
- rows = @handle.fetch_many(cnt)
- if rows.nil?
- @handle.cancel
- @fetchable = false
- return []
- else
- return rows.collect{|r| tmp = @row.dup; tmp.set_values(r); tmp }
- end
- end
6、fetch_scroll(direction, offset=1)
类似于对文件的 seek 操作,可以向前,向后检索记录,direction 有如下三种:
- SQL_FETCH_NEXT: fetch 下一条记录.
- SQL_FETCH_LAST: fetch 上一条记录.
- SQL_FETCH_RELATIVE: fetch the result at the offset.
- # File lib/dbi/handles/statement.rb, line 348
- def fetch_scroll(direction, offset=1)
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- raise InterfaceError, "Statement must first be executed" unless @fetchable
- row = @handle.fetch_scroll(direction, offset)
- if row.nil?
- #@handle.cancel
- #@fetchable = false
- return nil
- else
- @row.set_values(row)
- return @row
- end
- end
7、fetchable?()
看名字就知道干啥用的了,感觉没啥用
8、rows()
返回 sth.execute 执行后,数据被修改的数量。
源代码:
- # File lib/dbi/handles/statement.rb, line 195
- def rows
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- @handle.rows
- end
9、finish()
关闭 statement
源代码:
- # File lib/dbi/handles/statement.rb, line 134
- def finish
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- @handle.finish
- @handle = nil
- end
10、finished?()
一般不会用到吧。
11、column_names()
获取查询语句获取结果的 column 名字
源代码:
- # File lib/dbi/handles/statement.rb, line 155
- def column_names
- raise InterfaceError, "Statement was already closed!" if @handle.nil?
- return @cols unless @cols.nil?
- @cols = @handle.column_info.collect {|col| col['name'] }
- end
例子
- sth = dbh.execute(...)
- p sth.column_names
12、column_info()
获取字段信息,oci8 似乎尚未实现
查询了一下资料,发现是 dbi 的 bug,参考文章:
http://rubyforge.org/tracker/index.php?func=detail&aid=28548&group_id=234&atid=967
修复方法如下:
- Here is the (quick) fix I used to make it go away:
- --- columninfo.rb.orig 2010-09-07 17:22:34.000000000 -0500
- +++ columninfo.rb 2010-09-08 14:27:01.000000000 -0500
- @@ -39,7 +39,7 @@
- @hash ||= Hash.new
-
- # coerce all strings to symbols
- - @hash.each_key do |x|
- + @hash.dup.each_key do |x|
- if x.kind_of? String
- sym = x.to_sym
- if @hash.has_key? sym
例子:http://www.kitebird.com/articles/ruby-dbi.html
- sth = dbh.execute(stmt)
- puts "Statement: #{stmt}"
- if sth.column_names.size == 0 then
- puts "Statement has no result set"
- printf "Number of rows affected: %d\n", sth.rows
- else
- puts "Statement has a result set"
- rows = sth.fetch_all
- printf "Number of rows: %d\n", rows.size
- printf "Number of columns: %d\n", sth.column_names.size
- sth.column_info.each_with_index do |info, i|
- printf "--- Column %d (%s) ---\n", i, info["name"]
- printf "sql_type: %s\n", info["sql_type"]
- printf "type_name: %s\n", info["type_name"]
- printf "precision: %s\n", info["precision"]
- printf "scale: %s\n", info["scale"]
- printf "nullable: %s\n", info["nullable"]
- printf "indexed: %s\n", info["indexed"]
- printf "primary: %s\n", info["primary"]
- printf "unique: %s\n", info["unique"]
- printf "mysql_type: %s\n", info["mysql_type"]
- printf "mysql_type_name: %s\n", info["mysql_type_name"]
- printf "mysql_length: %s\n", info["mysql_length"]
- printf "mysql_max_length: %s\n", info["mysql_max_length"]
- printf "mysql_flags: %s\n", info["mysql_flags"]
- end
- end
- sth.finish
13、column_types()
oci8 尚未实现
14、bind_param(param, value, attribs=nil)
此方法我用得较少,但很有用处,潜力很大,留待进一步研究。
DBI::SQL::PreparedStatement 值得深究,在此基础上可以编写自定义的 sql 解析,留待以后吧。
三、总结
DBI::StatementHandle 中最常用的就是 fetch 了。得益于完善的对象体系,相较 perl dbi 中一堆乱起八糟的 fetch 方法,ruby 中的代码要清晰简洁得多。
看看 perl 中有些什么 fetch :
fetchrow_arrayref
fetchrow_array
fetchrow_hashref
fetchall_arrayref
fetchall_hashref
是不是非常乱?
......是不是非常乱?