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 192.168.100.1 -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:
putenv('TDSVER=70');

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:
[TDS_NAME_TO_CONNECT]
host = 192.168.0.1
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');





http://www.hoyo.idv.tw/hoyoweb/document/436.html
====================

PDO_DBLIB


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


http://ca2.php.net/manual/en/ref.pdo-dblib.connection.php

http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

http://swik.net/MySQL/MySQL+vs+MS+SQL+Server




http://192.168.184.130/test3.php

http://www.developersdex.com/sql/message.asp?p=581&r=6535941

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/fa474346-3063-4b79-8830-98411029bdc0/

http://www.sql-server-performance.com/tips/blocking_p1.aspx

SET LOCK_TIMEOUT -1

=============================

include("adodb5/adodb.inc.php");
//odbc_connect("DSN=MyServer2k","db_user","db_pass");


//$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=192.168.200.119;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 );
$sth->execute();

/* 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) . '
';
print_r($result);
//echo $result->id_report;
}

unset($conn);