PHP ODBC
Reaching non-MySQL sources via ODBC: DSN setup, odbc_* functions, and PDO_ODBC.
Key PointsODBC is a database-agnostic middle layer; the DSN holds driver, address and credentials.
When to use ODBC
- SQL Server, Oracle, DB2, Access —— when PDO lacks a driver
- Vendor data sources —— ERP and accounting systems often expose ODBC
- Cross-database joins —— some drivers support it
Prefer native drivers (pdo_sqlsrv, oci8). ODBC is the fallback when nothing better exists — usually slower and weaker on multibyte.
Configuring a DSN
Two flavours: a system DSN in /etc/odbc.ini or the Windows registry, and DSN-less connection strings.
INI
; /etc/odbc.ini
[mydb]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libsqlite3odbc.so
Database = /var/data/app.dbPHP
<?php
// 有 DSN
$conn = odbc_connect("mydb", "user", "pass");
// 无 DSN：直接把驱动和参数拼进连接串
$dsnless = "Driver={SQL Server};Server=127.0.0.1;Database=demo;";
// $conn = odbc_connect($dsnless, "sa", "secret");
echo $conn === false ? "连接失败: " . odbc_errormsg() : "连接成功", "\n";Output
连接成功Running queries
PHP
<?php
$conn = odbc_connect("mydb", "user", "pass");
if ($conn === false) {
exit("连接失败: " . odbc_errormsg());
}
$res = odbc_exec($conn, "SELECT id, name FROM users ORDER BY id");
if ($res === false) {
exit("查询失败: " . odbc_errormsg($conn));
}
while ($row = odbc_fetch_array($res)) {
echo $row["id"] . " " . $row["name"] . "\n";
}
odbc_free_result($res);
odbc_close($conn);Output
1 Tom
2 Amy
3 BobKey case depends on the driver — SQL Server may return
NAME. Normalise with array_change_key_case or fetch by index.Parameterised queries
PHP
<?php
$conn = odbc_connect("mydb", "user", "pass");
$sql = "SELECT name FROM users WHERE id = ?";
$prep = odbc_prepare($conn, $sql);
if ($prep === false) {
exit("预处理失败: " . odbc_errormsg($conn));
}
odbc_execute($prep, [1]);
while ($row = odbc_fetch_array($prep)) {
echo $row["name"] . "\n";
}Output
Tomodbc_prepare with odbc_execute supports positional ?. **Never concatenate into odbc_exec** — as dangerous as the old mysql_query.
Using PDO_ODBC
Prefer PDO's interface? The pdo_odbc driver behaves like any other.
PHP
<?php
$pdo = new PDO("odbc:mydb", "user", "pass", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute([2]);
echo $stmt->fetchColumn(), "\n";Output
AmyCommon issues
| Symptom | Cause | Fix |
|---|---|---|
| [IM002] Data source name not found | DSN 没配或名字拼错 | 查 odbcinst -q -d、odbc.ini 路径 |
| [IM003] Specified driver could not be loaded | 驱动 .so 路径错或缺依赖 | ldd 查缺的库 |
| [28000] Login failed | 认证失败 | 查账号、认证模式（SQL / Windows） |
| 中文乱码 | 客户端字符集与库不一致 | 连接串里指定字符集，或 SET NAMES |
odbc_exec 返回 false 但无错误 | 没开 odbc.defaultlrl 或超时 | odbc_errormsg() 看详情 |
Linux has system and per-user
odbc.ini with the user one winning. When a DSN seems missing, check the paths reported by odbcinst -j.什么时候用 ODBC
- 连 SQL Server / Oracle / DB2 / Access —— PDO 没有对应驱动时
- 连商业系统自带的数据源 —— 财务软件、ERP 常提供 ODBC 接口
- 跨库联合查询 —— 部分驱动支持
能直连就用原生驱动（pdo_sqlsrv、oci8），ODBC 是没有更好选择时的通用退路，性能和多字节支持通常弱一些。
配置 DSN
DSN 分两种：系统 DSN（写在 /etc/odbc.ini 或 Windows 注册表）和「无 DSN 连接」（把参数直接写在连接串里）。
INI
; /etc/odbc.ini
[mydb]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libsqlite3odbc.so
Database = /var/data/app.dbPHP
<?php
// 有 DSN
$conn = odbc_connect("mydb", "user", "pass");
// 无 DSN：直接把驱动和参数拼进连接串
$dsnless = "Driver={SQL Server};Server=127.0.0.1;Database=demo;";
// $conn = odbc_connect($dsnless, "sa", "secret");
echo $conn === false ? "连接失败: " . odbc_errormsg() : "连接成功", "\n";Output
连接成功执行查询
PHP
<?php
$conn = odbc_connect("mydb", "user", "pass");
if ($conn === false) {
exit("连接失败: " . odbc_errormsg());
}
$res = odbc_exec($conn, "SELECT id, name FROM users ORDER BY id");
if ($res === false) {
exit("查询失败: " . odbc_errormsg($conn));
}
while ($row = odbc_fetch_array($res)) {
echo $row["id"] . " " . $row["name"] . "\n";
}
odbc_free_result($res);
odbc_close($conn);Output
1 Tom
2 Amy
3 Bobodbc_fetch_array 的键大小写取决于驱动。SQL Server 返回的列名可能全大写，$row["name"] 取不到而 $row["NAME"] 可以。用 odbc_fetch_array 前先 array_change_key_case，或者统一用 odbc_result($res, 2) 按序号取。参数化查询
PHP
<?php
$conn = odbc_connect("mydb", "user", "pass");
$sql = "SELECT name FROM users WHERE id = ?";
$prep = odbc_prepare($conn, $sql);
if ($prep === false) {
exit("预处理失败: " . odbc_errormsg($conn));
}
odbc_execute($prep, [1]);
while ($row = odbc_fetch_array($prep)) {
echo $row["name"] . "\n";
}Output
Tomodbc_prepare + odbc_execute 支持 ? 位置占位符。**不要用 odbc_exec 拼字符串**——那和 mysql_query 时代一样危险。
用 PDO_ODBC
更喜欢 PDO 的接口就用 pdo_odbc 驱动，API 和其它 PDO 驱动一致。
PHP
<?php
$pdo = new PDO("odbc:mydb", "user", "pass", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute([2]);
echo $stmt->fetchColumn(), "\n";Output
Amy常见问题
| 现象 | 原因 | 解决 |
|---|---|---|
| [IM002] Data source name not found | DSN 没配或名字拼错 | 查 odbcinst -q -d、odbc.ini 路径 |
| [IM003] Specified driver could not be loaded | 驱动 .so 路径错或缺依赖 | ldd 查缺的库 |
| [28000] Login failed | 认证失败 | 查账号、认证模式（SQL / Windows） |
| 中文乱码 | 客户端字符集与库不一致 | 连接串里指定字符集，或 SET NAMES |
odbc_exec 返回 false 但无错误 | 没开 odbc.defaultlrl 或超时 | odbc_errormsg() 看详情 |
Linux 上
odbc.ini 有系统级（/etc/odbc.ini）和用户级（~/.odbc.ini）两份，用户级优先。排查「明明配了却找不到」先看 odbcinst -j 报的路径。