KDGundermann commented 14 years ago

Hi Githubbies,

I am trying to change a datetime field in my database:

lm = Liefermenge.find(12345) lm.verladenam = Time.zone.now lm.save

EXECUTE (0.0ms) BEGIN TRANSACTION Liefermenge Update (0.0ms) ODBC::Error: 22008 (242) [Microsoft][SQL Native Client][SQL Server]Bei der Konvertierung e ines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert au¯erhalb des g³ltigen Bereichs.: UPDATE [liefermengen] SET [VerladenAm] = '2010-05-18 17:17:07.084' WHERE [AL_ID] = 12345 EXECUTE (0.0ms) ROLLBACK TRANSACTION

The interpretation of the date format '2010-05-18 17:17:07.084' known as ANSI SQL depends on the current LANGUAGE of the connection and fails here in germany

Please try :

SET LANGUAGE German

SELECT CAST('17.05.2010' AS datetime) SELECT CAST('2010-30-05' AS datetime) SELECT CAST('2010-05-30' AS datetime) SELECT CAST('2010-05-30T18:01:02' AS datetime)

SET LANGUAGE us_english

SELECT CAST('17.05.2010' AS datetime) SELECT CAST('2010-30-05' AS datetime) SELECT CAST('2010-05-30' AS datetime) SELECT CAST('2010-05-30T18:01:02' AS datetime)

It would be better to specify the date in ISO 8601 format: '2010-05-18T17:17:07.084'

But I am sorry, I am just a beginner in Ruby and can't find the code part where the conversion happens. ( I would suggest in sqlserver_adapter.rb -> quoted_date in row 309, but I am not quiet sure .. )

Greetings

Klaus

metaskills commented 14 years ago

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?

KDGundermann commented 14 years ago

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...

KDGundermann commented 14 years ago

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/

metaskills commented 14 years ago

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

KDGundermann commented 14 years ago

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.

KDGundermann commented 14 years ago

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 ??

KDGundermann commented 14 years ago

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 ....

metaskills commented 14 years ago

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.

KDGundermann commented 14 years ago

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 ?

metaskills commented 14 years ago

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

KDGundermann commented 14 years ago

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, 'david@loudthinking.com', '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', 'david@loudthinking.com', '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 |david@loudthinking.com| : 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 ??

KDGundermann commented 14 years ago

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 ?

KDGundermann commented 14 years ago

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) ??

metaskills commented 14 years ago

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

KDGundermann commented 14 years ago

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

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

metaskills commented 14 years ago

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?

KDGundermann commented 14 years ago

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 ;-)

metaskills commented 14 years ago

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 :)

KDGundermann commented 14 years ago

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

metaskills commented 14 years ago

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.

metaskills commented 13 years ago

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.

papillon commented 13 years ago

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.

metaskills commented 13 years ago

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.

rails-sqlserver / activerecord-sqlserver-adapter

Update of datetime field on german database fails #37

Please try :

"#{super}.#{sprintf("%03d",value.usec/1000)}"

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