ActiveRecord::ConnectionAdapters::JdbcConnection#columns returns empty array on MS SqlServer

We need more information to investigate this. Please try with JRuby trunk and up-to-date ActiveRecord-JDBC and provide more information on the failure. Thanks!

Here is more information on the issue.

I had the same issue connecting to an axisting table on MS SQL Server.

I had a chat about this on irc://irc.freenode.net/jruby with "nicksieger" on October 30, 2007. You can check the archives.

The issue is that the JDBC adapter tried to look for columns information in the schema that is the same name as the "username" (user used to log into SQL Server). In many situation, the default schema is "dbo".

The thing is that in JdbcAdapterInternalService, there is a different logic to check for a user default schema in the "tables" method than in the "columns_internal" method.

I guess both method should use the same logic to get the default schema if none is specified.

On the other hand, it should probably be nice to be able to add a "schema: schema-name" configuration in database.yml, that the JDBC adapter would use in the "columns" method.

There are two workaround for this problem:

1. Create a schema with the same name as the user you will use to logon to SQL Server and move the tables in that shema,
2. Add the following code at the end of environment.rb:

   module JdbcSpec
     module MsSQL
         def columns(table_name, name = nil)
           cc = @connection.columns(table_name.to_s, nil, "dbo") # replace dbo by the schema your tables are in
           cc.each do |col|
             col.identity = true if col.sql_type =~ /identity/i
             col.is_special = true if col.sql_type =~ /text|ntext|image/i
           end
           cc
         end
     end
   end
   

This is also a problem in oracle. See the columns method in the normal oracle adaptor for the fix.

Here is the patch that I am using for oracle

module ::JdbcSpec
module Oracle
def columns(table_name, name = nil) #:nodoc:

config = ActiveRecord::Base.configurations[RAILS_ENV]
puts config.to_yaml
if /\./.match(table_name)
(owner, table_name) = table_name.split('.')
else
owner = config['username']
end

table_cols = <<-SQL
select column_name as name, data_type as sql_type, data_default, nullable,
decode(data_type, 'NUMBER', data_precision,
'FLOAT', data_precision,
'VARCHAR2', data_length,
'CHAR', data_length,
null) as limit,
decode(data_type, 'NUMBER', data_scale, null) as scale
from all_tab_cols
where owner = '#

'
and table_name = '#

'
order by column_id
SQL

cols = select_all(table_cols, name).map do |row|
limit, scale = row['limit'], row['scale']
if limit || scale
row['sql_type'] << "(#

" + ((scale = scale.to_i) > 0 ? ",#

)" : ")")
end

1. clean up odd default spacing from Oracle
   if row['data_default']
   row['data_default'].sub!(/^(.?)\s$/, '\1')
   row['data_default'].sub!(/^'(.*)'$/, '\1')
   row['data_default'] = nil if row['data_default'] =~ /^null$/i
   end

ActiveRecord::ConnectionAdapters::JdbcColumn.new(config,
row['name'].downcase,
row['data_default'],
row['sql_type'],
row['nullable'] == 'Y')

end
cols
end
end
end

Anther try at the code

module ::JdbcSpec
  module Oracle
    def columns(table_name, name = nil) #:nodoc:
      
      config = ActiveRecord::Base.configurations[RAILS_ENV]
      puts config.to_yaml
      if /\./.match(table_name)
        (owner, table_name) = table_name.split('.')
      else
        owner = config['username']
      end
        
      table_cols = <<-SQL
	select column_name as name, data_type as sql_type, data_default, nullable,
	       decode(data_type, 'NUMBER', data_precision,
				 'FLOAT', data_precision,
				 'VARCHAR2', data_length,
				 'CHAR', data_length,
				  null) as limit,
	       decode(data_type, 'NUMBER', data_scale, null) as scale
	  from all_tab_cols
	 where owner      = '#{owner.upcase}'
	   and table_name = '#{table_name.upcase}'
	 order by column_id
      SQL

      cols = select_all(table_cols, name).map do |row|
	limit, scale = row['limit'], row['scale']
	if limit || scale
	  row['sql_type'] << "(#{(limit || 38).to_i}" + ((scale = scale.to_i) > 0 ? ",#{scale})" : ")")
	end

	# clean up odd default spacing from Oracle
	if row['data_default']
	  row['data_default'].sub!(/^(.*?)\s*$/, '\1')
	  row['data_default'].sub!(/^'(.*)'$/, '\1')
	  row['data_default'] = nil if row['data_default'] =~ /^null$/i
	end

        
	ActiveRecord::ConnectionAdapters::JdbcColumn.new(config,
                         row['name'].downcase,
			 row['data_default'],
			 row['sql_type'],
			 row['nullable'] == 'Y')
        
      end
      cols
    end
  end
end

@Uwe - please try to log in as 'sa' and see if .columns returns the expected values. On sqlserver it might be a permission problem.

I tried dumping the database yesterday using activerecord-jdbc-adapter 0.7.2 and it worked!

However, all table names were converted to lower case. This is bad since the primary use of the database is a Java application that accesses using camel case table names.

Can you look at that, or should I file a separate issue for this?

Please file a bug for the separate issues. Thank you!