Unicode on SQL Server with Rails 1.2 RC1

Rails
1

Hi there,

I’m trying to test the new Unicode support in Rails 1.2 RC1 with a
Microsoft SQL Server 2000 database. If I use the out-of-the-box
configuration, I get UTF8 support in Rails. I can connect up to a SQL
Server, using nvarchar columns, and a scaffolded demo will save and
display data correctly.

However since SQL Server isn’t using UTF8 (it’s UTF16 I believe), if I
look at the actual data in the database through a 3rd party tool like
Query Analyzer, the data is mangled. It appears that the database layer
is simply storing the UTF8 in byte-pairs. This will mean that non-exact
where clauses won’t work, database ordering will be wrong, 3rd party
tools won’t be able to query etc.

So am I doing something wrong or is this how Rails is supposed to work
with SQL Server? I realise that Oracle/MySQL both support UTF8 and so
won’t have this problem, but what about SQL Server?

Thanks in advance, and apologies if this is the wrong list.

Jon Rowland

2

I have the same problems you have, and thought the I could write some
conversion encoding function for my models, making the conversions in
after_read and before_save Active Record Callbacks.
But now, yesterday, I have known about this adapter from Sam S., that
I think makes the conversion automatically.
I still had no time to try, but I’ll do it for sure, asap

mssqlclient:

http://rubyforge.org/projects/mssqlclient
http://substantiality.net/past/2006/10/19/mssqlclient-gem-now-available/

And also check this (It seems interesting to me):

I hope it helps you.

Juanma C.

Jon wrote:

Hi there,

I’m trying to test the new Unicode support in Rails 1.2 RC1 with a
Microsoft SQL Server 2000 database. If I use the out-of-the-box
configuration, I get UTF8 support in Rails. I can connect up to a SQL
Server, using nvarchar columns, and a scaffolded demo will save and
display data correctly.

However since SQL Server isn’t using UTF8 (it’s UTF16 I believe), if I
look at the actual data in the database through a 3rd party tool like
Query Analyzer, the data is mangled. It appears that the database layer
is simply storing the UTF8 in byte-pairs. This will mean that non-exact
where clauses won’t work, database ordering will be wrong, 3rd party
tools won’t be able to query etc.

So am I doing something wrong or is this how Rails is supposed to work
with SQL Server? I realise that Oracle/MySQL both support UTF8 and so
won’t have this problem, but what about SQL Server?

Thanks in advance, and apologies if this is the wrong list.

Jon Rowland

3

On Tue, 02 Jan 2007 03:08:13 -0800
“Jon” [email protected] wrote:

I’m trying to test the new Unicode support in Rails 1.2 RC1 with a
Microsoft SQL Server 2000 database. If I use the out-of-the-box
configuration, I get UTF8 support in Rails. I can connect up to a SQL
Server, using nvarchar columns, and a scaffolded demo will save and
display data correctly.

Is this a Windows or Linux box. I got FreeTDS+unixODBC to work
properly with unicode hitting SQL Server.

from my /etc/freetds.conf:

[Developer02]
host = developer02
port = 1433
tds version = 8.0
client charset = UTF-8

From my database.yml:
development:
adapter: sqlserver
mode: odbc
dsn: ***
username: ***
password: ***
encoding: utf8

$ uname -a
Linux nomad-ii 2.6.18-gentoo-r3 #2 PREEMPT Mon Nov 27 12:43:53 EST
2006 x86_64 AMD Athlon™ 64 Processor 3200+ AuthenticAMD GNU/Linux

  • dev-db/freetds
    Latest version available: 0.64
    Latest version installed: 0.64

Jim P.