Model.where(:column => value) error with CP1252 string

[César Amador]

Hi there:

We have a legacy DB with CP1252 encoding. If we try to retrieve all posts with "titulo = 'Cafe'", rails console says:

1.8.7-head :051 > NoticiasPost.where(:titulo => Iconv.conv('cp1252', 'utf8', 'Cafe'))
  NoticiasPost Load (2.0ms)  EXEC sp_executesql N'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Cafe'''
 => []

Which is fine.

But, if we try the CP1252 encoded string 'Café' then it says:

>1.8.7-head :052 > NoticiasPost.where(:titulo => Iconv.conv('cp1252', 'utf8', 'Café'))
  NoticiasPost Load (2.0ms)  EXEC sp_executesql 'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Caf�'''
ActiveRecord::StatementInvalid: TinyTds::Error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.: EXEC sp_executesql 'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Caf�'''


Note the missing N preceding the select sentence in the second case.

How can we fix it?

Config and versions we are using:

MS-SQL Server 2014 with Modern_Spanish_CI_AS collation.

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

No freetds.conf

$ ruby -v
ruby 1.8.7 (2014-01-28 patchlevel 376) [x86_64-linux]

$ rails -v
Rails 3.2.22

$ gem list
.
.
.
activerecord-sqlserver-adapter (3.2.13)
tiny_tds (0.5.1)

database.yml:
development:
    adapter: sqlserver
    mode: dblib
    dataserver: <server_ip>
    username: <username>
    password: <password>
    encoding: cp1252


Many thanks.

[Ken Collins]

You need to get on a Ruby version that has proper string encoding. The latest TinyTDS does not even support 1.8.7. Please upgrade to 1.9 at least, but I really recommend 2.x.

 - Ken

[César Amador]

Hi Ken,

thank you for your quick answer.

We are on our very first steps on our way to migrate from  Rails 2.3.x/Ruby 1.8.7/SQL Server 2000 to Rails 4/Ruby 2.x/SQL Server 2014.

After upgrade Ruby version (1.9.3) and a few gems (tiny_tds 0.7.0 instead of 0.5.1 and i18n 0.7.0 instead of 0.6.11), this are the results:

Loading development environment (Rails 3.2.22)
1.9.3-p547-latest :001 > NoticiasPost.where(:titulo => 'Cafe'.encode('cp1252'))
  NoticiasPost Load (2.2ms)  EXEC sp_executesql N'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Cafe'''
 => []
1.9.3-p547-latest :002 > NoticiasPost.where(:titulo => 'Café'.encode('cp1252'))
  NoticiasPost Load (3.2ms)  EXEC sp_executesql 'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Caf�'''

ActiveRecord::StatementInvalid: TinyTds::Error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.: EXEC sp_executesql 'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Caf�'''

Basically, we are in the same situation. We've tried Ruby 2.x but got some errors running Rails console.

Are we doing anything wrong?

Thanks

[Ken Collins]

I have no idea why you are forcing your string encoding on the predicate. That would break the adapter quoting for sure. The way the adapter and all the C extensions are written underneath, encoding your predicates is something you should not be have to do or really want to do.

Could you explain more the reasoning behind this?

 - Ken

[César Amador]

Sure.

Data is stored as Windows-1252 due to a very old Windows application with no Unicode support. In Rails 2.3.x we retrieve data, convert it to UTF-8 and send it to the browser, the client sends back UTF-8 encoded data and we store it as CP1252. All source files in the Rails app are UTF-8 as well.

We are using fake_arel and 'where(:column => <CP1252 String>)' works fine. In fact, in Rails 3.2.x we can create new records with CP1252 strings, but no update them nor search them using those strings, as the example shows.

The configuration for DB connection is exactly the same (except the ActiveRecord adapter gem version) both in Rails 2 and 3.

Maybe we are completly wrong. In that case, what's the right way to do it?

[César Amador]

I've been poking around with Encoding.default_internal/Encoding.default_external without any luck.

For the sake of completeness, one more example showing searches with UTF-8 and CP1252 strings:

1.9.3-p547-latest :001 > NoticiasPost.last.titulo
  NoticiasPost Load (1.9ms)  EXEC sp_executesql N'SELECT TOP (1) [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] ORDER BY [dbo].[noticias_posts].[id] DESC'
 => "Caf\xE9"
1.9.3-p547-latest :002 > NoticiasPost.last.titulo.encoding
  NoticiasPost Load (2.4ms)  EXEC sp_executesql N'SELECT TOP (1) [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] ORDER BY [dbo].[noticias_posts].[id] DESC'
 => #<Encoding:Windows-1252>
1.9.3-p547-latest :003 > NoticiasPost.last.titulo.encode('utf-8')
  NoticiasPost Load (2.4ms)  EXEC sp_executesql N'SELECT TOP (1) [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] ORDER BY [dbo].[noticias_posts].[id] DESC'
 => "Café"
1.9.3-p547-latest :004 > value = 'Café'
 => "Café"
1.9.3-p547-latest :005 > value.encoding
 => #<Encoding:UTF-8>
1.9.3-p547-latest :006 > NoticiasPost.where(titulo: value)
  NoticiasPost Load (2.5ms)  EXEC sp_executesql N'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Café'''
 => []
1.9.3-p547-latest :007 > value.encode!('cp1252')
 => "Caf\xE9"
1.9.3-p547-latest :008 > NoticiasPost.where(titulo: value)
  NoticiasPost Load (3.1ms)  EXEC sp_executesql 'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Caf�'''

ActiveRecord::StatementInvalid: TinyTds::Error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.: EXEC sp_executesql 'SELECT [dbo].[noticias_posts].* FROM [dbo].[noticias_posts] WHERE [dbo].[noticias_posts].[titulo] = N''Caf�'''

    from /home/user/.rvm/gems/ruby-1.9.3-p547-latest@rails3/gems/activerecord-sqlserver-adapter-3.2.13/lib/active_record/connection_adapters/sqlserver/database_statements.rb:416:in `each'
    from /home/user/.rvm/gems/ruby-1.9.3-p547-latest@rails3/gems/activerecord-sqlserver-adapter-3.2.13/lib/active_record/connection_adapters/sqlserver/database_statements.rb:416:in `handle_to_names_and_values_dblib'
.
.
.

There must be a way to achieve that. I'm stuck in all this messy encoding thing.

Sorry to bother you.

[César Amador]

Finally I managed to make it work. I think I misunderstood the TinyTds 'encoding' setting. Removing it from our database.yml solved the problem.

Now we can use UTF-8 strings on 'where' clauses and get UTF-8 strings as response, although the database is encoded as CP1252.

I am very sorry to bother you.

Many thanks.

[Ken Collins]

No bother, I have just been busy. This is indeed the right way to go.

 - Ken

--
You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rails-sqlserver-a...@googlegroups.com.
To post to this group, send email to rails-sqlse...@googlegroups.com.
Visit this group at https://groups.google.com/group/rails-sqlserver-adapter.
For more options, visit https://groups.google.com/d/optout.