Issue with DateTime column parsing

[Chris]

I have been completely stonewalled by an issue that occurs when
attempting to parse a DateTime column. I have a pretty simple table
(create script below) that I have attempted to make a *very* simple
rails editor for. Unfortunately when I attempt to look at this data it
always fails on parsing either of the DateTime columns. Below is the
error message with full stack trace. This is occurring because the
parse method for SqlserverTimestamp is receiving a DateTime object
instead of the string its expecting... I am currently fixing this by
modifying the line "date, time, nanoseconds = obj.split(' ')" to where
it formats it as a db string. The fixed (and working) line is "date,
time, nanoseconds = obj.to_s(:db).split(' ')"

I'm assuming there's a good reason it expected the data coming in to
be a string, but that's definitely *not* what DBI was giving it!
Please let me know if there's a better fix for this issue, Google
failed me completely! I would love it if this issue could be fixed in
the actual adapter!

--------
Stack trace:
--------
NoMethodError in Category phrasesController#show

private method `split' called for Tue, 01 Jan 1980 00:00:00
+0000:DateTime

RAILS_ROOT: C:/Users/cgolden/My Documents/Aptana Studio Workspace/
TestRR
Application Trace | Framework Trace | Full Trace

c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.21/
lib/active_record/connection_adapters/sqlserver_adapter/core_ext/
dbi.rb:35:in `parse'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.2/lib/dbi/row.rb:66:in
`convert_types'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
base.rb:2036:in `each_with_index'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.2/lib/dbi/row.rb:65:in `each'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.2/lib/dbi/row.rb:65:in
`each_with_index'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.2/lib/dbi/row.rb:65:in
`convert_types'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.2/lib/dbi/row.rb:75:in
`set_values'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.2/lib/dbi/handles/statement.rb:
213:in `fetch'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.2/lib/dbi/handles/statement.rb:
240:in `each'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.21/
lib/active_record/connection_adapters/sqlserver_adapter.rb:879:in
`inject'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.21/
lib/active_record/connection_adapters/sqlserver_adapter.rb:879:in
`handle_as_array'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.21/
lib/active_record/connection_adapters/sqlserver_adapter.rb:865:in
`raw_select'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.21/
lib/active_record/connection_adapters/sqlserver_adapter.rb:816:in
`select'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
connection_adapters/abstract/database_statements.rb:7:in
`select_all_without_query_cache'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
connection_adapters/abstract/query_cache.rb:60:in `select_all'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
connection_adapters/abstract/query_cache.rb:81:in `cache_sql'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
connection_adapters/abstract/query_cache.rb:60:in `select_all'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
base.rb:661:in `find_by_sql'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
base.rb:1548:in `find_every'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
base.rb:1583:in `find_one'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
base.rb:1569:in `find_from_ids'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/
base.rb:616:in `find'
C:/Users/cgolden/My Documents/Aptana Studio Workspace/TestRR/app/
controllers/category_phrases_controller.rb:17:in `show'



---------
Table create script:
---------
CREATE TABLE [dbo].[CategoryPhrases](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[CategoryId] [bigint] NOT NULL,
[Phrase] [nvarchar](384) NOT NULL,
[StartDate] [smalldatetime] NOT NULL,
[EndDate] [smalldatetime] NOT NULL,
CONSTRAINT [PK_CategoryPhrases] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[CategoryPhrases] WITH CHECK ADD CONSTRAINT
[FK_CategoryID] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Categories] ([id])

[Ken Collins]

What version of ruby and rails are you using?

- Ken

[Scott Jacobsen]

This looks familiar. I think I saw it with dbi 0.4.2, which is what the stack trace shows. Try install dbi 0.4.1 and see what happens.
In your envionment.rb put:

config.gem 'dbi', :version => '0.4.1'

[Ken Collins]

I just noticed something. I do not think we have any tests for
smalldatetime columns. I could start to add some to make sure this is
not an adapter bug. Curious tho... any reason why you have that vs
just datetime? Just curious.

BTW, some background. Other adapters like the PostgreSQL one supports
storing nanoseconds while SQL Server only stores microseconds. We
wanted to be as cool as we could and take ruby objects that acted like
datetime as in core and store the appropriate amount we could (in and
out) of the DB that SQL Server supported. I had a series of commits
back in the data that added that as well as our own tests. Search the
CHANGELOG if your interested or run the tests.

Hmm... I just noticed I did add tests for smalldatetime. Take a look
at the column_test_sqlserver.rb, there is a context around line 155
called "For datetime columns" with a sub context called "For
smalldatetime types". Can you run the test to see if they are even
passing for you? This could be language and/or DB config specific. The
tests are a great place to start and they help point us to what is
going wrong. FYI, all tests pass for me in 2000/2005/2008 in Rails
2.3.4 with ruby 1.9.1 and 1.8.7.

- Ken


On Sep 14, 2009, at 3:42 PM, Chris wrote:

>

[Chris]

I am running activerecord-sqlserver-adapter v2.2.21, ruby v1.8.6,
Rails v2.3.4, DBI v0.4.2 all talking to SQL Server 2005. So far I have
been developing using RadRails since I'm relatively familiar with
Eclipse. :(

These are the versions I came across when I got started on Rails last
week. I had never touched even Ruby code before last week... I have
been developing primarily in C# for years with lots of Java and C++ on
the side with a little bit of Python. While I've been doing Unit
Testing for a while I've never used it in Ruby/RoR! I can see how to
run tests that were generated for *my* RoR project, but not for your
test file. I will research how to do this and hopefully have a
verdict soon on whether it works!

Since the focus appears to be on the smalldatetime column type I would
like to add that originally these columns were just DateTime. They
were changed in the hopes that it would change the behavior! Currently
which type it is doesn't matter as far as I know. Honestly it doesn't
need more granularity than to the day.


- Chris

[Ken Collins]

Perhaps you could add some logging before that method fires to help us
see what is happening and what obj.inspect and obj.class.inspect might
be in your cases. You can see that in this method I added notes that
states what I am expecting DBI/ODBC to do. If this is not true for
your case, I'd like to know why and what objects you are getting. Let
us know.


- Ken

[Chris]

Added some logging on the line before where date, time and nanoseconds
are assigned (line 35 of dbi.rb)

Executing this line:
"puts "Checking date. ClassInspect= #{obj.class.inspect}, Inspect= #
{obj.inspect}, Normal= #{obj}, DB= #{obj.to_s(:db)}""

Gave the following example output:
"Checking date. ClassInspect= DateTime, Inspect= Tue, 14 Sep 2010
00:00:00 +0000, Normal= 2010-09-14T00:00:00+00:00, DB= 2010-09-14
00:00:00"


The DB format one is what I'm using now to make anything work... The
core problem from what I can see is still that its getting a DateTime
object which doesn't even offer a split method! Even using its its
to_s method returns a different format than what you appear to be
expecting. Were you expecting a standard string to come in?


- Chris

[Ken Collins]

Wait a minute... Scott is right. Your using the wrong version of DBI.
The supported version is 0.4.1

- Ken

[Chris]

That seems like a pretty serious problem if going up to the next (and
current) version breaks it! I will drop back down to 0.4.1 in the
interest of things *working* though... When do ya'll expect to have
support for the newer version? I do see that in your installation you
specify which version to use for each. I must admit that I didn't use
that part since most docs I see are slightly out of date even for the
system they're talking about. In my experience when they give a
specific version its often the wrong one! I apologize for that
assumption. Hopefully going back a version will fix it all.


- Chris

[Ken Collins]

Well awhile back ago on IRC with the DBI team I was told they were
going to significantly start to move/change the underlying code base
post 0.4.0. Version 0.4.1 was a maintenance release for ruby 1.9.1
compatibility and we took our support from 0.4.0 to 0.4.1 then.

For the benefit of the adapter and the goal to simplify the moving
parts in an already complicated setup, I have not followed DBI heavily
for the sake of pushing forward on what is essentially a low level
transport for us. We are passing all the tests in 2000/2005/2008 and
Mac/Pc in 1.8.x/1.9 and to suggest that we are missing something in
that new version may be a mischaracterization. To date I put my focus
on support a reasonable pre version of rails and staying current with
edge/releases.

Did you have a reason for using that new version that describes a
serious problem? That just sounds brash and I am not understanding the
reason for it and/or criticizing what I call a great open source
project. Sigh.

- Ken

[Chris]

Nope, no particular reason for me to use that version. It just seems
very unusual that they are making such major breaking changes on a
small version change like 0.4.1 to 0.4.2, very counter-intuitive!
Obviously not your fault, with all of the facts available your
reasoning makes perfect sense. The available context-clues and my
general pessimism led to using an incompatible version. Please,
continue as you are! If you feel inclined to help folks like myself
then a small not in the installation instructions might make things a
bit more clear.

Thanks a bunch for the very rapid support. I have grown used to
waiting days for a good response to such queries! Definitely improves
my perception of this adapter and its development. I'll try to leave
ya'll alone unless I run across an actual issue with your code... ;)


- Chris

> ...
>
> read more »

[Ken Collins]

I checked the README and it did say a bit about the background and "IT
IS HIGHLY RECOMMENDED" that you use 0.4.1. Now that we know it is down
right critical till we sync back up with them, I'll change it. :)

- Ken