Development Environment on Windows

This information is obsolete now and only shows the history how difficult it was to get the adapter running.

---

I tried to test the new adapterfor Rails3 on Windows and thought it would be a good idea to document my steps.
I didn’t know where to put my documentation, so I created a new entry here in the wiki.
( please tell me if I should put these information elsewhere .. maybe I should create an Issue ?)
-———————————
- Testing sqlserver-adapter 3.0.0.beta1
with ruby-odbc-099992pre3
with rails v3.0.0.beta4
on Windows XP on SQL Server 2005 (German)

---

24.06.2010

>>ruby -v
ruby 1.9.1p378 (2010-01-10 revision 26273) [i386-mingw32]

>> cd \
>> mkdir \RailsTest
>> cd \RailsTest
>> rails rails3test

>> git clone git://github.com/rails-sqlserver/arel.git rails3test/vendor/arel
>> git clone git://github.com/rails/rails.git rails3test/vendor/arel/vendor/rails
>> git clone git://github.com/rails-sqlserver/2000-2005-adapter.git rails3/vendor/plugins/adapters/sqlserver

>> cd rails3test

>> cd arel
>> git checkout origin/sqlserver

>> cd vendor/rails
>> git checkout

>> cd vendor\plugings\adapters\sqlserver
>> git branch adapter_test_on_windows
>> git checkout adapter_test_on_windows

>> set AREL_SOURCE=C:/RailsTest/rails3test/vendor/arel/
>> set RAILS_SOURCE=C:/RailsTest/rails3test/vendor/arel/vendor/rails # see sqlserver_helper.rb
>> set ARSQLSERVER_SOURCE=C:/RailsTest/rails3test/vendor/plugings/adapters/sqlserver #

1. Forward slashes on Windows ?!?!

>> gem install bundler
>> gem install shoulda —version 2.10.3
>> gem install tzinfo
>> gem install i18n
…
>> [ Insert gem ‘ruby-odbc’ in gemfile ]

>> rake test

C:/RailsTest/rails3test/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:211:in `rescue in log’: ODBC::Er
ror: 37000 (2750) [Microsoft][SQL Native Client][SQL Server]Spalte oder Parameter Nr. 8: Die angegebene Spaltengenauigkeit 55 ist gr÷▀er als die maximale Genauigkeit 38.: CREATE TABLE [numeric_data] ([id] int NOT NULL IDENTITY PRIMARY KEY, [bank_balance] decimal(10,2), [big_bank_balance] decimal(15,2), [world_population] decimal(10,0), [my_house_population] decimal(2,0), [decimal_number_with_default] decimal(3,2) DEFAULT 2.78, [temperature] float(8), [atoms_in_universe] decimal(55,0)) (ActiveRecord::StatementInvalid)

decimal only allows 38 digits on SQL Server 2005

see lighthouseapp

---

25.06.2010

rake test

1) Error:

test: For DatabaseStatements altering isolation levels should barf if the requested isolation level is not valid. (AdapterTestSqlserver):
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 [tasks] ([id], [starting], [ending]) VALUES (1, ‘2005-03-30 07:30:00.000’, ‘2005-03-30 09:30:00.000’)
..
C:/RailsTest/rails3test/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:239:in `insert_fixture’
…

169 tests, 231 assertions, 1 failures, 95 errors, 0 skips
rake aborted!

and most of them failing due to the DateTime Format !!!!

I modified quoted_date, Now down to 27 errors:
169 tests, 370 assertions, 2 failures, 27 errors, 0 skips
rake aborted!

The first error is in test: For chronic data types with a usec finding existing DB objects should find 003 millisecond in the DB with before and after casting. (AdapterTestSqlserver):
bc. 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.: DELETE FROM [sql_server_chronics] WHERE [datetime] = ‘2012-12-31 10:24:36.003’

      setup do
        @time = Time.now
        @db_datetime_003 = '2012-12-31 10:24:36.003'
        @db_datetime_123 = '2012-12-31 10:24:36.123'
        @all_datetimes = [@db_datetime_003, @db_datetime_123]
        @all_datetimes.each do |datetime|
          @connection.execute("INSERT INTO [sql_server_chronics] ([datetime]) VALUES('#{datetime}')")
        end
      end

This test tries to insert dates in the american dateformat in my database running on german language… Fail ;-)

When I change @db_datetime_003 to an ISO8601 format ‘2012-12-31T10:24:36.003’
the test fails too:

test: For chronic data types with a usec finding existing DB objects should find 003 millisecond in the DB with before and after casting. (AdapterTestSqlserver) [C:/RailsTest/rails3test/vendor/plugins/adapters/sqlserver/test/cases/adapter_test_sqlserver.rb:225]:
<"2012-12-31T10:24:36.003"> expected but was
<"2012-12-31 10:24:36.003">.

the test:

assert_equal @db_datetime_003, existing_003.datetime_before_type_cast

so the test is comparing to a string and assumes that datetime_before_type_cast is also a string ?!?!

ruby-odbc returns a ODBC::TimeStamp
In the adapter: handle_to_fields_and_rows_odbc will convert it to a string !!??

            row.each_with_index do |value, i|
              if value.is_a? ODBC::TimeStamp
                row[i] = value.to_sqlserver_string
              end
which is defined in ...\sqlserver\lib\active_record\connection_adapters\sqlserver_adapter\core_ext\odbc.rb
          def to_sqlserver_string
            date, time, nanoseconds = to_s.split(' ')
            "#{date} #{time}.#{sprintf("%03d",nanoseconds.to_i/1000000)}"
          end

and somewhere up in the chain it will converted into a Time value …
Is it expected, that the adapter stores datetime values as strings ?? ( for datetime_before_type_cast )
but in which format ?
- long english format
- short english format
- in the locale of the db
- as ISO8601 format ?

Searching Lighthouse for datetime related problems:
“Format two-digit years correctly ":https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/2019
before_type_cast not available for datetime fields
“ActiveRecord does not write invalid datetime and timestamp data when enable time zone support ":https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/3630
“TimeWithZone comparison fails for distinct instances of same time ":TimeWithZone comparison fails for distinct instances of same time
quoted_date converts time objects to default_timezone

RFC3339

30.06.2010

after the last change to quoted_date mosts test from the sql-server-adapter are running fine.
But I have still a lot of failing tests in rails. All Tests on tables with timestamp (created_at, updated_at) are failing due to the wrong dateformat.

Ticket and patch in:
Lighthouse #5019

---

Now at 2378 tests, 7891 assertions, 19 failures, 37 errors, 0 skips

Most errors are :

ActiveRecord::StatementInvalid: ODBC::Error: 37000 (156) [Microsoft][SQL Native Client][SQL Server]Falsche Syntax in der N婥 des ‘DISTINCT’-Schl��lwortes.: SELECT TOP (1) DISTINCT [companies].id FROM