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
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 =~ /^\snull\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
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
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
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
Tom W. 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.eobject.StartTime=DateTime.new(1900, 1, 12, 12, 34, 00) # => 1900-01-12
12:34:00Tom
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
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
On 18/07/06, Wes G. [email protected] 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
Tom W. wrote:
On 18/07/06, Wes G. [email protected] 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:
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.
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
On 18/07/06, Wes G. [email protected] 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 19/07/06, Wes G. [email protected] wrote:
- 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)
- 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 W. 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:increate_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:increate’
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:increate_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:insave_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:inr
quire’
from script/runner:3
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.
Peter F.
On 19/07/06, Xavier N. [email protected] 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
On Jul 19, 2006, at 18:26, Tom W. wrote:
On 19/07/06, Xavier N. [email protected] 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)
On Jul 19, 2006, at 19:09, Xavier N. 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 N. [email protected] wrote:
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 F. [email protected] 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
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
Sponsor our Newsletter | Privacy Policy | Terms of Service | Remote Ruby Jobs