ActiveRecord Migration: what is the correct way to set the length of the nvarchar data type?

[RubyNewbie]

Yesterday, I installed the latest version of this adapter from this
uri: http://github.com/adzap/rails-sqlserver-adapter/tree/master

I upgraded in hope of being able to support utf-8 encoding for data
stored in SQLServer 2005's nvarchar data type. I've installed each of
the 3 outlined gems and followed the steps to create a migration using
the new nvarchar datatype.

I have this following migration class definition:

class CreateContacts < ActiveRecord::Migration
def self.up
create_table :contacts do |t|
t.column :organization_id, :integer, :null=>false
t.column :first_name, :nvarchar, :limit=>10, :null=>false
t.column :last_name, :nvarchar, :null=>false
t.column :title, :string
t.column :contact_type_id, :integer, :null=>false
t.column :fax, :string
t.timestamps
end
end

def self.down
drop_table :contacts
end
end


When I look at the generated table definition in SQLSever 2K5, the
table's 'first_name; column
created has a length of 2 bytes rather than 10 bytes as specified by
the :limit parameter. In fact all of the columns of type :nvarchar
were created with a default length of 2 bytes.

If I try what the web site documentation has, namely:

t.column :body2_utf8, :nvarchar_max # Creates nvarchar(max)

or in my case:
t.column :first_name, :nvarchar_max, :null=>false

I get an error that says:
DBI::DatabaseError: Execute
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL
Server
Column, parameter, or variable #3: Cannot find data type
nvarchar_max.
HRESULT error code:0x80020009

What is the correct way to set the maximum length of the nvarchar data
type?

[Ken Collins]

Morning,

A few things.

1) That version, althought it should be fine, is adzap's fork. You can
install the main version from this location <http://github.com/rails-sqlserver/2000-2005-adapter/tree/master
> or as the docs say the official ruby forge mirror as the gem
"activerecord-sqlserver-adapter".

2) To date I have never had any issues with the national/unicode types
on either 2000/2005/2008 and all the tests are passing. That said, I
am noticing that you are using a different stack than most. It looks
like you are using ADO and that you are running from Windows. Can you
confirm?

If the answer is yes, currently the latest adapter does not work very
well in the legacy ADO mode with the older DBI 0.2.2. I tried testing
this for the first time the week before last and failed miserably. The
reason being is that most people (like myself) run the adapter from a
UNIX style box with the latest version of the DBI/DBD-ODBC drivers.
The latest version of DBI dropped ADO support and I had hoped that the
recent changes to the adapter tested under ODBC would be a abstract
enough to still allow ADO mode to work. I was horribly wrong, but I
fell that most of my pain was setting up and actually testing under
Windows.

** aside... god I hate ruby on windows **

My hope for better operability on windows is as follows:

a) Find someone in the windows world that knows the kinks for getting
the MRI ruby running and the adapter installed and tested using the
DBI 0.2.2 with the legacy ADO mode and make the discreet changes to
get it working. I still think those changes would be minimal.

b) I have already started a few side conversations with the IronRuby
guys and the adapter will be making some future changes to support the
native ADONET on windows. Once that comes to fruition, the best way to
use the adapter on Windows would possibly be to just install IronRuby.
I still wish someone would pick up (a) above tho too.

In closing, you are doing the right things above, and I've had no
issue with them from either the adapter tests or in production usages.


- Ken

[RubyNewbie]

Ken, I appreciate the detailed reply. I'll try to elaborate a bit but
please bear with me as I am still green in my Ruby and Rails
lifetime.

1. Thanks for clarifying the correct source uri. I believe I have the
official version since I just ran >gem install activerecord-sqlserver-
adapter.

2. What was it that prompted you that I am using the ADO version on
Windows? I can confirm that I am indeed running RoR on Windows and
that is pretty much mandated by our company right now. I do have a
file called C:\Ruby\lib\ruby\site_ruby\1.8\DBD\ADO\ADO.rb that I
manually had to copy there to get SQL Server connectivity working with
the previous version. You refer to this a legacy ADO mode with older
DBI 0.2.2. Is there a more functional or optimal mode of working with
this adpater on Windows? Is this attainable by adding these lines to
my

config.gem 'dbi', :version => '0.4.1'
config.gem 'dbd-odbc', :version => '0.2.4', :lib => 'dbd/ODBC'
config.gem 'activerecord-sqlserver-adapter', :version =>
'1.0.0.9250'

The reason I have :version => '1.0.09250' is because when I do a gem
list, I get the following:
PS C:\TFS\Royalty_System\royalties> gem list
*** LOCAL GEMS ***
actionmailer (2.2.2, 2.0.2, 2.0.0, 1.3.3)
actionpack (2.2.2, 2.0.2, 2.0.0, 1.13.6, 1.13.3)
actionwebservice (1.2.6, 1.2.3)
activerecord (2.2.2, 2.0.2, 2.0.0, 1.15.6, 1.15.3)
activerecord-sqlserver-adapter (1.0.0.9250)


Just a few comments about your section: 'My hope for better

operability on windows is as follows'

a) Sort of a repeat of the question above but, is there another option
for me to not work in the "legacy ADO mode" with the latest adapter"
on Windows? I am the only developer at my company using this RoR and
just starting to reach out to the "greater community" but don't know
who I'd reach out to quite yet.

b) I installed IronRuby a while back along the RubyInStell plugin for
VS 2008 and it had some issues that made me look away and ultimately
wound up using Aptana RadRails IDE where I can interactively debug. I
would welcome a day where I can use VS 2008 and work with RoR in
coherent and robust manner. I don't have a good sense for when that
will come though.

[RubyNewbie]

My mistake on what I had previously indicated about the adpater
version.

I actually have version 2.2.19 and verified that when I did a gem list
from the command prompt. Even when I change the environment.rb file
to load up that version as the following:

config.gem 'dbi', :version => '0.4.1'
config.gem 'dbd-odbc', :version => '0.2.4', :lib => 'dbd/ODBC'

config.gem 'activerecord-sqlserver-adapter', :version => '2.2.19'

I am able to start the ruyb script/console but upon loading up the
list of clients with this command
y Client.all, I get the following error:
>> cl = Client.all
DBI::InterfaceError: Unable to load driver 'ADO' (underlying error:
uninitialized constant DBI::DBD::ADO)
from C:/Ruby/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:
294:in `load_driver'
from C:/Ruby/lib/ruby/1.8/monitor.rb:242:in `synchronize'
from C:/Ruby/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:
236:in `load_driver'
from C:/Ruby/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:
154:in `_get_full_driver'
from C:/Ruby/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:
139:in `connect'

Any and all insight is much appreciated. Thanks, again!

[Ken Collins]

Like I was saying, ADO mode is not supported right now in the
adapter. It would appear that you have no mode setting in your
database.yml and the default ADO is being loaded. Note the ADO DBD
from the DBI library is only in the DBI 0.2.2 and lower. That means if
you are loading DBI::DBD::ADO, you must have an old version in your
gems.

Feel free to set the mode in database.yml to odbc, but I have no idea
how and even if that works on Windows. Perhaps someone here on the
list can speak to it. But my plan is to drop ADO all together and go
with the ADONET mode for IronRuby support.

- Ken

[Ken Collins]

Running this new adapter on Windows ruby is not supported now till I
find the time this week to sit down with the IronRuby guys and get the
ADONET stuff tested. If you are on UNIX then this is what your env.rb
might look like.

config.gem 'dbi', :version => '0.4.1'
config.gem 'dbd-odbc', :version => '0.2.4', :lib => 'dbd/ODBC'

config.gem 'activerecord-sqlserver-adapter', :version => '2.2.19'

See for installing.

-=>> sudo gem install activerecord-sqlserver-adapter
Successfully installed activerecord-sqlserver-adapter-2.2.19
1 gem installed
Installing ri documentation for activerecord-sqlserver-adapter-2.2.19...
Installing RDoc documentation for activerecord-sqlserver-
adapter-2.2.19...

-=>> gem list | grep sqlserver-adapter
activerecord-sqlserver-adapter (2.2.19)

On windows when it is supported... might look like this.

config.gem 'activerecord-sqlserver-adapter', :version => '2.2.19'

IE... if you are on windows, the other DBI/DBD stuff is not needed.
Right now your not going to get this working as I not sure if the
legacy DBD/ADO stuff is supported. I'm just not that windows friendly.
Sorry... but you may be SOL for a few weeks till I get this sorted out
or find someone on the list more familiar windows to direct you and
confirm that the legacy DBI/ADO even works. IE... an env.rb like this.

config.gem 'dbi', :version => '0.2.2'
config.gem 'activerecord-sqlserver-adapter', :version => '2.2.19'

- Ken

[RubyNewbie]

Ken, I appreciate the clarification. It was very infomative. I am
looking to hearing future updates on this adapter as it becomes more
Windows compatible. Sometimes I am not Windows friendly either but
it's sort of our legacy and culture for now... Thanks.

[Ken Collins]

NP, I'll be doing work on the adapter to add an ADONET mode this week
with the IronRuby project. The positive, it will give the adapter a
real low low entry point for those using Windows/IronRuby. The
downside, IronRuby is still only passing 85-90% of ruby's tests. I
could be wrong, but it sounds like it is a ways off from being a first
choice ruby implementation.

My hope is that someone in the Windows world could contribute back to
this OSS project and get the legacy ADO mode working. Windows
contributions are few and far between in my observation, hence my
frustration sometimes when dealing with support issues like this.


- Ken

[tpet...@gmail.com]

would it be possible to also get this working under JRuby? I wanted to
use this on a project i'm going to be wokring on but alas the adapter
isn't working since dbd-odbc isn't loading. i'm suspecting that this
has something to do with ruby-odbc.

[Ken Collins]

Well if you can get all the dependent libs working with JRuby then
this works. So it sound like a feature/bug request for somewhere else,
not the adapter. I myself do not use JRuby and I doubt I ever will.
Is there anyone else on the list that does? If not, do you have the
skills to pick this up, investigate and let us know?

- Ken

[Murray Steele]

My guess would be that ruby-odbc is a C library and Jruby (probably?) doesn't like C libraries.  If there's a dbd-jdbc driver you could try using that instead of ruby-odbc.  To do that you'd want to set the mode of your database.yml configs to :jdbc.  You'd also have to mess with the sqlserver_connection method in the sqlserver-adapter (http://github.com/rails-sqlserver/2000-2005-adapter/blob/8f3ad0977a18017ec7e6acdf1bbd42e0ef65692a/lib/active_record/connection_adapters/sqlserver_adapter.rb#L11) to deal with a jdbc mode and setting up a jdbc based driver_url.  I suspect that activerecord-jdbc (http://jruby-extras.rubyforge.org/ActiveRecord-JDBC/) might help with that.  

One thing to note is that activerecord-jdbc claims to have sqlserver support in it, so you might want to try using that instead of the activerecord-sqlserver adapter.

Cheers,

Murray

2009/8/18 Ken Collins <k...@metaskills.net>