Configure unixODBC and FreeTDS on Linux to access MSSQLServer

1. Install the unixODBC download installation package. There is a unixODBC-2.2.11-1.RHEL4.1.i386.rpmunixODBC-devel-2.2.11-1.RHEL4.1. I ON THE RedHat installation disc

1. Install the unixODBC download installation package. There is a unixODBC-2.2.11-1.RHEL4.1.i386.rpmunixODBC-devel-2.2.11-1.RHEL4.1. I ON THE RedHat installation disc

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 supports ODBC in Linux, but it is only an ODBC manager. to connect to the actual database, you 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 in source code, -- with-unixodbc =/usr/local
This option is not required if unixODBC is installed at rpm.

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.
So client charset can be set to GB2312 (equal to), GB18030 (greater than), UTF-8 (greater than) 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, FreeTDS's client charset cannot be set to UTF16. currently, the software design is limited. its official website said that support for client charset UTF16 may be increased 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 of freetds. conf, while/etc/odbc. use Servername in ini to point to freetds. the DSN.
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 TDS_Version in/etc/odbc. ini.

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

Method (1) Although complicated, freetds can be controlled in more detail, for example, the character set of the client can be specified.

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 accessing the ms SQL Server library MYTESTDB at 192.168.0.5. the character set of the query result is 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 the charset on the client. This is the charset that the client application expects to use for the data obtained from FreeTDS.
Once the client charset is set, FreeTDS converts data from the server charset <--> client charset.

If both client applications have access to the same MS SQLServer, but unfortunately the character sets accepted by both client applications are UTF-8 and GB2312. then the solution is in FreeTDS. in conf, set two sets of DataSource, their server settings are the same, but the client charset is set to UTF-8 and GB2312. in odbc. in ini, you also set two different DSN groups to point to the DataSource groups. in the end, the two client programs use their corresponding DSN.

,