Connect php to mssql in CentOS and configure unixODBC

Preface:
After changing my work, I have been busy with the LINUX system architecture, and I have never been too familiar with myself. In the course of work, Baidu has always been the preferred search engine. But later, I had to and eventually used Google. This helps me better and more.

OK, pulled back to the topic, the company is now using the database is more complex, ms SQL, ORACLE, MYSQL are in use, before the use of WINDOWS + PHP environment, in this case, it is easier to use PHP to connect to ms SQL, but the performance is much worse than LAMP. Therefore, the main task of the new job is the migration of system and WEB configuration.

However, in the LAMP environment, PHP has many problems with ms SQL. The company's single-point login problem cannot be achieved. It cannot reflect the advantages of LAMP over WAMP. What should I do? use BAIDU. However, it is found that many Chinese solutions cannot be implemented either when the version is old. Later, I chose GOOGLE and found many E-files solutions. I tried to do it. Although I had taken some detours, I finally realized my needs. period, refer to the freetds official website that provides drivers, carefully read the documentation, and some solutions from our predecessors. Thanks to them.

Currently, I have installed it in the following situations:
First, LAMP has been installed and configured during system installation. It is quite difficult to re-compile the PHP environment.
I use the RPM package and TAR package to install the environment. PHP installation and configuration are not changed, but only ODBC can be used to connect to ms SQL (data source ).

Second, after the system is installed, use the TAR package to install and configure the LAMP environment. In this case, you can re-compile PHP.
I use the TAR package for installation, and there are two ways to re-compile PHP and not re-compile PHP. Recompilation can achieve direct connection and ODBC connection. ODBC connection to ms SQL (Data Source) can be implemented without recompilation)

.

In this blog post, freetds is installed with the TAR package and unixODBC-devel and unixODBC are installed with rpm. Use ODBC to connect to ms SQL (data source ). (For more information about how to connect ms SQL in php, and for several methods, refer to the HOWTO document in [url] www.freetds.org [/url, I used two direct connections and odbc .)

I. installation environment
1, CentOs 5.1 + FreeTds0.64.tar.gz + unixODBC-2.2.11-7.1 (except FreeTds is an additional package, other can be installed using YUM, or installed on the system)
2. It is best to install XWINDOWS. I installed GNOME because it will be used when configuring the DSN (data source. Of course, you can also configure it in the text interface.

Ii. Installation and version display of some packages
1. [root @ rpm-mssql ~] # Rpm-aq | grep httpd
System-config-httpd-1.3.3.1-1.el5
Httpd-2.2.3-11.el5_1.centos.3
Httpd-devel-2.2.3-11.el5_1.centos.3 (this package contains Apache HTTP Server's inclusion files, header files, and APXS utilities. If you want to load additional modules (except for those provided by this product), you need it. Hey hey, if you don't use HTTPD for TAR package when installing PHP, you can use -- with-apxs2 =/usr/sbin/apxs. Additional instructions on installing this)
Httpd-manual-2.2.3-11.el5_1.centos.3

2. [root @ rpm-mssql ~] # Rpm-aq | grep php
Php-cli-5.1.6-15.el5
Php-common-5.1.6-15.el5
Php-5.1.6-15.el5
Php-odbc-5.1.6-15.el5
Php-pdo-5.1.6-15.el5
Php-pear-1.4.9-4
Php-mysql-5.1.6-15.el5
Php-ldap-5.1.6-15.el5
You do not need to install as many as you need. This is because I customized the installation to the LAMP environment during system installation.

3. [root @ rpm-mssql ~] # Rpm-aq | grep unixODBC
UnixODBC-devel-2.2.11-7.1 (this package is the same as httpd-devel,you must install it, install freetds.tar.gz to use, it will automatically find the installation path of unixODBC. Otherwise, compilation errors may occur .)
UnixODBC-2.2.11-7.1
UnixODBC-kde-2.2.11-7.1 (comrades, this to install ah, was originally KDE Desktop graphics configuration ODBC Data Source Tool. It can also be used in GNOME .)

OK. I will not mention the installation package of mysql, because ODBC (DSN data source) is used to connect PHP to ms SQL in the LAMP environment.

4. packages required for installation:
Freetds-0.6.tar.gz [url] http://www.ibiblio.org/pub/linux/alpha/freetds/stable/#/url]
UnixODBC-kde-2.2.11-7.1
UnixODBC-devel-2.2.11-7.1.i386.rpm yum install-y unixODBC-devel [url] http://developer.centos.org/centos/5.1/ OS/I #/centos/#/url]

Iii. installation, testing and Configuration:
1. install freetds (both under the root user. If you are under another user, remember to run make install su-root)
Note: Install GCC compiler and other development tools and libraries during system installation. However, yum is used to install anything less in CentOs.
Note: The same conditions can be installed under REDHAT AS5.1 and as5.

[Root @ rpm-mssql freetds-0.64] # tar zxvf freetds-0.64.tar.gz
[Root @ rpm-mssql ~] # Cd freetds-0.64
[Root @ rpm-mssql freetds-0.64] #./configure -- with-tdsver = 7.0 -- with-unixODBC

Run the following command under the root user, especially make install
[Root @ rpm-mssql freetds-0.64] # make; make install
[Root @ rpm-mssql freetds-0.64] # make clean
OK. You have installed it now. The freetds. conf file is generated in/usr/local/etc.
[Root @ rpm-mssql freetds-0.64] # cd/usr/local/etc
[Root @ rpm-mssql etc] # ll
Total 12
-Rw-r -- 1 root 3572 Jan 24 19:13 freetds. conf
-Rw-r -- 1 root 362 Jan 24 19:13 locales. conf
-Rw-r -- 1 root 219 Jan 24 19:13 pool. conf

You can use ODBCConfig to configure it.
Note: After unixODBC-devel is installed, two files odbc. ini odbcinst. ini are generated in/etc:
[Root @ rpm-mssql freetds-0.64] # ll/etc/od
Odbc. ini odbcinst. ini. rpmnew oddjobd. conf. d/
Odbc. ini. rpmnew oddjob/
Odbcinst. ini oddjobd. conf

2. Test and configure
A. After freetds is installed, use tsql to test it:
[Root @ rpm-mssql etc] #/usr/local/bin/tsql-S 211.103.155.xxx-U sa Password: password (xxx is a three-digit number, changed for security. And the following password is the actual SA password)
Locale is "en_US.UTF-8"
Locale charset is "UTF-8"
Password: (enter the Password again here)
1>
If you are lucky enough, you will see "1>", indicating that your previous compilation is correct.
Next, let's get started. Configure freetds. conf.

B. Edit freetds. conf (this step can be omitted after testing. Because the purpose of installing freetds is to drive freetds. In fact, this also implies what you need to do when using the rpm package)

[Root @ rpm-mssql etc] # vi/usr/local/etc/freetds. conf
Add the following code,
[MySql2k] (In FreeTds0.64, this parameter is very important. In "System DSN", you need to use. This is also a difference from the previous version)
Host = 211.103.155.xxx)
Port = 1433
Tds version = 8.0

C. Configure/etc/odbc. ini/etc/odbcinst. ini)
Here we need to use ODBCConfig/usr/bin/ODBCConfig
[Root @ rpm-mssql etc] #/usr/bin/ODBCConfig
ODBCConfig: cannot connect to X server

D. Switch to x windows. As mentioned earlier, GNOME is used. You can also use KDE. UnixODBC-kde is dedicated for KDE.
[Root @ rpm-mssql etc] #/usr/bin/ODBCConfig, as shown in the following figure. However, it should be declared that the FreeTds version I use is 0.64, which is different from the previous version 0.63. It mainly changes "Servername" and "Setup. In the next series of diagrams, they are described separately.


The purpose of FreeTds installation is to provide a PHP driver for connecting to MSSQL. All the above actions are prepared for this purpose. If the driver has a loading path, you need to add a data source to make the connection to MSSQL successful.

The following E and F are highlighted in illustrated text.
E. The Driver setting process is the process of configuring/etc/odbcinst. ini.
Freetds0.64 adds support for 64-bit databases.


The following figure shows the odbcinst. ini file after graphical configuration. MySql2k is a new location:
[Root @ rpm-mssql ~] # Cat/etc/odbcinst. ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver =/usr/lib/libodbcpsqlS. so
Driver64 =/usr/lib
Setup =/usr/lib
Setup64 =/usr/lib
UsageCount = 1
CPTimeout =
CPReuse =

[MySql2k]
Description = v0.64 with Protocol v8.0
Driver =/usr/local/lib/libtdsodbc. so
Driver64 =/usr/local/lib/libtdsodbc. so
Setup =/usr/lib/libtdsS. so
Setup64 =/usr/lib/libtdsS. so
UsageCount = 1
CPTimeout =
CPReuse =

F. Creation of DSN data sources and precautions:
The image Setting Process of "System DSN" is the process of configuring the/etc/odbc. ini file:



The following figure shows the odbc. ini file after "System DSN" is configured. If you do not have or do not want to install the X Window, set it directly.
[Root @ rpm-mssql ~] # Cat/etc/odbc. ini
[Mssql]
Description = MySql2k
Driver = MySql2k
Servername = MySql2k (here, do not set an IP address or something. It should be the same as setting in FREETDS. CONF. At least I tried this. This is not the case in FREETDS0.63 .)
Database =
UID = sa
PWD =
Port = 1433

G, isql testing:

This is a success. In fact, you can stop this article. However. Let's test the effect of connecting PHP to ms SQL.

H. php Page Test
[Root @ rpm-mssql ~] # Vi/var/www/html/phpmssql. php
<?
$ Conn_beidabiz = odbc_connect ("mssql", sa, "password"); ### password, which is your own direct password. ####
{
Echo "OK ";
}
?>


The full text is over. The next article is estimated to write down the configuration of php connection to MSSQL using the rpm package.

This article is from the blog of "The people of xialicang", please be sure to keep this http://rickyfang.blog.51cto.com/1213/125837