rails-sqlserver/activerecord-sqlserver-adapter

SQL Server Adapter For Rails

activerecord-sqlserver-adapter's Issues

DSN-less connections

Since DBI was removed from the equation DSN-less connections do not work. We use them, so that we can deploy to servers without requiring additional server configuration.

Tables that named with schema could not get length of column.

Hi Ken.

I found problem in 'column' method.

For tables that named with schema(ex. rails.users),
they could not get length of column.
column of varchar(40) gets length => nil

I fixed the problem as likes as Issue 15.

Patch file is here.
git://gist.github.com/306342.git

I ran unit test and passed successfully.

Please confirm. I hope this patch would be applied to the master.

Best Regards.

Could not convert Guid value into string when insert a new record

I am using SQL 2008, IronRuby 1.1, Adapter version is branch 2-3-stable

Here is my db connection setting:

ActiveRecord::Base.establish_connection(

:mode => 'ADONET',
:adapter => 'sqlserver',
:host => 'localhost\SQLEXPRESS',
:database => 'Test',
:integrated_security=> 'true'
In my db, I got a table that have Guid column as its primary key.

But when I tried to create a new record to db, I got the following exception:

C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract_adapter.rb:221:in `log': System::Data::SqlClient::SqlException: Conversion failed when converting from a character string to uniqueidentifier.:
ring to uniqueidentifier.: INSERT INTO [OrgHeader]([OH_Code], [OH_PK]) VALUES('', '--- !ruby/object:System::Guid {}\n') (ActiveRecord::StatementInvalid)

Here is the stack trace:

from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.8/lib/active_record/connection_adapters/sqlserver_adapter.rb:945:in `do_execute'

from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.8/lib/active_record/connection_adapters/sqlserver_adapter.rb:409:in `execute'

from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:259:in `insert_sql'

from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.8/lib/active_record/connection_adapters/sqlserver_adapter.rb:932:in `insert_sql'

from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert'

from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/query_cache.rb:19:in `insert_with_query_dirty'

from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2949:in `create'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/timestamp.rb:53:in `create_with_timestamps'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/callbacks.rb:266:in `create_with_callbacks'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2915:in `create_or_update'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/callbacks.rb:250:in `create_or_update_with_callbacks'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2590:in `save!'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/validations.rb:1100:in `save_with_validation!'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/dirty.rb:87:in `save_with_dirty!'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:200:in `save_with_transactions!'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:136:in `transaction'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:182:in `transaction'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:200:in `save_with_transactions!'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:208:in `rollback_active_record_state!'
from C:/Program Files/IronRuby 1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:200:in `save_with_transactions!'

As you can see from the SQL, it did not collectly convert the GUID value into string .

I change 'handle_to_fields_and_rows_adonet(handle)' in sqlserver_adapter like this:

def handle_to_fields_and_rows_adonet(handle)
if handle.has_rows
fields = []
rows = []
fields_named = false
while handle.read
row = []
handle.visible_field_count.times do |row_index|
value = handle.get_value(row_index)
value =
if value.is_a? System::String
value.to_s
elsif value.is_a? System::DBNull
nil
elsif value.is_a? System::DateTime
value.to_string("yyyy-MM-dd HH:MM:ss.fff").to_s
elsif value.is_a? System::Guid
value.to_s
else
value
end
row << value
fields << handle.get_name(row_index).to_s unless fields_named
end
rows << row
fields_named = true
end
else
fields, rows = [], []
end
[fields,rows]
end
It seems to work then. But I just not sure whether I change it in a right way.

Update of datetime field on german database fails

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

Spatial/GIS support

Does the SQL Server adapter support Spatial/GIS data types? If so, is it stand alone or with GeoRuby extensions?

Execute Procedure Friendly With Multiple Result Sets

We are currently using 2.2.19 which is obviously still using DBI.
What we did here was create a mixin with new methods to call SPs that
return multiple result sets. Basically all it did was grab the raw
connection to call the SP, however we had problems with this due to
dropped connections which were not handled by the auto reconnection
functionality built into the adapter since we dropped down to the raw
ODBC connection. What I did to get around this was to make the
auto_reconnected? method public so we could hook into that. That has
worked great, but I don't like that it lives outside the adapter.

Now that the adapter is not using DBI, it would be much cleaner to
implement multiple result sets directly in the adapter. I've already
started coding for returning multiple result sets and hope to have
something ready soon. I did test 2.3.4 for multiple result sets after
I sent my original email, and I'm only getting the first result back.
I agree that returning a 2-dim array is bad form so I'm looking into
reducing the array returned to 1-dim if there was only one result set.

I'll let you know how it goes.

As far as the transaction issue goes, we're just going to work around
it as we have other issues associated with it that I don't really want
to go into. Suffice it to say, our original idea still wouldn't have
worked anyways.

Chris

Well, here's a patch against the latest clone I did from this morning,
hope this works for you. I haven't had the time to create or even run
any tests, so any guidance there would be appreciated.

All you'll need to do is create a test rails app, unpack the adapter
and patch the connection adapter. Next you'll need to create an SP
that returns mutiple result sets. unfortunately, I'm not fluent in
SQL Server, we rely on our dba's to do their magic, so I don't have an
SP for you to test with, so you'll need to create one of your own that
returns multiple result sets. I've asked one of the dba's here to
create a simple one for me I can include in the tests I plan to write,
so if needed, I could send that along afterwards.

So once you have all that, just start up a dev console, and run
execute_procedure and check the results. Let me know what you think.

Chris

I went thru the adapter and found which proc I was using that returned multiple result sets, it was:

EXEC sp_helpconstraint tablename

When I used this in #remove_default_constraint I had to pass 'nomsg' as the second argument to avoid the blow up the second result set would cause. Can you confirm using this proc with your changes?

• Ken

ActiveRecord::Base.execute_procedure("sp_helpconstraint", 'accounts')

[[{"Object Name"=>"accounts"}], [{"status_enabled"=>"(n/a)",
"status_for_replication"=>"(n/a)", "delete_action"=>"(n/a)",
"constraint_name"=>"DF__accounts__active__217D344A",
"constraint_keys"=>"((1))", "update_action"=>"(n/a)",
"constraint_type"=>"DEFAULT on column active"}, ... ]]

Several patches including 1205 deadlock victim retry logic, fixes for auto-reconnect, ensure finish statement handles

We have implemented several patches on top of 2_3_4 against rails and activerecord 2.2.2

see http://github.com/jrafanie/2000-2005-adapter/commits/2_3_4_patches

Here's a summary:

1205 "deadlock victim" retry logic at the transaction and single query level. Don't try to rollback the transaction on a 1205 since SQL server does this and just complains about a "rollback" without a "begin".

Convenience methods: spid, database version, product version, product level, product edition,

Apply lock timeout and transaction isolation level to a connection on initialize and reconnect

Don't auto-reconnect on commits, rollback, and in the middle of a transaction.

Ensure we finish the statement handles in a few places.

Of special note, the log method and transaction method both come from 2.2.2 and were modified to handle the 1205 deadlock victim errors. It appears that the transaction method has undergone many changes from 2.2.2 to 2.3.2. Oh well. We can work to get it into the mainstream adapter.

invalid sql statement generated

In our staging environment:
ActiveRecord::StatementInvalid (DBI::DatabaseError: 37000 (8169) [unixODBC][FreeTDS][SQL Server]Conversion failed when converting from a character string to uniqueidentifier.: UPDATE [users] SET [session_id] = 'c688f203a8c065ce384bfadd307b3aef', [perishable_token] = 'JQExkWlp3SpYkEALJNy5' WHERE [user_id] = '37fe291973943e428127c131b01eb2a8')

But, locally it works:
UPDATE [users] SET [session_id] = '640911447673ec4d9f309aacc40d7abc', [perishable_token] = 'IoQ3Wl53f18u-tc2Vz3E' WHERE [user_id] = '1929FE37-9473-423E-8127-C131B01EB2A8'

Any thoughts on what would be causing this issue with just a different environment? The gems are consistent on both machines, so are the ruby versions. The local machine is running OS X, and the staging machine is running Ubuntu 9.10. Development mode on the staging environment still raises this error. We are connecting to the same MSSQL server in both environments. We are thinking that the statement generated would be the same on both but for some reason it isn't.

hardcoded primary key

There is add_limit_offset_for_association_limiting!(sql,options) method wich contains hardcoded primary_key column name - "id":

  def add_limit_offset_for_association_limiting!(sql, options)
    sql.replace %|
      SET NOCOUNT ON
      DECLARE @row_number TABLE (row int identity(1,1), id int)
      INSERT INTO @row_number (id)
        #{sql}
      SET NOCOUNT OFF
      SELECT id FROM (
        SELECT TOP #{options[:limit]} * FROM (
          SELECT TOP #{options[:limit] + options[:offset]} * FROM @row_number ORDER BY row
        ) AS tmp1 ORDER BY row DESC
      ) AS tmp2 ORDER BY row
    |.gsub(/[ \t\r\n]+/,' ')
  end

while ActiveRecord in associations.rb processes parametr primary_key:

    def select_limited_ids_list(options, join_dependency)
      pk = columns_hash[primary_key]

      connection.select_all(
        construct_finder_sql_for_association_limiting(options, join_dependency),
        "#{name} Load IDs For Limited Eager Loading"
      ).collect { |row| connection.quote(row[primary_key], pk) }.join(", ")
    end

So it doesn't work with custom :primary_key option

No Memory Error on Windows ODBC with nvarchar(max)

Reported by Colin Stevens | June 10th, 2009 @ 05:51 AM

When using ODBC on the Windows plaform, the nvarchar(max) datatype causes rows to overflow. This is a similar ticket to that already posted, in that case the answer was to change the text field to something realistic in freetds.ini but this solution is not available when using native odbc drivers on windows.

Regenerate dependent views when migrations alter tables

When columns are added/removed from tables views that depend on those tables might become invalid. This is definitely the case where a view is defined as "select * from table...".

The sp_refresh stored proc can be called against the invalid view to make the view valid again.

There is actually a tree of dependencies where view A depends on the modified table T, and view B depends on view A. In that case view A must be refreshed, then view B must be refreshed.

If the various schema methods such as add_column and remove_column could take this into account that would be very helpful.

It seems like the key is figuring out how to build the dependency tree and then iterating through the tree calling sp_refresh on the appropriate views in the appropriate order.

There's some sql here: http://msdn.microsoft.com/en-us/library/ms187821.aspx which could offer some clues on how to build the dependency tree.

I don't know if a view refresh is required if a column's data type is changed. That is something I'll need to test.

UTF-8 conversion on ruby 1.9.1

Hi,

When using your excellent sqlserver adapter with ruby 1.9.1, uploading a binary (PNG in my case) will fail in the binary_to_string method with the following error:

ArgumentError (invalid byte sequence in UTF-8):
/Users/jeroen/.bundle/ruby/1.9.1/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:61:in binary_to_string' /Users/jeroen/.bundle/ruby/1.9.1/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:70:intype_cast'

I looked at the source of activerecord and found that the sqlite adapter has an apparent fix/workaround for this:

def binary_to_string(value)
  value = value.dup.force_encoding(Encoding::BINARY) if value.respond_to?(:force_encoding)
  # etc.

Adding the above line to the sqlserver adapter fixes the problem for me.

The sqlite adapter also uses this for string_to_binary:

def string_to_binary(value)
  value = value.dup.force_encoding(Encoding::BINARY) if value.respond_to?(:force_encoding)
  # etc.

Could you incorporate these changes in your adapter and release a new version?

Thanks,
Jeroen

ADO Might Not Work. Add ODBC database.yml Example

Here was my understanding and goals.

• I'd really like to not support ADO at some point in time, especially since the DBD for it is non existent anymore for DBI moving forward.
• The working version (#?) of ADO for DBI is old and I believe only works with DBI v0.1 or even v0.023 or something like that.

So when I did the adapter, I knew the versions of DBI/DBD-ODBC were not ADO friendly and they were not going to be from what I heard from the author. That said, there may only be a few needed tweaks to make those old versions of DBI work for the ADO.

All of this may be wrong info to. I'd look forward to seeing what errors happen and knowing the version numbers of the moving parts.

bigint column returning Numeric value out of range on select

When using bigint in tables with 2.2.19, following error returned:

SomeClass.find(:first, :conditions => {:some_bigint_column => 13}, :select => : some_bigint_column)
DBI::DatabaseError: 22003 (0) [unixODBC][FreeTDS][SQL Server]Numeric value out of range
from /usr/local/lib/ruby/gems/1.8/gems/dbd-odbc-0.2.4/lib/dbd/odbc/statement.rb:43:in fetch' from /usr/local/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi/handles/statement.rb:211:infetch'
from /usr/local/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi/handles/statement.rb:240:in each' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:879:ininject'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:879:in handle_as_array' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:865:inraw_select'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:816:in select' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/abstract/database_statements.rb:7:inselect_all_without_query_cache'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/connection_adapters/abstract/query_cache.rb:61:in select_all' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:586:infind_by_sql'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:1345:in find_every' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:1307:infind_initial'
from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.1.1/lib/active_record/base.rb:538:in find' from /home/deploy/ahl/vendor/plugins/geokit/lib/geo_kit/acts_as_mappable.rb:109:infind'
from (irb):1

2.3 exception on implicit DateTime comparison

I noticed this happened in my app when I updated to 2.3.

Here's some system info and the stack trace:

$ uname -a
Linux lkyrala-ibex-64 2.6.28-17-generic #58-Ubuntu SMP Tue Dec 1 21:27:25 UTC 2009 x86_64 GNU/Linux
$ ruby -v
ruby 1.8.6 (2009-06-08 patchlevel 369) [x86_64-linux]
$ gem -v
1.3.4

NoMethodError (private method `split' called for Thu, 01 Mar 2007 15:56:00 +0000:DateTime):
  /local/ruby-1.8.6/gems/gems/activerecord-sqlserver-adapter-2.3/lib/active_record/connection_adapters/sqlserver_adapter/core_ext/dbi.rb:35:in `parse'
  dbi (0.4.3) lib/dbi/row.rb:66:in `convert_types'
  dbi (0.4.3) lib/dbi/row.rb:65:in `each'
  dbi (0.4.3) lib/dbi/row.rb:65:in `each_with_index'
  dbi (0.4.3) lib/dbi/row.rb:65:in `convert_types'
  dbi (0.4.3) lib/dbi/row.rb:75:in `set_values'
  dbi (0.4.3) lib/dbi/handles/statement.rb:213:in `fetch'
  dbi (0.4.3) lib/dbi/handles/statement.rb:240:in `each'
  /local/ruby-1.8.6/gems/gems/activerecord-sqlserver-adapter-2.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:879:in `inject'
  /local/ruby-1.8.6/gems/gems/activerecord-sqlserver-adapter-2.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:879:in `handle_as_array'
  /local/ruby-1.8.6/gems/gems/activerecord-sqlserver-adapter-2.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:865:in `raw_select'
  /local/ruby-1.8.6/gems/gems/activerecord-sqlserver-adapter-2.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:816:in `select'

Any idea why this happens?

ROW_NUMBER based limit for 2005 and later

Altered add_limit_offset! to use new ROW_NUMBER() method of limiting in
SQL 2005 and higher

penwellr@31b7a5cb39c5eb7a73695ac3079f63039b8efb1d

Use database specified in the file database.yml

The only way to use the driver is set a default database in the odbc connection. I would not like to create one for each database so I added the new tag :database in the database.yml

2000-2005-adapter\lib\active_record\connection_adapters\sqlserver_adapter.rb

Insert at line 198:
raw_connection_do "USE #{config[:database]}" if config[:database]

190   def initialize(logger,config)
191     @connection_options = config
192     connect
193     super(raw_connection, logger)
194     initialize_sqlserver_caches
195     unless SUPPORTED_VERSIONS.include?(database_year)
196       raise NotImplementedError, "Currently, only #{SUPPORTED_VERSIONS.to_sentence} are supported."
197     end
198     raw_connection_do "USE #{config[:database]}" if config[:database]
199   end

New configurations:

D:\Marco\Progetti\Ruby\Gems\2000-2005-adapter\test\connections\native_sqlserver_odbc\connection.rb

ActiveRecord::Base.configurations = {
  'arunit' => {
    :adapter  => 'sqlserver',
    :mode     => 'ODBC',
    :host     => 'localhost',
    :database => 'activerecord_unittest',
    :username => 'rails',
    :dsn => 'activerecord_unittest'
  },
  'arunit2' => {
    :adapter  => 'sqlserver',
    :mode     => 'ODBC',
    :host     => 'localhost',
    :database => 'activerecord_unittest2',
    :username => 'rails',
    :dsn => 'activerecord_unittest'
  }
}

Default Functions for Column Values

Re: this thread on the list.

When attempting to connect to a SQL Server instance using v2.2.19, the adapter would bomb out with a stack trace along the lines of:

/usr/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.19/ 
lib/active_record/connection_adapters/sqlserver_adapter.rb:1084:in 
`column_definitions': undefined method `[]' for nil:NilClass 
(NoMethodError) 
        from /usr/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005- 
adapter-2.2.19/lib/active_record/connection_adapters/ 
sqlserver_adapter.rb:1062:in `collect' 
        from /usr/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005- 
adapter-2.2.19/lib/active_record/connection_adapters/ 
sqlserver_adapter.rb:1062:in `column_definitions' 
        from /usr/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005- 
adapter-2.2.19/lib/active_record/connection_adapters/ 
sqlserver_adapter.rb:618:in `columns' 
        from /usr/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005- 
adapter-2.2.19/lib/active_record/connection_adapters/ 
sqlserver_adapter.rb:1109:in `special_columns' 
        from /usr/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005- 
adapter-2.2.19/lib/active_record/connection_adapters/ 
sqlserver_adapter.rb:1113:in `repair_special_columns' 
        from /usr/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005- 
adapter-2.2.19/lib/active_record/connection_adapters/ 
sqlserver_adapter.rb:815:in `select' 
        from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/ 
active_record/connection_adapters/abstract/database_statements.rb:7:in 
`select_all_without_query_cache' 
        from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/ 
active_record/connection_adapters/abstract/query_cache.rb:62:in 
`select_all' 
        from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/ 
active_record/base.rb:661:in `find_by_sql' 
        from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/ 
active_record/base.rb:1553:in `find_every' 
        from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/ 
active_record/base.rb:615:in `find' 
        from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/ 
active_record/base.rb:635:in `all' 
        from ./parse_mail.rb:6 

This seems to be related to the following line:

ci[:default_value].match(/\A\(+N?'?(.*?)'?\)+\Z/)[1] 

When we add print out ci[:default_value].inspect on line 1090 of sqlserver_adapter.rb we get the following output right before the stack trace:

nil 
nil 
nil 
nil 
nil 
"((-1))" 
"create default [CurrentDateTime] as getdate()"

Now, adding a rescue nil suffix to the :default_value regex line catches the NilClass error and fixes it locally for us, but doesn't seem sustainable.

Identity insert detection false positives

When an insert statement contains a column name that includes the word "insert" and a value with the string "id" the adapter turns on identity insert.

Proposed fix at http://github.com/bfabry/2000-2005-adapter/commit/113ca7e97c662a53ac60bebff1fc3dbd4ce8d0d7

sqlserver_adapter.rb:929: [BUG] Segmentation fault

Rails version: 2.3.5
Ruby version: 1.8.7
activerecord-sqlserver-adapter version: 2.3.5

i have setup a connection with a MSql 2005 database via FreeTDS and iODBC, and both the tsql and the iodbctest commands work propertly.

When triing to get a ActiveRecord model i get the following error message:

warmachine:MSSqlTest cor3y$ script/console
Loading development environment (Rails 2.3.5)
:: EventRegistrationMail

/Library/Ruby/Gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:929: [BUG] Segmentation fault
ruby 1.8.7 (2009-06-12 patchlevel 174) [universal-darwin10.0]

Abort trap
warmachine:MSSqlTest cor3y$

What should i do?

table and column alias can't handle alias like t12_r6

adapter breaks session_id?

Seems like

config.action_controller.session_store = :active_record_store

doesn't work properly with adapter.
I get error while accessing saved session stored in sqlserver

incompatible marshal file format (can't be read)
format version 4.8 required; 29.230 given

I tried same code with default sqlite3 adapter, and with default session_store (wich is cookies) and there were no problems.

I watched
request.session_options[:id]
and it looks different while using sqlserver-adapter.

While stored in cookies or sqlite3 session_id always start with BAh7 sequence (the same as "data" field starts), and when i switch to sqlserver adapter id field becomes shorter and starts with something different.

session Id that works:
BAh7CToPc2Vzc2lvbl9pZCIlZjU5NTAxMWY2YmUxMDg5Yjk3MWQ5N2U2NWRiNTAyYzI6CXVzZXIiEdCQ0LvRjNC+0YjQsDoQX2NzcmZfdG9rZW4iMXByRDd5UlhuYlU0dStPNmFLTHBWOWE4UGt4MWIwNU53b0swZzRWT3Vwalk9IgpmbGFzaElDOidBY3Rpb25Db250cm9sbGVyOjpGbGFzaDo6Rmxhc2hIYXNoewAGOgpAdXNlZHsA--a765cf0a8cf7467a3273fbaca8c6a826fbbfd309

session id that doesn't work (stored in sqlserver):
c9e537dea19340ca5b7082a3e0b3d7cb

Something concerned marshlizing and converting datatypes?

Rakefile no longer contains test tasks

As of commit 9265961 the Rakefile no longer has the test tasks that RUNNING_UNIT_TESTS refers to. We need to either:

a) update RUNNING_UNIT_TESTS to no longer refer to running the rake test task inside the adapter

or (more usefully)

b) update Rakefile to have those tasks again (or provide a tasks/ directory they can be loaded from).

JDBC - JRUBY

Will this adapter work with jruby - jdbc connectivity to sql server?

Multiple order by with same column name on different tables fail

Reported by Tomas Varsavsky | May 12th, 2009 @ 09:09 PM

A find that has multiple order bys on different tables but the same column name fails. The first order by is applied but the last one is removed. For example:

Facility.find(:all, 
  :include => [:duty_holder], 
  :order => 'duty_holders.name ASC, facilities.name ASC'
)

The SQL generated only has the first order by.

The problem is in method add_order_with_sqlserver_unique_checking in SQLServerActiveRecordExtensions. This method tries to remove duplicate order bys by looking at the column name. In our case the column name is the same and so the second one is ignored. The solution is to include the table name in the hash key used for duplicate checking.

def add_order_with_sqlserver_unique_checking!(sql, order, scope = :auto)
  ...
  unique_order_key = "#{ord_table_name}#{ord_column_name}"
  if (ord_table_name && ord_table_name == select_table_name && unique_order_hash[unique_order_key]) || unique_order_hash[unique_order_key]
    array
  else
    unique_order_hash[unique_order_key] = true
    array << "#{ord} #{dir}".strip
  end
  ...
end

Еncoding is not configurable

If one needs to connect a database with the encoding that deffers from UTF8 he have to past Iconv methods inside raw metods
(usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3/lib/active-record/connection_adapters/sqlserver_adapter.rb)

rowguid uniqueidentifier column - default value causes error on create

After using the sql server replication wizard on my tables, which added in the rowguid column of type 'uniqueidentifier', I am now getting an error when trying to save a new record. The error is:

ActiveRecord::StatementInvalid in MallsController#create

ODBC::Error: 37000 (8169) [unixODBC][FreeTDS][SQL Server]Conversion failed when converting from a character string to uniqueidentifier.: INSERT INTO [malls]([name], [created_at], [updated_at], [rowguid]) VALUES('ewqrweq', '2010-06-28 18:18:27.139', '2010-06-28 18:18:27.139', 'newsequentialid(')

partial stack trace:

/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract_adapter.rb:221:in log' /usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.6/lib/active_record/connection_adapters/sqlserver_adapter.rb:916:indo_execute'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.6/lib/active_record/connection_adapters/sqlserver_adapter.rb:389:in execute' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:259:ininsert_sql'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.6/lib/active_record/connection_adapters/sqlserver_adapter.rb:903:in insert_sql' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:44:ininsert_without_query_dirty'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/query_cache.rb:19:in insert' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2949:increate_without_timestamps'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/timestamp.rb:53:in create_without_callbacks' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/callbacks.rb:266:increate'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2915:in create_or_update_without_callbacks' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/callbacks.rb:250:increate_or_update'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/base.rb:2573:in save_without_validation' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/validations.rb:1090:insave_without_dirty'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/dirty.rb:79:in save_without_transactions' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:229:insend'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:229:in with_transaction_returning_status' /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/database_statements.rb:136:intransaction'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/transactions.rb:182:in `transaction_without_cache_transaction'

Last character in nchar and nvarchar columns breaks

Hi,

I'm using:
Windows Vista SP1
SQLServer 2008 Express
Rails 2.3.4
activerecord-sqlserver-adapter (2.2.22)
dbd-odbc (0.2.4)
dbi (0.4.1)
SQL Server ODBC driver (non-native)

I created the following table:
CREATE TABLE [dbo].[Region]([RegionID] [int] NOT NULL ,
[RegionDescription] [nchar] %2850%29 NOT NULL) ON [PRIMARY]

I used this model:
class Region < ActiveRecord::Base
set_table_name 'Region'
set_primary_key "RegionID"
end

And these database.yml settings:
development:
adapter: sqlserver
mode: odbc
dsn: northwind_dev
username: ***
password: ***
host: localhost

The last character of the RegionDescription column is interpreted as binary or breaks in a way I cannot explain. See the following rails console excerpt:

>> r = Region.new
=> #<Region RegionID: nil, RegionDescription: nil>
>> r.RegionID = 5
=> 5
>> r.RegionDescription = 'Test'
=> "Test"
>> y r
--- !ruby/object:Region
attributes:
  RegionDescription: Test
  RegionID: 5
attributes_cache: {}

changed_attributes:
  RegionDescription:
  RegionID:
new_record: true
=> nil
>> r
=> #<Region RegionID: 5, RegionDescription: "Test">
>> r.save
=> true
>> r.reload
=> #<Region RegionID: 5, RegionDescription: "Test                                             \000\000...">
>> y r
--- &id001 !ruby/object:Region
attributes:
  RegionDescription: !binary |
    VGVzdCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
    ICAgIAAA

  RegionID: 5
attributes_cache: {}

changed_attributes: {}

errors: !ruby/object:ActiveRecord::Errors
  base: *id001
  errors: {}

marked_for_destruction: false
new_record: false
=> nil

I get the same problem with nvarchar columns, but only if they are full. E. g. if I have nvarchar(5) and put in 'ABCD', there is no problem. With 'ABCDE', something like 'ABCD\000\000...' is returned.

This does not happen with the SQL Server Native Client 10.0 ODBC driver. However, I experience other issues here (with ntext and nvarchar(max) columns).

Maybe I am doing something wrong or missing a workaround. I tried to google for this but couldn't find anything. Any ideas?

Thanks,
Simon

missing lib/activerecord-sqlserver-adapter.rb?

I am sure I must be missing something obvious but when I tried to use the gem in a Rails app after installing the gem I got this error:
no such file to load -- activerecord-sqlserver-adapter
It seems that lib/activerecord-sqlserver-adapter.rb is not in the .gemspec.

sqlserver 2008 varchar(max) returned as binary via EasySoft odbc driver

Swapping the FreeTDS odbc driver for the EasySoft driver on ubuntu 9.04 with RoR and sqlserver 2008 caused fields typed as varchar(max) to be interpreted as binary.

EasySoft support response:
The problem is that your application is taking the large size of the
field and assuming it is a BLOB type. FreeTDS is working because they
do not support VARCHAR(MAX) to its limit - they only return a subset
of the data. In your ODBC data source try adding / amending :-
VarMaxAsLong = Yes
If that does not work try :-
DisguiseLong = Yes
LimitLong = size - Where size is a byte value of the maximum size of
the field.

Setting all three attributes as suggested in /etc/odbc.ini file made
the problem did go away. The varchar(max) data displays correctly as
text.

2.2.19 breaks on tables with non-ASCII chars in column names

As reported via Google Group: http://groups.google.com/group/rails-sqlserver-adapter/browse_thread/thread/b8037769e73a391c

Thanks :)

RegexpError (premature end of regular expression: /([\w-]*)\s+as\s
+AnlassLektionPeriodizit t/):
/usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/
lib/active_record/connection_adapters/sqlserver_adapter.rb:1004:in
views_real_column_name' /usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/ lib/active_record/connection_adapters/sqlserver_adapter.rb:1076:in column_definitions'
/usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/
lib/active_record/connection_adapters/sqlserver_adapter.rb:1062:in
collect' /usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/ lib/active_record/connection_adapters/sqlserver_adapter.rb:1062:in column_definitions'
/usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/
lib/active_record/connection_adapters/sqlserver_adapter.rb:618:in
columns' /usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/ lib/active_record/connection_adapters/sqlserver_adapter.rb:1109:in special_columns'
/usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/
lib/active_record/connection_adapters/sqlserver_adapter.rb:1113:in
repair_special_columns' /usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/ lib/active_record/connection_adapters/sqlserver_adapter.rb:815:in select'
app/models/evento/sync.rb:10:in write_course' app/models/course.rb:90:insync_write'
app/controllers/courses_controller.rb:80:in `update'

Is there any way to debug this further or is there anything else that
would help you solve this or narrow down where the problem is? We are
using:

UNIXODBC 2.2.11
FreeTDS 0.82
Debian GNU/Linux 5
Rails and AR 2.3.2

the add_limit_offset can not work?

:limit => 3, :offset => 5
SELECT * FROM (SELECT TOP 3 * FROM (SELECT TOP 8 * FROM books) AS tmp1) AS tmp2

the result of this sql maybe equals this?

:limit => 3, :offset => 80000
SELECT * FROM (SELECT TOP 3 * FROM (SELECT TOP 80003 * FROM books) AS tmp1) AS tmp2

Use correct SQL in migrations to not allow NULL for existing rows with default value.

As my co-worker pointed out.

alter table add column blah blah default x WITH VALUES

"gem install" on Windows can hit the path size limitation becuase of deeply nested modules

As seen below, generating RI documentation for ActiveRecord::ConnectionAdapters::SQLServerCoreExtensions::ActiveRecord::JoinAssociationChanges::InstanceMethods fails because the path is too long. A workaround is to install IronRuby to "c:\IronRuby 0.9.5.0" but we would like to be able to support non-admin scenarios where the user cannot install to "c:". Also, even if IronRuby is installed to "c:", RubyGems installs gems to %HOME% (which can also be a long path) if "gem install" is run by a non-admin, or by an admin without elevating permissions.

It would be nice if such long module names were not used. Ofcourse, it would be nice if Windows did not have this limitation, but that is unlikely to change because of legacy reasons as described in https://blogs.msdn.com/bclteam/archive/2007/02/13/long-paths-in-net-part-1-of-3-kim-hamilton.aspx

set GEM_HOME="C:\Program Files (x86)\IronRuby 0.9.5.0\Lib\ironruby\gems\1.8"
gem install activerecord-sqlserver-adapter
Successfully installed activerecord-sqlserver-adapter-2.3.4
1 gem installed
Installing ri documentation for activerecord-sqlserver-adapter-2.3.4...
ERROR: While executing gem ... (Errno::ENOENT)
No such file or directory ./ActiveRecord/ConnectionAdapters/SQLServerCoreExtensions/ActiveRecord/JoinAssociationChanges/InstanceMethods/cdesc-InstanceMethods.yaml

Duplicate last row with group by query

Using this query:

select distinct terms.id, count(workshops.id) as workshop_count, sum(workshops.time_spent)/3600 as term_time_spent, terms.semester, terms.year
from terms left join workshops on workshops.term_id=terms.id
and workshops.cancelled = 0
and workshops.visible = 1
and workshops.enabled = 1
where terms.id like '1'
group by terms.id, terms.semester, terms.year
order by terms.year

We're always getting one additional row, which is a duplicate row.

Table definitions are:

create_table "workshops", :force => true do |t|
  t.text     "prerequisites"
  t.integer  "department_id"
  t.integer  "seats"
  t.text     "comments"
  t.string   "number"
  t.integer  "offering_id"
  t.integer  "location_id"
  t.datetime "starts_at"
  t.datetime "ends_at"
  t.boolean  "visible"
  t.boolean  "enabled"
  t.datetime "created_at"
  t.boolean  "cancelled"
  t.boolean  "attendance_submitted"
  t.integer  "term_id"
end

create_table "terms", :force => true do |t|
 t.string  "semester"
 t.string  "year"
 t.boolean "is_current"
end

This seems like a fairly simple query, and we did run this through SQLServer 2005's console directly and received only one record.

Rails 2.3.2
Adapter version 2.2.19

Any help would greatly be appreciated.

column_definitions still generates invalid sql

The error originally reported here:

http://github.com/rails-sqlserver/2000-2005-adapter/issues/closed/#issue/3

Is still present.

The problem is line 1058 of sqlserver_adapter.rb

It reads:

FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns

That causes the db_name to be appended directly on to the front of INFORMATION_SCHEMA without the necessary period between the two. However, you cannot just add the period, because if the db_name is absent you get a period immediately after the FROM statement.

This works:

FROM #{(db_name + ".") if db_name}INFORMATION_SCHEMA.COLUMNS columns

but is not very elegant.

Identity-key-column judgement on multiple schema environment

Hello, I highly appreciate your development. this library always helps my job.
I'm using MSSQL in multi-schema environment like as,
sales.orders
production.orders
mst.users
production.users
...and so on.
and found problem.

For tables which is not in login user's default-schema,
AR-ConnectionAdapter wrongs identity-key-column judgement

I searched the point of problem.

lib/active_record/connection_adapters/sqlserver_adapter.rb
def column_definitions(table_name)
CASE
WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL
ELSE 1
END as is_identity
FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns

For tables which is not in default-schema,
OBJECT_ID(columns.TABLE_NAME) returns null,
and this "CASE" returns always 1(=true).

I replaced OBJECT_ID command like follow.
1054c1054
< WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL
---
> WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_SCHEMA+'.'+columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL

In my local environment, it works well.
I am using MSSQL 2005 and cannot confirm 2008 or other environment.
my environment.

dbi (0.4.1)
dbd-odbc (0.2.4)
rails-sqlserver-2000-2005-adapter (2.2.19)

Please confirm this point. I hope some correspondence will be applied.
Best Regards.

table and column alias can't handle alias like t12_r6

If you have more than 9 tables to join, a ActiveRecord::StatementInvalid error will occur, that's because the but in the regular expression that detect aliases.

in file sqlserver_adapter
line 485:
if sql =~ /#{Regexp.escape(order_by_column)} AS (t\d_r\d\d?)/

it should be :

if sql =~ /#{Regexp.escape(order_by_column)} AS (t\d\d?_r\d\d?)/

or :
if sql =~ /#{Regexp.escape(order_by_column)} AS (t\d+_r\d+)/

Better add_limit_offset! for SQL Server 2005

Reported by Seth Ladd | April 29th, 2009 @ 08:04 PM

Thanks for maintaining a SQL Server adapter.

As you know, the current strategy for pagination in the adapter is pretty gnarly. As you're also aware, SQL Server 2005 has a supported way to do efficient pagination.

Here's the code that we have been using for years:

def add_limit_offset!(sql, options)
  if options[:limit] and options[:offset]
    sql.sub!(/ORDER BY.*$/i, '')
    sql.sub!(/SELECT/i,
            "SELECT row_number() over( order by #{options[:order]} ) as row_num, \n")
    sql.replace("select top #{options[:limit]} * from (#{sql}) as tmp_table1 \n" +
          "where row_num > #{options[:offset]}")
  end
end

I hope you can use it to enhance the adapter, if only for 2005 support.

Binary to string conversion fails for non ascii characters

activerecord-sqlserver-adapter Version: 2.3.8
ruby-odbc Version: 0.99991
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
May 26 2009 14:24:20
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 6.0 (Build 6002: Service Pack 2)
Encoding cp1252.

After adding:

require 'odbc_utf8'

all the char and varchar columns are converted from cp1252 to utf8 as they should (my site is using utf8), but the binary 'image' columns that contains text are not converted to utf8, non ascii charters are still shown as weird '?' instead of ÅÄÖ.

I can convert it by using:
Iconv.conv('utf-8', 'cp1252', str)
but I was hoping it would work automatically just like for the varchar columns (it even works when the column name contains non ascii charaters!).

I don't know if it should work or not or if this is a bug in ruby-odbc instead of activerecord-sqlserver-adapter?

S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string or buffer length

Using the adapter on Windows XP with Microsoft SQL 2005 Express, with this database.yml

development:
adapter: sqlserver
host: SCADA\SQLEXPRESS
username: guest
password: guest
dsn: Server=SCADA\SQLEXPRESS;Database=rails;Uid=guest;Pwd=guest

Running rake db:migrate returns me:

rake db:migrate --trace
S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string or buffer length
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:775:in initialize' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:775:inconnect'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:775:in connect' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:188:ininitialize'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:26:in new' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.4/lib/active_record/connection_adapters/sqlserver_adapter.rb:26:insqlserver_connection'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in send' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:innew_connection'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in checkout_new_connection' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:incheckout'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in loop' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:incheckout'
D:/Ruby/lib/ruby/1.8/monitor.rb:242:in synchronize' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:incheckout'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in connection' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:inretrieve_connection'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in retrieve_connection' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:inconnection'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb:435:in initialize' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb:400:innew'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb:400:in up' D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/migration.rb:383:inmigrate'
D:/Ruby/lib/ruby/gems/1.8/gems/rails-2.3.5/lib/tasks/databases.rake:116
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:in call' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:inexecute'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:in each' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:inexecute'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:597:in invoke_with_call_chain' D:/Ruby/lib/ruby/1.8/monitor.rb:242:insynchronize'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:590:in invoke_with_call_chain' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:583:ininvoke'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2051:in invoke_task' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:intop_level'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in each' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:intop_level'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:in standard_exception_handling' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2023:intop_level'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2001:in run' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:instandard_exception_handling'
D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:1998:in run' D:/Ruby/lib/ruby/gems/1.8/gems/rake-0.8.7/bin/rake:31 D:/Ruby/bin/rake:19:inload'
D:/Ruby/bin/rake:19
(in D:/rails/test)
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate

Any hint?

Pessimistic locking via add_lock! adds lock clause on nested selects and can cause deadlock

Reported by Joe Rafaniello | June 12th, 2009 @ 05:28 PM

I am using the 2.2.16 version of the adapter and noticed that I occasionally receive deadlocks while using the :lock option, like this:

Account.find(:first,
  :conditions => [cond, *cond_param],
  :order => "priority, id",
  :lock => "WITH(UPDLOCK, ROWLOCK)"
)

The error shows itself as a process being chosen as a deadlock victim. Notice that we have nested selects and that each one has a WITH(UPDLOCK,ROWLOCK) and they are on the same table:

An error has occurred during work processing: DBI::DatabaseError: 37000 (1205) [unixODBC][FreeTDS][SQL Server]Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.: SELECT TOP 1 * FROM [accounts] WITH(UPDLOCK, ROWLOCK) WHERE (state = 'active' and acct_type = 'priority' and (created_on is null or created_on <= '2009-06-04 23:06:27.404') and (task_id is NULL or task_id not in ( select task_id FROM accounts WITH(UPDLOCK, ROWLOCK) where state = 'expired' and task_id is not NULL )) and (region = 'NorthEast' or region = '' or region IS NULL) and (role in ('administrator','operator') or role = '' or role IS NULL) ORDER BY priority, id /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in log' /var/www/miq/vmdb/vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb:811:inraw_execute' /var/www/miq/vmdb/vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb:834:in raw_select' /var/www/miq/vmdb/vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb:787:inselect' /var/www/miq/vmdb/vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb:787:in select' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:inselect_all_without_query_cache' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in select_all' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:635:infind_by_sql' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:1490:in find_every' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:1452:infind_initial' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:587:in find'

I found that the sql.gsub! line in the add_lock! method of the adapter is adding the LOCK phrase to each of the FROM clauses. I have a hack to the adapter to only add the lock for the first select we find. I'm not sure this is really the correct way of handling it. I'm not sure how to handle the JOIN gsub! either. Any ideas?

Attached is a svn diff of my local patch.
http://rails-sqlserver.lighthouseapp.com/attachments/132867/add_lock_deadlock.patch

SELECT DISTINCT with a subquery get mangled

Queries containing a DISTINCT clause and a subquery are mangled by the method add_order_by_for_association_limiting! in lib/active_record/sqlserver_adapter.rb at line 701. It uses an eager regular expression match as follows:
columns = sql.match(/SELECT\s+DISTINCT(.*)FROM/)[1].strip

That causes it to transform this:
SELECT DISTINCT [column] FROM [table] WHERE EXISTS (SELECT NULL FROM
[another_table])

into this:
SELECT [column] FROM [table] WHERE EXISTS (SELECT NULL FROM
[another_table]) GROUP BY [column] FROM [table] WHERE EXISTS (SELECT
NULL

The expected behavior is this:
SELECT [column] FROM [table] WHERE EXISTS (SELECT NULL FROM
[another_table]) GROUP BY [column]

Changing the eager match to a lazy match fixes the problem:
columns = sql.match(/SELECT\s+DISTINCT(.*?)FROM/)[1].strip

This fix has not been tested for regression.

Adapter doesn't extract default values containing newlines fully

Line 1084 of sqlserver_adapter.rb attempts to extract the default value for a column from a string returned from the database:

match_data = ci[:default_value].match(/\A(+N?'?(.*?)'?)+\Z/)

It appears that this regex won't correctly match a default value that contains a newline, which for an older version of the adapter would cause it to crash, and for the current version to return nil. Adding m to the end of the regex so that . matches newlines fixed the crash for me

Can we get a warning on SQL Server 2000 instead of requiring >=2005 ?

I've been using this great gem for sometime now and have upgraded our app to rails 3. I know from google groups that plans don't include sqlserver 2000 support but am hoping for just a warning statement for those of us who don't have the option of upgrading our servers.

I'm using this to pull legacy data out in a read-only fashion so feel comfortable going this route.

I've commented out the version check in my local gem bundle and found everything working fine.

Thanks
Greg

SQL Server 2000

Do you think it's much work to support it again with Rails3?

column_definitions generates invalid sql

Reported by Seth Ladd | April 29th, 2009 @ 10:39 PM

Hello,

This may be a configuration issue, as I can't believe the released version has this bug. However, I'll include the diff for reference. Notice the missing period between the db_name and the INFORMATION_SCHEMA

My configuration looks like this:

development: adapter: sqlserver mode: odbc dsn: DRIVER=/opt/local/lib/libtdsodbc.so;TDS_Version=8.0;SERVER=10.0.6.20;DATABASE=dses_production;Port=1433;uid=pandora;pwd=XXXXXXX;

The diff:
http://gist.github.com/104210

Confirmation on correct setup? Can't imagine I'm the only one that is running into this, so tips on correct usage are much appreciated. Thanks

NoMemoryError: negative allocation size (or too big)

Got this error after selecting rows after establishing connection to another/non-default database in odbc.

ActiveRecord::StatementInvalid in BookingsController#index

NoMemoryError: negative allocation size (or too big): SELECT * FROM [Orders] 

Trace:

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:914:in `raw_select'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:863:in `select'

String won't be quoted while querying with unicode enable

Assume there's a model inherit from ActiveRecord named Patron with an attribute name, and make enable_default_unicode_types works.

Firstly, create a patron with unicode name as following
p = Patron.create!(:name => '姓名')
Secondly, query the patron which just created
Patron.find(:all, :conditions => {:name => p.name})

As expected, a patron should be found, but actually an empty array returned back.
The generated SQL statement looks like:
SELECT * FROM [patrons] WHERE [patrons].[name] = '姓名'
which implies an N missed while quoting the column value

I've digged into the AR source code, found that while AR invoking the connection.quote method, it never pass the column as an input parameter, so the adapter cannot convert it into unicode format, so the N missed as above implies.

A modified one forked here. http://github.com/syhan/2000-2005-adapter/commit/aea4acebf7c574a974b3b647df4af22dae240517

ODBC::Error: S1090 (0) [unixODBC][Driver Manager]Invalid string or buffer length

Hello Ken,

I am getting trying to use the adapter with Rails 3 and I am getting this error
"ODBC::Error: S1090 (0) [unixODBC][Driver Manager]Invalid string or buffer length" in rails console, as soon as I try to make my first query.

You can see the console log here
http://gist.github.com/442285

I am on Ubuntu 10.04, using rvm and using ruby-odbc bindings from a gem.
This is the Gemfile

gem 'rails', '3.0.0.beta4'
gem 'ruby-odbc', '0.99991'
gem 'dbi', '0.4.5'
gem 'dbd-odbc', '0.2.5', :require => "dbd/ODBC"
gem 'activerecord-sqlserver-adapter', :git => 'git://github.com/rails-sqlserver/2000-2005-adapter.git', :branch => '3.0.0.beta1'

When I use my system version of ruby (not with RVM), I have ruby-odbc 0.9997 installed from repository and I have no problem with Rails 2.3.8, but this library is not found when I use RVM.
Any idea?
Thank you very much

Juanma