我正在尝试使用以下方式连接到 MS SQL 数据库自由数据和unixodbc。我读过各种指南,但都不太适合我。当我尝试使用以下方法连接到数据库时数据库工具,我收到以下错误:
$ isql -v TS username password
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
有没有人已经使用以下方法成功建立了与 MS SQL 数据库的连接自由数据和unixodbc在 Ubuntu 12.04 上?如果能得到帮助我将非常感激。
下面是我用来配置的程序自由数据和unixodbc。提前感谢您的帮助!
程序
首先,我安装了以下软件包:
sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc
并配置自由数据如下:
--- /etc/freetds/freetds.conf ---
[TS]
host = SERVER
port = 1433
tds version = 7.0
client charset = UTF-8
使用查询语言工具我可以通过执行成功连接到数据库
tsql -S TS -U username -P password
因为我需要一个odbc我配置的连接odbcinst.ini如下:
--- /etc/odbcinst.ini ---
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout =
CPResuse =
client charset = utf-8
和odbc配置文件如下:
--- /etc/odbc.ini ---
[TS]
Description = "test"
Driver = FreeTDS
Servername = SERVER
Server = SERVER
Port = 1433
Database = DBNAME
Trace = No
尝试使用以下方式连接数据库数据库具有此类配置的工具会导致以下错误:
$ isql -v TS username password
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
答案1
谢谢,你的帖子对我很有用。我可以通过从 odbcinst.ini 文件中删除以下几行来让它正常工作
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout =
CPResuse =
client charset = utf-8
所以现在我的 odbcinst.ini 文件如下所示:
--- /etc/odbcinst.ini ---
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
我的 odbc.ini 文件现在如下所示:
--- /etc/odbc.ini ---
[TS]
Description = "test"
Driver = FreeTDS
Server = SERVER
Port = 1433
Database = DBNAME
一旦我简化了一切,它就运行得很好。我仍然无法让它与 RODBC 一起工作,但它可以与 isql 一起工作。
我不知道这是否有用,但你的帖子对我有帮助。谢谢。
答案2
这是一个最小但完整的示例,如何连接到Azure SQL 数据库使用isql
Ubuntu 14.04.1 LTS。示例摘自如何从 Ubuntu 连接 Azure SQL 数据库(免责声明:这是我的个人维基)。
安装必要的软件包
$ sudo apt-get -y install freetds-bin tdsodbc unixodbc
配置 FreeTDS
文件/etc/freetds/freetds.conf
[global]
tds version = 7.1
[<SERVERNAME>]
host = <HOST>.database.windows.net
port = 1433
测试连接
此时连接tsql
应该可以工作:
$ tsql -S <SERVERNAME> -U <USERNAME>@<HOST> -P <PASSWORD>
请注意,这@<HOST>
是必需的。否则连接将以错误结束:
Msg 40531 (severity 11, state 1) from [<SERVERNAME>] Line 1:
"Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match."
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
配置 ODBC 驱动程序
文件/etc/odbcinst.ini
[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
配置 ODBC 数据源
文件/etc/odbc.ini
[<DATA_SOURCE_NAME>]
Driver = FreeTDS
Servername = <SERVERNAME>
Port = 1433
Database = <DBNAME>
<SERVERNAME>
与 相同freetds.conf
。
使用 isql 进行连接
$ isql -v <DATA_SOURCE_NAME> <USER>@<HOST> <PASSWORD>
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select current_timestamp
+------------------------+
| |
+------------------------+
| 2015-01-02 09:05:55.593|
+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>
请注意,这@<HOST>
是必需的。否则连接将以错误结束:
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[37000][unixODBC][FreeTDS][SQL Server]Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match.
[ISQL]ERROR: Could not SQLConnect
答案3
就我而言,问题是由于我的配置文件中的简单缩进引起的。因此/etc/odbc.ini
,我删除所有缩进瞧!
(odbcinst.ini
表现得像一个正常的孩子并且似乎不会发脾气。)
答案4
12.04 之前的 Ubuntu 在 /etc/odbcinst.ini 文件中有不同的 odbc 路径。
旧的驱动路径是:
Driver = /usr/lib/odbc/libtdsodbc.so
我将其改为:
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
完整配置如下:
--- /etc/odbcinst.ini ---
[FreeTDS]
Description = tdsodbc
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5
现在效果很好!谢谢!