[jira] Created: (JRUBY-4221) AR-JDBC: CREATE VIEW wrongly executed as query, fails because doesn't return result set

AR-JDBC: CREATE VIEW wrongly executed as query, fails because doesn't return result set
---------------------------------------------------------------------------------------

                 Key: JRUBY-4221
                 URL: http://jira.codehaus.org/browse/JRUBY-4221
             Project: JRuby
          Issue Type: Bug
          Components: Miscellaneous
    Affects Versions: JRuby 1.3.1
         Environment: JRuby 1.3.1 on JDK 1.6.0_16; Rails 2.3.2; fully updated Windows 2008 Enterprise; fully updated MS SQL Server 2005
            Reporter: Christopher Jeris


[I was unable to file this bug on the AR-JDBC project on kenai; I got the message "Errors: Reporter: Reporter is required", but I was logged into kenai, and there was no reporter field visible.]

The following database migration fragment:
    execute %{
      CREATE VIEW catenated_titles WITH SCHEMABINDING AS
        SELECT id, (title_nonsort_prefix + sort_title) AS catenated_title
          FROM biblio.core_records
    }

yields the following error when 'rake db:migrate --trace' is run:
rake aborted!
An error has occurred, all later migrations canceled:

ActiveRecord::ActiveRecordError: The statement did not return a result set.:
      CREATE VIEW catenated_titles AS
        SELECT id, (title_nonsort_prefix + sort_title) AS catenated_title
          FROM biblio.core_records

C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:212:in `log'
I:/work/biblio/trunk/biblio/vendor/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:565:in `execute'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:352:in `method_missing'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:328:in `say_with_time'
c:/jruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:328:in `say_with_time'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:348:in `method_missing'
db/migrate//20091106170821_create_catenated_titles.rb:3:in `up'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:282:in `migrate'
c:/jruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:282:in `migrate'
(__DELEGATION__):2:in `migrate'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:486:in `migrate'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:562:in `call'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:562:in `ddl_transaction'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:485:in `migrate'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:472:in `each'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:472:in `migrate'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:400:in `up'
C:/jruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/migration.rb:383:in `migrate'
C:/jruby/lib/ruby/gems/1.8/gems/rails-2.3.2/lib/tasks/databases.rake:116
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:in `call'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:in `execute'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:in `each'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:in `execute'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:597:in `invoke_with_call_chain'
c:/jruby/lib/ruby/1.8/monitor.rb:191:in `mon_synchronize'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:590:in `invoke_with_call_chain'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:583:in `invoke'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2051:in `invoke_task'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `top_level'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `each'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `top_level'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2023:in `top_level'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2001:in `run'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:1998:in `run'
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/bin/rake:31
C:/jruby/lib/ruby/gems/1.8/gems/rake-0.8.7/bin/rake:19:in `load'
c:\jruby\bin\rake:19

The problem comes from _execute in lib/jdbc_adapters/jdbc_mssql.rb:
      def _execute(sql, name = nil)
      # [...]
        elsif sql.lstrip =~ /^\(?\s*(select|show)/i
          repair_special_columns(sql)
          @connection.execute_query(sql)
      # [...]
      end

The CREATE VIEW statement above is recognized as a query because the SELECT keyword appears at the beginning of the second line.

I don't know what the right way to do the recognition is.  Maybe DDL statements should always be executed using execute_update?

A workaround for the problem is to move AS from the end of the first line to the start of the second.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email