一、前面学习了 DBI::ROW ，接下来学习 DBI::StatementHandle。先看下这个例子：
-
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 同义词。
源代码：
-
# 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
从源代码可以看出，如果传入一个 block ，则对 fetch 自身进行循环调用，直到结果记录取完。
例子：
-
sth = dbh.execute(...)
-
sth.fetch do |row|
-
p row.by_field('xxxx')
-
end
-
-
#以下代码等价
-
while ( row = sth.fetch ) != nil {
-
p row.by_field(xxxx'')
-
}
2、
提取所有查询结果，本质上就是对 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 。实际上我们只要使用 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_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
5、
提取 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、
类似于对文件的 seek 操作，可以向前，向后检索记录，direction 有如下三种：
-
SQL_FETCH_NEXT: 下一条记录.
-
SQL_FETCH_LAST: 上一条记录.
-
SQL_FETCH_RELATIVE: 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、
看名字就知道干啥用的了，感觉没啥用
8、
返回 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、
关闭 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、
一般不会用到吧。
11、
获取查询语句获取结果的 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、
获取字段信息，oci8 似乎尚未实现
查询了一下资料，发现是 dbi 的 bug，参考文章：
修复方法如下：
-
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
例子：
-
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、
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
是不是非常乱？
......
阅读(1488) | 评论(0) | 转发(0) |
