Access to MSSQL Server through ODBC in the Linux environment

In order to solve the problem of the Linux system connecting MSSQL Server, Microsoft provided the ODBC official driver to the Linux system to connect the MSSQL server. With the official driver, the Linux program can easily access the MSSQL server.

The official website provides three versions of the drivers for the following Linux systems for distribution:

64bit Red Hat Enterprise Linux 5

64bit Red Hat Enterprise Linux 6

64bit SUSE Linux Enterprise Service Pack 2

(Actual 64bit CentOS 6.4 and 64bit CentOS 7.1 can be installed using 64bit Red Hat Enterprise Linux 6 version of ODBC driver)

The installation configuration process can be accomplished through the following steps:

1. Download the installation package

Red Hat 5/6:https://www.microsoft.com/en-us/download/details.aspx?id=36437

SUSE 11:http://www.microsoft.com/en-us/download/details.aspx?id=34687

2. Install Unixodbc 2.3.0

# take Red Hat version 6 as an example tar xzf msodbcsql-11.0. 2270.0. Tar . GZCD msodbcsql-11.0. 2270.0 . /build_dm.SH

3. Install Microsoft ODBC Driver One-for-SQL Server on Linux

./Install. SH Install

4. Connect to the database server from the command line

After installing the drive, you can test the connection by driving the command-line tool that comes with it:

192.168. 1.10 sa-d Master ' Select "Hello World" '

If the connection succeeds, the command outputs the query result of "Hello world"

5. Configuring the UNIXODBC Data source

The sqlcmd command can also connect to a database by specifying a data source name, in addition to connecting to the database by specifying the address and user name password. The data source can be configured with the ODBCINST command provided by UNIXODBC:

1). Create a template configuration file Template.ini, which reads as follows

[datasourcenameone for192.168. 1.10 = = = Master

2). Import Configuration

Odbcinst-i-s-l-F Template.ini

Once you're done, you can test the connection again through the SQLCMD command tool

DataSourceName ' Select "Hello World" '

This command and the previous command to directly specify the IP address and username password get the same result

6. Connect to the database server via PHP

This example is done by modifying the MSSQL driver of the CodeIgniter 3.0.0, replacing all the original mssql_* APIs with the Odbc_* API, so that all database operations are performed via an ODBC connection, The support of the original CodeIgniter framework to MSSQL was also reused. The project code is hosted on GitHub: Https://github.com/ratzhou/CodeIgniter. The following is an example of using the modified CodeIgniter framework to connect MSSQL server via ODBC:

<?PHPdefined(' BasePath ') ORExit(' No Direct script access allowed ');/** File name:application/controllers/test.php*/classTestextendsCi_controller { Public functionindex () {
$config [' dbdriver '] = ' mssql '; $config[' DSN '] = 'DataSourceName‘;

You still need to specify the database name and user name password
$config [' database '] = ' my_database '; $config[' username '] = ' sa '; $config[' password '] = ' sa ';

$mssql=$this->load->database ($config,true); $result=$mssql->get (' my_table '); Var_dump($result-Result_array ()); }}

Access to MSSQL Server through ODBC in the Linux environment