uniqueidentifier column error

true

2009-11-06T10:00:53-05:00

23964

40 uniqueidentifier-column-error 175767

20277

invalid uniqueidentifier column error

2009-12-04T18:07:51-05:00

15346

Ken Collins

Sputz

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/40

Thank you for the SQL Server adapter. I have a problem with an uniqueidentifier column. Here is the error that i get: DBI::DatabaseError: 37000 (8169) [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting from a character string to uniqueidentifier.: INSERT INTO [Users] ([salt], [updated_at], [UserName], [remember_token_expires_at], [Password], [Email], [EmailValidationStatus], [remember_token], [ConfirmationKey], [created_at]) VALUES(NULL, '2009-11-06 14:32:23.640', 's', NULL, 's', 's', 0, NULL, 'newid(', '2009-11-06 14:32:23.640') The problem must be here: 'newid('. If edit the query and eliminate the quotes and close the parenthesis it runs perfectly in SQL Management

<div>Thank you for the SQL Server adapter. I have a problem with an uniqueidentifier column. Here is the error that i get: DBI::DatabaseError: 37000 (8169) <a href= "/projects/20277/changesets/Microsoft" title= "Changeset [Microsoft]">[Microsoft]</a>[SQL Server Native Client 10.0][SQL Server]Conversion failed when converting from a character string to uniqueidentifier.: INSERT INTO <a href= "/projects/20277/changesets/Users" title= "Changeset [Users]">[Users]</a> ([salt], [updated_at], <a href= "/projects/20277/changesets/UserName" title= "Changeset [UserName]">[UserName]</a>, [remember_token_expires_at], <a href="/projects/20277/changesets/Password" title= "Changeset [Password]">[Password]</a>, <a href= "/projects/20277/changesets/Email" title= "Changeset [Email]">[Email]</a>, <a href= "/projects/20277/changesets/EmailValidationStatus" title= "Changeset [EmailValidationStatus]">[EmailValidationStatus]</a>, [remember_token], <a href= "/projects/20277/changesets/ConfirmationKey" title= "Changeset [ConfirmationKey]">[ConfirmationKey]</a>, [created_at]) VALUES(NULL, '2009-11-06 14:32:23.640', 's', NULL, 's', 's', 0, NULL, 'newid(', '2009-11-06 14:32:23.640') The problem must be here: 'newid('. If edit the query and eliminate the quotes and close the parenthesis it runs perfectly in SQL Management</div>

true

2009-09-13T12:09:46-04:00

69553

39 timeusec-support 0

20277

invalid time#usec time#usec support?

2009-09-14T08:28:06-04:00

15346

Ken Collins

Julio Fernández

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/39

__activerecord-sqlserver-adapter-2.2.21 Micro second support. Time#usec is automatically converted to SQL Server’s 3.33 millisecond limitation?? not running for me.__ Completed in 188ms (View: 79, DB: 78) | 200 OK [http://localhost/store] /!\ FAILSAFE /!\ Sun Sep 13 17:46:58 +0200 2009 Status: 500 Internal Server Error DBI::DatabaseError: 22008 (242) [Microsoft][ODBC SQL Server Driver][SQL Server ]La conversi¾n del tipo de datos varchar en datetime produjo un valor fuera de i ntervalo.: UPDATE [sessions] SET [updated_at] = '2009-09-13 15:46:58.890', [data ] = 'BAh7CToJY2FydG86CUNhcnQGOgtAaXRlbXNbADoMdXNlcl9pZGkGOhBfY3Ny Zl90b2tlbiIxaTkyZ3ZiQzhDb3ppN2w4TnM2UnlTRGQwWkpZcHNXWVdiV05E cXVQY1hNbz0iCmZsYXNoSUM6J0FjdGlvbkNvbnRyb2xsZXI6OkZsYXNoOjpG bGFzaEhhc2h7AAY6CkB1c2VkewA= ' WHERE [id] = 1 C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connecti on_adapters/abstract_adapter.rb:219:in `log' C:/Ruby/lib/ruby/gems/1.8/gems/__activerecord-sqlserver-adapter-2.2.21__/lib/act ive_record/connection_adapters/sqlserver_adapter.rb:840:in `raw_execute'

<div>activerecord-sqlserver-adapter-2.2.21 Micro second support. Time#usec is automatically converted to SQL Server’s 3.33 millisecond limitation?? not running for me. Completed in 188ms (View: 79, DB: 78) | 200 OK [<a href= "http://localhost/store">http://localhost/store</a>] /!\ FAILSAFE /!\ Sun Sep 13 17:46:58 +0200 2009 Status: 500 Internal Server Error DBI::DatabaseError: 22008 (242) <a href= "/projects/20277/changesets/Microsoft" title= "Changeset [Microsoft]">[Microsoft]</a>[ODBC SQL Server Driver][SQL Server ]La conversi¾n del tipo de datos varchar en datetime produjo un valor fuera de i ntervalo.: UPDATE <a href= "/projects/20277/changesets/sessions" title= "Changeset [sessions]">[sessions]</a> SET [updated_at] = '2009-09-13 15:46:58.890', [data ] = 'BAh7CToJY2FydG86CUNhcnQGOgtAaXRlbXNbADoMdXNlcl9pZGkGOhBfY3Ny Zl90b2tlbiIxaTkyZ3ZiQzhDb3ppN2w4TnM2UnlTRGQwWkpZcHNXWVdiV05E cXVQY1hNbz0iCmZsYXNoSUM6J0FjdGlvbkNvbnRyb2xsZXI6OkZsYXNoOjpG bGFzaEhhc2h7AAY6CkB1c2VkewA= ' WHERE <a href="/projects/20277/changesets/id" title= "Changeset [id]">[id]</a> = 1 <pre> <code>C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connecti</code> </pre> on_adapters/abstract_adapter.rb:219:in <code>log' </code> <pre> <code>C:/Ruby/lib/ruby/gems/1.8/gems/__activerecord-sqlserver-adapter-2.2.21__/lib/act</code> </pre> ive_record/connection_adapters/sqlserver_adapter.rb:840:in <code>raw_execute'</code></div>

true

2009-09-03T05:01:58-04:00

54849

38 remove-echoe-dep-when-running-rake-test_sqlserver 137103

20277

invalid Remove Echoe dep when running rake test_sqlserver

2009-09-05T20:06:31-04:00

15346

Ken Collins

Bob Jelica

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/38

Echoe is required to run tests. As I understand Echoe is similar to hoe, and should not be required to run tests. Cheers, and keep up the good work!

<div>Echoe is required to run tests. As I understand Echoe is similar to hoe, and should not be required to run tests. Cheers, and keep up the good work!</div>

15346

true

2009-04-30T00:36:42-04:00

55672

29 use-of-ntext-causing-nomemoryerror-exception-in-fetch 103762

20277

invalid Use of 'ntext' causing NoMemoryError, exception in 'fetch'

2009-08-25T14:52:10-04:00

15346

Ken Collins

TimG

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/29

Just beginning to work in Ruby and Rails for the first time and find that your adapter (Version 2.2.15) is failing when trying to read a record back from a SQL Server 2005 table that includes a column with the datatype 'ntext'. The exception thrown is as follows: NoMemoryError in PackagesController#index negative allocation size (or too big) D:/ruby/lib/ruby/gems/1.8/gems/dbd-odbc-0.2.4/lib/dbd/odbc/statement.rb:41:in `fetch' If I change the column type to 'text', the problem disappears. It seems to make no difference if the ntext/text column contains a value, or is null. Release notes for 2.1.15 suggest that ntext is supported, but that's not what I'm seeing using Ruby 1.8.6, Rails 2.3.2, DBI 0.4.0, and adapter 2.2.15. Anything I might be missing? Thanks, Tim

<div>Just beginning to work in Ruby and Rails for the first time and find that your adapter (Version 2.2.15) is failing when trying to read a record back from a SQL Server 2005 table that includes a column with the datatype 'ntext'. The exception thrown is as follows: NoMemoryError in PackagesController#index negative allocation size (or too big) D:/ruby/lib/ruby/gems/1.8/gems/dbd-odbc-0.2.4/lib/dbd/odbc/statement.rb:41:in <code>fetch'</code> If I change the column type to 'text', the problem disappears. It seems to make no difference if the ntext/text column contains a value, or is null. Release notes for 2.1.15 suggest that ntext is supported, but that's not what I'm seeing using Ruby 1.8.6, Rails 2.3.2, DBI 0.4.0, and adapter 2.2.15. Anything I might be missing? Thanks, Tim</div>

15346

true

2009-01-04T22:44:07-05:00

15346

12 ado-might-not-work-add-odbc-databaseyml-example 45356

20277

resolved ADO Might Not Work. Add ODBC database.yml Example

2009-06-18T11:11:50-04:00

15346

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/12

I need some help in determining if ADO works anymore. Older version of DBI back to 0.023 are confirmed to work, but I'd like to know more. Also take a look at talking about this in the README and/or provide an example database.yml snippet.

<div>I need some help in determining if ADO works anymore. Older version of DBI back to 0.023 are confirmed to work, but I'd like to know more. Also take a look at talking about this in the README and/or provide an example database.yml snippet.</div>

15346

true

2009-04-07T18:12:39-04:00

25048

24 explicit-db-name-in-table-failsmultiple-databases 98453

20277

resolved Explicit DB name in table fails/Multiple databases

2009-06-18T11:08:42-04:00

15346

Ken Collins

chagar

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/24

When the model has set_table_name 'MYDATABASE.dbo.myTable', I get the following error, because the column_definitions method lacks a period in its query, or because unqualify_db_name is getting a database name that lacks a period. Adding a single period in one place, makes it work. The relevant line in column_definitions is: FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns Error message: DBI::DatabaseError: S0002 (208) [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDATABASEINFORMATION_SCHEMA.COLUMNS'.: SELECT columns.TABLE_NAME as table_name, columns.COLUMN_NAME as name, columns.DATA_TYPE as type, columns.COLUMN_DEFAULT as default_value, columns.NUMERIC_SCALE as numeric_scale, columns.NUMERIC_PRECISION as numeric_precision, CASE WHEN columns.DATA_TYPE IN ('nchar','nvarchar') THEN columns.CHARACTER_MAXIMUM_LENGTH ELSE COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) END as length, CASE WHEN columns.IS_NULLABLE = 'YES' THEN 1 ELSE NULL end as is_nullable, CASE WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL ELSE 1 END as is_identity FROM MYDATABASEINFORMATION_SCHEMA.COLUMNS columns WHERE columns.TABLE_NAME = 'myTable' ORDER BY columns.ordinal_position

<div>When the model has set_table_name 'MYDATABASE.dbo.myTable', I get the following error, because the column_definitions method lacks a period in its query, or because unqualify_db_name is getting a database name that lacks a period. Adding a single period in one place, makes it work. The relevant line in column_definitions is: FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns Error message: DBI::DatabaseError: S0002 (208) [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDATABASEINFORMATION_SCHEMA.COLUMNS'.: SELECT columns.TABLE_NAME as table_name, columns.COLUMN_NAME as name, columns.DATA_TYPE as type, columns.COLUMN_DEFAULT as default_value, columns.NUMERIC_SCALE as numeric_scale, columns.NUMERIC_PRECISION as numeric_precision, CASE WHEN columns.DATA_TYPE IN ('nchar','nvarchar') THEN columns.CHARACTER_MAXIMUM_LENGTH ELSE COL_LENGTH(columns.TABLE_NAME, columns.COLUMN_NAME) END as length, CASE WHEN columns.IS_NULLABLE = 'YES' THEN 1 ELSE NULL end as is_nullable, CASE WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL ELSE 1 END as is_identity FROM MYDATABASEINFORMATION_SCHEMA.COLUMNS columns WHERE columns.TABLE_NAME = 'myTable' ORDER BY columns.ordinal_position</div>

true

2009-04-29T20:04:35-04:00

20594

27 better-add_limit_offset-for-sql-server-2005 103303

20277

resolved pagination Better add_limit_offset! for SQL Server 2005

2009-06-18T11:06:01-04:00

15346

Ken Collins

Seth Ladd

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/27

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.

<div>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: <pre> <code> 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 </code> </pre> I hope you can use it to enhance the adapter, if only for 2005 support.</div>

15346

true

2009-05-12T21:09:43-04:00

52369

31 multiple-order-by-with-same-column-name-on-different-tables-fails 106715

20277

resolved Multiple order by with same column name on different tables fails

2009-06-18T11:00:13-04:00

15346

Ken Collins

Tomas Varsavsky

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/31

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.

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

15346

true

2009-04-29T22:39:45-04:00

20594

28 column_definitions-generates-invalid-sql 103645

20277

resolved column_definitions generates invalid sql

2009-06-18T10:53:50-04:00

15346

Ken Collins

Seth Ladd

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/28

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: Index: vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb =================================================================== --- vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb (revision 18159) +++ vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb (working copy) @@ -1043,7 +1043,7 @@ 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 + FROM #{db_name}.INFORMATION_SCHEMA.COLUMNS columns WHERE columns.TABLE_NAME = '#{table_name}' ORDER BY columns.ordinal_position }.gsub(/[ \t\r\n]+/,' ') Any ideas?

<div>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: <h1>Index: vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb</h1> --- vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb (revision 18159) +++ vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb (working copy) @@ -1043,7 +1043,7 @@ <pre> <code> WHEN COLUMNPROPERTY(OBJECT_ID(columns.TABLE_NAME), columns.COLUMN_NAME, 'IsIdentity') = 0 THEN NULL ELSE 1 END as is_identity </code> </pre> <ul> <li> <pre> <code> FROM #{db_name}INFORMATION_SCHEMA.COLUMNS columns </code> </pre></li> <li> <pre> <code> FROM #{db_name}.INFORMATION_SCHEMA.COLUMNS columns WHERE columns.TABLE_NAME = '#{table_name}' ORDER BY columns.ordinal_position }.gsub(/[ \t\r\n]+/,' ') </code> </pre></li> </ul> Any ideas?</div>

15346

true

2009-06-10T05:51:21-04:00

59528

34 no-memory-error-on-windows-odbc-with-nvarcharmax 113373

20277

resolved No Memory Error on Windows ODBC with nvarchar(max)

2009-06-18T10:49:06-04:00

15346

Ken Collins

Colin Stevens

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/34

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.

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

15346

true

2009-06-12T17:28:21-04:00

38760

37 pessimistic-locking-via-add_lock-adds-lock-clause-on-nested-selects-and-can-cause-deadlock 113375

20277

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

2009-06-18T10:47:17-04:00

15346

Ken Collins

Joe Rafaniello

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/37

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:in `raw_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:in `select' /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:in `select_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:in `find_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:in `find_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.

<div>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: <pre> <code> Account.find(:first, :conditions => [cond, *cond_param], :order => "priority, id", :lock => "WITH(UPDLOCK, ROWLOCK) ") } </code> </pre> 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 <code>log' /var/www/miq/vmdb/vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb:811:in</code>raw_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 <code>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:in</code>select' /var/www/miq/vmdb/vendor/gems/rails-sqlserver-2000-2005-adapter-2.2.16/lib/active_record/connection_adapters/sqlserver_adapter.rb:787:in <code>select' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in</code>select_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 <code>select_all' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:635:in</code>find_by_sql' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:1490:in <code>find_every' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:1452:in</code>find_initial' /var/www/miq/vmdb/config/../vendor/gems/activerecord-2.2.2/lib/active_record/base.rb:587:in <code>find'</code> 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.</div>

true

2009-06-12T09:33:33-04:00

47358

36 quote_column_name-messes-up-quoted-column-names 118816

20277

resolved quotes quoting #quote_column_name messes up quoted column names

2009-06-18T10:23:31-04:00

15346

Ken Collins

Vince Puzzella

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/36

Quick Fix: @@@ def quote_column_name(column_name) #column_name.to_s.split('.').map{ |name| "[#{name}]" }.join('.') column_name.to_s.split('.').map{ |name| name =~ /^\[.*\]$/ ? name : "[#{name}]" }.join('.') end @@@

<div>Quick Fix: <pre><code> def quote_column_name(column_name) #column_name.to_s.split('.').map{ |name| "[#{name}]" }.join('.') column_name.to_s.split('.').map{ |name| name =~ /^\[.*\]$/ ? name : "[#{name}]" }.join('.') end </code></pre></div>

15346

true

2009-04-01T13:10:08-04:00

20272

21 active_scaffold-doesnt-work-properly 96864

20277

resolved Active_scaffold doesn't work properly

2009-06-18T09:52:15-04:00

15346

Ken Collins

Jordan Yeo

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/21

Trying to use the active_scaffold plugin with the adapter doesn't work. Gives the error: DBI::DatabaseError: 42000 (102) [FreeTDS][SQL Server]Incorrect syntax near '\.'.: SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 10 [people].* FROM [people] ORDER BY people.[id] ASC) AS tmp1 ORDER BY people\.\[[id]\] DESC) AS tmp2 ORDER BY people\.\[[id]\] ASC

<div>Trying to use the active_scaffold plugin with the adapter doesn't work. Gives the error: DBI::DatabaseError: 42000 (102) [FreeTDS][SQL Server]Incorrect syntax near '.'.: SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 10 [people].* FROM [people] ORDER BY people.[id] ASC) AS tmp1 ORDER BY people.[[id]] DESC) AS tmp2 ORDER BY people.[[id]] ASC</div>

15346

true

2009-01-28T18:33:48-05:00

44451

17 using-limit-and-offset-does-not-produce-correct-results-on-server-2005 67162

20277

resolved limit offset pagination Using limit and offset does not produce correct results on Server 2005

2009-06-18T09:51:07-04:00

15346

Ken Collins

Paul

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/17

When querying against Server 2005 using both `:limit` and `:offset` simultaneously only the top limit results from the query are produced and the offset seems to be ignored. Example: @@@ ruby Site.find(:all, :limit => 5, :offset => 10, :order => 'id ASC').each { |site| puts "#{site.id}" } # Returns # => 1 # => 2 # => 3 # => 4 # => 5 # Expected # => 11 # => 12 # => 13 # => 14 # => 15 @@@ I have, however, found a way of obtaining the correct results using some 2005-specific functionality: @@@ sql WITH pagination AS (SELECT row_number() OVER (order by id) AS row_num, * FROM sites) SELECT * FROM pagination WHERE row_num BETWEEN 10 AND 15 ORDER BY row_num @@@ Considering that there is the `sqlserver_2005?` method would it be possible to use this type of query only when the adapter is connected to 2005?

<div>When querying against Server 2005 using both <code>:limit</code> and <code>:offset</code> simultaneously only the top limit results from the query are produced and the offset seems to be ignored. Example: <pre><code class="ruby"> Site.find(:all, :limit => 5, :offset => 10, :order => 'id ASC').each { |site| puts "#{site.id}" } # Returns # => 1 # => 2 # => 3 # => 4 # => 5 # Expected # => 11 # => 12 # => 13 # => 14 # => 15 </code></pre> I have, however, found a way of obtaining the correct results using some 2005-specific functionality: <pre><code class="sql"> WITH pagination AS (SELECT row_number() OVER (order by id) AS row_num, * FROM sites) SELECT * FROM pagination WHERE row_num BETWEEN 10 AND 15 ORDER BY row_num </code></pre> Considering that there is the <code>sqlserver_2005?</code> method would it be possible to use this type of query only when the adapter is connected to 2005?</div>

15346

true

2009-06-11T10:06:53-04:00

136

35 add_limit_offset-doesnt-respect-orderings-from-scopes 113374

20277

resolved limit named_scope offset pagination add_limit_offset! doesn't respect orderings from scopes

2009-06-18T09:50:31-04:00

15346

Ken Collins

Murray Steele

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/35

If we have a scope like: @@@ ruby class Donut < ActiveRecord::Base named_scope :ordered, :order => 'sprinkles' end @@@ and then try to paginate results of that scope: @@@ ruby Donut.ordered.find(:all, :limit => 10, :offset => 10) @@@ What we get, instead of the 2nd page of results, is the 1st page of results. It doesn't matter what offset we put in, we're still gonna get the 1st page of results. What's being generated is: @@@ SQL SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM [donuts] ORDER BY [sprinkles]) AS tmp1) AS tmp2 @@@ What should be generated is: @@@ SQL SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM [donuts] ORDER BY [sprinkles]) AS tmp1 ORDER BY [sprinkles] DESC) AS tmp2 ORDER BY [sprinkles] @@@ The attached patch takes care of this.

<div>If we have a scope like: <pre><code class="ruby"> class Donut < ActiveRecord::Base named_scope :ordered, :order => 'sprinkles' end </code></pre> and then try to paginate results of that scope: <pre><code class="ruby"> Donut.ordered.find(:all, :limit => 10, :offset => 10) </code></pre> What we get, instead of the 2nd page of results, is the 1st page of results. It doesn't matter what offset we put in, we're still gonna get the 1st page of results. What's being generated is: @@@ SQL SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM [donuts] ORDER BY [sprinkles]) AS tmp1) AS tmp2 <pre><code> What should be generated is: @@@ SQL SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM [donuts] ORDER BY [sprinkles]) AS tmp1 ORDER BY [sprinkles] DESC) AS tmp2 ORDER BY [sprinkles] </code></pre> The attached patch takes care of this.</div>

15346

true

2009-04-08T02:00:09-04:00

3914

25 cant-have-a-table-called-applications 98500

20277

resolved Can't have a table called 'Applications'

2009-06-08T18:24:09-04:00

3914

Hugh

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/25

This is due to the calling of classify on the table_name in order to check if it has an ActiveRecord model. I made a quick hack to get me out of this pickle: http://github.com/artpop/2000-2005-adapter/commit/0925a1baeedef9acb9625d0a51b0ac293357dac4 However there has to be a better way than calling classify on table_name because there are bound to be other class name clashes.

<div>This is due to the calling of classify on the table_name in order to check if it has an ActiveRecord model. I made a quick hack to get me out of this pickle: <a href="http://github.com/artpop/2000-2005-adapter/commit/0925a1baeedef9acb9625d0a51b0ac293357dac4"> http://github.com/artpop/2000-20...</a> However there has to be a better way than calling classify on table_name because there are bound to be other class name clashes.</div>

15346

true

2009-05-29T20:46:46-04:00

58377

33 table_klass-doesnt-rescue-loaderror 1

20277

resolved loaderror table_klass table_klass doesn't rescue LoadError

2009-06-08T13:48:27-04:00

58377

Larry Kyrala

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/33

Under certain circumstances, constantize raises a LoadError which is not rescued by the method table_klass in around sqlserver_adapter.rb, line 100. (This may solve the problem reported in #25, which I suspect is caused by the same underlying issue). I've been troubleshooting a model for work that fails while using version 2.2.17 of the adapter. We have a model that is set up like this: in "foo.rb": @@@ class Foo < ActiveRecord::Base set_table_name :FOO # this is a legacy database name we can't change end @@@ When we try to use this with rails-sqlserver-2000-2005-adapter (2.2.17), we get a stack trace similar to: @@@ % ruby script/console >> Foo.first LoadError: Expected /local/gems/gems/foo/lib/foo.rb to define FOO from /local/gems/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:428:in `load_missing_constant' from /local/gems/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:77:in `const_missing' from /local/gems/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:89:in `const_missing' from /local/gems/gems/activesupport-2.2.2/lib/active_support/inflector.rb:352:in `constantize' from /local/gems/gems/activesupport-2.2.2/lib/active_support/inflector.rb:351:in `each' from /local/gems/gems/activesupport-2.2.2/lib/active_support/inflector.rb:351:in `constantize' from /local/gems/gems/activesupport-2.2.2/lib/active_support/core_ext/string/inflections.rb:162:in `constantize' from /local/gems/gems/rails-sqlserver-2000-2005-adapter-2.2.17/lib/active_record/connection_adapters/sqlserver_adapter.rb:100:in `table_klass' @@@ We tracked this to the following code around sqlserver_adapter.rb, line 100 @@@ def table_klass @table_klass ||= table_name.classify.constantize rescue nil (@table_klass && @table_klass < ActiveRecord::Base) ? @table_klass : nil end @@@ It seems like the intent of this method at is to find an existing class constant that matches the table name or return nil. This would work in our situation if the rails method load_missing_constant only raised StandardError exceptions. However, in one case (notably ours and I think #25), this method actually raises a LoadError exception. (see activesupport-2.2.2/lib/active_support/dependencies.rb:428:in `load_missing_constant') Since table_klass does not explicitly rescue LoadErrors, the exception bubbles up to the developer. This seems to only happen if there is a likely file candidate for load_missing_constant in the file paths. In our case, this is "foo.rb". In #25's case, this is "application.rb". This might be a potential fix: @@@ def table_klass begin @table_klass ||= table_name.classify.constantize rescue StandardError, LoadError nil end (@table_klass && @table_klass < ActiveRecord::Base) ? @table_klass : nil end @@@ but I wanted to check that this fix matches your original intent. Thanks and hope this helps!

<div>Under certain circumstances, constantize raises a LoadError which is not rescued by the method table_klass in around sqlserver_adapter.rb, line 100. (This may solve the problem reported in <a href="/projects/20277/tickets/25" title="Ticket #25">#25</a>, which I suspect is caused by the same underlying issue). I've been troubleshooting a model for work that fails while using version 2.2.17 of the adapter. We have a model that is set up like this: in "foo.rb": <pre><code> class Foo < ActiveRecord::Base set_table_name :FOO # this is a legacy database name we can't change end </code></pre> When we try to use this with rails-sqlserver-2000-2005-adapter (2.2.17), we get a stack trace similar to: <pre><code> % ruby script/console >> Foo.first LoadError: Expected /local/gems/gems/foo/lib/foo.rb to define FOO from /local/gems/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:428:in `load_missing_constant' from /local/gems/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:77:in `const_missing' from /local/gems/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:89:in `const_missing' from /local/gems/gems/activesupport-2.2.2/lib/active_support/inflector.rb:352:in `constantize' from /local/gems/gems/activesupport-2.2.2/lib/active_support/inflector.rb:351:in `each' from /local/gems/gems/activesupport-2.2.2/lib/active_support/inflector.rb:351:in `constantize' from /local/gems/gems/activesupport-2.2.2/lib/active_support/core_ext/string/inflections.rb:162:in `constantize' from /local/gems/gems/rails-sqlserver-2000-2005-adapter-2.2.17/lib/active_record/connection_adapters/sqlserver_adapter.rb:100:in `table_klass' </code></pre> We tracked this to the following code around sqlserver_adapter.rb, line 100 <pre><code> def table_klass @table_klass ||= table_name.classify.constantize rescue nil (@table_klass && @table_klass < ActiveRecord::Base) ? @table_klass : nil end </code></pre> It seems like the intent of this method at is to find an existing class constant that matches the table name or return nil. This would work in our situation if the rails method load_missing_constant only raised StandardError exceptions. However, in one case (notably ours and I think <a href="/projects/20277/tickets/25" title="Ticket #25">#25</a>), this method actually raises a LoadError exception. (see activesupport-2.2.2/lib/active_support/dependencies.rb:428:in <code>load_missing_constant')</code> Since table_klass does not explicitly rescue LoadErrors, the exception bubbles up to the developer. This seems to only happen if there is a likely file candidate for load_missing_constant in the file paths. In our case, this is "foo.rb". In <a href="/projects/20277/tickets/25" title="Ticket #25">#25</a>'s case, this is "application.rb". This might be a potential fix: <pre><code> def table_klass begin @table_klass ||= table_name.classify.constantize rescue StandardError, LoadError nil end (@table_klass && @table_klass < ActiveRecord::Base) ? @table_klass : nil end </code></pre> but I wanted to check that this fix matches your original intent. Thanks and hope this helps!</div>

15346

true

2009-05-27T16:19:06-04:00

58109

32 sql-server-2008-not-supported 113372

20277

resolved 2008 connection SQL Server 2008 not supported

2009-06-05T19:22:42-04:00

58109

howardk

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/32

The adapter refuses to talk to SQL Server 2008 (hardcoded to reject anything other than 2000 or 2005), though it works fine it you lift the hardcoded restriction. The patch is quite simple for 2.2.17, just recognize SQL Server 2008 and handle it same as 2005 is handled. Diff is attached, amounting to: * Recognize version='2008' * Add sqlserver_2008? test method, a la sqlserver_2005? * Add sqlserver_2005_or_2008? test method * Replace the 2 tests for 2005 to also accept 2008 * Update 1 comment noting also applicable to 2008 Tests and development to date confirm the adapter seems to work fine for SQL Server 2008, but we haven't done exhaustive testing of the adapter so I wouldn't swear it's flawless. Will be happy to report if we encounter any problems in the future, but so far it's looking quite good. DISCLAIMER: I work for Microsoft, but I have no connection to the SQL Server team or any other data access team. Just trying to use ActiveRecord and SQL Server 2008 together, and stumbling face first into the brick wall here.

<div>The adapter refuses to talk to SQL Server 2008 (hardcoded to reject anything other than 2000 or 2005), though it works fine it you lift the hardcoded restriction. The patch is quite simple for 2.2.17, just recognize SQL Server 2008 and handle it same as 2005 is handled. Diff is attached, amounting to: <ul> <li>Recognize version='2008'</li> <li>Add sqlserver_2008? test method, a la sqlserver_2005?</li> <li>Add sqlserver_2005_or_2008? test method</li> <li>Replace the 2 tests for 2005 to also accept 2008</li> <li>Update 1 comment noting also applicable to 2008</li> </ul> Tests and development to date confirm the adapter seems to work fine for SQL Server 2008, but we haven't done exhaustive testing of the adapter so I wouldn't swear it's flawless. Will be happy to report if we encounter any problems in the future, but so far it's looking quite good. DISCLAIMER: I work for Microsoft, but I have no connection to the SQL Server team or any other data access team. Just trying to use ActiveRecord and SQL Server 2008 together, and stumbling face first into the brick wall here.</div>

15346

true

2009-05-12T21:01:14-04:00

52369

30 text-fields-are-created-as-strings-in-schemarb 106801

20277

resolved schema text type Text fields are created as strings in schema.rb

2009-05-14T16:11:40-04:00

15346

Ken Collins

Tomas Varsavsky

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/30

When text fields are created in the SQLServer database as varchar(max) the type in schema.rb is changed to string! This makes my tests fail because the test database is created from schema.rb not migrations. In my migration I have: create_table :audits, :force => true do |t| t.text :changes end In the DB the changes column is created as varchar(max). In my schema.rb I have: create_table "audits", :force => true do |t| t.string "changes", :limit => nil end

<div>When text fields are created in the SQLServer database as varchar(max) the type in schema.rb is changed to string! This makes my tests fail because the test database is created from schema.rb not migrations. In my migration I have: <pre> <code>create_table :audits, :force => true do |t| t.text :changes end </code> </pre> In the DB the changes column is created as varchar(max). In my schema.rb I have: create_table "audits", :force => true do |t| <pre> <code>t.string "changes", :limit => nil </code> </pre> end</div>

15346

true

2009-04-01T15:30:32-04:00

20272

22 exception-with-new-line-character 96890

20277

resolved Exception with new line character

2009-04-21T16:07:20-04:00

15346

Ken Collins

Jordan Yeo

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/22

NoMethodError (You have a nil object when you didn't expect it! You might have expected an instance of Array. The error occurred while evaluating nil.split): /Library/Ruby/Gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:649:in `set_utf8_values!' /Library/Ruby/Gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:311:in `insert_sql' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert_without_query_dirty' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:18:in `insert' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:2733:in `create_without_callbacks' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/callbacks.rb:237:in `create_without_timestamps' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/timestamp.rb:29:in `create' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:2699:in `create_or_update_without_callbacks' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/callbacks.rb:222:in `create_or_update' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:2383:in `save_without_validation' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:1009:in `save_without_dirty' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/dirty.rb:79:in `save_without_transactions' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:179:in `send' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:179:in `with_transaction_returning_status' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:66:in `transaction' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:129:in `transaction' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:138:in `transaction' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:178:in `with_transaction_returning_status' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:146:in `save' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:158:in `rollback_active_record_state!' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:146:in `save' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:693:in `create' /app/controllers/apply_controller.rb:72:in `add_reference' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:1253:in `send' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:1253:in `perform_action_without_filters' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:617:in `call_filters' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:610:in `perform_action_without_benchmark' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/benchmark.rb:293:in `measure' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/rescue.rb:136:in `perform_action_without_caching' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/caching/sql_cache.rb:13:in `perform_action' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:34:in `cache' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/query_cache.rb:8:in `cache' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/caching/sql_cache.rb:12:in `perform_action' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:524:in `send' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:524:in `process_without_filters' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:606:in `process_without_session_management_support' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/session_management.rb:134:in `process' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:392:in `process' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:183:in `handle_request' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:110:in `dispatch_unlocked' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:123:in `dispatch' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:122:in `synchronize' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:122:in `dispatch' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:132:in `dispatch_cgi' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:39:in `dispatch' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/../lib/mongrel/rails.rb:76:in `process' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/../lib/mongrel/rails.rb:74:in `synchronize' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/../lib/mongrel/rails.rb:74:in `process' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:159:in `process_client' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:158:in `each' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:158:in `process_client' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `initialize' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `new' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:268:in `initialize' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:268:in `new' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:268:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/configurator.rb:282:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/configurator.rb:281:in `each' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/configurator.rb:281:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/mongrel_rails:128:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/command.rb:212:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/mongrel_rails:281 /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load_without_new_constant_marking' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:521:in `new_constants_in' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load' /Library/Ruby/Gems/1.8/gems/rails-2.2.2/lib/commands/servers/mongrel.rb:64 /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require' /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:153:in `require' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:521:in `new_constants_in' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:153:in `require' /Library/Ruby/Gems/1.8/gems/rails-2.2.2/lib/commands/server.rb:49 /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require' /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require' script/server:3

<div>NoMethodError (You have a nil object when you didn't expect it! You might have expected an instance of Array. The error occurred while evaluating nil.split): <pre> <code>/Library/Ruby/Gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:649:in `set_utf8_values!' /Library/Ruby/Gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:311:in `insert_sql' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert_without_query_dirty' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:18:in `insert' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:2733:in `create_without_callbacks' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/callbacks.rb:237:in `create_without_timestamps' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/timestamp.rb:29:in `create' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:2699:in `create_or_update_without_callbacks' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/callbacks.rb:222:in `create_or_update' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:2383:in `save_without_validation' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/validations.rb:1009:in `save_without_dirty' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/dirty.rb:79:in `save_without_transactions' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:179:in `send' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:179:in `with_transaction_returning_status' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:66:in `transaction' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:129:in `transaction' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:138:in `transaction' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:178:in `with_transaction_returning_status' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:146:in `save' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:158:in `rollback_active_record_state!' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/transactions.rb:146:in `save' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:693:in `create' /app/controllers/apply_controller.rb:72:in `add_reference' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:1253:in `send' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:1253:in `perform_action_without_filters' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:617:in `call_filters' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:610:in `perform_action_without_benchmark' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/benchmark.rb:293:in `measure' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/rescue.rb:136:in `perform_action_without_caching' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/caching/sql_cache.rb:13:in `perform_action' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:34:in `cache' /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/query_cache.rb:8:in `cache' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/caching/sql_cache.rb:12:in `perform_action' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:524:in `send' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:524:in `process_without_filters' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:606:in `process_without_session_management_support' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/session_management.rb:134:in `process' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:392:in `process' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:183:in `handle_request' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:110:in `dispatch_unlocked' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:123:in `dispatch' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:122:in `synchronize' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:122:in `dispatch' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:132:in `dispatch_cgi' /Library/Ruby/Gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:39:in `dispatch' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/../lib/mongrel/rails.rb:76:in `process' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/../lib/mongrel/rails.rb:74:in `synchronize' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/../lib/mongrel/rails.rb:74:in `process' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:159:in `process_client' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:158:in `each' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:158:in `process_client' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `initialize' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `new' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:285:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:268:in `initialize' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:268:in `new' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel.rb:268:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/configurator.rb:282:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/configurator.rb:281:in `each' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/configurator.rb:281:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/mongrel_rails:128:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/lib/mongrel/command.rb:212:in `run' /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/mongrel-1.1.4/bin/mongrel_rails:281 /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load_without_new_constant_marking' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:521:in `new_constants_in' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load' /Library/Ruby/Gems/1.8/gems/rails-2.2.2/lib/commands/servers/mongrel.rb:64 /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require' /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:153:in `require' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:521:in `new_constants_in' /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:153:in `require' /Library/Ruby/Gems/1.8/gems/rails-2.2.2/lib/commands/server.rb:49 /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require' /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require' script/server:3 </code> </pre></div>

15346

true

2009-02-04T14:15:29-05:00

38760

18 sql-server-adapter-doesnt-reconnect-on-lost-connection 1

20277

resolved connection dbi::databaseerror SQL Server adapter doesn't reconnect on lost connection

2009-04-21T07:09:38-04:00

15346

Ken Collins

Joe Rafaniello

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/18

I noticed that mysql added the ability to reconnect on a lost connection, see [edge-rails](http://weblog.rubyonrails.com/2009/1/31/this-week-in-edge-rails, "edge-rails"). It was added through this ticket: [#1293](http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/1293 "#1293") This is also problem with the sql server adapter regardless if you lose connectivity, the DB loses connectivity, or if the DB kills the DB session. In my custom sql server adapter based off of 1.2.3, I have to explicitly rescue the "execute" and retry after reconnecting. Can this type of logic be added to the adapter? It should also cover any time we explicitly connect via DBI. The exception raised at "execute" time is ActiveRecord::StatementInvalid. If using FreeTDS and DBI, lower level exceptions are: if err.message =~ /^DBI::DatabaseError: 08S01 $(0|20004|20006)$/ reconnect! retry #FreeTDS exceptions: #08S01 (0) Communication link failure #08S01 (20004) Read from the server failed #08S01 (20006) Write to the server failed end We currently establish the connection in sqlserver_connection in ActiveRecord::Base. I would suggest we defer the connection to the initialize of the adapter. In the initialize, the connect method would establish the @connection, and rescue and retry the connect on DBI::DatabaseError. The current reconnect! method does not retry, will only raise the error. In production, we cannot rely on the connection being always available and need an seemless retrying of queries. If I can, I was going to port my changes to the latest adapter and post my changes. Not sure if these changes are the best... only my suggestion.

<div>I noticed that mysql added the ability to reconnect on a lost connection, see <a href="http://weblog.rubyonrails.com/2009/1/31/this-week-in-edge-rails%2C" title="edge-rails">edge-rails</a>. It was added through this ticket: <a href="http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/1293" title="#1293">#1293</a> This is also problem with the sql server adapter regardless if you lose connectivity, the DB loses connectivity, or if the DB kills the DB session. In my custom sql server adapter based off of 1.2.3, I have to explicitly rescue the "execute" and retry after reconnecting. Can this type of logic be added to the adapter? It should also cover any time we explicitly connect via DBI. The exception raised at "execute" time is ActiveRecord::StatementInvalid. If using FreeTDS and DBI, lower level exceptions are: if err.message =~ /^DBI::DatabaseError: 08S01 ((0|20004|20006))/ reconnect! retry #FreeTDS exceptions: <a href="/projects/20277/tickets/08" title="Ticket #08">#08</a>S01 (0) Communication link failure <a href="/projects/20277/tickets/08" title="Ticket #08">#08</a>S01 (20004) Read from the server failed <a href="/projects/20277/tickets/08" title="Ticket #08">#08</a>S01 (20006) Write to the server failed end We currently establish the connection in sqlserver_connection in ActiveRecord::Base. I would suggest we defer the connection to the initialize of the adapter. In the initialize, the connect method would establish the @connection, and rescue and retry the connect on DBI::DatabaseError. The current reconnect! method does not retry, will only raise the error. In production, we cannot rely on the connection being always available and need an seemless retrying of queries. If I can, I was going to port my changes to the latest adapter and post my changes. Not sure if these changes are the best... only my suggestion.</div>

15346

true

2009-04-16T18:35:42-04:00

54330

26 cant-have-a-description-column-and-sort-desc 100735

20277

resolved Can't have a 'description' column and sort DESC

2009-04-20T13:54:57-04:00

54330

pclowser

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/26

Given these two migrations: @@@ class CreateStatuses < ActiveRecord::Migration def self.up create_table :statuses do |t| t.string :description t.timestamps end end def self.down end end class AddDataToStatuses < ActiveRecord::Migration def self.up Status.create(:description => 'Active') Status.create(:description => 'Inactive') Status.create(:description => 'Pending') end def self.down end end @@@ Results in the following: @@@ testapp: script/console Loading development environment (Rails 2.2.2) >> statuses = Status.find(:all, :order => "description") => [#<Status id: 1, description: "Active", created_at: "2009-04-16 22:11:43", updated_at: "2009-04-16 22:11:43">, #<Status id: 2, description: "Inactive", created_at: "2009-04-16 22:11:43", updated_at: "2009-04-16 22:11:43">, #<Status id: 3, description: "Pending", created_at: "2009-04-16 22:11:43", updated_at: "2009-04-16 22:11:43">] >> statuses = Status.find(:all, :order => "description desc") ActiveRecord::StatementInvalid: DBI::DatabaseError: 42000 (156) [FreeTDS][SQL Server]Incorrect syntax near the keyword 'DESC'.: SELECT * FROM [statuses] ORDER BY ription desc DESC from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in `log' from /opt/local/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.15/lib/active_record/connection_adapters/sqlserver_adapter.rb:728:in `raw_execute' from /opt/local/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.15/lib/active_record/connection_adapters/sqlserver_adapter.rb:751:in `raw_select' from /opt/local/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.15/lib/active_record/connection_adapters/sqlserver_adapter.rb:704:in `select' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:635:in `find_by_sql' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:1490:in `find_every' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:589:in `find' from (irb):4 @@@

<div>Given these two migrations: <pre><code> class CreateStatuses < ActiveRecord::Migration def self.up create_table :statuses do |t| t.string :description t.timestamps end end def self.down end end class AddDataToStatuses < ActiveRecord::Migration def self.up Status.create(:description => 'Active') Status.create(:description => 'Inactive') Status.create(:description => 'Pending') end def self.down end end </code></pre> Results in the following: <pre><code> testapp: script/console Loading development environment (Rails 2.2.2) >> statuses = Status.find(:all, :order => "description") => [#<Status id: 1, description: "Active", created_at: "2009-04-16 22:11:43", updated_at: "2009-04-16 22:11:43">, #<Status id: 2, description: "Inactive", created_at: "2009-04-16 22:11:43", updated_at: "2009-04-16 22:11:43">, #<Status id: 3, description: "Pending", created_at: "2009-04-16 22:11:43", updated_at: "2009-04-16 22:11:43">] >> statuses = Status.find(:all, :order => "description desc") ActiveRecord::StatementInvalid: DBI::DatabaseError: 42000 (156) [FreeTDS][SQL Server]Incorrect syntax near the keyword 'DESC'.: SELECT * FROM [statuses] ORDER BY ription desc DESC from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in `log' from /opt/local/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.15/lib/active_record/connection_adapters/sqlserver_adapter.rb:728:in `raw_execute' from /opt/local/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.15/lib/active_record/connection_adapters/sqlserver_adapter.rb:751:in `raw_select' from /opt/local/lib/ruby/gems/1.8/gems/rails-sqlserver-2000-2005-adapter-2.2.15/lib/active_record/connection_adapters/sqlserver_adapter.rb:704:in `select' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:635:in `find_by_sql' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:1490:in `find_every' from /opt/local/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:589:in `find' from (irb):4 </code></pre></div>

15346

true

2009-04-07T13:43:06-04:00

53328

23 cant-load-adapter-with-ruby-191-rails-232 98342

20277

invalid can't load adapter with Ruby 1.9.1 + Rails 2.3.2

2009-04-20T12:05:01-04:00

15346

Ken Collins

Erik DeBill

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/23

Using rails-sqlserver-2000-2005-adapter (2.2.15) I can upgrade from rails 2.2.2 to 2.3.2 just fine as long as I stay on Ruby 1.8.5. If I upgrade to ruby 1.9.1-p0 webrick will fail to start, complaining that it can't find the ActiveRecord adapter. Is this a known incompatibility? This is the exact error I'm getting: [edebill@webdev auth]$ ruby script/server => Booting WEBrick => Rails 2.3.2 application starting on http://0.0.0.0:3000 /usr/lib/ruby/gems/1.9.1/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:76:in `rescue in rescue in establish_connection': Please install the sqlserver adapter: `gem install activerecord-sqlserver-adapter` (no such file to load -- parsedate) (RuntimeError) from /usr/lib/ruby/gems/1.9.1/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:73:in `rescue in establish_connection' my database.yml looks like so: development: # adapter: odbc adapter: sqlserver mode: ODBC dsn: devserver username: PLACEHOLDER\username password: PLACEHOLDER pool: 5 timeout: 5000 schema_search_path: dbo I'm using the following versions of things: [edebill@webdev auth]$ ruby -v ruby 1.9.1p0 (2009-01-30 revision 21907) [x86_64-linux] [edebill@webdev auth]$ gem list *** LOCAL GEMS *** actionmailer (2.3.2) actionpack (2.3.2) activerecord (2.3.2) activeresource (2.3.2) activesupport (2.3.2) builder (2.1.2) daemons (1.0.10) datacats4r (1.2.0.7) dbd-odbc (0.2.4) dbi (0.4.0) deprecated (2.0.1) httpclient (2.1.4) odbc-rails (1.5) rails (2.3.2) rails-sqlserver-2000-2005-adapter (2.2.15) rake (0.8.4) soap4r (1.5.8)

<div>Using rails-sqlserver-2000-2005-adapter (2.2.15) I can upgrade from rails 2.2.2 to 2.3.2 just fine as long as I stay on Ruby 1.8.5. If I upgrade to ruby 1.9.1-p0 webrick will fail to start, complaining that it can't find the ActiveRecord adapter. Is this a known incompatibility? This is the exact error I'm getting: [edebill@webdev auth]$ ruby script/server => Booting WEBrick => Rails 2.3.2 application starting on <a href="http://0.0.0.0:3000">http://0.0.0.0:3000</a> /usr/lib/ruby/gems/1.9.1/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:76:in <code>rescue in rescue in establish_connection': Please install the sqlserver adapter:</code>gem install activerecord-sqlserver-adapter<code>(no such file to load -- parsedate) (RuntimeError)</code> <pre> <code> from /usr/lib/ruby/gems/1.9.1/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:73:in `rescue in establish_connection' </code> </pre> my database.yml looks like so: development: <h1>adapter: odbc</h1> adapter: sqlserver mode: ODBC dsn: devserver username: PLACEHOLDER\username password: PLACEHOLDER pool: 5 timeout: 5000 schema_search_path: dbo I'm using the following versions of things: [edebill@webdev auth]$ ruby -v ruby 1.9.1p0 (2009-01-30 revision 21907) [x86_64-linux] [edebill@webdev auth]$ gem list LOCAL GEMS actionmailer (2.3.2) actionpack (2.3.2) activerecord (2.3.2) activeresource (2.3.2) activesupport (2.3.2) builder (2.1.2) daemons (1.0.10) datacats4r (1.2.0.7) dbd-odbc (0.2.4) dbi (0.4.0) deprecated (2.0.1) httpclient (2.1.4) odbc-rails (1.5) rails (2.3.2) rails-sqlserver-2000-2005-adapter (2.2.15) rake (0.8.4) soap4r (1.5.8)</div>

15346

true

2009-02-09T05:19:26-05:00

136

20 provide-support-for-running-queries-at-different-isolation-levels 69767

20277

resolved Provide support for running queries at different isolation levels

2009-04-20T11:34:33-04:00

15346

Ken Collins

Murray Steele

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/20

In one of our apps we found that an external process was locking tables that we wanted to query to show the user status updates. The external process was taking a long time and the sql queries from the server were blocking until the external process released the lock. What we ended up doing was using SQL Servers Isolation Level stuff to allow our webserver queries to run regardless of the lock status of the tables they were querying. This patch provides that functionality as a connection level method that takes a block. All code within the block is run at the specified isolation level and the original isolation level is restored when the block completes. It's not super-clear to me if this sort of functionality is supposed to be catered for by the :lock option for sqlqueries or not, but I think this might be a bit more flexible.

<div>In one of our apps we found that an external process was locking tables that we wanted to query to show the user status updates. The external process was taking a long time and the sql queries from the server were blocking until the external process released the lock. What we ended up doing was using SQL Servers Isolation Level stuff to allow our webserver queries to run regardless of the lock status of the tables they were querying. This patch provides that functionality as a connection level method that takes a block. All code within the block is run at the specified isolation level and the original isolation level is restored when the block completes. It's not super-clear to me if this sort of functionality is supposed to be catered for by the :lock option for sqlqueries or not, but I think this might be a bit more flexible.</div>

15346

true

2009-02-06T10:33:46-05:00

18871

19 schemaownership-prefix-on-table-names-in-generated-sql 69359

20277

invalid schema/ownership prefix on table names in generated sql

2009-02-09T09:44:18-05:00

15346

Ken Collins

ChrisH

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/19

does the adapter support this? ie instead of generating: select * from [table_name] does it support: select * from schema.[table_name] Thanks. Our sqlserver dba's are telling us it is more efficient to specify the owner/schema prefix and I'm not seeing that I can set that anywhere. Thanks.

<div>does the adapter support this? ie instead of generating: select * from [table_name] does it support: select * from schema.[table_name] Thanks. Our sqlserver dba's are telling us it is more efficient to specify the owner/schema prefix and I'm not seeing that I can set that anywhere. Thanks.</div>

15346

true

2009-01-24T03:03:08-05:00

44835

16 support-encoding-property-in-databaseyml 64316

20277

invalid support encoding property in database.yml

2009-01-26T16:45:59-05:00

11371

Adam Meehan

phoenix

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/16

I had set "encoding: utf8" in my database.yml,but it seems not work. All of the records I selected from my SqlServer2005 is encoding with gbk,and I have to convert evey record to utf8 by IConv.There is some way to set the encoding just like the database.yml of mysql? There are my gems: rails-sqlserver-2000-2005-adapter-2.2.4 dbi (0.4.1) dbd-odbc (0.2.4) I am using odbc to connect my sqlserver,because if I use native connection,I can't commit any change to my database,It will raise " HRESULT error code:0x80020009: COMMIT TRANSACTION".

<div>I had set "encoding: utf8" in my database.yml,but it seems not work. All of the records I selected from my SqlServer2005 is encoding with gbk,and I have to convert evey record to utf8 by IConv.There is some way to set the encoding just like the database.yml of mysql? There are my gems: rails-sqlserver-2000-2005-adapter-2.2.4 dbi (0.4.1) dbd-odbc (0.2.4) I am using odbc to connect my sqlserver,because if I use native connection,I can't commit any change to my database,It will raise " HRESULT error code:0x80020009: COMMIT TRANSACTION".</div>

15346

true

2009-01-19T06:10:44-05:00

3299

14 to_sqlserver_string-does-not-handle-leading-zeros-in-usec 62810

20277

resolved to_sqlserver_string does not handle leading zeros in usec

2009-01-23T12:09:39-05:00

15346

Ken Collins

Jonathan Lim

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/14

to_sqlserver_string does not handle usecs with a leading 0.

<div>to_sqlserver_string does not handle usecs with a leading 0.</div>

15346

true

2009-01-20T09:00:31-05:00

15346

15 information_schemaviews-definitions-are-4000-chars-long 63176

20277

resolved INFORMATION_SCHEMA.VIEWS Definitions Are 4000 Chars Long

2009-01-21T21:56:51-05:00

15346

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/15

Note to self, I recently found this out when dealing with a long view definition. http://joseph.randomnetworks.com/archives/2006/03/21/overcoming-the-limitation-of-information_schemaviews-in-sql-server-2000/ http://msdn.microsoft.com/en-us/library/aa933201.aspx I would love to discuss if I should cope with this by just allow the code to cope with NULL and/or go about a different way.

<div>Note to self, I recently found this out when dealing with a long view definition. <a href="http://joseph.randomnetworks.com/archives/2006/03/21/overcoming-the-limitation-of-information_schemaviews-in-sql-server-2000/"> http://joseph.randomnetworks.com...</a> <a href="http://msdn.microsoft.com/en-us/library/aa933201.aspx">http://msdn.microsoft.com/en-us/...</a> I would love to discuss if I should cope with this by just allow the code to cope with NULL and/or go about a different way.</div>

15346

true

2009-01-11T02:21:15-05:00

38936

13 possible-improvement-of-limitoffset-implementation-not-sure-how-to-patch 45357

20277

invalid pagination Possible improvement of limit/offset implementation (not sure how to patch)

2009-01-12T08:56:14-05:00

15346

Ken Collins

j2fly

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/13

I think I found a better way to do the offset and limit handling but I'm not sure how to go about implementing it into the code as a patch. I can see the method I need to modify on line 357 of the current adapter (2.2.8) add_limit_offset!(sql, options) but I'm not enough of a ruby ninja to implement it. Here are my findings though... hopefully Ken can wire it up? The current adapter when used in conjunction with the will_paginate gem requires an order by to work at all. With no order by my page numbers change, but my record set spins out and stays at the first 10. Here is the query the adapter generates without an order by: <code> SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * FROM [users]) AS tmp1) AS tmp2 </code> You run that in SQL Server Management Studio all day changing the inner query to get a different subset of data to select the top 10 from but with no order by you'll get the same 10 results every time. With an order by it works... sort of... <code> SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * FROM [users] ORDER BY updated_at DESC) AS tmp1 ORDER BY [updated_at] ASC) AS tmp2 ORDER BY [updated_at] desc </code> The problem I ran into (the reason I say sort of above) was that not all of my updated_at columns were populated with a date. We did a historical load of user data and the engineer that wrote the SSIS script didn't put in the current date/time or anything to the updated_at so after the first 9 pages of my 18k rows or users (10 per page) I started seeing the same set of users over and over. I believe it was because all their updated_at columns were the same "NULL" value. However, I don't think this situation is unique to my NULL fields on the order by. What happens if there is an order by but it's a lot of repeated data, like say a state name or something. Would this possibly cause the same issue as my NULL field with the adapter's current implementation of the offset and limiting? I guess I don't know 100%, just raising the question. In either case, I think I may have found a solution. I did some research on other ways to achieve limits and offsets in SQL and I believe I have found a better way. It's 2 separate queries for the 2 different situations. The first if you know you have an order by specified in the options hash, and the 2nd if you have no order by. This query gets the first 10 records from my users table ordered by updated_at with the newest updated record at the top. <code> SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY updated_at desc) AS rnum, * FROM users) AS user_with_rnum where rnum >= 1 and rnum <= 10 </code> To see the next 10, simply add 10 to both numbers in there where, like so... <code> SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY updated_at desc) AS rnum, * FROM users) AS user_with_rnum where rnum >= 11 and rnum <= 20 </code> This seems to continue to work even when I reach my null fields (numbers 71 and 80 for me), providing the next set of them based on that row count, which I believe is the key. This second option would be if you know there is no order by, it would still allow you to page through the users (10 at a time in this case) ordered by ID in an ascending (ASC) fashion (the default when nothing is specified in SQL) Again, this query gets the first 10 records.... <code> select top 10 * from users usr where usr.id not in (select top 0 usr2.id from users usr2) </code> For this one, to see the next 10 just add 10 to the 0 like so... (this gets you records 11-20) even though the syntax seems weird. <code> select top 10 * from users usr where usr.id not in (select top 10 usr2.id from users usr2) </code> This gets you records 21-30 ... and so on (again, I know... sort of creepy syntax, but it does work!) <code> select top 10 * from users usr where usr.id not in (select top 20 usr2.id from users usr2) </code> Is there a way to have both of these options implemented to help shore up this limitation with the existing adapter? Again, I apologize that I can not provide a patch directly, but hopefully I've highlighted what I believe is a good fix with direct SQL anyway. Thanks, -Jon

<div>I think I found a better way to do the offset and limit handling but I'm not sure how to go about implementing it into the code as a patch. I can see the method I need to modify on line 357 of the current adapter (2.2.8) add_limit_offset!(sql, options) but I'm not enough of a ruby ninja to implement it. Here are my findings though... hopefully Ken can wire it up? The current adapter when used in conjunction with the will_paginate gem requires an order by to work at all. With no order by my page numbers change, but my record set spins out and stays at the first 10. Here is the query the adapter generates without an order by: SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * FROM [users]) AS tmp1) AS tmp2 You run that in SQL Server Management Studio all day changing the inner query to get a different subset of data to select the top 10 from but with no order by you'll get the same 10 results every time. With an order by it works... sort of... SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * FROM [users] ORDER BY updated_at DESC) AS tmp1 ORDER BY [updated_at] ASC) AS tmp2 ORDER BY [updated_at] desc The problem I ran into (the reason I say sort of above) was that not all of my updated_at columns were populated with a date. We did a historical load of user data and the engineer that wrote the SSIS script didn't put in the current date/time or anything to the updated_at so after the first 9 pages of my 18k rows or users (10 per page) I started seeing the same set of users over and over. I believe it was because all their updated_at columns were the same "NULL" value. However, I don't think this situation is unique to my NULL fields on the order by. What happens if there is an order by but it's a lot of repeated data, like say a state name or something. Would this possibly cause the same issue as my NULL field with the adapter's current implementation of the offset and limiting? I guess I don't know 100%, just raising the question. In either case, I think I may have found a solution. I did some research on other ways to achieve limits and offsets in SQL and I believe I have found a better way. It's 2 separate queries for the 2 different situations. The first if you know you have an order by specified in the options hash, and the 2nd if you have no order by. This query gets the first 10 records from my users table ordered by updated_at with the newest updated record at the top. SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY updated_at desc) AS rnum, * FROM users) AS user_with_rnum where rnum >= 1 and rnum <= 10 To see the next 10, simply add 10 to both numbers in there where, like so... SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY updated_at desc) AS rnum, * FROM users) AS user_with_rnum where rnum >= 11 and rnum <= 20 This seems to continue to work even when I reach my null fields (numbers 71 and 80 for me), providing the next set of them based on that row count, which I believe is the key. This second option would be if you know there is no order by, it would still allow you to page through the users (10 at a time in this case) ordered by ID in an ascending (ASC) fashion (the default when nothing is specified in SQL) Again, this query gets the first 10 records.... select top 10 * from users usr where usr.id not in (select top 0 usr2.id from users usr2) For this one, to see the next 10 just add 10 to the 0 like so... (this gets you records 11-20) even though the syntax seems weird. select top 10 * from users usr where usr.id not in (select top 10 usr2.id from users usr2) This gets you records 21-30 ... and so on (again, I know... sort of creepy syntax, but it does work!) select top 10 * from users usr where usr.id not in (select top 20 usr2.id from users usr2) Is there a way to have both of these options implemented to help shore up this limitation with the existing adapter? Again, I apologize that I can not provide a patch directly, but hopefully I've highlighted what I believe is a good fix with direct SQL anyway. Thanks, -Jon</div>

15346

true

2008-11-24T09:23:12-05:00

38704

1 problem-with-queries-from-will_paginate-plugin 1

20277

invalid pagination Problem with queries from will_paginate plugin

2009-01-10T17:32:11-05:00

38936

j2fly

Juanma Cervera

Ken Collins

http://rails-sqlserver.lighthouseapp.com/projects/20277/tickets/1

I was having troubles with some queries, when I use the will_paginate plugin, and I have to specify the order for the result with the fields prefixed by table names. This is a sample case. @@@ ruby class Author < AvtiveRecord::Base #columns:(id, name) has_many :books end class Book < ActiveRecord::Base #columns:(id, name, author_id) belongs_to :author end . @@@ This fails, but already failed with the old adapter: @@@ ruby Author.paginate :all, :include => :books, :per_page => 10, :page => 2, :order => "authors.name, books.name" @@@ I can see what is the problem, but I can't think a way to avoid it. Juan M. Cervera

<div>I was having troubles with some queries, when I use the will_paginate plugin, and I have to specify the order for the result with the fields prefixed by table names. This is a sample case. <pre><code class="ruby"> class Author < AvtiveRecord::Base #columns:(id, name) has_many :books end class Book < ActiveRecord::Base #columns:(id, name, author_id) belongs_to :author end . </code></pre> This fails, but already failed with the old adapter: <pre><code class="ruby"> Author.paginate :all, :include => :books, :per_page => 10, :page => 2, :order => "authors.name, books.name" </code></pre> I can see what is the problem, but I can't think a way to avoid it. Juan M. Cervera</div>