PHP连接TimesTen ODBC驱动获取列名失败，报SQL_DESC_OCTET_LENGTH错误

问题描述 #

我使用PHP编写连接TimesTen数据库的代码，通过odbc_connect成功建立连接后，执行SELECT查询时调用odbc_prepare多次出现以下警告：

odbc_prepare(): SQLColAttribute can't handle SQL_DESC_OCTET_LENGTH: [S1C00] [TimesTen][TimesTen 18.1.4.42.0 CLIENT]Driver not capable

同时用odbc_field_name获取列名时返回空数组，查询结果的键也无法正确显示列名，需要解决该问题以正常获取列名。

原代码 #

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$dsn = 'DSN=demo;Server=demo;UID=demo;PWD=demo';

$conn = odbc_connect($dsn, '', '');

if ($conn) {
    echo "Connection successful.<br>";

    $sql = "SELECT * FROM employee";

    $stmt = odbc_prepare($conn, $sql);
    if (odbc_execute($stmt)) {
        $columns = [];
        $num_fields = odbc_num_fields($stmt);
        for ($i = 1; $i <= $num_fields; $i++) {
            $columns[] = odbc_field_name($stmt, $i);
        }

        print_r($columns);
        echo "<br>";

        while ($row = odbc_fetch_array($stmt)) {
            print_r($row);
            echo "<br>";
        }
    } else {
        echo "Query execution failed: " . odbc_errormsg($conn);
    }

    odbc_close($conn);
} else {
    echo "Connection failed: " . odbc_errormsg();
}
?>

运行输出 #

Warning: odbc_prepare(): SQLColAttribute can't handle SQL_DESC_OCTET_LENGTH: [S1C00] [TimesTen][TimesTen 18.1.4.42.0 CLIENT]Driver not capable in C:\xampp\htdocs\timesten\test_connection.php on line 20

Warning: odbc_prepare(): SQLColAttribute can't handle SQL_DESC_OCTET_LENGTH: [S1C00] [TimesTen][TimesTen 18.1.4.42.0 CLIENT]Driver not capable in C:\xampp\htdocs\timesten\test_connection.php on line 20

Warning: odbc_prepare(): SQLColAttribute can't handle SQL_DESC_OCTET_LENGTH: [S1C00] [TimesTen][TimesTen 18.1.4.42.0 CLIENT]Driver not capable in C:\xampp\htdocs\timesten\test_connection.php on line 20
Array ( [0] => [1] =>  [2] =>  )
Array ( [John Doe] => John Doe [] => 30 [] => john.doe@example.com )
Array ( [Jane Smith] => Jane Smith [] => 25 [] => jane.smith@example.com )
Array ( [Alice Johnson] => Alice Johnson [] => 28 [] => alice.johnson@example.com )
Array ( [Bob Brown] => Bob Brown [] => 35 [] => bob.brown@example.com )
Array ( [Charlie Davis] => Charlie Davis [] => 40 [] => charlie.davis@example.com )

解决方案 #

1. 替换预处理逻辑为直接执行 #

TimesTen的ODBC驱动对odbc_prepare调用中的某些属性支持不完善，改用odbc_exec直接执行SQL语句，可避免触发驱动不支持的属性调用，同时正常获取列名：

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$dsn = 'DSN=demo;Server=demo;UID=demo;PWD=demo';

$conn = odbc_connect($dsn, '', '');

if ($conn) {
    echo "Connection successful.<br>";

    // 显式指定列名，提升兼容性与可读性
    $sql = "SELECT name, age, email FROM employee";

    // 使用odbc_exec替代预处理流程
    $stmt = odbc_exec($conn, $sql);
    if ($stmt) {
        $columns = [];
        $num_fields = odbc_num_fields($stmt);
        for ($i = 1; $i <= $num_fields; $i++) {
            $columns[] = odbc_field_name($stmt, $i);
        }

        print_r($columns);
        echo "<br>";

        while ($row = odbc_fetch_array($stmt)) {
            print_r($row);
            echo "<br>";
        }
    } else {
        echo "Query execution failed: " . odbc_errormsg($conn);
    }

    odbc_close($conn);
} else {
    echo "Connection failed: " . odbc_errormsg();
}
?>

2. 手动映射列名（保留预处理场景） #

如果必须使用odbc_prepare，可以在SQL语句中显式指定列名，之后手动将结果的索引键映射为列名：

// 定义列名数组
$columnNames = ['name', 'age', 'email'];
// 执行查询后，遍历结果转换键名
while ($row = odbc_fetch_row($stmt)) {
    $mappedRow = [];
    foreach ($columnNames as $index => $name) {
        $mappedRow[$name] = odbc_result($stmt, $index + 1);
    }
    print_r($mappedRow);
    echo "<br>";
}

3. 升级TimesTen驱动 #

考虑升级到更高版本的TimesTen客户端驱动，新版本通常会修复ODBC兼容性问题，解决SQL_DESC_OCTET_LENGTH相关的警告。

4. 临时抑制警告（不推荐） #

如果警告不影响功能，可在odbc_prepare前添加@符号临时抑制警告，但这只是规避手段，不解决根本问题：

$stmt = @odbc_prepare($conn, $sql);

内容的提问来源于stack exchange，提问作者Md. Atikur Rahman