Date::to_s doesn't respect connection language

[henrymazza]

If the machine isn't configured in english the connection will be stablished in that other language which breaks all .where(:date => Date.today) and similar commands because SQLServer starts to look for locale aware date format.

In my case

Devolucao.connection.user_options[:language]

returns "us_english" in my development machine and "Português" on my customer's server. On my machine '2010-12-24' is acceptable, on theirs it causes a out-of-range date conversion error. '24-12-2010' middle endian date otherwise is acceptable.

Easy way out is force the connection to use english, that should be done as soon the connection happens:

SET language 'us_english'

The better way would be to overload the :db conversion of Date::to_s to a locale sensible SQLServer compatible code.

PS: Error obtained

TinyTds::Error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.:

[metaskills]

This was a duplicate of issue 37. It was noted there to about changing the core extension of the :db format, but I think we can do better than that, especially since some people use the adapter for cross DB support and doing so would mess things up for others.

During development of TinyTds, I learned that is possible to just use ISO formatted date/times for everyone and most every column type. It had some oddities under 2000 but this is moot under the master/3.x version since we do not support 2000. So, if someone wanted to look at the text schemas for TinyTds, then make a nice patch to the adapter where type casting is done, I'd be happy to review it.

[henrymazza]

The adapter already has a to_sqlserver_string function for Datetime format for ODBC connections that doesn't support native types.

Correct me if I'm wrong, but the way to go is to make a method like that work for datetime like objects returning something like:

'CONVERT(DATETIME, #{my_ruby_date}, 127)'

And revamp the handle_to_names_and_values_dblib to handle that, right?

Sorry I'm not such a skilled programmer to fix it my self!

[metaskills]

No, that would be a bit heavy handed. There is a much simpler way. I'll get to this if no one else does later on.

[metaskills]

OK, this is coming up again from another issue and I will look at solving this for the 3.1 release. It should be as easy as switching over the casting to ISO8601 format.

[metaskills]

To give you something to go on, this is the query that is failing.

EXEC sp_executesql N'INSERT INTO [products] ([created_at], [name], [updated_at]) VALUES (@0, @1, @2); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', N'@0 datetime, @1 nvarchar(255), @2 datetime', @0 = '2011-07-19 10:28:59.247', @1 = N'MACBOOK AIR', @2 = '2011-07-19 10:28:59.247'

You should be able to open SMS and paste this in and see the same error in SMS. I would like to know a few details about that error, severity level, db number, etc. I would also like to know if chaining the date time to ISO8601 helps. So, does this work? And can you also tell me if the [created_at] column is indeed a datetime and not something fancy like a [date] or [datetime2], etc. Please double check in SMS.

EXEC sp_executesql N'INSERT INTO [products] ([created_at], [name], [updated_at]) VALUES (@0, @1, @2); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', N'@0 datetime, @1 nvarchar(255), @2 datetime', @0 = '2011-07-19 10:28:59.247', @1 = N'MACBOOK AIR', @2 = '2011-07-19T14:28:59Z'

[kranz]

Tried twice:

1. In SQL Server Express 2008R2:
   Copied and pasted in SMS, the insert went OK without any problem.
   No error, the record is created in the table, only message is (1 row(s) affected).

2. In SQL Server 2005 (the one i worked with in the last days)
   Copied and pasted in SMS, insert aborted, this is the error message

Messaggio 8114, livello 16, stato 5, riga 0
Errore durante la conversione del tipo di dati da varchar a datetime.
(Error during conversion of data type from varchar to datetime)

Created_at is (datetime,null) in both databases, and has been created by rake db:migrate in SQL 2005,
and by rake db:schema:load in SQL 2008

Can you tell me how to chain date time to ISO8601?

(I forgot to say that I tried both statements you pasted above, and both had the same behaviour: ok on 2008 and fail on 2005)

[metaskills]

Not right now, it would be just as easy if I did vs telling you and that will have to wait a bit since I am slammed at work right now. It should be only a few line change. May get to it soon. But I want to run the tests too.

[kranz]

I made further investigation:
created a new "Nation" model im my sample app.
First I created it with only name:string attribute, and deleted timestamps creation from the migration.
In this case CRUD operations work smoothly.
Then I added a migration that creates a new datetime field called "foundation_date".
A new [datetime] column is created in the database.
Then, if I create a new nation, all goes ok but only if both day and month values are less or equal to 12.
And the reason is that month and day are swapped, do not know why.

Example: in the web form in the foundation_date is filled with the string 5/10/1958, which is 5 October 1958
It is correctly detected and inserted in the database

Started POST "/nations" for 127.0.0.1 at 2011-07-21 19:15:42 +0200
Processing by NationsController#create as HTML
Parameters: {"utf8"=>"✓", "authenticity_token"=>".....................................", "nation"=>{"name"=>"FRANCE", "foundation_date"=>"5/10/1958"}, "commit"=>"Create Nation"}
EXECUTE (0.6ms) BEGIN TRANSACTION
SQL (1.4ms) EXEC sp_executesql N'INSERT INTO [nations]([foundation_date], [name]) VALUES (@0, @1); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', N'@0 datetime, @1 nvarchar(255)', @0 = '1958-10-05 00:00:00.000', @1 = N'FRANCE' [["foundation_date", Sun, 05 Oct 1958 00:00:00 UTC +00:00], ["name", "FRANCE"]]
EXECUTE (0.7ms) COMMIT TRANSACTION
Redirected to http://localhost:3000/nations/27
Completed 302 Found in 39ms

In the database it is stored as 1958-05-10 00:00:00.000

After creating from CRUD if i go to the index view i find that the date is showed wrong!
I use @nation.foundation_date.to_formatted_s(:long) to display in index.html.erb, and the date now shows as

May 10, 1958 00:00

So while source is dd/mm/yyyy, it is stored in database as mm/dd/yyyy and if you use a day >12 you get an error because it goes in place of the month...

When I try to enter a date with day>12 this is what happens

Started POST "/nations" for 127.0.0.1 at 2011-07-21 19:23:32 +0200
Processing by NationsController#create as HTML
Parameters: {"utf8"=>"✓", "authenticity_token"=>".....................................", "nation"=>{"name"=>"ITALY", "foundation_date"=>"17/3/1861"}, "commit"=>"Create Nation"}
EXECUTE (0.7ms) BEGIN TRANSACTION
SQL (2.9ms) EXEC sp_executesql N'INSERT INTO [nations]([foundation_date], [name]) VALUES (@0, @1); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', N'@0 datetime, @1 nvarchar(255)', @0 = '1861-03-17 00:00:00.000', @1 = N'ITALY' [["foundation_date", Sun, 17 Mar 1861 00:00:00 UTC +00:00], ["name", "ITALY"]]
TinyTds::Error: Errore durante la conversione del tipo di dati da varchar a datetime.: EXEC sp_executesql N'INSERT INTO [nations]([foundation_date], [name]) VALUES (@0, @1); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', N'@0 datetime, @1 nvarchar(255)', @0 = '1861-03-17 00:00:00.000', @1 = N'ITALY'
Completed 500 Internal Server Error in 57ms

ActiveRecord::StatementInvalid (TinyTds::Error: Attempt to initiate a new Adaptive Server operation with results pending: EXEC sp_executesql N'SELECT @@TRANCOUNT'):

And here the misleading error comes out. It is impossible from here to understand that the problem is a datetime conversion....

I also tried to assign iso861 value to the foundation_date in the create action in the controller but nothing changes...

Francesco

[kranz]

After studying and playing with the debugger now I have a first "draft" solution for "datetime", I would like to know if this is correct

lib /active_record/connection_adapters/sqlserver/database_statements.rb

at row 240 I did change from

v = value

to

v = ar_column && column.sql_type == 'datetime' ? value.iso8601 : value

It seems to work for attributes with "datetime" column data type, but is surely does not work for timestamps.
I also do not know what happens for "date" or "time" columns
Also, this works only when creating a new record, I have not found yet where do I have to look for update...

[metaskills]

Nice, you should really run the tests too. That should help immensely, we even have our own test files for our data types and you can edit the rake task (or set an env var) to only run our tests to get quick feedback.

[kranz]

What do I have to do in order to run tests?
Do not really know where to start from....

[metaskills]

The project readme talks about it. We run our tests pretty much like any other ruby library/project. In this case a rake task. But since we build our project on top of ActiveRecord, a clone of the rails repo is needed as well as an env var to specify the path to said clone. All detailed in the README and RUNNING_UNIT_TESTS files.

[metaskills]

Remember too, that as it stands now, our master branch is for the future 3-1-stable of rails. So when you clone the rails repo, make sure to checkout the 3-1-stable branch of rails before you bundle up and run the tests from our repo.

[kranz]

Read RUNNING_UNIT_TESTS and have many questions (as I told you I am not acquainted to the whole testing thing)
but the very first one is:

where do I find
test/connections/native_sqlserver_<connection_method>/connection.rb.

[metaskills]

The dir structure has changed a lot in rails 3.1 and that file is a bit outdated, the new config is test/config.yml.