PHP &SQL Server - 字段名称被截断

2022-01-16 00:00:00 odbc php sql-server sql-server-2008-r2

下面是相关代码:

function connect(){
    // DB credentials and info defined here....
    $connection = odbc_connect("DRIVER={SQL Server Native Client 11.0}; Server=$server; Database=$db;", $loginname, $loginpass);
    return $connection;
}

function odbc_fetch_results($stmt, &$results) {
    $numrows = odbc_num_rows($stmt);
    $row = odbc_fetch_array($stmt);
    print_r($row); // Prints: Array ( [MEASUREMENT_UNI] => kg)
    if($row){
         $results = array ($row);
         while( $row = odbc_fetch_array($stmt)){
            array_push($results, $row);
         }
    }
    return $numrows;
}

$sql = "select * from measurements where ID=$id";
$stmt = executeSQL($conn,$sql);
$nrows = odbc_fetch_results($stmt, $results);
odbc_free_result($stmt);
print_r($result[0]); // Prints: Array ( [0] => Array ( [MEASUREMENT_UNI] => kg) ) 

结果应该包含一个名为 MEASUREMENT_UNIT 的列(当我执行 print_r 我可以验证时)被截断为 MEASUREMENT_UNI 这只是15 个字符.最后一个字母T被截断.

The result should contain a columnn called MEASUREMENT_UNIT which (when I do a print_r I can verify) is truncated to MEASUREMENT_UNI which is only 15 characters. The last letter T is cut off.

我还尝试使用 SQL Server 数据库上的 different 表和 different 列进行查询,以确保它没有任何奇怪的设置我正在使用的特定表或列.我验证了不同的表/列也会发生同样的事情:当我运行上面的选择查询时,列名被截断为最多 15 个字符.

I also tried a query with a different table and a different column on the SQL Server database as a test to make sure it wasn't any strange setup with the particular table or column that I'm working with. I verified the same thing occurs with a different table/column: column names are truncated to 15 characters max when I run a select query as above.

我还尝试了一个选择,它指定字段名称,如 select MEASUREMENT_UNIT from from measurement where ID=$id 而不是 select * 但这并不能解决也有问题.

I have also tried a select which specifies the field name like select MEASUREMENT_UNIT from from measurements where ID=$id instead of select * but that doesn't solve the problem either.

我在这里看到过其他类似的帖子,但它们似乎都表明我应该能够获得至少 30 个字符,而不是我看到的 15 个字符的限制.

I've seen other similar posts here about this but they all seem to indicate that I should be able to get at least 30 characters, not the 15 character limit that I'm seeing.

为什么列名被截断为 15 个字符?

Why is the column name being truncated to 15 characters?

编辑:连接到 MySQL 服务器数据库似乎没有导致同样的问题.MySQL 表中的 DB 列名没有被截断,这让我相信这不是 ODBC 插件的问题.

Edit: Connecting to a MySQL server database did not seem to result in the same problem. DB column names from the MySQL tables were NOT truncated which leads me to believe that this is not a problem with the ODBC plugin.

$connection = odbc_connect("DRIVER={MySQL};Server=$server; Database=$db;", $loginname, $loginpass);
$sql = "select * from measurements where ID=$id";
$stmt = executeSQL($conn,$sql);
$nrows = odbc_fetch_results($stmt, $results);
odbc_free_result($stmt);
print_r($result[0]); // Prints CORRECTLY: Array ( [0] => Array ( [MEASUREMENT_UNIT] => kg) )

请注意,上述两个代码部分是在同一服务器上的同一文件中使用相同的 PHP + ODBC 安装进行测试的.

Note that both of the above code sections were tested in the same file on the same server with the same PHP + ODBC installation.

推荐答案

问题肯定出在 Microsoft ODBC 驱动程序版本 11,并在 ODBC Driver 13 Preview for SQL Server.

The problem is definitely with the Microsoft ODBC drivers version 11, and are fixed in ODBC Driver 13 Preview for SQL Server.

我发现这是因为我的开发机器运行 ubuntu 14.04 并安装了驱动程序 13 预览版工作正常,但是当我部署到运行 RHEL 7 和驱动程序 11 的生产服务器时,由于列名被截断,各种破坏随之而来15 个字符.

I discovered this as my development machine running ubuntu 14.04 with the Driver 13 Preview installed works fine, but then when I deployed to our production server running RHEL 7 with the Driver 11, all kinds of havoc ensued as column names were truncated at 15 chars.

Microsoft 的文档对 Linux 的支持乏善可陈,所以如果您运行的是 ubuntu,那么这里是 gist 安装它.

Microsoft's documentation is lackluster for Linux support, so if you're running ubuntu, then here's the gist of getting it installed.

相关文章