PHP和SQL Server -字段名称被截断。

Here is the relevant code:

以下是相关代码:

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) )

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.

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

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.

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

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.

我还尝试了一个select，它指定了字段名，比如从ID=$ ID(而不是select *)的度量中选择度量值，但这也不能解决问题。

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.

我在这里也看到过类似的文章，但它们都表明我应该至少能写30个字符，而不是我看到的15个字符的限制。

Why is the column name being truncated to 15 characters?

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

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.

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

$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) )

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

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

3 个解决方案

#1

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

问题肯定出在Microsoft ODBC驱动程序版本11上，并且在SQL Server的ODBC驱动程序13预览中得到了解决。

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.

我发现这一点是因为我的开发机器在安装了驱动程序13的情况下运行ubuntu 14.04运行得很好，但是当我部署到使用驱动程序11运行RHEL 7的生产服务器上时，当列名在15个字符处被截断时，各种各样的破坏接踵而至。

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

微软的文档在Linux支持方面乏善可陈，所以如果你正在运行ubuntu，下面是安装它的要点。

#2

Apparently the problem is with ODBC. There's a bug in PHP where column names are truncated at 31 characters, and the only way to fix this is recompiling PHP, changing the array length of name in /ext/odbc/php_odbc_includes.h (usually 32 but apparently it was 16 in your case), but this is not proven to be either safe or unsafe. Go here and here to see more information about this.

显然，问题出在ODBC上。PHP中有一个错误，列名被截断为31个字符，解决这个问题的唯一方法是重新编译PHP，更改/ext/odbc/php_odbc_include中的名称的数组长度。h(通常是32，但显然是16)，但这并不能证明是安全的或不安全的。到这里和这里看看更多的信息。

#3

The permanent solution is to recompile your PHP as suggested in PHP bug threads or try updating to newer PHP version.

永久的解决方案是按照PHP bug线程中的建议重新编译PHP，或者尝试更新到更新的PHP版本。

You can work around the problem by selectively renaming columns in your select to shorter ones:

您可以通过选择性地将select中的列重命名为更短的列来解决这个问题:

$sql = "SELECT measurement_unit AS measure_unit, * FROM measurements WHERE ID=$id";
// now in your array you will have new index called "measure_unit"

#1