Tuesday, April 21, 2009

FreeBSD MS SQL note

依照 php 的手冊的說法, 在 MS平台上的 php 要讀 MSSQL 只要從 SQL Server
上copy 一 DLL檔到 php 平台的 C:%sysroot% 這樣就可以了, sure, 要在php.ini
裡把相關的 extension 給 unmark掉!

但要在 Linux/FreeBSD/UNIX平台要從 php 讀 MSSQL 就得要透過 FreeTDS 來跑了!

我抓了 freetds-0.62.4.tar.gz 下來!

1.首先要把 freetds給compile & install
我下了 configure 的參數為 :
./configure –prefix=/usr/local/freetds –with-tdsver=8.0 –enable-msdblib

2.重新compile php
在 configure 時要加上一個參數 –with-mssql=/usr/local/freetds

接下來就可以直接用 php 內建連 sql server 的函式玩了!
我連的是 sql server 2000


# make TDS_VER=8.0 install

# tsql -C
Compile-time settings (established with the "configure" script):
Version: freetds v0.64
MS db-lib source compatibility: no
Sybase binary compatibility: unknown
Thread safety: yes
iconv library: yes
TDS version: 8.0
iODBC: no
unixodbc: yes

# tsql -S -U danny

For all of you connecting to MS SQL Server using freeTDS, having trouble to log on with a domain login:

Add the line:

just before calling mssql_connect()

This worked for me with a MS SQL Server 2K Ent.

FreeTDS is work!!!

But after install you shoul add in config file
1. Add in freetds.conf client cahrset:
host =
port = 1433
tds version = 8.0
client charset = UTF-8
;client charset = CP1251

2. For datetime add in locales.conf in [default]:
date format = %Y-%m-%d %I:%M:%S.%z

3. restart apache

4. In your php-script you should connect to [TDS_NAME_TO_CONNECT]
mssql_connect('TDS_NAME_TO_CONNECT', 'php', '123');

This took me a while to figure out, so I may as well pass it along to save somebody time.

If you are having trouble connecting with a DSNless connection using unixodbc with freetds, don't forget to specify the TDS_Version in your connection string.

Example (8.0 is for SQL Server 2000 exclusively):

$dsn = "DRIVER=FreeTDS;TDS_Version=8.0; TRACE=No; SERVER=someserver; PORT=someport; DATABASE=somedb; UID=somelogin; PWD=somepassword";
Couple of notes:
When setting this up, you might notice that the unixODBC isql command likes the password wrapped in single quotes:
isql -v MyDSN MyUserID 'MyPa$$W0rd'

Additionally, if you happen to have a dollar-sign in your password (or username, or DSN) -- you must avoid using double quotes. This is a normal PHP gotcha, but worth mentioning.
Won't work:
$con = mssql_connect ("MyDSN", "MyUserID", "MyPa$$W0rd");

Will work (single quotes):
$con = mssql_connect ("MyDSN", "MyUserID", 'MyPa$$W0rd');



cd /usr/ports/databases/php5-pdo_dblib

ADOdb Database Abstraction Library for PHP (and Python


ports 裡面裝 php4-extensions OR php5-extensions 的時候 把 "sybase_ct" 選起來 FreeTDS 就會自動幫你裝上啦...

PHP 手冊有一段

PDO_DBLIB DSN — Connecting to Microsoft SQL Server and Sybase databases

DSN prefix
The DSN prefix is

sybase: if PDO_DBLIB was linked against the FreeTDS libraries

mssql: if PDO_DBLIB was linked against the Microsoft SQL Server libraries

dblib: if linked against any other variety of DB-lib.

但上面這段似乎有些怪怪的,,, phpinfo() 裡面 pdo_dblib 有 freetds enabled,,

php5-sybase_ct-5.2.8 The sybase_ct shared extension for php

foreach(PDO::getAvailableDrivers() as $driver)
echo $driver.'

new PDO ("sybase:host=$hostname;dbname=$dbname",$username,$pw)
但卻說找不到 driver

new PDO ("dblib:host=$hostname;dbname=$dbname",$username,$pw)

可以連,但是 相當的慢 (光連線到 DB 就要 15 秒! 還沒有做 select 的動作喔)

後來,關鍵在 這個 BSD port:
php5-sybase_ct-5.2.8 The sybase_ct shared extension for php

安裝以後,用 dblib 來連,速度就正常了,但用 sybase 仍然說 driver 找不到 :P

沒裝速度相當慢,, 裝了就正常

( 一開始還搞不清楚幾個東西之間的關係,,, 例如 FreeTDS, php-mssql, sybase, pdo-dblib, etc)

然後分別用 new pdo() 和 mssql_connect() 做 insert 的測試,,, new pdo() 的分式快一些些

$dbh = new PDO ("dblib:host=$hostname;dbname=$dbname;charset=UTF-8",$username,$pw );

lock request time out period exceeded










//$db_connection = new COM("ADODB.Connection");

$db = &ADONewConnection("mssql");
$db->Connect('MyServer2k', 'db_user', 'db_pass', 'danny_testing');
$rs = $db->Execute('select top 10 * from my_member2');
print_r( $rs );


$conn = mssql_connect("MyServer2k", "db_user", "db_pass" );
mssql_select_db('danny_testing', $conn);

$conn = new COM ("ADODB.Connection", NULL, CP_UTF8)


$dsn = "odbc:Driver={SQL Server};Server=;Database=db_name;Uid=db_user;Pwd=db_pass;charset=UTF-8";
//$conn = new PDO ("odbc:odbc_connection_name;charset=UTF-8","db_user","db_pass");

$conn = new PDO($dsn, "db_user", "db_pass", null);

$query = "SELECT TOP 10 id_report, title FROM nai_news";

$sth = $conn->prepare( $query );

/* Exercise PDOStatement::fetch styles */

while( $result = $sth->fetch(PDO::FETCH_ASSOC) ) {
//echo $result['id_report']." ".$result['title']."
//$block_content .= l($links->title, 'node/'.$links->id_report) . '
//echo $result->id_report;


No comments: