Configure unixODBC and FreeTDS to access MSSQLServer on linux

Two sets of DataSource, whose server settings are the same, but clientcharset is set to UTF-8 and GB2312.

1. install unixODBC

Download the installation package.
UnixODBC-2.2.11-1.RHEL4.1.i386.rpm
UnixODBC-devel-2.2.11-1.RHEL4.1.i386.rpm
UnixODBC-kde-2.2.11-1.RHEL4.1.i386.rpm

Install
Rpm-Uvh unixODBC-2.2.11-1.RHEL4.1.i386.rpm
Rpm-Uvh unixODBC-devel-2.2.11-1.RHEL4.1.i386.rpm
If you are prompted to have dependencies on other installation packages during installation, follow the prompts to install
Packages.

Or install unixODBC from the source code.
Download source code set
UnixODBC-2.2.11.tar.gz
# Tar xzf unixODBC-2.2.11.tar.gz
# Cd unixODBC-2.2.11
#./Configure
# Make
# Make install

./Configure may also prompt that some dependent libraries cannot be found.
Download the source code, compile, and install these dependent libraries.

The difference between rpm installation and source code compilation and installation lies in the configuration file odbc. ini and odbcinst. ini.
The former is/etc, and the latter is/usr/local/etc.

2. install FreeTDS

UnixODBC provides support for ODBC in Linux, but it is only an ODBC manager and needs to be connected
The actual database must provide the ODBC driver for this database.

FreeTDS is a free ODBC driver used in Linux to connect ms SQL Server and Sybase.
Note: you must first install unixODBC and then freetds.

Freetds-0.64 is currently the latest stable version.
Download source package freetds-stable.tgz
# Tar xzf freetds-stable.tgz
#./Configure-with-unixodbc =/usr/local-with-tdsver = 8.0
# Make
# Su
# Make install
Install to/usr/local

If unixODBC is installed by source code
-With-unixodbc =/usr/local
If unixODBC is installed at rpm
This option is not required

3. configure unixODBC and FreeTDS

3.1 register FreeTDS driver with unixODBC
Modify/etc/odbcinst. ini (or/usr/local/etc/odbcinst. ini)
Add
=========================================/Etc/odbcinst. ini =
# Driver from FreeTDS package
# Setup from FreeTDS package
[FREETDS]
Description = ODBC of FreeTDS for ms SQL 2000
Driver =/usr/local/lib/libtdsodbc. so
Setup =/usr/local/lib/libtds. so
FileUsage = 1

========================================================== ======================================

3.2 add access information pointing to a specific database in the FreeTDS configuration file
Modify/usr/local/etc/freetds. conf
Add the following content at the end of the configuration file. Read the original content carefully. this is a good teaching material.
========================================================== ======================================
[MYSQLSERVER]
Host = 192.168.0.5; IP address or domain name of the ms SQL Server
Port = 1433
Tds version = 8.0
Client charset = UTF-8; character set that can be recognized by client software.
========================================================== ======================================
Note that the client charset must be greater than or equal to the character set used by the server database.
For example, the Server database is ms SQL Server 2000 and the character set is GB2312.
Then the client charset can be set to GB2312 (equal to), GB18030 (greater than), UTF-8 (greater)
But cannot be set to ISO-8859-1 (less than), Shift_JIS (not equal ).
Since the UTF-8 is the superset of the character set, it is always feasible to set it to the UTF-8.
In addition, the FreeTDS client charset cannot be set to UTF16. at present, the limitations of the software design are as follows,
According to its official website, support for client charset UTF16 may be added in future versions.

3.3 Modify/etc/odbc. ini (if odbc is installed from the source code,/usr/local/etc/odbc. ini)
Add DSN.
===========================================/Etc/odbc. ini =
[ODBC Data Sources]
TEST1dsn = My first Test DSN
TEST2dsn = My second Test DSN

[TEST1dsn]; DSN name
Driver =/usr/local/lib/libtdsodbc. so
Description = My First Test DSN
Trace = No
Servername = MYSQLSERVER; defined in freetds. conf
Database = MYTESTDB; Database name

[TEST2dsn]
Driver =/usr/local/lib/libtdsodbc. so
Description = My Second Test DSN
Trace = No
Server = 192.168.0.5; you can directly write the access information of the database Server.
Database = MyTESTDB
Port = 1433
TDS_Version = 8.0

[Default]
Driver =/usr/local/freetds/lib/libtdsodbc. so
========================================================== ======================================

Note:
There are two configuration methods to access ms SQL Server through freeTDS with unixODBC.

(1) write the server information in the configuration file $ PREFIX/etc/freeTDS. conf of freetds,
In/etc/odbc. ini, Servername is used to point to the DSN set in freetds. conf.
In the above example, [TEST1dsn]
(2). Another method is to write the server information in/etc/odbc. ini. [TEST2dsn] in the preceding example.
Note that the keywords are different. for example, the tds version in freetds. conf is
In/etc/odbc. ini, TDS_Version is used.

Method (2) is relatively simple, but there are only a few keywords to control freetds.
For other features, use the default configuration of freetds.

Method (1) Although complicated, freetds can be controlled in more detail. for example, you can specify a client
Character set.

Recommended method (1) for configuration.

4. access the database:

Whether using client software or programming to access the database, three parameters are usually provided.
DSN, UserName, Password.
For example,
DSN = TEST1dsn or TEST2dsn
UserName = somename,
Password = somepasswd,

It means to access the ms SQL Server library MYTESTDB at 192.168.0.5. query results
Character Set for UTF-8.

A universal GUI database connection client provided by unixODBC is DataManager.

V. character set:

FreeTDS can automatically identify the charset on the server. Therefore, FreeTDS requires you to set
Charset. this is the charset used by the client application to obtain data from FreeTDS.
Once the client charset is set, FreeTDS implements
.

If both client applications have access to the same ms SQL server, but unfortunately, both client applications
The accepted character sets are UTF-8 and GB2312. then the solution is to set in FreeTDS. conf
Two sets of DataSource, whose server settings are the same, but the client charset is set to UTF-8 and GB2312.
Odbc. ini also sets two different DSN groups to point to the two DataSource groups respectively.
Use the corresponding DSN respectively.

6. test the connection between freetds and sqlserver

/Usr/local/freetds/bin/tsql-S Server2005-U game-P test. Com-H 192.168.1.1-p 1433

If garbled characters occur, try

Run freetds etc

Export LANG = zh_CN.GB18030

 

7. odbc uses freetds dsn to connect to sqlserver

Odbc. ini

[Game]

Servername = Server2005 [this is the dsn configured for freetds]
Driver =/usr/local/lib/libtdsodbc. so
Description = SQLSERVER
Database = testgame
# UserName =
# Password =
# Port = 1433
Fileusage = 1

/Usr/local/unixODBC/bin/isql-v game 123123