Welcom ,I hope yo will enjoy here!

rails通过freetds连接sqlserver

    在网上Google了一翻, 很多解决方案都是围绕的ODBC, 这种方法配置麻烦, 在操作系统的不同版本之间也有细微的差异, 依赖的东西很多, 按着步骤配都会一路error.

    Ruby官方推荐的解决方案是用ActiveRecord, 由于文档很少, 很少有人配置成功过, 这里给出详细步骤:

 

一 安装freetds

 

1 freetds与tiny_tds的关系

 

1.1 FreeTDS(TDS协议的一种开源实现方式)

FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.

Technically speaking, FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by these databases for their own clients. It supports many different flavors of the protocol and three APIs to access it. Additionally FreeTDS works with other software such as Perl and PHP, providing access from those languages as well.

If you are looking for a Java implementation, we refer you to the jTDS project on SourceForge.

注意: 没有说FreeTDS支持windows, 也没有说可以访问mysql或oracle.

 

1.2 tiny_tds(可以理解为FreeTDS的ruby版本实现, java版本的实现叫jTDS)

TinyTDS - A modern, simple and fast FreeTDS library for Ruby using DB-Library.

The TinyTDS gem is meant to serve the extremely common use-case of connecting, querying and iterating over results to Microsoft SQL Server databases from ruby. Even though it uses FreeTDS’s DB-Library, it is NOT meant to serve as direct 1:1 mapping of that C API.

The benefits are speed, automatic casting to ruby primitives, and proper encoding support. It converts all SQL Server datatypes to native ruby objects supporting :utc or :local time zones for time-like types. To date it is the only ruby client library that allows client encoding options, defaulting to UTF-8, while connecting to SQL Server. It also properly encodes all string and binary data. The motivation for TinyTDS is to become the de-facto low level connection mode for the SQL Server adapter for ActiveRecord. For further details see the special thanks section at the bottom

注意: tiny_tds可以用于windows

 

2 freetds的安装

2.1 编辑安装

    1） 下载freetds-stable.gz

    2） 解压安装

./configure --prefix=/usr/local/freetds --enable-msdblib --with-tdsver=7.0

    3）

    默认安装在/usr/local/freetds目录当中，库文件在相应的lib目录下。 

打开配置文件 vim /etc/ld.so.conf
   添加一行 /usr/local/freetds/lib 
    重新加载动态链接库 ldconfig

 2.2 通过apt-get命令安装

sudo apt-get install freetds-dev freetds-bin tdsodbc

     该命令是ubuntu下的安装，其余平台安装参考 https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/platform-installation

 

3.freetds测试

3.1 查看安装信息

可以通过 tsql -C 命令查看FreeTDS的版本，引用的配置文件等信息

效果如下：

Compile-time settings (established with the "configure" script)
                              Version: freetds v0.82
           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

3.2 测试安装FreeTDS连接MSSQL是否成功

tsql -H MSSQL服务器服务IP -p 1433 -U MSSQL服务器登陆帐号 －P MSSQL服务器登陆密码 –D 连接的数据库

 

测试效果：

root@ubuntu:/etc/freetds# tsql -H XXXXXX-p 1433 -U sa -P XXXXXX -D rails_test
locale is "zh_CN.utf8"
locale charset is "UTF-8"
Default database being set to rails_test
1> select @@version
2> go

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
    Apr 22 2011 11:57:00 
    Copyright (c) Microsoft Corporation
    Enterprise Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)

(1 row affected)
1> select * from users;
2> go
id    name    age    created_at    updated_at
1    a    12     5月 27 2012 05:45下午     5月 27 2012 05:45下午
(1 row affected)
1>

注：tsql测试时，命令需要执行go才运行…

二 rails 连接sqlserver

 

1. 修改Gemfile，添加下列两行

gem 'tiny_tds' 
gem 'activerecord-sqlserver-adapter'

2. 修改/etc/freetds/freetds.conf

[sql2008]
        host = 192.168.0.109
        port = 1433
        tds version = 7.0

3.修改数据库连接文件

development:
  adapter: sqlserver
  mode: dblib
  dataserver: sql2008
  host: 192.168.0.109
  port: 1433
  database: rails_test
  username: sa
  password: "XXXXX" #如果全是数字的话，会自动转为fixnumber,所以最好加上双引号
#  timeout: 5000
#  azure: true

 

三 问题解决

1. Error converting characters into server's character set.

windows下可以直接运行tiny_tds,不用另外安装freetds，这个比较方便~~

但是windows 2008 server + sqlsever 2008 R2， 运行时一段时间，网站叫挂了， 后台报错：

ActiveRecord::StatementInvalid (TinyTds::Error: Error converting characters into server's character set. Some character(s) could not be converted: EXEC sp_executesql N'SELECT @@TRANCOUNT'):

 

原因：有个解密数据，通过cmd调用了windows的exe获取解密数据，windows cmd返回的是gb2312编码，需要转码为utf-8，否则在普通字符下能够插入sql，但是遇到unicode字符就会报错，网站就挂挂掉了。

分析过程： 分析日志，找到最后一条正确的正确的记录，同时找到第一条失败的记录，分析其中的差别，测试时用这个两条作为测试数据反复修改测试。。。。

              将记录里面的数据解密后对比（原数据进行了加密）,

              分析后发现正常的日志记录里面的字符全是 ascii字符（字母+数字+常见符号），失败的记录里面的字符含有中文….

              大概相当编码是编码问题， 就转码了一下，OK了。

decode_data = Iconv.conv( "utf-8", "gb2312", decode_data) if win_os? # win_os为自定义函数，判断系统环境

总结：这个转码语句我一开始就写了，测试时发现不用这句也没有错，就注释掉了，太悲剧了！！这个教训就是告诉我测试一定要具有全面性。

参考：http://www.freetds.org/userguide/localization.htm


 

参考

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/platform-installation #安装参考

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-TinyTDS #配置Tyni_tds

posted on 2012-05-28 02:54  北国的雨  阅读(2812)  评论(0)  编辑  收藏  举报

导航