Chinaunix首页 | 论坛 | 博客
  • 博客访问: 395653
  • 博文数量: 69
  • 博客积分: 1984
  • 博客等级: 上尉
  • 技术积分: 953
  • 用 户 组: 普通用户
  • 注册时间: 2007-03-28 00:43
个人简介

学无所长,一事无成

文章分类

全部博文(69)

文章存档

2015年(19)

2014年(14)

2013年(9)

2012年(17)

2010年(10)

我的朋友

分类: Python/Ruby

2012-02-19 22:10:34

一、前面学习了 DBI::ROW ,接下来学习 DBI::StatementHandle。先看下这个例子:

  1. 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
代码中 sth 就是 DBI::StatementHandle 类的实例。让我们看看这个类有什么功能。

二、DBI::StatementHandle 解析
参考:

1、  , 
提取一条记录,如果后面跟有一个 block,则会循环调用 fetch 对结果集合进行遍历迭代。each 为 fetch 同义词。

源代码:

  1. # File lib/dbi/handles/statement.rb, line 207
  2.         def fetch(&p)
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?

  4.             if block_given? 
  5.                 while (res = @handle.fetch) != nil
  6.                     @row = @row.dup
  7.                     @row.set_values(res)
  8.                     yield @row
  9.                 end
  10.                 @handle.cancel
  11.                 @fetchable = false
  12.                 return nil
  13.             else
  14.                 res = @handle.fetch
  15.                 if res.nil?
  16.                     @handle.cancel
  17.                     @fetchable = false
  18.                 else
  19.                     @row = @row.dup
  20.                     @row.set_values(res)
  21.                     res = @row
  22.                 end
  23.                 return res
  24.             end
  25.         end
从源代码可以看出,如果传入一个 block ,则对 fetch 自身进行循环调用,直到结果记录取完。

例子:
  1. sth = dbh.execute(...)
  2. sth.fetch do |row| 
  3.     p row.by_field('xxxx')
  4. end

  5. #以下代码等价
  6. while ( row = sth.fetch ) != nil {
  7.     p row.by_field(xxxx'')
  8. }
2、

提取所有查询结果,本质上就是对 fetch 的循环调用

  1. # File lib/dbi/handles/statement.rb, line 325
  2.         def fetch_all
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             raise InterfaceError, "Statement must first be executed" unless @fetchable

  5.             cols = column_names
  6.             fetched_rows = []

  7.             begin
  8.                 while row = fetch do
  9.                     fetched_rows.push(row)
  10.                 end
  11.             rescue Exception
  12.             end

  13.             @handle.cancel
  14.             @fetchable = false

  15.             return fetched_rows
  16.         end

3、

同 fetch 类似,只是返回结果是 array 。实际上我们只要使用 fetch 就可以了,DBI::ROW 本身也有 to_a() 方法。
源代码:

  1. # File lib/dbi/handles/statement.rb, line 249
  2.         def fetch_array
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             raise InterfaceError, "Statement must first be executed" unless @fetchable

  5.             if block_given?
  6.                 while (res = @handle.fetch) != nil
  7.                     yield res
  8.                 end
  9.                 @handle.cancel
  10.                 @fetchable = false
  11.                 return nil
  12.             else
  13.                 res = @handle.fetch
  14.                 if res.nil?
  15.                     @handle.cancel
  16.                     @fetchable = false
  17.                 end
  18.                 return res
  19.             end
  20.         end

4、

同 fetch_array 类似,只是返回结果是 hash 结构。同理,也可以用 fetch 取代,DBI::ROW 也有 to_h 方法。

源代码:
  1. # File lib/dbi/handles/statement.rb, line 275
  2.         def fetch_hash
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             raise InterfaceError, "Statement must first be executed" unless @fetchable

  5.             cols = column_names

  6.             if block_given?
  7.                 while (row = @handle.fetch) != nil
  8.                     hash = {}
  9.                     row.each_with_index {|v,i| hash[cols[i]] = v}
  10.                     yield hash
  11.                 end
  12.                 @handle.cancel
  13.                 @fetchable = false
  14.                 return nil
  15.             else
  16.                 row = @handle.fetch
  17.                 if row.nil?
  18.                     @handle.cancel
  19.                     @fetchable = false
  20.                     return nil
  21.                 else
  22.                     hash = {}
  23.                     row.each_with_index {|v,i| hash[cols[i]] = v}
  24.                     return hash
  25.                 end
  26.             end
  27.         end
5、

提取 cnt 条记录,返回结果为 DBI::ROW 的数组。网页编程应该用处比较大

源代码:
  1. # File lib/dbi/handles/statement.rb, line 307
  2.         def fetch_many(cnt)
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             raise InterfaceError, "Statement must first be executed" unless @fetchable

  5.             cols = column_names
  6.             rows = @handle.fetch_many(cnt)
  7.             if rows.nil?
  8.                 @handle.cancel
  9.                 @fetchable = false
  10.                 return []
  11.             else
  12.                 return rows.collect{|r| tmp = @row.dup; tmp.set_values(r); tmp }
  13.             end
  14.         end

6、

类似于对文件的  seek 操作,可以向前,向后检索记录,direction 有如下三种:
  • SQL_FETCH_NEXT:  下一条记录.
  • SQL_FETCH_LAST:  上一条记录.
  • SQL_FETCH_RELATIVE:  the result at the offset.
如果驱动不支持,会报一个  错误。

  1. # File lib/dbi/handles/statement.rb, line 348
  2.         def fetch_scroll(direction, offset=1)
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             raise InterfaceError, "Statement must first be executed" unless @fetchable

  5.             row = @handle.fetch_scroll(direction, offset)
  6.             if row.nil?
  7.                 #@handle.cancel
  8.                 #@fetchable = false
  9.                 return nil
  10.             else
  11.                 @row.set_values(row)
  12.                 return @row
  13.             end
  14.         end

7、
看名字就知道干啥用的了,感觉没啥用

8、

返回 sth.execute 执行后,数据被修改的数量。
源代码:

  1. # File lib/dbi/handles/statement.rb, line 195
  2.         def rows
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             @handle.rows
  5.         end

9、

关闭  statement

源代码:

  1. # File lib/dbi/handles/statement.rb, line 134
  2.         def finish
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             @handle.finish
  5.             @handle = nil
  6.         end

10、

一般不会用到吧。

11、
获取查询语句获取结果的 column 名字

源代码:
  1. # File lib/dbi/handles/statement.rb, line 155
  2.         def column_names
  3.             raise InterfaceError, "Statement was already closed!" if @handle.nil?
  4.             return @cols unless @cols.nil?
  5.             @cols = @handle.column_info.collect {|col| col['name'] }
  6.         end

例子
  1. sth = dbh.execute(...)
  2. p sth.column_names

12、

获取字段信息,oci8 似乎尚未实现

查询了一下资料,发现是 dbi 的 bug,参考文章:

修复方法如下:
  1. Here is the (quick) fix I used to make it go away:

  2. --- columninfo.rb.orig 2010-09-07 17:22:34.000000000 -0500
  3. +++ columninfo.rb 2010-09-08 14:27:01.000000000 -0500
  4. @@ -39,7 +39,7 @@
  5.              @hash ||= Hash.new
  6.  
  7.              # coerce all strings to symbols
  8. - @hash.each_key do |x|
  9. + @hash.dup.each_key do |x|
  10.                  if x.kind_of? String
  11.                      sym = x.to_sym
  12.                      if @hash.has_key? sym

例子:

  1. sth = dbh.execute(stmt)

  2.    puts "Statement: #{stmt}"
  3.    if sth.column_names.size == 0 then
  4.      puts "Statement has no result set"
  5.      printf "Number of rows affected: %d\n", sth.rows
  6.    else
  7.      puts "Statement has a result set"
  8.      rows = sth.fetch_all
  9.      printf "Number of rows: %d\n", rows.size
  10.      printf "Number of columns: %d\n", sth.column_names.size
  11.      sth.column_info.each_with_index do |info, i|
  12.        printf "--- Column %d (%s) ---\n", i, info["name"]
  13.        printf "sql_type: %s\n", info["sql_type"]
  14.        printf "type_name: %s\n", info["type_name"]
  15.        printf "precision: %s\n", info["precision"]
  16.        printf "scale: %s\n", info["scale"]
  17.        printf "nullable: %s\n", info["nullable"]
  18.        printf "indexed: %s\n", info["indexed"]
  19.        printf "primary: %s\n", info["primary"]
  20.        printf "unique: %s\n", info["unique"]
  21.        printf "mysql_type: %s\n", info["mysql_type"]
  22.        printf "mysql_type_name: %s\n", info["mysql_type_name"]
  23.        printf "mysql_length: %s\n", info["mysql_length"]
  24.        printf "mysql_max_length: %s\n", info["mysql_max_length"]
  25.        printf "mysql_flags: %s\n", info["mysql_flags"]
  26.      end
  27.    end
  28.    sth.finish

13、

oci8 尚未实现

14、

此方法我用得较少,但很有用处,潜力很大,留待进一步研究。
DBI::SQL::PreparedStatement 值得深究,在此基础上可以编写自定义的 sql 解析,留待以后吧。

三、总结

DBI::StatementHandle 中最常用的就是 fetch 了。得益于完善的对象体系,相较 perl dbi 中一堆乱起八糟的 fetch 方法,ruby 中的代码要清晰简洁得多。

看看 perl 中有些什么 fetch :
fetchrow_arrayref
fetchrow_array
fetchrow_hashref
fetchall_arrayref
fetchall_hashref
是不是非常乱?



......






阅读(1476) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~