dbd, ado, and ms sql server

[jd33526]

I get this error when I try to run the following code. Anyone have any
ideas why it's not working?

c:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb:135:in `execute': Execute

(DBI:atabaseError)
OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server

Cannot create new connection because in manual or distributed
transactionmode.
HRESULT error code:0x80020009
Exception occurred. from
c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:768:in `execute'
from test.rb:12

///////////////////////////////////////////////////////////////////
code
///////////////////////////////////////////////////////////////////

sth=dbh.prepare("select * from products where id = '38'")
sth.execute

while row=sth.fetch do
manf_id = row[5]

tmp = dbh.prepare("select * from manufacturers where id = " +
manf_id.to_s)
tmp.execute #this is the line that fails

end

 

[rpardee]

I think the issue is that the connection behind dbh is in a
spew-out-records mode & you've got to go through the whole return set
before it will be willing to do other work.

If you open a second connection, you should be able to use that to
..execute your inner select there.

Another option is to populate an array or hash w/your manf_id's in the
loop, and then loop through that separately.

HTH,

-Roy

 

[jd33526]

I tried two connections and got the same error. I could collect all the
rows first, but that seems like an ugly hack. I use ado with python and
it doesn't have this problem so I think there is something wrong with
the ruby implementation.

Also, I'm trying to use rails (activerecord) with sql server and am
getting a similar error. I dug into it a bit and I think that the error
i posted above with pure dbd is causing the rails error. This makes me
think that maybe I'm doing something wrong because I assume someone
else somewhere is using rails with sql server and not getting this
error.

 

[rpardee]

Hmmm... That's strange. FWIW--in the below, if I change the line that
assigns to stmt2 so that it's is .prepared by db1, I get the
transaction mode error you mention. As it is, it works.

require 'dbi'

CONNECTSTRING = "dbi:ADO:" +
"provider=SQLOLEDB.1;" +
"Integrated Security=SSPI;" +
"Persist Security Info=False;" +
"Initial Catalog=MENU;" +
"Data Source=."

ALTCONNECTSTRING = "dbi:ADO:" +
"provider=SQLOLEDB.1;" +
"Integrated Security=SSPI;" +
"Persist Security Info=False;" +
"Initial Catalog=MENU;" +
"Data Source=."

DBI.connect(CONNECTSTRING) do |db1|
db2 = DBI.connect(CONNECTSTRING)
stmt1 = db1.prepare("select * from statuses where status > ?")
stmt2 = db2.prepare("insert into drop_me (status) values (?)")
stmt1.execute(12)
stmt1.fetch do |row|
puts row[0]
stmt2.execute(row[0])
end
db2.commit
db2.disconnect
end

puts "finished!"

 

[jd33526]

Thanks for all the help Roy. I've sort of figured it out. Apparently
you *have* to iterate over the stmt2 or else you get the error I got
above. Now let's see if I can get rails working with ado and sql
server!

# connect to a datbase
db1 = DBI.connect(CONNECTSTRING)
db2 = DBI.connect(CONNECTSTRING)
stmt1 = db1.prepare("select top 5 * from products")

stmt1.execute
while row = stmt1.fetch do
puts row[0]
stmt2 = db2.prepare("select top 5 * from manufacturers")
stmt2.execute

#uncomment and error will go away
#stmt2.fetch do |row2|
# puts row2[0]
#end
end

db1.disconnect
db2.disconnect
gets

puts "finished!"