24

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.

15
  • Is odbc_fetch_results a custom function? I'm not seeing it in the PHP documentation. Commented Dec 12, 2012 at 21:57
  • @GigaWatt Seems I wasn't thinking clearly. I posted the code for the odbc_fetch_results function. Commented Dec 12, 2012 at 22:04
  • 4
    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. Commented Dec 12, 2012 at 23:03
  • @GigaWatt I added the print_r($row); which printed the same result: Array ( [MEASUREMENT_UNI] => kg) Commented Dec 13, 2012 at 13:57
  • 1
    @WillC. Database is MS SQL Server. Commented Dec 19, 2012 at 14:05

3 Answers 3

14
+50

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.

Sign up to request clarification or add additional context in comments.

8 Comments

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?
You have to compile it from the sources, sadly. It's not just a field in a config file.
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
Well, reinstalling PHP should do. PHP by default has that field set to 32, so it's a bit weird that yours had 16.
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?
|
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.

1 Comment

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

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"

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.