-
Notifications
You must be signed in to change notification settings - Fork 557
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Update of datetime field on german database fails #37
Comments
Interesting... I'll have to look at this more next week. Some initial thoughts. Here is the code to talk around.
First, the super would just bring back a string that did not have usec/millisecond support in this format:
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? |
For information on "Using Date and Time Data" have a look at: I changed it to : I removed: I also tried: 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... |
Addendum: I have just found it in the docs:
A very interesting article by Robyn Page: |
Where did we leave off on this? What can I do to help? |
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. |
Now we have a running test environment for the adapter ( i think I have to document how to setup the environment ,-) Could it be that I am the first one using Ruby on Rails on Windows with an SQL Server with German language ?? |
Hi Ken, |
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. |
Thanks for your fast response. Here are the user options: Should I post here the steps I have taken so far, or should I post it in the Google Group ? |
Hard to say, can you detail here a summary of your issues at the lowest level with code examples and console output? |
Test documentation: ( Running on WinXP, Ruby 1.9.1p378, Rails 2.3.7, activerecord-sqlserver-adapter 2.3.6) cd git clone git://github.com/rails/rails.git sqlserver_test/vendor/rails cd sqlserver_test git branch adapter_test_on_windows rake test Using SQLServer via ODBC
Finished in 282.680933 seconds. 2402 tests, 2542 assertions, 11 failures, 1793 errors, 0 pendings, 0 omissions, 0 notifications Problem 1 : DateTime Format not in ISO 8601 Change 1 : def quoted_date(value) Finished in 243.892876 seconds. 2402 tests, 4056 assertions, 18 failures, 1335 errors, 0 pendings, 0 omissions, 0 notifications Problem 2: Why are these values Strings ??!?!? Change 2: timestamp fields are datetime fields and not Strings !!!! now = now.to_s(:db)Problem 3: -- control frame ---------- maybe in >>>instantiate_fixtures flowers ?? Shit.... I cant find why it is crashing with a segmentation fault. Change 3: Finished in 120.1728 seconds. 2402 tests, 7219 assertions, 32 failures, 148 errors, 0 pendings, 0 omissions, 0 notifications Problem 4: there is some garbage in the content field... there seems to be a problem with:
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 ?? |
Problem 5: redbeard: so \vendor\rails\activerecord\lib\active_record\connection_adapters\abstract\quoting.rb will receive a string for the date column
So the question is: Is it allowed to assign string to datetime columns in ActiveRecord ? |
Problem 6: dt = Time.zone.parse '2012-11-08 10:24:36.003' dt.to_s(:iso8601) And the usec are missing !!!! dt.to_s(:db) and thats neither IS08601.. |
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. |
The easiest way would be: http://gist.github.com/457485 but that will NOT support all options of datetime2 und datetimeoffset in SQL Server2008 |
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. 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. That should get you passing all the tests by inserting fixtures in the correct format? Along with the adapter change, should work well? |
Remark on MSDN for MS SQL-Server 2008 "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 ;-) |
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 :) |
Comment on ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS.merge!(:db... It feels wrong for me, that the >application< should know, how the current >database < expects date/time values to be formatted |
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. |
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. |
In Rails3 (where ActiveSupport::CoreExtensions are gone), the following works fine: Time::DATE_FORMATS[:db] = "%Y-%m-%dT%H:%M:%S" Just in case anyone stumbles on this. |
I'm gonna close this out and focus on it under this ticket. Please direct all comments to there. |
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
The text was updated successfully, but these errors were encountered: