基于Linux下PHP连接SQL Server的FreeTDS配置

CentOS 5.4 Linux 下的 PHP(FastCGI) 需要连接相关部门的SQL Server 2000数据库，配置了扩展FreeTDS扩展。

1、编译安装FreeTDS

<ol class="dp-xml">
<li class="alt"><span><span>mkdir -p /data0/software/  </span></span></li>
<li><span>cd /data0/software/  </span></li>
<li class="alt"><span>wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz  </span></li>
<li><span>tar zxvf freetds-stable.tgz  </span></li>
<li class="alt"><span>cd freetds-0.82/  </span></li>
<li>
<span>./configure </span><span class="attribute">--prefix</span><span>=/usr/local/webserver/freetds </span><span class="attribute">--with-tdsver</span><span>=</span><span class="attribute-value">8</span><span>.0 --enable-msdblib  </span>
</li>
<li class="alt"><span>make &amp;&amp; make install  </span></li>
<li><span>cd ../  </span></li>
<li class="alt"><span> </span></li>
<li>
<span>echo "/usr/local/webserver/freetds/lib/" </span><span class="tag">&gt;</span><span> /etc/ld.so.conf.d/freetds.conf  </span>
</li>
<li class="alt"><span>ln -s /usr/local/webserver/freetds/lib/libsybdb.so.5.0.0 /usr/local/webserver/freetds/lib/libsybdb.so.4  </span></li>
<li><span>/sbin/ldconfig  </span></li>
<li class="alt"><span> </span></li>
<li><span>rm -f /usr/local/webserver/freetds/etc/freetds.conf  </span></li>
<li class="alt"><span>vi /usr/local/webserver/freetds/etc/freetds.conf  </span></li>
</ol>

输入以下内容：

引用

<ol class="dp-xml">
<li class="alt"><span><span>[global]  </span></span></li>
<li><span>        # TDS protocol version  </span></li>
<li class="alt">
<span>;       tds </span><span class="attribute">version</span><span> = </span><span class="attribute-value">4</span><span>.2  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>        # Whether to write a TDSDUMP file for diagnostic purposes  </span></li>
<li><span>        # (setting this to /tmp is insecure on a multi-user system)  </span></li>
<li class="alt">
<span>;       dump </span><span class="attribute">file</span><span> = /tmp/freetds.log  </span>
</li>
<li>
<span>;       debug </span><span class="attribute">flags</span><span> = </span><span class="attribute-value">0xffff</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li><span>        # Command and connection timeouts  </span></li>
<li class="alt">
<span>;       </span><span class="attribute">timeout</span><span> = </span><span class="attribute-value">10</span><span> </span>
</li>
<li>
<span>;       connect </span><span class="attribute">timeout</span><span> = </span><span class="attribute-value">10</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li><span>        # If you get out-of-memory errors, it may mean that your client  </span></li>
<li class="alt"><span>        # is trying to allocate a huge buffer for a TEXT field.  </span></li>
<li><span>        # Try setting 'text size' to a more reasonable limit  </span></li>
<li class="alt">
<span>        text </span><span class="attribute">size</span><span> = </span><span class="attribute-value">64512</span><span> </span>
</li>
<li><span> </span></li>
<li class="alt">
<span>        </span><span class="attribute">host</span><span> = </span><span class="attribute-value">mssql</span><span>.yourdomain.com  </span>
</li>
<li>
<span>        </span><span class="attribute">port</span><span> = </span><span class="attribute-value">1433</span><span> </span>
</li>
<li class="alt">
<span>        tds </span><span class="attribute">version</span><span> = </span><span class="attribute-value">8</span><span>.0  </span>
</li>
<li>
<span>        client </span><span class="attribute">charset</span><span> = </span><span class="attribute-value">UTF</span><span>-8 </span>
</li>
</ol>

2、编译安装PHP自带MSSQL扩展

进入本地已存在的php-5.2.XX源码包目录：

<ol class="dp-xml">
<li class="alt"><span><span>cd /data0/software/php-5.2.XX/ext/mssql/  </span></span></li>
<li><span>/usr/local/webserver/php/bin/phpize  </span></li>
<li class="alt">
<span>./configure </span><span class="attribute">--with-php-config</span><span>=/usr/local/webserver/php/bin/php-config </span><span class="attribute">--with-mssql</span><span>=/usr/local/webserver/freetds/  </span>
</li>
<li><span>make &amp;&amp; make install  </span></li>
</ol>

3、在php.ini配置文件中增加mssql.so

<ol class="dp-xml"><li class="alt"><span><span>vi /usr/local/webserver/php/etc/php.ini  </span></span></li></ol>

增加一行：

引用

<ol class="dp-xml"><li class="alt"><span><span class="attribute">extension</span><span> = </span><span class="attribute-value">"mssql.so"</span><span> </span></span></li></ol>

4、重启PHP FastCGI

<ol class="dp-xml"><li class="alt"><span><span>/usr/local/webserver/php/sbin/php-fpm restart  </span></span></li></ol>

5、测试文件(test_mssql.php)：

<ol class="dp-xml">
<li class="alt"><span><span class="tag"></span><span class="tag-name">php</span><span>     </span></span></li>
<li>
<span>header("Content-type: text/html; </span><span class="attribute">charset</span><span>=</span><span class="attribute-value">utf</span><span>-8");     </span>
</li>
<li class="alt">
<span>$</span><span class="attribute">msdb</span><span>=</span><span class="attribute-value">mssql_connect</span><span>("mssql.yourdomain.com:1433","username","password");     </span>
</li>
<li><span>if (!$msdb) {     </span></li>
<li class="alt"><span>        echo "connect sqlserver error";     </span></li>
<li><span>        exit;     </span></li>
<li class="alt"><span>}     </span></li>
<li><span>mssql_select_db("database_name",$msdb);     </span></li>
<li class="alt">
<span>$</span><span class="attribute">result</span><span> = </span><span class="attribute-value">mssql_query</span><span>("SELECT top 5 * FROM table", $msdb);     </span>
</li>
<li>
<span>while($</span><span class="attribute">row</span><span> = </span><span class="attribute-value">mssql_fetch_array</span><span>($result)) {     </span>
</li>
<li class="alt"><span>        var_dump($row);     </span></li>
<li><span>}     </span></li>
<li class="alt"><span>mssql_free_result($result);     </span></li>
<li>
<span class="tag">?&gt;</span><span>    </span>
</li>
</ol>