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.

Is `odbc_fetch_results` a custom function? I'm not seeing it in the PHP documentation. — Mr. Llama, Dec 12 '12 at 21:57
@GigaWatt Seems I wasn't thinking clearly. I posted the code for the `odbc_fetch_results` function. — nmc, Dec 12 '12 at 22:04
Throw in a `print_r($row);` immediately after `$row = odbc_fetch_array($stmt);`. That should let us see if the truncation is in your code or in the `obdc_fetch_array` call. — Mr. Llama, Dec 12 '12 at 23:03
@GigaWatt I added the `print_r($row);` which printed the same result: `Array ( [MEASUREMENT_UNI] => kg)` — nmc, Dec 13 '12 at 13:57
What database management system are you using? (MySQL, Oracle, Sybase, etc) — Will C., Dec 19 '12 at 03:39
@shiplu.mokadd.im collation of the `MEASUREMENT_UNIT` column is `SQL_Latin1_General_CP1_CI_AS`. Not sure where I can find the charset info. — nmc, Dec 19 '12 at 14:07
what version of MS SQL server are you using? (7, 2000, 2005, etc) — Will C., Dec 19 '12 at 14:19
Are you able to use the [`sqlsrv`](http://php.net/book.sqlsrv) extension instead of ODBC? — Charles, Dec 20 '12 at 05:40
@Charles I just realized that sqlsrv is only available for Windows. Is that correct? My application is on a Linux server. — nmc, Dec 20 '12 at 14:46

score 14 · Answer 1 · answered Dec 19 '12 at 04:09

14

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.

answered Dec 19 '12 at 04:09

Carlos Vergara

3,592
4
31
56

Thanks, I did see that bug report but have been unable to find the file `/ext/odbc/php_odbc_includes.h` as specified. Is there another file that this config could possibly be in? – nmc Dec 19 '12 at 16:27
1

You have to compile it from the sources, sadly. It's not just a field in a config file. – Carlos Vergara Dec 19 '12 at 19:15
Ahhh, that's the key: must compile from source. I wonder if there are any other solutions to this than having to compile PHP from source – nmc Dec 20 '12 at 14:16
Well, reinstalling PHP should do. PHP by default has that field set to 32, so it's a bit weird that yours had 16. – Carlos Vergara Dec 21 '12 at 01:58
I was finally able to test this by connecting to a different database (MySQL instead of MS SQL Server) on the same server with the same php installation and **field names in the code that connects to MySQL are NOT truncated**. Which suggests to me that it isn't an issue with ODBC, correct? – nmc Jan 15 '13 at 21:15
Are you still using ODBC to connect to MySQL or are you using other drivers to do so? – Carlos Vergara Jan 16 '13 at 03:20
Still using ODBC with MySQL drivers instead of SQL Server drivers. I added more info to my question. – nmc Jan 16 '13 at 14:03
Try downloading the driver again from the Microsoft website and tell me if anything changes. – Carlos Vergara Jan 16 '13 at 19:22

score 2 · Accepted Answer · answered Mar 30 '16 at 04:56

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.

Finally got around to testing this and it seems ODBC Driver 13 does fix this problem! `$connection = odbc_connect("DRIVER={ODBC Driver 13 for SQL Server}; Server=$server; Database=$db;", $loginname, $loginpass);` — nmc, Jan 10 '18 at 14:39

score 1 · Answer 3 · answered Dec 20 '12 at 15:07

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"

PHP & SQL Server - field names truncated

3 Answers3

Linked

Related