Repeated last row of query
I am using ruby-dbi to access a MS SQL database. The problem is th开发者_运维知识库at whenever I select more than one row from the DB, the result contains correct number of items, but all of them are the same, when they shouldn't be:
irb(main):001:0> require 'dbi'
=> true
irb(main):010:0> db=DBI.connect('dbi:ODBC:dataSource', 'userName', '****')
=> #<DBI::DatabaseHandle:0xff3df8 @handle=#<DBI::DBD::ODBC::Database:0xff3e88 @h
andle=#<ODBC::Database:0xff3f30>, @attr={}>, @trace_output=nil, @trace_mode=nil,
@convert_types=true, @driver_name="odbc">
irb(main):009:0> db.select_all('select distinct top 10 id from rawdata')
=> [[308], [308], [308], [308], [308], [308], [308], [308], [308], [308]]
The problem seems to be the as the one discussed here, but the solution proposed there (using alias
) didn't work for me (or maybe I misunderstood it).
How can I fix this?
I'm using DBI 0.4.5, and Ruby 1.9.2 on Windows.
That looks kind of strange because select_all
are supposed to return DBI::Row
objects. Try
rows = db.select_all('select distinct top 10 id from rawdata')
rows.each do |row|
printf "ID: %d\n", row["id"]
end
I can only recommend: Go for TinyTds
https://github.com/rails-sqlserver/tiny_tds
Its - easier to install and configure - faster - more stable
In the end, after realizing (at least partially) what was the post I linked in the question talking about, I modified the file row.rb from the source code of DBI:
I removed the code
if RUBY_VERSION =~ /^1\.9/
def __getobj__
@arr
end
def __setobj__(obj)
@delegate_dc_obj = @arr = obj
end
else
and the acommpanying end
and I also removed the inheritance: < DelegateClass(Array)
.
I had the same problem on a MS-SQL database with ruby 1.9.2p180 (2011-02-18)
This is how I solved it:
def myDBIexecute(dbhash,query)
begin
# open the connection
conn = DBI.connect('DBI:ODBC:'+dbhash['datasource'].to_s,dbhash['username'].to_s,dbhash['password'].to_s)
sth = conn.prepare(query)
sth.execute()
outputme=[]
while row = sth.fetch
mrow={}
sth.column_names.each{|aname|
mrow[aname]=row[aname].to_s
}
outputme << mrow
end
sth.finish
return outputme
rescue DBI::DatabaseError => e
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
conn.disconnect if conn
end
end
精彩评论