Interesting... I'll have to look at this more next week. Some initial thoughts. Here is the code to talk around.

  def quoted_date(value)
    if value.acts_like?(:time) && value.respond_to?(:usec)
      "#{super}.#{sprintf("%03d",value.usec/1000)}"
    else
      super
    end
  end

First, the super would just bring back a string that did not have usec/millisecond support in this format:

  Time.now.in_time_zone.to_s(:db)
  "2010-05-19 12:12:59"

We aded the first condition in the adapter to get fraticional second support that SQL Server offers. I did this from a visual inspection of what appeared to be a common format to me. So here are my questions. Do all versions of SQL Server support the date time in an ISO 8601 string format? If not, is there something I can reflect on at the database level, a setting, current language, etc that could help me tell which way to format in ruby?

from activerecord-sqlserver-adapter.

For information on "Using Date and Time Data" have a look at:
http://msdn.microsoft.com/en-us/library/ms180878.aspx

I changed it to :
def quoted_date(value)
"#{value.strftime("%Y-%m-%dT%H:%M:%S")}.#{sprintf("%03d",value.usec/1000)}"
end

I removed:
if value.acts_like?(:time) && value.respond_to?(:usec)
as this condition is always true for ActiveSupport::TimeWithZone

I also tried:
value.iso8601

but currently it gives me an error on SQL-Server 2005 due to the zone offset generated in this format ?!?

Regarding SQL Server 2000 : regarding the documentation it should accept the ISO8601 format, but I can't test it as I no longer have one...

from activerecord-sqlserver-adapter.

Addendum: I have just found it in the docs:

• SQL Server 2000 + 2005 does NOT support a zone offset in the ISO 8601 Format

A very interesting article by Robyn Page:
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

from activerecord-sqlserver-adapter.

Where did we leave off on this? What can I do to help?

from activerecord-sqlserver-adapter.

With some help of a ruby professional I will try to set up a test environment for the sqlserver-adapter tomorrow and I will try to create a patch for it.

from activerecord-sqlserver-adapter.

Now we have a running test environment for the adapter ( i think I have to document how to setup the environment ,-)
running on WinXp, Ruby 1.9.1, SQL Server 2005 in GERMAN language
Running rake test throws 1524 errors !!!!
We found that ActiveRecord is writing the current date and time in the columns created_at/created_on updated_at/updated_on and is NOT using quoted_date ?!?

Could it be that I am the first one using Ruby on Rails on Windows with an SQL Server with German language ??

from activerecord-sqlserver-adapter.

Hi Ken,
I have severe troubles running the tests on Windows.
Currently I get stuck where the ODBC driver seems to return garbage for varchar(max) columns.
I think, I would need some help to proceed ....

from activerecord-sqlserver-adapter.

This just sounds like a low level problem in ODBC layer. I'll do what ever I can, but I'm not sure what I can do. I really want to write a few tests. BTW, can you show me your #user_options? See that connection.user_options in the adapter. Run it from console or that raw SQL and show me what yours are.

from activerecord-sqlserver-adapter.

Thanks for your fast response. Here are the user options:
LagerInfo/development: a.connection.user_options
=> {"textsize"=>"10000", "language"=>"Deutsch", "dateformat"=>"dmy", "datefirst"=>"1", "lock_timeout"=>"-1", "quoted_identifier"=>"SET", "an
si_null_dflt_on"=>"SET", "ansi_warnings"=>"SET", "ansi_padding"=>"SET", "ansi_nulls"=>"SET", "concat_null_yields_null"=>"SET", "isolation_level"=>"read committed"}

Should I post here the steps I have taken so far, or should I post it in the Google Group ?

from activerecord-sqlserver-adapter.

Hard to say, can you detail here a summary of your issues at the lowest level with code examples and console output?

from activerecord-sqlserver-adapter.

Test documentation:

( Running on WinXP, Ruby 1.9.1p378, Rails 2.3.7, activerecord-sqlserver-adapter 2.3.6)

cd
mkdir \RailsTest
cd \RailsTest
rails sqlserver_test

git clone git://github.com/rails/rails.git sqlserver_test/vendor/rails
git clone git://github.com/rails-sqlserver/2000-2005-adapter.git sqlserver_test/vendor/plugins/adapters/sqlserver

cd sqlserver_test
cd vendor\plugings\adapters\sqlserver

git branch adapter_test_on_windows
git checkout adapter_test_on_windows

rake test

Using SQLServer via ODBC

• DEFERRED: For abstract behavior with different language should do a date insertion when language is german.
  Undefined coerced test: AdapterTest#test_add_limit_offset_should_sanitize_sql_injection_for_limit_without_comas
  Undefined coerced test: AdapterTest#test_add_limit_offset_should_sanitize_sql_injection_for_limit_with_comas
  Undefined coerced test: EagerAssociationTest#test_eager_with_has_many_and_limit_and_high_offset_and_multiple_array_conditions
  Undefined coerced test: EagerAssociationTest#test_eager_with_has_many_and_limit_and_high_offset_and_multiple_hash_conditions
  Undefined coerced test: EagerAssociationTest#test_count_with_include
  Undefined coerced test: AttributeMethodsTest#test_typecast_attribute_from_select_to_false
  Undefined coerced test: AttributeMethodsTest#test_typecast_attribute_from_select_to_true
  Undefined coerced test: BasicsTest#test_read_attributes_before_type_cast_on_datetime
  Undefined coerced test: CalculationsTest#test_should_sum_expression
  Undefined coerced test: InheritanceTest#test_a_bad_type_column
  Undefined coerced test: InheritanceTest#test_eager_load_belongs_to_primary_key_quoting
  Undefined coerced test: NestedScopingTest#test_merged_scoped_find
  Undefined coerced test: MigrationTest#test_add_column_not_null_without_default
  Undefined coerced test: ChangeTableMigrationsTest#test_string_creates_string_column
  Undefined coerced test: NamedScopeTest#test_named_scopes_honor_current_scopes_from_when_defined
  Undefined coerced test: QueryCacheTest#test_cache_does_not_wrap_string_results_in_arrays
  Undefined coerced test: ValidationsTest#test_validate_uniqueness_with_limit_and_utf8
  Loaded suite c:/Ruby19/lib/ruby/gems/1.9.1/gems/rake-0.8.7/lib/rake/rake_test_loader

Finished in 282.680933 seconds.

2402 tests, 2542 assertions, 11 failures, 1793 errors, 0 pendings, 0 omissions, 0 notifications
30.3081% passed

Problem 1 :
ActiveRecord::StatementInvalid: ODBC::Error: 22008 (242) [Microsoft][SQL Native Client]
[SQL Server]Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs.:

DateTime Format not in ISO 8601

Change 1 :
C:\RailsTest\sqlserver_test\vendor\rails\activerecord\lib\active_record\connection_adapters\abstract\sqlserver_adapter.rb
line 313 :

def quoted_date(value)
if value.acts_like?(:time) && value.respond_to?(:usec)
# "#{super}.#{sprintf("%03d",value.usec/1000)}"
"#{value.strftime("%Y-%m-%dT%H:%M:%S")}.#{sprintf("%03d",value.usec/1000)}"
else
# super
"#{value.strftime("%Y-%m-%dT%H:%M:%S")}"
end
end

Finished in 243.892876 seconds.

2402 tests, 4056 assertions, 18 failures, 1335 errors, 0 pendings, 0 omissions, 0 notifications
49.0841% passed

Problem 2:
ActiveRecord::StatementInvalid: ODBC::Error: 22008 (242) [Microsoft][SQL Native Client]
[SQL Server]Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs.:
INSERT INTO [parrots]([name], [parrot_sti_class], [created_at], [created_on], [updated_at], [updated_on], [id])
VALUES ('Curious George', 'LiveParrot', '2010-05-28 12:02:59', '2010-05-28 12:02:59', '2010-05-28 12:02:59', '2010-05-28 12:02:59', 380982691)
Fields [created_at], [created_on], [updated_at], [updated_on]
quote(2010-05-28 12:15:20 : String, created_at)

Why are these values Strings ??!?!?

Change 2:
C:\RailsTest\sqlserver_test\vendor\rails\activerecord\lib\active_record\fictures.rb
lin 555 in def insert_fixtures:

timestamp fields are datetime fields and not Strings !!!! now = now.to_s(:db)

Problem 3:
C:/RailsTest/sqlserver_test/vendor/plugins/adapters/sqlserver/lib/active_record/connection_adapters/sqlserver_adapter.rb:956:
[BUG] Segmentation fault
ruby 1.9.1p378 (2010-01-10 revision 26273) [i386-mingw32]

-- control frame ----------
c:0059 p:---- s:0252 b:0252 l:000251 d:000251 CFUNC :each
c:0058 p:---- s:0250 b:0250 l:000249 d:000249 CFUNC :inject
c:0057 p:0034 s:0246 b:0246 l:000245 d:000245 METHOD C:/RailsTest/sqlserver_test/vendor/plugins/adapters/sqlserver/lib/active_record/connect
ion_adapters/sqlserver_adapter.rb:956
c:0056 p:0054 s:0239 b:0239 l:001bd8 d:000238 BLOCK C:/RailsTest/sqlserver_test/vendor/plugins/adapters/sqlserver/lib/active_record/connect
ion_adapters/sqlserver_adapter.rb:931

maybe in >>>instantiate_fixtures flowers ??
raw_select(SELECT * FROM [binaries] WHERE ([binaries].[id] = 1) )

Shit.... I cant find why it is crashing with a segmentation fault.

Change 3:
I remove the binaries test

Finished in 120.1728 seconds.

2402 tests, 7219 assertions, 32 failures, 148 errors, 0 pendings, 0 omissions, 0 notifications
92.5062% passed

Problem 4:
test_clone(BasicsTest):
ActiveRecord::StatementInvalid: ODBC::Error: 37000 (105) [Microsoft][SQL Native Client]
[SQL Server]Kein schließendes Anführungszeichen nach der Zeichenfolge ''.:
INSERT INTO [topics]([approved], [author_email_address], [author_name], [bonus_time], [content], [last_read], [parent_id], [parent_title], [replies_count], [title], [type], [written_on])
VALUES(0, '[email protected]', 'David', '2000-01-01T15:28:00.000', '

there is some garbage in the content field...

there seems to be a problem with:
3) Failure:
test_array_content(BasicsTest)
[C:/RailsTest/sqlserver_test/vendor/rails/activerecord/test/cases/base_test.rb:131:in test_array_content' C:/RailsTest/sqlserver_test/vendor/rails/activesupport/lib/active_support/testing/setup_and_teardown.rb:62:inrun']:
<["one", "two", "three"]> expected but was
<"\x00K\xB2\x01/K\xB2\x01\x00\x00\x00\x00\x01\x00\x00\x00\x18\x88\x89\x041K\xB2\x010">.
which leaves garbage in the content field

class Topic < ActiveRecord::Base
  serialize :content

 ODBC is returning some garbage data:

update(DELETE FROM [topics])
execute(DELETE FROM [topics])
select(SELECT @@rowcount AS AffectedRows)
execute(INSERT INTO [topics]([id], [title], [author_name], [author_email_address], [written_on], [last_read], [bonus_time], [content], [approved], [replies_count]) VALUES (1, 'The First Topic', 'David', '[email protected]', '2003-07-16T16:28:11.223', '2004-04-15T00:00:00', '2005-01-30T15:28:00.000', 'Have a nice day', 0, 1))
execute(INSERT INTO [topics]([id], [title], [author_name], [written_on], [content], [approved], [replies_count], [parent_id], [type]) VALUES (2, 'The Second Topic of the day', 'Mary', '2004-07-15T16:28:00.009', 'Have a nice day', 1, 0, 1, 'Reply'))
execute(INSERT INTO [topics]([id], [title], [author_name], [written_on], [content], [approved], [replies_count]) VALUES (3, 'The Third Topic of the day', 'Nick', '2005-07-15T16:28:00.009', 'I''m a troll', 1, 1))
execute(INSERT INTO [topics]([id], [title], [author_name], [written_on], [content], [approved], [type], [parent_id]) VALUES (4, 'The Fourth Topic of the day', 'Carl', '2006-07-15T16:28:00.009', 'Why not?', 1, 'Reply', 3))
select(SELECT @@TRANCOUNT)
select(SELECT * FROM [topics] WHERE ([topics].[id] = 1) )
raw_select(SELECT * FROM [topics] WHERE ([topics].[id] = 1) )
handle_to_fields_and_rows_odbc ["id", "title", "author_name",
"author_email_address", "written_on", "bonus_time", "last_read", "content",
"approved", "replies_count", "parent_id", "parent_title", "type"]
Row content
4 : Fixnum |1|
15 : String |The First Topic|
5 : String |David|
22 : String |[email protected]|
: ODBC::TimeStamp |2003-07-16 16:28:11 223000000|
: ODBC::TimeStamp |2005-01-30 15:28:00 0|
: ODBC::TimeStamp |2004-04-15 00:00:00 0|
15 : String | | <<< 15 NUL Chars here .. or some other garbage
4 : Fixnum |0|
4 : Fixnum |1|
nil
nil
nil

unserialize_attribute(content) =>
"content"=>#<ODBC::Column:0x3aca2c0 @name="content", @table="", @type=12, @Length=0, @nullable=true, @scale=0, @precision=0, @searchable=true, @unsigned=true, @autoincrement=false>
length = 0 ????????

CREATE TABLE [topics]([id] int NOT NULL IDENTITY%281, 1%29 PRIMARY KEY, [title] varchar%28255%29, [author_name] varchar%28255%29, [author_email_address] varchar%28255%29, [written_on] datetime, [bonus_time] datetime, [last_read] datetime, [content] varchar%28max%29, [approved] bit DEFAULT 1, [replies_count] integer DEFAULT 0, [parent_id] integer, [parent_title] varchar%28255%29, [type] varchar%28255%29)

[content] is declared as varchar(max) but the ODBC adapter tells us it has length=0, Maybe there is a problem ??

from activerecord-sqlserver-adapter.

Problem 5:
There is a fixture for the test in pirates.yml, which uses strings for the date columns:

redbeard:
catchphrase: "Avast!"
parrot: louis
created_on: <%= 2.weeks.ago.to_s(:db) %>
updated_on: <%= 2.weeks.ago.to_s(:db) %>

so \vendor\rails\activerecord\lib\active_record\connection_adapters\abstract\quoting.rb will receive a string for the date column
and will NOT call quoted_date but will treat it as a string...

quote(Yar. : String, catchphrase)
quote(Avast! : String, catchphrase)
quote(2010-05-15 17:31:40 : String, created_on)
quote(2010-05-15 17:31:40 : String, updated_on)

So the question is: Is it allowed to assign string to datetime columns in ActiveRecord ?
Who is responsible for converting these to DateTime ?

from activerecord-sqlserver-adapter.

Problem 6:

dt = Time.zone.parse '2012-11-08 10:24:36.003'
dt.to_s(:iso8601) == dt.iso8601
=> false

dt.to_s(:iso8601)
=> "2012-11-08 10:24:36 +0100"
dt.iso8601
=> "2012-11-08T10:24:36+01:00"

And the usec are missing !!!!

dt.to_s(:db)
=> "2012-11-08 09:24:36"

and thats neither IS08601..
should the sqlserver-adapter override DateTime.to_s(:db) ??

from activerecord-sqlserver-adapter.

Here is what you could do and I'll do the rest. Do the legwork and find out the cases and give me a symbol back to operate off of. For instance, something like this.

http://gist.github.com/457443

from activerecord-sqlserver-adapter.

The easiest way would be: http://gist.github.com/457485

but that will NOT support all options of datetime2 und datetimeoffset in SQL Server2008

from activerecord-sqlserver-adapter.

Nice. I can confirm that passes all my tests in 2008. Will find some time today to run that thru 2000/2005. Would love to have a regression test to match too.

Here is what I think I'm gonna do. ActiveSupport was kind enough to give us date/time formatters for code abstraction and easy over rides or additional hooks. These even take a Proc object. So I'll be adding this formatter to during the initialization of the adapter and changing the method to use it.

http://gist.github.com/458629

Pretty cool egh? Now about that thing I told you about. You can do the same thing to fix all :db formatters for your app too. Create a file in app/initializers/"anyname.rb", perhaps date_time_formats.rb and put these in it.

http://gist.github.com/458631

That should get you passing all the tests by inserting fixtures in the correct format? Along with the adapter change, should work well?

from activerecord-sqlserver-adapter.

Remark on MSDN for MS SQL-Server 2008
http://msdn.microsoft.com/library/ms187819.aspx

"Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications."

so sooner or later these column types will hit us ;-)

from activerecord-sqlserver-adapter.

Interesting!! I'll read that in more detail later. From what I'm looking at tho they fly against the logic/convention of rails by making the DB dumb. The adapter should not stop anyone from using them but I do see some people complaining that datetime2 and/or datetimeoffset is not maintaining offsets right. Perhaps AR saying I want all things UTC and the DB saying another. Lovely :)

from activerecord-sqlserver-adapter.

Comment on ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS.merge!(:db...
"You can do the same thing to fix all :db formatters for your app too."

It feels wrong for me, that the >application< should know, how the current >database < expects date/time values to be formatted

from activerecord-sqlserver-adapter.

Agreed, I just read this ticket in Lighthouse that you mentioned in your wiki notes. What is the next steps I can do to help?

For instance I was looking for some way to set my DB up like yours. Would doing something like SET DATEFORMAT "dmy" when I initialize a connection mimic all things being returned by my db? Seems not :/ Confused at what I can do from this point. Lemme know.

from activerecord-sqlserver-adapter.

Klaus,

Now that I finished the TinyTds work, I'll be taking a look at this again. From what I have learned in that project, we could easily have the adapter use ISO formatted strings for everyone. Will be looking into it.

from activerecord-sqlserver-adapter.

In Rails3 (where ActiveSupport::CoreExtensions are gone), the following works fine:

Time::DATE_FORMATS[:db] = "%Y-%m-%dT%H:%M:%S"
Date::DATE_FORMATS[:db] = "%Y%m%d"

(see https://github.com/rails/rails/blob/3270c58ebb3143b3ab3b349fe339cdd4587468ee/activesupport/lib/active_support/core_ext/date_time/conversions.rb#L35)

Just in case anyone stumbles on this.

from activerecord-sqlserver-adapter.

I'm gonna close this out and focus on it under this ticket.
https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/#issue/72

Please direct all comments to there.

from activerecord-sqlserver-adapter.