Rails - Jul 2006 - Inserting datetime value into SQL Server

I have a SQL Server column named StartTime of (SQL Server) type datetime

If I attempt to set the attribute using

  public
  def StartTime=(time)
    write_attribute(:StartTime, "{ts ''1899-12-30 
#{time.hour}:#{time.min}:#{time.sec}''}")
  end

it''s inserting a NULL value.

Anyone else able to successfully insert a date time value into a SQL 
Server table using AR?

Thanks,
Wes

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

Looks like the sqlserver adapter for AR only supports Time objects to 
represent datetimes in SQL Server, and Time objects start in 1970.

I will try using the odbc connection.

========================================
def type_cast(value)
        return nil if value.nil? || value =~ /^\s*null\s*$/i
        case type
        when :string    then value
        when :integer   then value == true || value == false ? value == 
true ? 1 : 0 : value.to_i
        when :float     then value.to_f
        when :datetime  then cast_to_datetime(value)
        when :timestamp then cast_to_time(value)
        when :time      then cast_to_time(value)
        when :date      then cast_to_datetime(value)
        when :boolean   then value == true or (value =~ /^t(rue)?$/i) == 
0 or value.to_s == ''1''
        else value
        end
      end

      def cast_to_time(value)
        return value if value.is_a?(Time)
        time_array = ParseDate.parsedate(value)
        time_array[0] ||= 2000
        time_array[1] ||= 1
        time_array[2] ||= 1
        Time.send(Base.default_timezone, *time_array) rescue nil
      end

      def cast_to_datetime(value)
        if value.is_a?(Time)
          if value.year != 0 and value.month != 0 and value.day != 0
            return value
          else
            return Time.mktime(2000, 1, 1, value.hour, value.min, 
value.sec) rescue nil
          end
        end
        return cast_to_time(value) if value.is_a?(Date) or 
value.is_a?(String) rescue nil
        value
      end

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

This is the only way I could get it to work - sigh - is this really 
necessary?  I guess I should get to work on the ol'' SQL Server driver 
:).

public
  def update_attributes!(attributes)
    time = self.StartTime
    super
    self.connection.execute("UPDATE JobData " +
                            "SET    StartTime = {ts ''1899-12-30 
#{time.hour}:#{time.min}:#{time.sec}''} " +
                            "WHERE  JobReferenceNumber = 
#{self.JobReferenceNumber}")
  end

This overrides an update_attributes! method that I created in 
environment.rb.

Wes


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

Wes,

You''re right, the SQL Server adapter doesn''t work well with
dates/times before 1970.  This is due both to the limits in ruby''s
Time class, and the fact that SQL Server doesn''t have separate column
types for dates and times.

But it''s not all bad news.  There''s a patch in the works to
mimic date
columns, using a discoverable constraint to mark a datetime column as
date only.

Also, the version of the adapter in trunk should return DateTime
objects for given attributes if you use
read_attribute_without_typecast.  It _should_ (as in, I haven''t
actually tested but from the top of my head) also be possible to write
a datetime attribute directly with a DateTime object, i.e

object.StartTime=DateTime.new(1900, 1, 12, 12, 34, 00) # => 1900-01-12
12:34:00

Tom

On 18/07/06, Wes Gamble <weyus@att.net> wrote:
> This is the only way I could get it to work - sigh - is this really
> necessary?  I guess I should get to work on the ol'' SQL Server
driver
> :).
>
> public
>   def update_attributes!(attributes)
>     time = self.StartTime
>     super
>     self.connection.execute("UPDATE JobData " +
>                             "SET    StartTime = {ts
''1899-12-30
> #{time.hour}:#{time.min}:#{time.sec}''} " +
>                             "WHERE  JobReferenceNumber >
#{self.JobReferenceNumber}")
>   end
>
> This overrides an update_attributes! method that I created in
> environment.rb.
>
> Wes
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Tom Ward wrote:
> Wes,
> 
> You''re right, the SQL Server adapter doesn''t work well
with
> dates/times before 1970.  This is due both to the limits in ruby''s
> Time class, and the fact that SQL Server doesn''t have separate
column
> types for dates and times.
> 
> But it''s not all bad news.  There''s a patch in the works
to mimic date
> columns, using a discoverable constraint to mark a datetime column as
> date only.
> 
> Also, the version of the adapter in trunk should return DateTime
> objects for given attributes if you use
> read_attribute_without_typecast.  It _should_ (as in, I haven''t
> actually tested but from the top of my head) also be possible to write
> a datetime attribute directly with a DateTime object, i.e
> 
> object.StartTime=DateTime.new(1900, 1, 12, 12, 34, 00) # => 1900-01-12 
> 12:34:00
> 
> Tom
Tom,

This is all good, but I believe that ultimately, I''ll still have to do 
the hard update because of the string formatting on the INSERT 
statement, which doesn''t seem to be able to handle DateTimes well.

And frankly, I don''t want to have to jump through all these hoops just 
to update a freaking datetime column.

Thanks for the help,
Wes


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

Something else I noticed (I wrote this before I gave up):

Thanks very much.  I am able to successfully call 
StartTime_before_type_cast() in my getter and this seems to return a 
Datetime object.

However, the auto-magic parameter setting still fails because Rails 
takes my 6 DateTime element fields and happily shoves them into a Time 
object.

So either I can attempt to intercept/redefine the code that takes
StartTime(1i), StartTime(2i), etc. and turns it into the Time object 
that ultimately gets passed to StartTime
OR

I can use attr_protected to keep the StartTime field from getting 
updated by default when I submit my form, and then manage have the 
controller tell the object to update itself based on the params hash?

But again, I think that it''s a non-issue because I probably will have
to
drop into SQL to get the INSERT statement to work.

Wes

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

One last thing:

Doesn''t the fact that ActiveRecord uses Time (instead of DateTime) 
values internally to represent database datetime columns imply that on 
_no_ database platform will anyone be able to store a date prior to 
1970?

WG

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

On 18/07/06, Wes Gamble <weyus@att.net> wrote:
> One last thing:
>
> Doesn''t the fact that ActiveRecord uses Time (instead of DateTime)
> values internally to represent database datetime columns imply that on
> _no_ database platform will anyone be able to store a date prior to
> 1970?
Dates can be stored on other databases using the Date type (which goes
back way earlier than 1970) mapped to date columns.  This is what the
new patch will attempt to mimic.  Not all databases have date columns
though, so isn''t a problem exclusive to SQL Server.

You''re right though that all adapters (as far as I''m aware)
have some
problem with DateTimes.

Tom

Tom

On 18/07/06, Wes Gamble <weyus@att.net> wrote:
> Something else I noticed (I wrote this before I gave up):
>
> Thanks very much.  I am able to successfully call
> StartTime_before_type_cast() in my getter and this seems to return a
> Datetime object.
>
> However, the auto-magic parameter setting still fails because Rails
> takes my 6 DateTime element fields and happily shoves them into a Time
> object.
>
> So either I can attempt to intercept/redefine the code that takes
> StartTime(1i), StartTime(2i), etc. and turns it into the Time object
> that ultimately gets passed to StartTime>
> OR
>
> I can use attr_protected to keep the StartTime field from getting
> updated by default when I submit my form, and then manage have the
> controller tell the object to update itself based on the params hash?
>
> But again, I think that it''s a non-issue because I probably will
have to
> drop into SQL to get the INSERT statement to work.
This is obviously a situation that needs improving.  I''ll take a look
at how to ensure that DateTime objects are stored correctly (even if
they''re not retrieved well).  Perhaps it should be possible to mark a
column as DateTime only to force the adapter to always return DateTime
objects.

Tom

Tom Ward wrote:
> On 18/07/06, Wes Gamble <weyus@att.net> wrote:
>> So either I can attempt to intercept/redefine the code that takes
>> drop into SQL to get the INSERT statement to work.
> This is obviously a situation that needs improving.  I''ll take a
look
> at how to ensure that DateTime objects are stored correctly (even if
> they''re not retrieved well).  Perhaps it should be possible to
mark a
> column as DateTime only to force the adapter to always return DateTime
> objects.
> 
> Tom
After much cogitating, I see two issues that I ran across here:

1) The coercion of datetime columns into Time objects by AR allows the 
automatic update of time components back into the model but imposes 
unreasonable limits on the values of your datetime model attributes. 
This is an issue with ActiveRecord::Base as far as I can tell.

2) There is poor support for writing datetime values back into SQL 
Server (I may be missing some info. here), without being forced to drop 
into SQL.

Tom, are you involved with the SQL Server adapter, then?

Wes


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

On 19/07/06, Wes Gamble <weyus@att.net> wrote:
> 1) The coercion of datetime columns into Time objects by AR allows the
> automatic update of time components back into the model but imposes
> unreasonable limits on the values of your datetime model attributes.
> This is an issue with ActiveRecord::Base as far as I can tell.
I think the ''rails way'' past this limitation is to use two
columns,
one for date and one for time.  Of course this isn''t possible using
the SQL Server adapter as it has no date column type (hence attempts
to mimic a date type)
> 2) There is poor support for writing datetime values back into SQL
> Server (I may be missing some info. here), without being forced to drop
> into SQL.
This should be easy to fix.
> Tom, are you involved with the SQL Server adapter, then?
Yeah, I''m the maintainer as of a couple of weeks ago.  I''ve
been
slowly working through the issues in trac (before it went down),
fixing failing tests and other minor issues, etc.  Next step is to
improve functionality, with Date/time handling being one of the key
areas that need sorting out.  I''m planning to work on the problems
that affect only SQL Server before going on to those that affect
ActiveRecord as a whole.  I''d welcome any thoughts on how you feel it
should work, particularly regarding your first point. It''s pretty
clear to me that the second issue, writing DateTime attributes, should
just work.  If it doesn''t, it needs fixing.

Tom

On Jul 19, 2006, at 17:39, Tom Ward wrote:
> should work, particularly regarding your first point. It''s pretty
> clear to me that the second issue, writing DateTime attributes, should
> just work.  If it doesn''t, it needs fixing.
I found an issue last week. Using Date attributes back and forth  
worked flawlessly in SQL Server until a one in Spanish entered the  
project. The convertion fails with an out of range, but in the  
googling I''ve done looks like the ISO format the adapter uses is not  
language dependent, so I can''t understand what happens. The trace  
goes below just in case it helps.

-- fxn

DBI::DatabaseError: 22008 (242) [Microsoft][ODBC
SQL Server Driver][SQL Server]La conversi?n del tipo de datos char a  
datetime p
odujo un valor datetime fuera de intervalo.: INSERT INTO movimientos  
([carpeta]
  [otras_caracteristicas], [fecha_valor], [importe_operacion],  
[fecha_operacion]
  [oficina], [referencia_1], [remite], [referencia_2], [divisa],  
[concepto], [cr
ado_en], [cuenta], [concepto_especifico], [entidad], [saldo],  
[estado]) VALUES(
53386185'', ''C00607-060714-105544-0095'',
''2006-07-14'', 235500,
''2006-07-14'', ''23
5 - POZUELO DE ALARCON - ESTACION - AV. JUAN PABLO II, 5 '',
'''',
''REMPO'', '''', ''E
R'', ''INGRESO POR VALIDAR'', ''2006-07-14
11:07:15'',
''21002128420200179838'', '''', ''
aCaixa'', 4110278, ''N'')
(ActiveRecord::StatementInvalid)
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
onnection_adapters/sqlserver_adapter.rb:279:in `insert''
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
ase.rb:1739:in `create_without_callbacks''
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
allbacks.rb:261:in `create_without_timestamps''
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
imestamp.rb:30:in `create''
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
ase.rb:1718:in `create_or_update_without_callbacks''
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
allbacks.rb:249:in `create_or_update''
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
ase.rb:1392:in `save_without_validation''
         from ./script/../config/../vendor/rails/activerecord/lib/ 
active_record/
alidations.rb:724:in `save_without_transactions''
          ... 10 levels...
         from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/ 
custom_require.rb:21:in `e
al''
         from ./script/../config/../vendor/rails/railties/lib/ 
commands/runner.rb
27
         from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/ 
custom_require.rb:21:in `r
quire''
         from script/runner:3

On 19/07/06, Xavier Noria <fxn@hashref.com> wrote:
>
> I found an issue last week. Using Date attributes back and forth
> worked flawlessly in SQL Server until a one in Spanish entered the
> project. The convertion fails with an out of range, but in the
> googling I''ve done looks like the ISO format the adapter uses is
not
> language dependent, so I can''t understand what happens.
Could you send me the schema for that table and any other information
you think might be useful (such as a translation of the trace?) and
I''ll take a look at it.  Does it fail with all dates or just certain
dates?  What happens if you run the activerecord test suite against
the same (or similar) database?

Normally the best thing to do would be to submit a bug in trac, but I
appreciate trac is currently down . . .

Tom

RE: the DateTime coersion problem:
I''ve just accepted the incompatibility and convert as needed between
ruby
types.
''Course, I''m originally from the VB world, where coersion of
types is an
everyday struggle.

I think changes to the sqlserver adapter in this have the *potential* to
break existing overcompensations.  So please strongly highlight the
documented change for users.

Thanks!

PS: running several apps on SQLServer and coming up with workarounds for
serveral gnarly issues of rails -vs- enterprise sql server installations.

-- 
------------------------------
Apple MacBook.  Black.  It''s the new White!
------------------------------
Peter Fitzgibbons

On 7/19/06, Tom Ward <tom@popdog.net> wrote:
>
> On 19/07/06, Xavier Noria <fxn@hashref.com> wrote:
> >
> > I found an issue last week. Using Date attributes back and forth
> > worked flawlessly in SQL Server until a one in Spanish entered the
> > project. The convertion fails with an out of range, but in the
> > googling I''ve done looks like the ISO format the adapter uses
is not
> > language dependent, so I can''t understand what happens.
>
> Could you send me the schema for that table and any other information
> you think might be useful (such as a translation of the trace?) and
> I''ll take a look at it.  Does it fail with all dates or just
certain
> dates?  What happens if you run the activerecord test suite against
> the same (or similar) database?
>
> Normally the best thing to do would be to submit a bug in trac, but I
> appreciate trac is currently down . . .
>
> Tom
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060719/b4837a4f/attachment.html

On Jul 19, 2006, at 18:26, Tom Ward wrote:
> On 19/07/06, Xavier Noria <fxn@hashref.com> wrote:
>>
>> I found an issue last week. Using Date attributes back and forth
>> worked flawlessly in SQL Server until a one in Spanish entered the
>> project. The convertion fails with an out of range, but in the
>> googling I''ve done looks like the ISO format the adapter uses
is not
>> language dependent, so I can''t understand what happens.
>
> Could you send me the schema for that table and any other information
> you think might be useful (such as a translation of the trace?)
Absolutely, the trace says "the conversion from datatype char to  
datetime gave a datetime value out of range".
> and
> I''ll take a look at it.  Does it fail with all dates or just
certain
> dates?
The first time it tries to use those, in this case for an insert. I  
don''t know which is the offending field (I don''t have access
to that
machine to debug this properly unfortunately, only some contact now  
and then). Nevertheless, the three fields are set to the same dat,  
which is 2006-07-14. The SQL looks fine to me.
> What happens if you run the activerecord test suite against
> the same (or similar) database?
>
> Normally the best thing to do would be to submit a bug in trac, but I
> appreciate trac is currently down . . .
Yeah, if that reminds you of something off the top of your head  
that''d be good. Otherwise I''ll try to report this when Trac
comes back.

Thank you for taking over maintenance of this adapter, I''ve been  
using it flawlessly in production for weeks.

-- fxn

Peter,

Glad to hear I''m not the only one hitting SQL Server.  And I''m
doing it
from Linux to boot :).

Wes (weyus at att dot net)


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

On Jul 19, 2006, at 19:09, Xavier Noria wrote:
> The first time it tries to use those, in this case for an insert. I  
> don''t know which is the offending field (I don''t have
access to
> that machine to debug this properly unfortunately, only some  
> contact now and then). Nevertheless, the three fields are set to  
> the same dat, which is 2006-07-14. The SQL looks fine to me.
Let me add that application run just fine in that very machine at the  
beginning of the month, and crashed afterwards. My conjecture is that  
day 14 is being interpreted as month 14 somewhere. I have no evidence  
for that, only that roughly coincides with the time when it started  
to fail there. I say roughly because the application is only run some  
days, so exactly on day 13 there was no execution.

Another relevant bit that contributes to weakly to that conjecture is  
that in Spanish the standard way to format days is dd-mm-yyyy, which  
reverses days and months from mm-dd-yyyy. Nothing of this seems  
relevant since the SQL uses a ISO format understood by SQL Server  
which is neither of those. But, well, just in case.

-- fxn

On 19/07/06, Xavier Noria <fxn@hashref.com> wrote:
> On Jul 19, 2006, at 19:09, Xavier Noria wrote:
>
> > The first time it tries to use those, in this case for an insert. I
> > don''t know which is the offending field (I don''t
have access to
> > that machine to debug this properly unfortunately, only some
> > contact now and then). Nevertheless, the three fields are set to
> > the same dat, which is 2006-07-14. The SQL looks fine to me.
>
> Let me add that application run just fine in that very machine at the
> beginning of the month, and crashed afterwards. My conjecture is that
> day 14 is being interpreted as month 14 somewhere. I have no evidence
> for that, only that roughly coincides with the time when it started
> to fail there. I say roughly because the application is only run some
> days, so exactly on day 13 there was no execution.
Soundds a reasonable theory, though I would have thought SQL Server
should still have recognised the date as supplied.  I''ll try and see
if I can recreate the problem in any way this weekend.

Thanks for the extra info.

Tom

On 19/07/06, Peter Fitzgibbons <peter.fitzgibbons@gmail.com> wrote:
>  I think changes to the sqlserver adapter in this have the *potential* to
> break existing overcompensations.  So please strongly highlight the
> documented change for users.
Will do.  All changes will be against trunk and so won''t get released
until 1.2 at the earliest.  I don''t want to rewrite my own
applications so I''m very sympathetic to exsiting users.
> PS: running several apps on SQLServer and coming up with workarounds for
> serveral gnarly issues of rails -vs- enterprise sql server installations.
Anything that might be useful to the rest of us?  We''re running a
number of apps on rails/linux/sqlserver and haven''t come across too
many nasty problems, but any info about potential problems/solutions
is always welcome.

Tom