timezone conversion execute_procedure versus select_all (@4.2.6)

[krzcho]

I have inconsistency: when procedure is returning dates they are treated using local system timezone while using select_all method it is treated as UTC (which correct and expected)
Am I missing something? I would except all adapter data retrieval methods behave the same way

CREATE PROC dbo.test AS
SELECT GETUTCDATE() utcdate

irb(main):041:0> ActiveRecord::Base.execute_procedure("test")[0]["utcdate"].zone
  Execute Procedure (0.0ms)  EXEC test
=> "Middle East Standard Time"

irb(main):040:0> ActiveRecord::Base.connection.select_all("select getutcdate()".force_encoding("utf-8"))[0]["utcdate"].zone
   (0.0ms)  EXEC sp_executesql N'select getutcdate() as utcdate'
=> "UTC"

irb(main):001:0> TinyTds::Client.default_query_options
=> {:as=>:hash, :symbolize_keys=>false, :cache_rows=>true, :timezone=>:local, :empty_sets=>true}
updating timezone from local to utc solves the problem but I am not sure if it should not be set by the adapter itself to be consistent

the same issue is here #266
but I must say that ActiveRecord's timezone is left at utc so it is somehow not being propagated lower into execute_procedure tiny_tds query...

I am using tiny_tds 0.7.0, I am aware of 0.9.5 being close, maybe it will solve this issue?

[metaskills]

So our execute_procedure was just a connivence method I added and not part of the ActiveRecord interface. As such, it does not hit all the internals as you might expect. Looking at the source I can see we are not calling handle_to_names_and_values_dblib in the implementation for some reason or another. I think it is due to us supporting yielding to a block for that method. Likely a mistake in interface on my part.

It is possible we could change the way things work and fix this. Maybe something like this?

--- a/lib/active_record/connection_adapters/sqlserver/database_statements.rb
+++ b/lib/active_record/connection_adapters/sqlserver/database_statements.rb
@@ -117,7 +117,12 @@ module ActiveRecord
             case @connection_options[:mode]
             when :dblib
               result = @connection.execute(sql)
-              result.each(as: :hash, cache_rows: true) do |row|
+              options = {
+                as: :hash,
+                cache_rows: true,
+                timezone: ActiveRecord::Base.default_timezone || :utc
+              }
+              result.each(options) do |row|
                 r = row.with_indifferent_access
                 yield(r) if block_given?
               end

[krzcho]

fine with me

[metaskills]

Did you test it?

[krzcho]

of course... not :)
it is so simple - do we need to try it?

[metaskills]

I'll test this in some final 4-2-stable work this week and will post back.

[metaskills]

Just pushed v4.2.9 with this fix. Thanks for the example SQL too, really helped me write a failing test case. See 32968ae