com_dotnet 擴展 (php.ini:extension=com_dotnet)<?php
/**
* Access 資料庫操作封裝類 (COM/ADO)
*/
class AccessDB_COM
{
private $conn;
private $connString;
public function __construct(string $mdbPath)
{
if (!file_exists($mdbPath)) {
throw new Exception("資料庫檔案不存在: {$mdbPath}");
}
$this->connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={$mdbPath};Persist Security Info=False;";
// 舊版 .mdb 可用: Provider=Microsoft.Jet.OLEDB.4.0
$this->connect();
}
private function connect(): void
{
$this->conn = new COM("ADODB.Connection") or die("ADO 連線建立失敗");
$this->conn->Open($this->connString);
}
public function query(string $sql): array
{
$rs = new COM("ADODB.Recordset");
$rs->Open($sql, $this->conn, 1, 3); // adOpenKeyset, adLockOptimistic
$results = [];
while (!$rs->EOF) {
$row = [];
foreach ($rs->Fields as $field) {
$row[$field->Name] = $field->Value;
}
$results[] = $row;
$rs->MoveNext();
}
$rs->Close();
return $results;
}
public function execute(string $sql): bool
{
try {
$this->conn->Execute($sql);
return true;
} catch (com_exception $e) {
error_log("Access COM Execute Error: {$e->getMessage()}");
return false;
}
}
public function __destruct()
{
if ($this->conn) {
$this->conn->Close();
}
}
}
// 使用範例
try {
$db = new AccessDB_COM(realpath('DATUM/cnbt.mdb'));
$rows = $db->query("SELECT title FROM dbo_dirs");
foreach ($rows as $row) {
echo htmlspecialchars($row['title']) . "
";
}
} catch (Exception $e) {
echo "錯誤: " . $e->getMessage();
}
?>
# Ubuntu/Debian
sudo apt-get install unixodbc unixodbc-dev mdbtools
# 配置 /etc/odbcinst.ini
[MDBTools]
Description = MDBTools Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5
# 配置 /etc/odbc.ini (系統 DSN)
[MyAccessDB]
Description = My Access Database
Driver = MDBTools
Database = /var/www/data/cnbt.mdb
# 可選:ReadOnly = Yes
<?php
/**
* Access 資料庫操作封裝類 (ODBC - 跨平台)
*/
class AccessDB_ODBC
{
private $conn;
private $dsn;
public function __construct(string $dsn = 'MyAccessDB', string $user = '', string $pass = '')
{
$this->dsn = $dsn;
$this->connect($user, $pass);
}
private function connect(string $user, string $pass): void
{
$this->conn = odbc_connect($this->dsn, $user, $pass);
if (!$this->conn) {
throw new Exception("ODBC 連線失敗: " . odbc_errormsg());
}
}
public function query(string $sql): array
{
$result = odbc_exec($this->conn, $sql);
if (!$result) {
throw new Exception("查詢失敗: " . odbc_errormsg($this->conn) . " | SQL: {$sql}");
}
$rows = [];
while (odbc_fetch_row($result)) {
$row = [];
$numFields = odbc_num_fields($result);
for ($i = 1; $i <= $numFields; $i++) {
$fieldName = odbc_field_name($result, $i);
$row[$fieldName] = odbc_result($result, $i);
}
$rows[] = $row;
}
odbc_free_result($result);
return $rows;
}
public function execute(string $sql): bool
{
$result = odbc_exec($this->conn, $sql);
if (!$result) {
error_log("ODBC Execute Error: " . odbc_errormsg($this->conn) . " | SQL: {$sql}");
return false;
}
odbc_free_result($result);
return true;
}
public function __destruct()
{
if ($this->conn) {
odbc_close($this->conn);
}
}
}
// 使用範例 (跨平台通用)
try {
$db = new AccessDB_ODBC('MyAccessDB'); // 使用系統 DSN
// 或無 DSN 連線: odbc_connect("Driver={MDBTools};DBQ=/path/to/file.mdb;", '', '')
$rows = $db->query("SELECT title FROM dbo_dirs");
foreach ($rows as $row) {
echo htmlspecialchars($row['title']) . "
";
}
} catch (Exception $e) {
echo "錯誤: " . $e->getMessage();
}
?>
| 維度 | COM/ADO (Windows) | ODBC + MDBTools (跨平台) |
|---|---|---|
| 作業系統支援 | 僅 Windows | Linux/Windows/macOS |
| .accdb (Access 2007+) 支援 | 完美 (ACE Driver) | 有限 (MDBTools 對 accdb 支援不完整) |
| 效能 | 優 (原生驅動) | 中等 (轉換層開銷) |
| SQL 相容性 | 完整支援 Jet/ACE SQL | 部分語法不支援 (如複雜 JOIN、子查詢) |
| 部署複雜度 | 低 (Windows 內建) | 中 (需配置 ODBC 驅動) |
| 併發支援 | 較好 | 較差 (MDBTools 鎖機制簡單) |
Access 屬桌面資料庫,**不適合 Web 多併發場景**。強烈建議遷移至:
<?php
// 簡單遷移腳本
$access = new AccessDB_ODBC('MyAccessDB');
$sqlite = new PDO('sqlite:/var/www/data/migrated.db');
$sqlite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 獲取所有資料表
$tables = $access->query("SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0");
foreach ($tables as $table) {
$tableName = $table['Name'];
echo "遷移資料表: {$tableName}\n";
// 獲取結構 (簡化版)
$columns = $access->query("SELECT * FROM {$tableName} WHERE 1=0"); // 僅取結構
// 實際需解析 ODBC 欄位元資料建立 CREATE TABLE
// 遷移資料
$rows = $access->query("SELECT * FROM {$tableName}");
if ($rows) {
$placeholders = '(' . implode(',', array_fill(0, count($rows[0]), '?')) . ')';
$stmt = $sqlite->prepare("INSERT INTO {$tableName} VALUES {$placeholders}");
foreach ($rows as $row) {
$stmt->execute(array_values($row));
}
}
}
echo "遷移完成";
?>
PHP 連接 Access:Windows 環境首選 COM/ADO (效能最好、相容性最全);跨平台或 Linux 部署必須用 ODBC + MDBTools (功能受限)。**長遠規劃請務必遷移至 SQLite/MySQL/PostgreSQL**,Access 非為 Web 多用戶併發設計,持續使用將面臨鎖衝突、效能瓶頸、資料損壞風險。