rails通过freetds连接sqlserver
来源:互联网 发布:mac系统下有ai 编辑:程序博客网 时间:2024/05/18 03:30
在网上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
- rails通过freetds连接sqlserver
- rails通过freetds连接sqlserver
- Linux下通过mono_C# + FreeTDS连接sqlserver数据库
- linux 通过 freetds下访问sqlserver
- linux 下用freetds 连接sqlserver
- Linux下通过freetds连接MSSQL数据库
- Linux下通过freetds连接MSSQL数据库
- Linux下通过freetds连接MSSQL数据库
- Linux下通过freetds连接MSSQL数据库
- Linux下通过freetds连接MSSQL数据库
- 使用PHP+FreeTDS在Linux下连接MS SQLServer数据库
- 也谈Linux下使用freetds连接sqlserver数据库
- 关于freetds连接sqlserver报错的问题原因
- 在linux下通过C语言基于freetds连接sql2000
- Linux下通过freetds连接MSSQL数据库解决方案
- ubuntu下通过unixodbc和freetds连接数据库
- linux下使用Freetds 连接MS SqlServer 2000 (C语言实现)
- 使用PHP、FreeTDS在Linux下连接MS SQLServer数据库 [linux]
- 全面理解Unity加载和内存管理
- Windows7 搭建PHP环境(Apache2.2 + PHP5.2 + mySql5.5)
- 6-2-a 用指针访问对象
- 第六周项目三—平面坐标点类
- Android Shell命令dumpsys
- rails通过freetds连接sqlserver
- 抽象类和接口的区别
- 黑马程序员_逻辑运算符
- java jdk & java api 帮助文档(中文、英文版)
- Hudson+Maven+Svn搭建持续集成环境
- C++应用领域
- 开发板如何开启telnet服务
- 如何解決ORACLE表死锁问题
- Slave_IO_Running: Connecting and Last_IO_Errno: 2003