PHP & SQL Server - field names truncated

Question

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.

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.

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.

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.

Why is the column name being truncated to 15 characters?

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

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

Answer 1

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.

Answer 2

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

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's documentation is lackluster for Linux support, so if you're running ubuntu, then here's the gist of getting it installed.

Answer 3

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

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

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