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:
Does anyone know of any adverse effects that I will incur by doing
this?
Does anyone know when this will be resolved in the sqlserver_adapter?
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
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.