There is something seriously wrong with DBI support for SQL server. I
have tried both DBI::ODBC and DBI:ADO and the following code always
fails.
---------- Code
require 'dbi'
host= "DEVELOPMENT_DATABASE_SERVER"
database="adventureworks2000"
username="someusername"
password="Somepwd"
db = DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data
Source=#{host};Initial Catalog=#{database};User
Id=#{username};Password=#{password};")
sql= 'Select FirstName, LastName from contact'
sth = db.prepare(sql)
sth.execute
# Print out each row
while row=sth.fetch do
p row
db.do("select * from contact where 1 = 2")
end
···
---------------------
This is the error message you get.
D:/InstantRails/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb:135:in
`execute': Execute (DBI::DatabaseError)
OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
Cannot create new connection because in manual or distributed
transaction mode.
HRESULT error code:0x80020009
The db.do statement always fails no matter what the query is. The
driver refuses to run more then one query at a time even if the query
does not return records.
I have seen other people report this problem because it seems to be
happening frequently in rails apps when the concurrency goes up.
Anyway at this stage it seems like DBI support for SQL server is
minimal at best.