How to install ODBC on Linux to connect to SQLServer database

Operating system: Centos7.2

1. Download the ODBC installation package

unixODBC-2.3.9.tar.gz

freetds-1.3.9.tar. gz

2. Installation steps

Step one: Upload two installation packages to the server

Step two: Install unixODBC, install this first, and then install freetds

Note: If the installation order is inconsistent here, the libtdsodbc.so driver file will not be generated (very important)

2.1. Install gcc on the operating system first:

yum install gcc

2.2. Installation odbc

tar -xvf unixODBC-2.3.9.tar.gz
cd unixODBC-2.3.9
./configure --prefix=/usr/local/unixODBC
make
make install

2.3. Install freetds

tar -xvf freetds-1.3.9.tar.gz
cd freetds-1.3.9
./configure --prefix=/usr/local/freetds --with-unixodbc=/usr/local/unixODBC/
make
make install

2.4. Configure freetds driver

Configuration file directory: /usr/local/freetds/etc/freetds.conf, at the end of the file Add the following content. The tds version refers to the default configuration. The default is 7.3. Just modify the host and port to the normal SQLServer address.

[SQLServer]
    host = serverip
    port = 1433
    tds version = 7.3

2.5. Test whether the configuration is correct

/usr/local /freetds/bin/tsql -S SQLServer -U username to connect to SQLServer (-S specifies the configured TDS service name, -U specifies the user name to log in to the database). A successful connection proves that the driver configuration is correct. If a connection error occurs, it is probably because the tds version setting is incorrect. You can try to modify the value and reconnect.

/usr/local/freetds/bin/tsql -S SQLServer -U sa
Password: 
locale is "zh_CN.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

2.6, configure unixODBC

Indicate the location and location of the database driver for unixodbc Authentication information required to connect to the database. Modify the specific values ​​in the following configuration information according to the actual situation, and configure the driver in /usr/local/unixODBC/etc/odbcinst.ini

[FreeTDS]                                         #驱动名称
Description=FreeTDS Driver
Driver=/usr/local/freetds/lib/libtdsodbc.so     #驱动位置

/usr/local/unixODBC/etc/odbc.ini

[mssql]  #DSN名称
Description=sqlserver connection
Driver=FreeTDS    #上面配置好的驱动名称
TDS_Version=7.3   #TDS驱动版本
TRACE=On　　　　#是否输出日志信息
TraceFile=stderr  #日志信息的位置
Server=serverip  #数据库网络地址
Port=1433       #数据库端口
User=name       #登录用户名
Password=pwd     #密码
Database=db name  #连接的数据库名称

2.7. Test whether the database can be connected

/usr/local/unixODBC/bin/isql mssql name pwd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from sys.objects
+---------------------------------------------------------------------------------------------------------------------------------+------------+-------------+------------+-----------------+-----+-------------------------------------------------------------+------------------------+------------------------+--------------+-------------+--------------------+
| name                                                                                                                            | object_id  | principal_id| schema_id  | parent_object_id| type| type_desc                                                   | create_date            | modify_date            | is_ms_shipped| is_published| is_schema_published|

Note: Since we have already configured the connection database name in the configuration file, do not bring the database name when querying SQL, otherwise An error will be reported.

2.8. Shell script to connect to the database

SQL="/usr/local/unixODBC/bin/isql mssql sa 123"
i_sql=" exec dbo.bulkinsert 10,20,'dbo.stu';    "
echo $i_sql
result=`echo $i_sql|$SQL`

Method 2:

SQL="select * from Student"
EMPLOYEES=`isql mssql sa 123 <<EOF
   $SQL
   go
   EOF

Method 3:

echo "$SQL" > $TMP/sql.$$
EMPLOYEES=`isql mssql sa 123 -i $TMP/sql.$$`
rm $TMP/sql.$$

2.9. Call the command execution on the machine where SQL Server is installed Stored procedure

osql.exe -S ip -U sa -P 123 -d WAI -Q "exec dbo.bulkinsert %start%,%end%,stu

The above is the detailed content of How to install ODBC on Linux to connect to SQLServer database. For more information, please follow other related articles on the PHP Chinese website!