Rails - Oct 2006 - Selecting datetime values from SQL Server (year < 1970)

All,

Rails 1.1.6.

Any AR find() call that generates a SELECT * type query against a table 
with a DATETIME column in SQL Server whose value occurs before 1970 will 
fail, because of the coercion of SQL Server datetime types to Time 
values in Ruby.

See sqlserver_adapter.rb line 490
(record[col] = record[col].to_time if record[col].is_a? DBI::Timestamp)

If I remove the coercion (just commenting out that line), I can 
successfully execute these find() calls.

I have a couple of questions:

1) Does anyone know of any adverse effects that I will incur by doing 
this?
2) Does anyone know when this will be resolved in the sqlserver_adapter?

A related thread that has to do with the input side of this may be found 
here: http://www.ruby-forum.com/topic/73435.

Any thoughts?

Still a big bug, in my opinion.

Wes



-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Further complications forced the following modification to the select() 
method in sqlserver_adapter.rb.  Basically, it creates a DateTime object 
instead of a time object so that older dates will be selected correctly.

 private
   def select(sql, name = nil)
     rows = []
     repair_special_columns(sql)
     log(sql, name) do
       @connection.select_all(sql) do |row|
         record = {}
         row.column_names.each do |col|
           record[col] = row[col]
	   #Coerce to DateTime in order to handle old datetime values. - 
10/19/06 WG
	   #record[col] = record[col].to_time if record[col].is_a? 
DBI::Timestamp
	   record[col] = DateTime.new(record[col].year, record[col].month, 
record[col].day, record[col].hour, record[col].minute, 
record[col].second, record[col].fraction) if 
record[col].is_a?(DBI::Timestamp)
         end
         rows << record
       end
     end
     rows
   end

On the input side of things, the following change is required to the 
quote() method in sqlserver_adapter.rb - the change is to put the "when 
Time, DateTime" _before_ "when Time".  Since DateTime is_a Time,
the
input formatting was not correct for true DateTime values.

def quote(value, column = nil)
  case value
    when String
      if column && column.type == :binary && 
column.class.respond_to?(:string_to_binary)
       
"''#{quote_string(column.class.string_to_binary(value))}''"
      else
        "''#{quote_string(value)}''"
      end
    when NilClass              		then "NULL"
    when TrueClass             		then ''1''
    when FalseClass            		then ''0''
    when Float, Fixnum, Bignum 		then value.to_s
    when Time, DateTime			then "''#{value.strftime("%Y-%m-%d 
%H:%M:%S")}''"
    when Date                  		then
"''#{value.to_s}''"
    else                            
"''#{quote_string(value.to_yaml)}''"
  end
end

Wes

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

> On the input side of things, the following change is required to the 
> quote() method in sqlserver_adapter.rb - the change is to put the
"when
> Time, DateTime" _before_ "when Time".  Since DateTime is_a
Time, the
> input formatting was not correct for true DateTime values.
> 
> def quote(value, column = nil)
...
>     when Time, DateTime			then
"''#{value.strftime("%Y-%m-%d
> %H:%M:%S")}''"
>     when Date                  		then
"''#{value.to_s}''"
>     else                            
"''#{quote_string(value.to_yaml)}''"
>   end
> end
> 
> Wes
To clarify: This change should successfully allow datetime values to be
updated in the database.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

This was just what I was looking for, thanks!

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---