New 2.3.2 Version (NO DBI)
Ken Collins
The newer and faster adapter is out! My tests now run 25% faster!
It now only uses RubyODBC and removes the DBI/DBD-ODBC dependencies. Thanks goes out to Erik Bryn for putting us in the right direction! If you care to go over the commits, you can see where I picked up from his work and started abstracting the low-level transport modes to a few key methods that can introspect on the connections mode. So even tho ODBC is the only way to go now, the plan is to open this up to any implementation that others care to use and submit work for. One that comes to mind is supporting and :ado_net mode for the IronRuby guys.
Remember, if you are using the adapter in ADO mode and the old DBI hacks, stay at version 2.3.1.
- Cheers,
Ken
Ken Collins
BTW, I released a 2.3.3 version shortly afterward that fixed a big bug that blew up my day job app. It was a mistake to try and cache info schema looks ups at the single class level vs. per connection instance. This blew up in our app because we connect to multiple DB's.
- Ken
> --
> You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
> To post to this group, send email to rails-sqlse...@googlegroups.com.
> To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.
>
Rich Cox
Ken Collins
Chris
to hack a way around DBI and use the ODBC connection directly so we
could call SP's that return multiple result sets. So does this mean I
can now use execute_procedure and get back multiple result sets? I
didn't see anything in the change log referencing this.
I also had an unrelated question regarding transactions. I'm in a
situation where a transaction would be helpful. I have to save a
record which has an after save callback to call an SP with a different
connection. I know distributed transactions are outside the scope of
AR but does rails-mssql adapter support distributed transactions or
no?
Ken Collins
Hey Chris,
I was not aware of any behavior needed for #execute_procedure to do that. I just did a google search and saw some links like this one [1] which mention some other lifting necessary to get to those result sets. I would not imagine the adapter blocking this at any level since the AR spec gives you the ability to use #raw_connection to get right down to what you need.
I think the real question is (1) does the adapter block you from doing this and if so let's get a patch in or (2) can the adapter do something to make your life easier? I would think returning a two dimensional array might just throw others for a loop where they only cared about the first result set. I'm not a SQL Server expert enough to quote from memory on which built in procs do that so I can test, however I do remember seeing them at some points in time. Maybe a two dimentsional array is the only way, I dunno. Would really like some thoughts here.
BTW, I found out just yesterday that casting of values returned from #execute_procedure are better (i think) going straight to ODBC. For instance, I had code like results[0]['foo'] == '1' and now that had to be results[0]['foo'] = 1 so just FYI.
About the transaction. I have always had models in my app back to rails 1.0 that fire off a stored proc and update it again without callbacks. Sure you can have that model use SomeOther.connection.execute_procedure and do the work, I doubt nothing stops you from that and I have done it myself, but you have to manage your own deadlocks that can happen from it and how to resolve cross db transaction, perhaps rescue StatementInvalid from connection B and raise it again in connection A. This is just outside the scope of the adapter/rails and something you have to wire up on your own, right?
[1] http://stackoverflow.com/questions/514239/ms-sql-stored-procedure-returned-result-sets-with-odbc
Chris Hall
We are currently using 2.2.19 which is obviously still using DBI.
What we did here was create a mixin with new methods to call SPs that
return multiple result sets. Basically all it did was grab the raw
connection to call the SP, however we had problems with this due to
dropped connections which were not handled by the auto reconnection
functionality built into the adapter since we dropped down to the raw
ODBC connection. What I did to get around this was to make the
auto_reconnected? method public so we could hook into that. That has
worked great, but I don't like that it lives outside the adapter.
Now that the adapter is not using DBI, it would be much cleaner to
implement multiple result sets directly in the adapter. I've already
started coding for returning multiple result sets and hope to have
something ready soon. I did test 2.3.4 for multiple result sets after
I sent my original email, and I'm only getting the first result back.
I agree that returning a 2-dim array is bad form so I'm looking into
reducing the array returned to 1-dim if there was only one result set.
I'll let you know how it goes.
As far as the transaction issue goes, we're just going to work around
it as we have other issues associated with it that I don't really want
to go into. Suffice it to say, our original idea still wouldn't have
worked anyways.
Chris
Ken Collins
Thanks Chris,
Would love to see the multiple results details. BTW, the transaction stuff is never fun. We have a few key places where models are synchronized under a transaction over multiple DBs. The callback methods rescue ActiveRecord::RecordInvalid, ActiveRecord::RecordNotSaved and ensures that the object is returned. The driver method then always checks to see if any validations failed, and merges errors from that object into itself. Finally it raises the exception again. Something like below.
return true if object.errors.empty?
object.errors.each { |attrib,msg| driving_object.errors.add("other_object_#{attrib}",msg) }
raise ActiveRecord::RecordInvalid, other
Chris Hall
hope this works for you. I haven't had the time to create or even run
any tests, so any guidance there would be appreciated.
All you'll need to do is create a test rails app, unpack the adapter
and patch the connection adapter. Next you'll need to create an SP
that returns mutiple result sets. unfortunately, I'm not fluent in
SQL Server, we rely on our dba's to do their magic, so I don't have an
SP for you to test with, so you'll need to create one of your own that
returns multiple result sets. I've asked one of the dba's here to
create a simple one for me I can include in the tests I plan to write,
so if needed, I could send that along afterwards.
So once you have all that, just start up a dev console, and run
execute_procedure and check the results. Let me know what you think.
Chris
Ken Collins
I went thru the adapter and found which proc I was using that returned multiple result sets, it was:
EXEC sp_helpconstraint tablename
When I used this in #remove_default_constraint I had to pass 'nomsg' as the second argument to avoid the blow up the second result set would cause. Can you confirm using this proc with your changes?
- Ken
> <sql_server_adapter_multi_result_set_SP_support.patch>
Chris Hall
ActiveRecord::Base.execute_procedure("sp_helpconstraint", 'accounts')
[[{"Object Name"=>"accounts"}], [{"status_enabled"=>"(n/a)",
"status_for_replication"=>"(n/a)", "delete_action"=>"(n/a)",
"constraint_name"=>"DF__accounts__active__217D344A",
"constraint_keys"=>"((1))", "update_action"=>"(n/a)",
"constraint_type"=>"DEFAULT on column active"}, ... ]]
Chris Hall
was looking at writing test(s) for this, and was following the
RUNNING_UNIT_TESTS doc and came across some issues.
first, doc mentions i should have the following directory:
#{RAILS_ROOT}/vendor/plugins/adapters/sqlserver
and I am not sure why I need this when I have the gem unpacked to
#{RAILS_ROOT}/vendor/gems/activerecord-sqlserver-adapter-2.3.4
also, once I cd into the gems directory, i can list the rake tasks,
but if I try to actually run tests and I get a failure requiring
cases/helper:
(in /private/tmp/test_mssql/vendor/gems/activerecord-sqlserver-adapter-2.3.4)
/usr/local/bin/ruby
-Ilib:test:test/connections/native_sqlserver_odbc:../../../rails/activerecord/test/
"/usr/local/lib/ruby/gems/1.8/gems/rake-0.8.3/lib/rake/rake_test_loader.rb"
"test/cases/aaaa_create_tables_test_sqlserver.rb"
"test/cases/adapter_test_sqlserver.rb"
"test/cases/attribute_methods_test_sqlserver.rb"
"test/cases/basics_test_sqlserver.rb"
"test/cases/calculations_test_sqlserver.rb"
"test/cases/column_test_sqlserver.rb"
"test/cases/connection_test_sqlserver.rb"
"test/cases/eager_association_test_sqlserver.rb"
"test/cases/execute_procedure_test_sqlserver.rb"
"test/cases/inheritance_test_sqlserver.rb"
"test/cases/method_scoping_test_sqlserver.rb"
"test/cases/migration_test_sqlserver.rb"
"test/cases/named_scope_test_sqlserver.rb"
"test/cases/offset_and_limit_test_sqlserver.rb"
"test/cases/pessimistic_locking_test_sqlserver.rb"
"test/cases/query_cache_test_sqlserver.rb"
"test/cases/schema_dumper_test_sqlserver.rb"
"test/cases/specific_schema_test_sqlserver.rb"
"test/cases/table_name_test_sqlserver.rb"
"test/cases/transaction_test_sqlserver.rb"
"test/cases/unicode_test_sqlserver.rb"
"test/cases/validations_test_sqlserver.rb"
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in
`gem_original_require': no such file to load -- cases/helper
(LoadError)
from /usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in
`require'
from ./test/cases/sqlserver_helper.rb:4
from ./test/cases/aaaa_create_tables_test_sqlserver.rb:2:in `require'
from ./test/cases/aaaa_create_tables_test_sqlserver.rb:2
I've tried running it from both the adapter's root dir and also from
within the adapter's test dir with the same result.
Can you give me any guidance on what I need to do here to run tests?
should I just remove that require? didn't know if i was missing
something.
I've got the db all setup correctly according to the doc, and also
have rails vendored.
Chris