??? 在网上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） 解压安装
?
|
1
./configure --prefix=/usr/local/freetds --enable-msdblib --with-tdsver=
7
.
0
??? 3）
??? 默认安装在/usr/local/freetds目录当中，库文件在相应的lib目录下。
1
2
3
打开配置文件 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
3
4
5
6
7
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 连接的数据库
?
测试效果：
7
8
9
10
11
12
13
14
15
16
17
18
19
root
@ubuntu
:/etc/freetds
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
) (
WOW64
)
?
row affected)
> select * from users;
> go
id??? name??? age??? created_at??? updated_at
1????
a???
12?????
5
月
27?
2012?
05
45
下午????
下午
row affected)
>
注：tsql测试时，命令需要执行go才运行…
二 rails 连接sqlserver
?
1. 修改Gemfile，添加下列两行
2
gem
'tiny_tds'
'activerecord-sqlserver-adapter'
2. 修改/etc/freetds/freetds.conf
4
[sql2008]
????????
host =
192
168
0
109
port =
1433
tds version =
0
3.修改数据库连接文件
11
development:
??
adapter: sqlserver
mode: dblib
dataserver: sql2008
host:
109
port:
1433
database: rails_test
username: sa
password:
"XXXXX"?
#? 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"
if?
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
分类:?
Rails,?
DataBase
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
